12 Pro PivotTable Formatting Tricks = No more UGLY PivotTables!

[@TheIvalen]

Pivot tables are my life. I'll take any of your design tips any day! I usually find myself using the pivot table for data analysis and then copying pasting the data to a static table so I can make it pretty/ready for consumption. Ok, I will try from now on just to use the Pivot table only. The reason for the static table is I often have to insert rows of 'dates' with no additional data to provide a continuous timeline and the Pivot table doesn't let me do that (at least I don't think it can) without modifying the raw input data.

[@MyOnlineTrainingHub]

Glad this video was helpful. This tutorial explains how to make PivotTables display missing dates: www.myonlinetraininghub.com/display-missing-dates-in-excel-pivottables

[@tristangeoffroy9286]

Despite decades of pivot table practice, you just taught me fews things (template formating) I always looked for, in a cristal clear manner. Very professional. Thank you very much.

[@MyOnlineTrainingHub]

Great to hear!

[@marcovirtual]

Yes! I work with Excel pretty much everyday, but Mynda's videos never fail to teach me new things. I love it!

[@koitaki]

What Tristan said...some really creative stuff here, thanks Myanda!

[@Adam_K_W]

This is one of the best videos you've done to date! Thank you so much for consolidating all of this into a simple, easy to navigate, useful set of tips and tricks! Pivot tables are so widely used these days, and even though they are sort of being replaced by Power BI, they still have their place. Awesome.

[@MyOnlineTrainingHub]

So pleased you liked it 🙏

[@mohammedelsakally540]

Thank you Mynda for your always valuable contributions to enhance the Excel skills which we are always awaiting from you especially that you are representing the content of each video by an easy and professional way ... Thank you and we are supporting you ever ...

[@MyOnlineTrainingHub]

Thank you so much for your support 🙏😊

[@krishna6296]

Perfect tutorial, straight to the point. Great work. Thank you!

[@MyOnlineTrainingHub]

So glad you liked it! 🙏

[@ashrafkamal2011]

please we need more videos related to financial statements and finance trick....

[@MyOnlineTrainingHub]

I'll keep it in mind 😊

[@ivanbork4175]

Thank you Mynda, always very inspirational to see your tutorials, sometime almost lifechanging. In this case I have done something similar, and have created my own PivotTable styles, and then saved the workbook as templates .xltx, including introduction sheet and print layout for some sheets added head and footer, and links to a couple of lists holding essential number /- names for categories. All in all it saves time end ensure a consistent in the way the different jobs have

[@MyOnlineTrainingHub]

Great idea, Ivan!

[@surkewrasoul4711]

Thanks luv , Gotta get mi blauuwwdy job first.

[@MyOnlineTrainingHub]

Good luck.

[@abdelouahedelmaazouzi6102]

Perfect format! Once done you can present directly to the Management without any changes or modifications.

[@MyOnlineTrainingHub]

Indeed!

[@silversolver7809]

Excellent! I've used pivots 'forever' but still learned a couple of things :)

[@MyOnlineTrainingHub]

Great to hear it was worth your time to watch 🙏

[@JJ_TheGreat]

10:07 Oh, wow! I didn't even think about re-naming the "(blank)" to "" (nothing). I always went into the Pivot Table options in order to hide blanks.

[@MyOnlineTrainingHub]

Typing over the blank in the cell is just a shortcut to editing how the PivotTable handles blanks in the Options dialog box.

[@Mark-Bench]

one addition. The only way to transfer a style from one excel file to another is by copying a pivot table with it's style into the other one and then delete it. the style formatting will stay and is now available in the sheet. also works for slicer styles created for Pivot tables.

[@MyOnlineTrainingHub]

I assume you mean PivotTable styles, in which case, great tip. Thanks for sharing.

[@Mark-Bench]

@@MyOnlineTrainingHub I just checked, doesn't work anymore, only works with slicer style - disappointed now 😞

[@warrennalty6599]

To change the headings, I press ctrl-h replace, type Sum of (no space after of) and leave replace with blank. This removes Sum of leaving leading space. Great if there are many headings.

[@MyOnlineTrainingHub]

Great tip 🙏

[@therealjeffg]

Awesome Mynda, gold nuggets everywhere in that one. Thank you.

[@MyOnlineTrainingHub]

Great to hear!

[@call-me-potato.]

Kindly put 50 advertisement , after every 2 second of tutorial their should be advertisement so we can waste a lot of time and then do not find solution .

[@RobinBond-j2l]

Oh and.... I have learned sooo much from watching your videos! In case you've had your doubts... your time has been well spent!!!

[@MyOnlineTrainingHub]

That's awesome to hear! 🙏😊

[@geovanrocha2822]

Hi Mynda, Thank you very much for sharing this. It would be nice if you make another video about pivot chart formatting tricks. I always struggle with them because they change format when I use slicers, for example.

[@MyOnlineTrainingHub]

With Pivot Charts, you must clear all filters, then apply formatting. HTH.

[@luisalonso3018]

Excellent video, thank you! Suggestion: can you do a video teaching us tips on how to format the slicers for pivot tables. That would be great! Thank you once again.

[@MyOnlineTrainingHub]

Already done one here: kzbin.info/www/bejne/gXa9q3qrZrJ8j5Y

[@miqueldespuig]

It would be sooooo useful to be able to save a Pivot Table Style to be used on all excel files. I usually populate the PnL from a PowerBI Dataset and recreating the style every time is painful

[@MyOnlineTrainingHub]

You can save the style in your default workbook and it will be there in all new files: www.myonlinetraininghub.com/customize-default-excel-workbook

[@markanthony7078]

Thank you Mynda, you have made my project i had so easy to present ..thanks for all the free tutorials you do here - I don't take it lightly as a lot of work gone in to prepare for free....I still intend to enroll on one of your paid programs in coming weeks! Thank you and see you on the other side very soon. Stay blessed!!

[@MyOnlineTrainingHub]

Wonderful to hear you've found my tutorials helpful 🙏😊 see you in a course someday where I can share even more.

[@sliceofsparta8985]

My biggest issue is formatting row label cells after grouping them, say if I want to show the count of rows of my data with a certain column value between 0-10%, 10-20%, and so on. Changing the cell format becomes useless after grouping; I can't get my row labels to display as percentages. They all say 0.1-0.2 instead of 10-20%.

[@MyOnlineTrainingHub]

Yeah, not much you can do about that other than maybe adding these labels outside the PivotTable and then hiding the PivotTable column.

[@NancyMancarious]

I now know that I've mastered the pivot tables when all of these tips are not new to me. Thank you - always helpful.

[@MyOnlineTrainingHub]

Great to hear!

[@walterstevens8676]

This is very useful. I'm trying to alternate row colours, but clicking on 'row banding' isn't working, despite me clearing any existing formatting I've tried formatting the source table that the pivot table is pulled from, but that doesn't come through. Any other ideas?

[@MyOnlineTrainingHub]

When you choose the style that doesn't have banding, you can't turn it off and on. You'd need to first apply a style that has it in order for you to then turn it off or on.

[@eleburciaga6769]

Hello, can you do a center of gravity tutorial to select an optimal location for an office?

[@mahathmasadineni2884]

Hi Mynda, If i expand the Gross Profit . The First line design is changing it has 2 bottom rows and one top row but ideally it should need to have no boarders above and below like Revenu right.. Please let me know how can fix it if we have the solution... Ideally it need to apply the boarders dynamically..

[@MyOnlineTrainingHub]

The Gross profit row is not supposed to be expanded.

[@al3xj]

Hi Mynda, thanks again - and a question - if you don't use pivot themes but format by like elements as in minutes: 4:46, how to easily bring same formatting to other tables? Or must it be done manually in your steps above? Or for a fixed style, would you create a theme, so it can be applied as needed auto? Thanks in advance - ah answered in the end! :) thanks Mynda

[@MyOnlineTrainingHub]

Glad you enjoyed it 😁

[@RobinBond-j2l]

I'm trying to use a pivot table to create an invoice and trying to add a blank row between items. But it adds a blank row after every single line. I see that your pivot table report layout looks to be either Compact or Outline. I have several dimensions, 5 of them. It looks like you have 2. I'd prefer Tabular so I have the field labels.

[@MyOnlineTrainingHub]

My report is in compact form 😊

[@walidgamal9309]

Hi Ma'am, I see in this video 2 pivot tables for Profit and Loss, and Budget variance, would you please to refere me to any of your video on how to form pivot tables from raw data to build profit and loss and budget variance report? thank you so much

[@MyOnlineTrainingHub]

Sure, you can see it here: Easy Excel PivotTable Profit & Loss Statements kzbin.info/www/bejne/a5y4goaud7ufkJo

[@anubhav19888]

tks. it has good information. whenever i click for 'subtotal'..sometime subtotal row is shaded and sometime not (i think when i have more columns). is there any method to apply shade to subtotal row so that it is distinctly visible

[@MyOnlineTrainingHub]

Go to the PivotTable Design tab on the ribbon and choose a style that suits, if there isn't one, then you can create a custom style.

[@joseferrato]

Mynda, the filter format go back to the standard when using slicers... after set to show top 10 items in the charts in my dashboard, when selecting a different year or different topic using slicers, it works fine, but when clicking to cancel the selections in the slicers, the pivot table reset also, showing all items, instead keep the top 10. Any tip on how to freeze the top 10 selection in the pivot table and don't get the filter reseted?

[@MyOnlineTrainingHub]

Clicking cancel in the Slicer removes the filters. Try reselecting all items in the slicer instead.

[@ChessmasterSunnyKow888]

Hi Mynda You are really the best.

[@MyOnlineTrainingHub]

Aw, thanks so much!

[@hectorbello2104]

Hi... is there an easy way to display % on the value field. For instance I got pivot table sections each with an input value and output value columns, and I want to include on the same output column the calculated % of it, but I do not want it to be an additional column. Perhaps in the conditional formatting instead of an icon, to show %?

[@MyOnlineTrainingHub]

Not sure what you mean. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@ConnerZee]

