6 Massively time saving Pivot Table tricks (Don't miss #4 & #6) ⏰

Pivot Tables are very useful. I have blogged and made videos about them many times already. But in this video, let me share some of my most favourite time saving tips.
For more on this video, visit - chandoo.org/wp/pivot-table-ti...
0:00 - Intro
0:51 - Double click to expand details of Pivot
1:37 - Format quickly with this right click option
2:20 - Include filtered items in subtotals
4:05 - Consistent axis in Pivot Charts
5:08 - Repeat item labels in tabular layout
5:55 - Multiple pages with Report filters
More videos on Pivot Tables:
Introduction to Pivot Tables - • Pivot Tables in Excel ...
Distinct count in Pivot Tables - • Easy way to get "Disti...
Link slicers to more than one pivot - • Easily link Slicers to...
Pivot Tables when you have lots of data - • How to make pivot tabl...
Advanced Pivot Table Tricks - Play list - • Advanced Pivot Table T...
More articles on Pivot Tables:
Introduction & 101 - chandoo.org/wp/excel-pivot-ta...
Advanced Tricks - chandoo.org/wp/advanced-pivot...
Working with data model & relationships - chandoo.org/wp/introduction-t...
#PivotTables #ExcelTips #Productivity

[@JonathanExcels]

setting your default pivot table options is also a time saver. File→Options→Data→Data Options→Edit Default Layout (start from a pivot table that is already setup the way you would like by default) also, Alt+F5 shortcut for refresh

[@chandoo_]

That is a good one about "Default layout". I might make a video about time saving defaults in Excel. Upvote Jonathan's comment if you want to see such video.

[@excelgazialimuhiddinhacibekir]

Doesn't exist in Excel 2013, though.

[@terryhearst4825]

Do you know of a way to automatically format numbers in a Pivot Table as well?

[@narasimhama3240]

Thank you so much Jonathan. As you said its a time saver & Excellent

[@TalkingTexts1]

Your haircut is as awesome as your Pivot table skills. Thanks for all the awesome videos.

[@lottedotti]

Thank you for that last tip! Massive time saver :)

[@chandoo_]

You bet!

[@omar_5352]

Awesome.. You just gave me a solution to a problem my team is facing with a pivot report. I am going to suggest it in today's meeting! Handy tip from me, with pivoting, you can have the option to do (distinct) count. But first you need to add your table to the data model for the option to be available.

[@satyapanthamsrinivas]

3 and 4 tips I've learned from this video.. Thank you.

[@jesudasig3088]

Whenever I watch your various sessions, I get to learn lot of new features and tricks. Thank you very much 👏👏👏

[@muhammadessa9694]

You are doing fantastic job.

[@pektuspektus612]

I always got something new and useful. Thanks Purna!

[@chandoo_]

Glad to hear that!

[@Somnath_Explorer_01]

Very helpful. Thanks, Chandoo for sharing such tips 👍👍

[@nonoobott8602]

Wow. So brilliant. Your videos are amazing. Thanks for sharing

[@chandoo_]

Glad you like them!

[@sshree]

"Repeating item labels" tip is a great help. 👍

[@chandoo_]

Absolutely!

[@amitjha52]

Tha last trick realy awesom time saver, Thanks chandoo ji....

[@excelworx8712]

Hi Chandoo. The last tip was just awesome! And an awesome haircut too!

[@chandoo_]

Thanks Rajan... :)

[@ch0c0holic]

Thanks a lot. No 6 is my favorite tip.

[@chandoo_]

It is my fav too

[@mymatemartin]

Great tips Chandoo.

[@pranitraj7701]

Truly Amazing Video. And needless to say, the last one was LIT **

[@chandoo_]

Glad you liked it!!

[@papachoudhary5482]

Thanks! Sir, looking Awesome after haircut.

[@sahilsinghal9472]

Loved the last trick. Thanks Chandoo

[@chandoo_]

I am glad you enjoyed it :)

[@shekharchandra9495]

Thanking You Chandoo Garu

[@meghanadighe6421]

Thanks for last tip. Very very useful and time saving

[@chandoo_]

Most welcome 😊

[@anv.4614]

Dear Chandoo, I have learned a lot from your Excel expertise. Could you please teach me how to use the function INDIRECT? It would be helpful to coach us with examples, particularly the option Row Column (R1C1). Thanks.

[@sanjayjha1966]

thanks for sharing such valuable tips, i follow ur video to learn some awesome tips.

[@onsenguy]

I LOVE your new haircut. 👍👍

[@wayneedmondson1065]

Hi Chandoo.. great tips.. as always :)) Thanks for sharing them. Thumbs up!!

[@chandoo_]

My pleasure 😊

[@aparajitashekhar6982]

Your haurcut is awesome😊😊

[@erdiaz39]

Awesome tips, thanks

[@chandoo_]

Happy to help!

[@terryhearst4825]

Loved #6. Thanks for the tip.

[@chandoo_]

You are so welcome!

[@jethrobakens3569]

I often prefer the classic view option in the pivot table options.

[@excelmylife1565]

Nice look sir

[@samirsourav6880]

Thanks really useful .

[@chandoo_]

Glad it was helpful!

[@venkatsurya4672]

Bingo👍 thanks for sharing it

[@chandoo_]