I thought I i knew all there was about pivot tables and im no novice haha! Im definitely going to implement a few if these tips ASAP

[@MyOnlineTrainingHub]

Awesome to hear 😁

[@kamran2265]

I can share with you my ledger sheet if needed.

[@flightforge30]

thats another level of pivot table knowledge..thanks a alot for sharing it to us

[@MyOnlineTrainingHub]

My pleasure 😊

[@florentgirard8849]

Hi, how can I format columns Header with auto carriage return ? and make it stick when the pivot is refreshed ?

[@MyOnlineTrainingHub]

Pretty sure you can’t do this with PivotTables. You could try formatting the column header in your source data with a carriage return (ALT+ENTER) and then apply the wrap text to the corresponding field in your PivotTable to see if it recognises the carriage return. You’ll also need to set the ‘Preserve formatting on refresh’ option in the PivotTable. Hope that works.

[@jamescopland6175]

I use Excel on a Mac and when I add a blank row at the subtotal level, it adds a blank row after every single row :(

[@MyOnlineTrainingHub]

That's a shame. Mac does have some quirks, unfortunately.

[@the_legend3185]

Great video, if you were to add another field under your accounts and add a blank rows it adds rows between each item. Do you know of any way around this?

[@MyOnlineTrainingHub]

Glad you liked it. Blank rows are pretty limited in options, they simply get inserted after each item based on the last item in the row labels area.

[@pkup10]

Being able to use pivot tables in vlookups will be a game changer, that has always annoyed me.

[@MyOnlineTrainingHub]

Not sure why you think you can't VLOOKUP a PivotTable.

[@budiwijoyo8388]

This video are nailed, but i wondering how you made that financial statement template ?

[@MyOnlineTrainingHub]

Thanks so much. See this video for creating the P&L: kzbin.info/www/bejne/a5y4goaud7ufkJo

[@mickaeltaieb6161]

Great video!

[@MyOnlineTrainingHub]

Thank you!

[@tanweerabbasabbas649]

mynda howdid you get the net profit line its not in the original data.. calculated field? ?

[@MyOnlineTrainingHub]

Yes. You can see how I built the P&L here: kzbin.info/www/bejne/a5y4goaud7ufkJo

[@goingvenus5603]

THANK YOU! I've been an Excel trainer for over 30 years and using PivotTables since they first came out. I never did learn that you can set PivotTable defaults in the Options dialog box. I"m ecstatic because it never made sense to me that we had to change the PT layout every. single. time. to a specific option. Amazing!

[@MyOnlineTrainingHub]

Glad you can make use of it 😊

[@kevinbush4300]

Brilliant… pivot tables play such a big part in my stuff, but they ARE ugly. This will solve that. I have to say, you and Leila have given me so much finesse in my workbooks. Thanks Mynda

[@MyOnlineTrainingHub]

Wonderful to hear, Kevin!

[@tun-tunninc.6492]

Sum of variance? Is that a field created in the original data?

[@MyOnlineTrainingHub]

No, it's a calculated field. Steps are in this video: kzbin.info/www/bejne/a5y4goaud7ufkJo

[@JJ_TheGreat]

7:20 Could you do something similar with Custom Formatting (I know you can, but does it work with Pivot Tables)? Thanks.

[@MyOnlineTrainingHub]

Yes, for the numeric value fields.

[@FutureCommentary1]

I had noted this video and woke up super early today to watch it. Everybody is all power pivot and pivot tables from data models today when really sometimes a regular old pivot table does the job. They look so ugly. I have been using Excel forever. I don't know why I never really explored the formatting options. I usually copy the data from the table and do the formatting elsewhere (smh at myself lol). Thanks for this vid.

[@MyOnlineTrainingHub]

Awesome to hear you'll be able to make use of these techniques!

[@jeannekoh9544]

Just subscribed! Thanks for these tips!😊

[@MyOnlineTrainingHub]

Awesome 🙏 welcome!

[@NixonBranch]

Побольше таких видео, хотя от куда взять столько времени

[@MyOnlineTrainingHub]

Glad you like them 😊

[@TheTinaMarbury]

You are the BEST excel teacher EVER!😁

[@MyOnlineTrainingHub]

Aw, thanks so much 🙏😊

[@coolzami1]

Hi Mynda, Thanks for the tutorial. Can we create calculated items for Gross Profit %? Also can we lock expand and collapse button as it is expanding and collapsing while clicking on calculated item?

[@MyOnlineTrainingHub]

Please see the Profit and Loss video here: kzbin.info/www/bejne/a5y4goaud7ufkJo You can turn off the expand & collapse buttons, as shown in the video.

[@marcolevasquez]

Hy Mynda, great video. This was very helpful for me; saving a bunch of time. Thanks!

[@MyOnlineTrainingHub]

Awesome to hear 🙏

[@ynotedaw6960]

Thank you! I did not know that pivot tables could have custom formatting. This is very helpful.

[@MyOnlineTrainingHub]

Glad I could help!

[@JJ_TheGreat]

Also, thanks for showing how to configure the default Pivot Table layout. I didn't know about that, either.

[@MyOnlineTrainingHub]

So pleased you found so many nuggets in this video 😊

[@SybedeJong]

Hi Mynda, Thanks for the great video. I was wondering why you mentioned having all row labels on for lookup purposes, while you can also use the getpivotdata function. I see there may be situations in which one is preferred over the other. Maybe a nice one for another video some day?

[@MyOnlineTrainingHub]

Thank you! GETPIVOTDATA can perform a lookup of sorts, but it's not as user friendly as XLOOKUP and for that you need row labels repeated. I love GETPIVOTDATA too. I did a video on it here: kzbin.info/www/bejne/gnXJkIKIo7h2q5Y

[@whazee]

I had no idea that you could just sort the pivot table by overtyping the name of the row! (Mind blown!) Hiding (blank) with a space is also a handy tip!

[@MyOnlineTrainingHub]

Glad you found something useful 😊

[@HenricAndersen]

I feel much like a pivot table master, but newertheless I learned a few new things. Thank you very much Mynda 🙏

[@MyOnlineTrainingHub]

I'm so glad it was worth your time to watch!

[@NewYears1978]

The number of times I have had a pivot table that I had to go and convert to just a normal data table and try to move over to the left the main field.... I had no clue you could repeat the labels and also TAB format which does what I was doing manually already. Wow. Great video =D

[@MyOnlineTrainingHub]

So pleased you can save some time with these techniques 😊

[@humansizedaperture]

Exactly. Same!!

[@rexbk]

Thank you, Minda. Q: Do you have a video that explain how to do pivot table to show petty cash in hand on any selected date?

[@MyOnlineTrainingHub]

I don't have anything that specifically covers that scenario, sorry.

[@rexbk]

@@MyOnlineTrainingHub Thanks

[@AZGATOR2002]

Agreed, the content is of high value. But do you produce your own videos? A reall well polished product, audio, transitions, all of it. Thank you!

[@MyOnlineTrainingHub]

Thanks so much! Yes, I record, edit and produce my own videos 😊

[@TheAndresOpeth]

AMAZING! Thanks a lot for everything!🤗

[@MyOnlineTrainingHub]

Glad you liked it! 🙏😊

[@stockshow7363]

How to link excel cell dynamically with pivot table by a formula, to be written outside the pivot table

[@MyOnlineTrainingHub]

Not sure what you mean exactly. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@stockshow7363]

@@MyOnlineTrainingHub I simply want to know how to write a slicer formula in excel cell, if you can make a video on it

[@sachinkishore4115]

Thank you so much. its very useful tricks

[@MyOnlineTrainingHub]

Awesome to hear!

[@ARTHURYUNZAL]

thank you, much appreciated :)

[@MyOnlineTrainingHub]

You're welcome!

[@IsAllAboutHealth]

Thank you! Love your videos. Great explanation with great tips!

[@MyOnlineTrainingHub]

Thanks so much. Glad you like them!

[@Earth-360]

You are a gem

[@MyOnlineTrainingHub]

Thank you!

[@stanTrX]

Thank you for these useful tips. Is it possible to save the final result as a custom pivot format style? Edit: I think you have already answered at 10:40 😅

[@MyOnlineTrainingHub]

Glad you found them helpful!

[@sjdyt]

I use pivot tables a lot but I still learned a couple of things here which I hadn’t really thought about before. I’ll be amending my defaults after I press post!

[@MyOnlineTrainingHub]

Awesome to hear 😊

[@savvaspierides7260]

Do you have any vendor list created on Excel?

[@MyOnlineTrainingHub]

No, sorry.

[@billyjayamunga]

Exactly what I was looking for. Brilliant as always. Thanks Mynda!