Boom

[@tajumir48]

Grand❤❤

[@rv_222]

yes sir its a nice haircut😂 and an awesome session👌

[@abdulahadh457]

#6 is my favourite

[@MrDhunpagla]

Nice hair cut Mr C....& ...thanks a lot for sharing these ...help me out with sorting of grand total coloum in pivot table 🙂

[@chandoo_]

Thank you... What do you mean "Sorting of grand total column"? If you want to sort items in a pivot by grand totals, right click on the grand total values and select "Sort".

[@hallmarkchemicals886]

superb

[@chandoo_]

Thanks 🤗

[@henrissonbeitzfamily5492]

Number six is a sweet feature! Thanks for that ! :-)

[@chandoo_]

Glad you like it!

[@pierrejarthon4254]

Excel Rockstar

[@hiteshdangodra6440]

You are Awesome, please help with monthly and annually HR attrition formula in excel pivot table. This will save my life. I really thankful to you sir😊

[@chandoo_]

Great suggestion! I will post something about this.

[@TaxMentors]

Chandoo baba ki Jai ho

[@chandoo_]

🤣🤣🤣

[@AnuPutravu75]

Hi Chandoo, how to get this view in pivot tables( the table view you have at 6:43 min). I am getting the Sales person under the same column as Product( with a -- product name and under it all the sales men names , in the same column and the next product name and same) or if in columns, across various columns.

[@shoaibrehman9988]

Thanks Sir nice hear cut. All tricks are superb time saver. Sir one real life issue i raised earlier how can we shift grant total row at top in pivot table it is hard to go down when you have thousands of lines. There was some tricks on youtube but not work perfectly. Pls when you have chance. Thanks Take care

[@chandoo_]

Interesting.. You can do one of the 4 things: 1. Insert a row above the pivot and link it to the grand total row 2. Use GETPIVOTDATA to get the grandtotals to show on top 3. Use a different pivot with just grandtotal right on top of original pivot 4. Use a picture link to grab a live image of the grand total row and place it anywhere (including on top) Refer to these links for some detail on the concepts. chandoo.org/wp/getpivotdata-in-dashboards/ chandoo.org/wp/how-to-use-picture-links/ All the best.

[@shoaibrehman9988]

Thanks Sir, Your article on this topic very descriptive i will do some prac with help of your download sheet. At the time when i have no solution i have done some tricky. I copied same pivot table and get only Grand total line of desired data moved up and connected both with slicer and hided header of 2nd pivot was working perfectly dynamically.

[@aashraynalawade]

Hi Chandoo, those were awesome tricks, thanks a ton for that. I have one more query. I belong to insurance field, if an agent has sold 1 or more policies he is considered as active for the month. When I create table on policy dump. For Example against one agent who has done 2 policy it comes as 2 if we take count. Whereas my requirement is I want it to come just 1 there. So that I can get unique active count for the month.is it possible?

[@sahilsinghal9472]

Kiran Nalawade add another column in your data with value as 1 for all rows. Then when creating a pivot table, add this new column as Average and not count

[@adlaialmeida9815]

Many I am “nearly” a pivot table convert. One question I do have is the generate report from the pivot table , instead of the file opening several sheets in the same file, is there a way of the regions to be broken into individual worksheets ? Thanks again

[@chandoo_]

The report filter functionality just makes _copies_ pivot tables. So you can't really get them to other workbooks without copying the data too. You can right click on the report pages and "move to" another workbook.

[@durvamayee1155]

Hi, for the 6th trick, is there any way to control the names of the new pivot tables that get generated... if there are 75 tables, its really annoying to label each of those manually.

[@chandoo_]

Hi Durva... Unfortunately no. But you can use VBA to rename such tabs once they are in place. It is not that hard. Here is a getting stared with VBA tutorial - kzbin.info/www/bejne/goGvg5ihZtR3hac

[@jrkl1622]

Coloring the data lables of pivot tables at times get changed upon change in data values or refresh.. could you pls throw some light on how to set a default format...

[@chandoo_]

Sure. I will research on this and either add a comment or make a video.

[@VijayPawar-sz6gq]

Sir, can u guide us wid some keyboard shortcuts.... Also share some knowledge on ppt presentation ... Thanks

[@srinivaskandru5034]

You came to know about that third trick for formatting numbers thru my comment in your recent KZbin video where you got surprised yourself.. say no I will show you the video.

[@chandoo_]

Of course you are right... I wanted to mention it but it slipped my mind during recording. I searched for the comment but couldn't locate it. I have mentioned your name in the blog article now.

[@srinivaskandru5034]

@@chandoo_ You don't need to give me any kind of credits for that small thing..I have already amazed by your honesty. But it is very surprising that how you guys missed those tricks that you were mentioned in the Bill Jelen's meetup video? We know three of them already...and I thought every power user know these..and we have some kick ass tricks like those...Anyways thanks Chandoo..keep it up.

[@chandoo_]

You are the exception Srinivas...

[@rash9366]

2 disliked person may dislike your new haircut 🤭☺️

[@chandoo_]

Hmm. This two shall pass. 🤣

[@nolatari]

#6 ...."Show report filter pages" .... unselectable...D:

[@chandoo_]

Have you set up a report filter? If so, are you using "data model" based pivots or regular ones?