[@MyOnlineTrainingHub]

You're so welcome! 😊🙏

[@JJ_TheGreat]

5:07 Wow! That is brilliant 👏! I never knew you could do that! 😀

[@MyOnlineTrainingHub]

It's the little things that count 😊

[@txreal2]

Forgot all about Default PivotTable Layout. Thanks!

[@MyOnlineTrainingHub]

Glad it was worth your time to watch 😊

[@vishwanath1810]

Learnt so many new things despite using excel for years now!! Thanks again 🤝

[@MyOnlineTrainingHub]

Great to hear!

[@vanbap5638]

Great. Thank you very much! I applied it to my report.

[@MyOnlineTrainingHub]

Awesome to hear!

[@ahmedshalaby9343]

Greetings from Egypt , great and very simple video thank you

[@MyOnlineTrainingHub]

Thanks for watching!

[@kamran2265]

I need running balance of a ledger like bank statement

[@MyOnlineTrainingHub]

If you're stuck, you can post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@raihansafa5514]

Thank you so much, really helpful

[@MyOnlineTrainingHub]

Glad it helped! 😊🙏

[@shrekikiki]

This is great! But how can i save this style so i can use it for different documents and do not need to recreate it again? I tried to open new document , but custom style i've created wasn't available

[@MyOnlineTrainingHub]

You can add it to your default workbook: www.myonlinetraininghub.com/customize-default-excel-workbook

[@SeaOfMadness]

The tip of adding the space is genius - Can't believe I didn't think of that before!

[@MyOnlineTrainingHub]

So pleased you can make use of it 😊

[@internet_wanderer8316]

Thank you so much. Very useful info

[@MyOnlineTrainingHub]

Great to hear!

[@2bczar4u]

Wow, quite a few little tricks I did not know. Thank you once again!

[@MyOnlineTrainingHub]

Great to hear 😊

[@MuhammadIrfan-wr8zo]

Pronounce extreme convex and sweet accent and perfection.. Outstanding.

[@MyOnlineTrainingHub]

Thank you!

[@humayungul2120]

Is there a separate video about how to get gross profit?

[@MyOnlineTrainingHub]

Yes, the original video on how to build a PivotTable Profit and Loss report is here: kzbin.info/www/bejne/a5y4goaud7ufkJo

[@AmjadKhan-ng2xk]

good job, nice way of explaination

[@MyOnlineTrainingHub]

Thanks so much!

[@kudifilipe9875]

Thanks 4 sharing, and level up my Excel skills...

[@MyOnlineTrainingHub]

Great to hear!

[@rajeshmajumdar4999]

Superb Very Useful. Thank You so much!

[@MyOnlineTrainingHub]

So nice of you 🙏

[@donaldknowles9622]

Your bottom double bottom border seems much thicker than mine, is that something special you did to get the effect.

[@MyOnlineTrainingHub]

No, it's just the default. It might look thicker because I'm zoomed in.

[@richaldaoud8416]

Thank you, very helpful video. Short and nice

[@MyOnlineTrainingHub]

So glad you liked it

[@MegaTrickykid]

Simply great! Thanks for this outstanding tutorial!

[@MyOnlineTrainingHub]

🙏 Glad you liked it!

[@mamtabhat5221]

You are the best...Many many thanks

[@MyOnlineTrainingHub]

So nice of you 🙏😊

[@TheNeilsolaris]

Thanks for the tutorial! Can I ask, in the first place, in order to be able to separate Cost of Goods Sold and Expenses (both with separate headings), did you have a separate column in your data source, where you could indicate which category it belonged? If not, how do I provide separate headings in a single pivot table, like you've done? Thanks.

[@MyOnlineTrainingHub]

You can see the step by step tutorial to create the Profit & Loss and get the example file here: kzbin.info/www/bejne/a5y4goaud7ufkJo

[@TheNeilsolaris]

Brilliant, thanks a lot! I can see straight away on that video that you do indeed have a separate column for Cost of Goods Sold, Expenses etc.@@MyOnlineTrainingHub

[@Koel0202]

This is genius! Thanks, Mynda for all the tricks!

[@MyOnlineTrainingHub]

My pleasure!

[@shadrackawunyo7786]

Very well showcased Mynda. I love the presentation

[@MyOnlineTrainingHub]

Thank you so much 🤗

[@saidechchababe9938]

Thanks a lot, Very useful

[@MyOnlineTrainingHub]

Glad to hear that 😊

[@StanEby1]

Wow! Thank you, Your Royal Awesomeness!

[@MyOnlineTrainingHub]

😁glad you liked it!

[@vijayarjunwadkar]

Thank you Mynda for yet another practical and useful video for the most commonly used but often confused; Pivot Tables. I got to know many new things. Keep up the great work!

[@MyOnlineTrainingHub]

Thanks so much, Vijay!

[@brianxyz]

The sorting tip at the beginning is amazing. Had no idea you could do that!

[@MyOnlineTrainingHub]

Glad it was helpful!

[@karennguyen7083]

Great video as usual. Thank u so much, Mynda 🤩

[@MyOnlineTrainingHub]

Thanks for watching!

[@AdverJohnson]

Could you show the data source for this PnL table?

[@MyOnlineTrainingHub]

Sure, you can get it and the video tutorial here: www.myonlinetraininghub.com/excel-pivottable-profit-and-loss

[@AdverJohnson]

@@MyOnlineTrainingHub perfect. Now imagine 10+ PnLs (budgets submitted by affilates) in 10 different files. Some fill it correctly, some uncompleted. Do you have an idea how to bring them into 1 file with PnL, separate multi dimensional sales aalysis (comming from those files ) by period by product family.. with waterfall (bridge) charts like in 2023 actual sales are 123 and in 2026 will be 234, and all reasons in between how are they achieved...