10 Mind Blowing Pivot Table Tricks 🤩 - How many do YOU know?

This is the last 'Pivot Table' tricks video's follow-up video. Here are 10 more Pivot Table Tricks that can help you to become smarter with Pivot Tables!
Join this channel to get access to perks:
/ @goodlychandeep
Macro Link - trumpexcel.com/split-each-exc...
===== ONLINE COURSES =====
✔️ Mastering DAX in Power BI -
goodly.co.in/learn-dax-powerbi/
✔️ Power Query Course-
goodly.co.in/learn-power-query/
✔️ Master Excel Step by Step-
goodly.co.in/learn-excel/
✔️ Business Intelligence Dashboards-
goodly.co.in/learn-excel-dash...
===== LINKS 🔗 =====
Blog 📰 - www.goodly.co.in/blog/
Corporate Training 👨‍🏫 - www.goodly.co.in/training/
Need my help on a Project 💻- www.goodly.co.in/consulting/
Download File ⬇️ - goodly.co.in/10-more-mind-blo...
===== CONTACT 🌐 =====
Twitter - / chandeep2786
LinkedIn - / chandeepchhabra
Email - [e-mail]
===== WHO AM I? =====
A lot of people think that my name is Goodly, it's NOT ;)
My name is Chandeep. Goodly is my full-time venture where I share what I learn about Excel and Power BI.
Please browse around, you'd find a ton of interesting videos that I have created :) Cheers!

[@jayeshsuthar]

I am able to surprise my manager every time I work on excel by watching your amazing tricks and tips. Thank you bro❤️

[@briandennehy6380]

That concatenatex example is superb and is gonna be a life saver for me, thanks!

[@andyadkins7593]

Dude off the chain

[@ArmandoReportes]

Pivot tables? Those I know... Well, I'll watch the video. There might be something new... Tip 1: 😱🤯

[@donduck7845]

For the first one whenever I get data like this, I hit Ctrl+A, Ctrl+C, Ctrl+Alt+V (paste special), hit E to select Transpose and then Return. That transposes the data in seconds perfectly for a pivot table which I think is a lot simpler and quicker than using a legacy pivot table within excel. As others have mentioned, Unpivot Columns in PQ is efficient too

[@Julyenderson]

The first one just blown my mind. It basically convert a table in a database and it's exactly what I was looking for and couldn't find it weeks ago. ❤❤❤

[@Blueboy30]

Keep them coming

[@ankursharma6157]

AMAZING! AMAZING! AMAZING! This Inspires Me So Much . . that there is SO MUCH to LEARN!

[@GoodlyChandeep]

Glad it inspires you! Thanks for the Tip.

[@ExcelWithChris]

Once again - BRILLIANT STUFF! Thanks from South Africa.

[@bluebellhs]

Mind blowing.

[@balrajvirdee1087]

Amazing 🙏🏽

[@hassab21]

Very nice, very hrlpful

[@AnimeshDas11]

9/10 Great going bro!

[@karenboatwright]

Another great video. Thanks for sharing

[@GoodlyChandeep]

Pleasure Karen !

[@ishaq79]

Hello there, your channel was suggested to me by my Arab friend, although I'm Indian. my Arab friend is just obsessed about your video .

[@GoodlyChandeep]

I hope you liked the video. Say a Hi to your friend for me!

[@ishaq79]

@@GoodlyChandeep passed your hi to him. He gain lots of information from your channel. Thank you and from me too!

[@fujinumagic]

You are the god. Awesome stuff. Great great thanks to you.

[@GoodlyChandeep]

Glad you like them!

[@user-bd1uf5cf3r]

nice, such a cool explaining

[@dilip1able]

Correcting Cross-Tabulated Data: If you have data in a cross-tabulated format (where you need to look up and left to understand the meaning of a number), you can use the old PivotTable wizard (Alt + D + P) to correct the data and make a clean PivotTable. Data Distribution Trick: You can create a PivotTable for every single value in a filter by using the "Show Report Filter Pages" option in the PivotTable Analyze tab. This will create a new sheet for each value. You can also use a simple Macro to create multiple Excel files for each value. Fixing Column Width: To prevent PivotTable from automatically adjusting the size of the column when it refreshes, you can uncheck the "Autofit column widths on update" option in the PivotTable Options. Custom Grouping: You can create custom groups in your PivotTable by selecting the items you want to group, right-clicking, and choosing "Group". You can then rename the group as you wish. Showing Calculations in Rows: You can show your calculations in rows instead of columns by dragging the Sigma Values to the Rows section in the PivotTable field list. Adding Financial Years and Quarters: You can add financial years and quarters to your PivotTable by adding two simple columns in your source data and using formulas to calculate the financial year and quarter based on your fiscal year. Showing Text in Value Section: You can show text in the value section of your PivotTable by using PowerPivot and DAX formulas. This allows you to concatenate multiple values into one cell. Setting Default Design Layout: You can set the default design layout of your PivotTable by choosing a design from the Design tab and right-clicking on it to set it as default. Changing Layout of Field List: You can change the layout of the field list in your PivotTable by clicking on the gear icon and choosing the layout you prefer. Merge and Center Labels: You can merge and center labels in your PivotTable by right-clicking anywhere on the PivotTable, going to PivotTable Options, and checking the "Merge and Center Cells with Labels" option.

[@martyc5674]

Thanks Chandeep- best trick I picked up was at 14:12 when you hit “more tables” to instigate sending the existing pivot to the data model, I did not know you could do that so easily.

[@Everythingexcelpro]

You alway deliver productive information.

[@nomatusi7514]

Love it, love it

[@GoodlyChandeep]

Thanks No Matusi!

[@guitars0206]

Thank you for the pivot table tricks. Next time could you please include an index or chapter timeline so I can easily jump to each trick. Love all the pivot table content!

[@ES-IvanCortinas]

Very complete, powerful and with many functionalities to apply in daily work. Thank you

[@GoodlyChandeep]

Glad you like it Iván !

[@karimallahwala7022]

Very very good content thank you so much

[@GoodlyChandeep]

Glad you liked it!

[@bijumohan2405]

Sure we did bro 👍👍👍🙌🙌

[@devangthakkar6847]

Superb sir...... Thanks a lot....

[@domarregala5932]

excell GOD!!! very awesome tricks... i always watch your videos. please keep on sharing. thank you so much. from PH

[@GoodlyChandeep]

Glad you like them!

[@GeertDelmulle]

Good stuff, Chandeep! That old wizard keep popping up and amaze me. And yes, trick no. 9: that is my favorite and default setting, too. Trick no. 10: I didn't know that was possible... PS: here's one more trick for you: use Grouping on a numerical field in the row headers to automatically create bins useful for histograms. Yet, be careful to keep the data in the pivot table cache, because of you add the table to the data model, you lose this capability. Thanks for another great video! 🙂👍

[@iantotheh]

"Whenever you get cross tabulated data sets you're absolutely screwed." LMAO!

[@GoodlyChandeep]

😂😂

[@amitmorag9922]

Thanks!

[@GoodlyChandeep]

Appreciate the Tip Amit!

[@szymonmatusiak3992]

Trick number 8 is amazing. I use pivots to create 90 percent of my reporting and this trick will allow me to go even further. Thank you so much!

[@user-rp3yx3tw1h]

Hi Chandeep, I'm a big fan of you and your videos. I've a nested table in a column with more than one records for each record on the main table. I want to filter the nested table to keep only the record which has matching ID on the current record of the main table, before expanding, in order to avoid duplicates. If I just remove duplicates after expanding, it may remove the matching ID and keep any one of the others, which I don't want. Thank you, and keep up the good work. You're simply the best!

[@HachiAdachi]

🤯 My ego destroyed (again) - I didn't know #1, 2, 4 & 10! (#1 is so hack-y! ☺ Love it!) Great one, Chandeep! 👍👍

[@GoodlyChandeep]

Glad you like them!

[@tjduprey]

Flipping amazing. Very useful

[@huytruong2026]

Trick #1 is incredible! I don't think many people out there know about this.

[@GoodlyChandeep]

Thanks Huy!

[@suvarnadeshpande8007]

Amazing tips, especially the 6th one. Thanks for sharing.

[@GoodlyChandeep]

You're so welcome!

[@ruzzputin]

Pretty awesome indeed! Thank you!

[@GoodlyChandeep]

Glad you liked it!

[@arpanapandey8183]

Where was this channel my entire life 😢😢

[@GoodlyChandeep]

Right under your nose.. you just had to look down :D I'm glad you finally found it.

[@michaeldavidson7]

Excellent content. Always so helpful!

[@GoodlyChandeep]

Glad to hear it!

[@alializadeh8195]

Thanks

[@brianchuck9264]

Good Job Sir!

[@GoodlyChandeep]

Thanks!

[@bojanjovanovic756]

Really good ! thx

[@GoodlyChandeep]

Glad you like it!

[@giovannizanocco5406]

Ecxellent job! Very useful. Thank you

[@GoodlyChandeep]

Glad you enjoyed it ,Giovanni !

[@AftabAhmed-uw4ki]

Awesome this video very nice

[@GoodlyChandeep]

Thanks!

[@ramarisonandry8571]

From Madagascar, thank you👌

[@GoodlyChandeep]

Pleasure!

[@gennarocimmino]

simply great :)

[@GoodlyChandeep]

Glad you think so!

[@cg24356]

You are legend. Thanks so much

[@DavidWarbeck]

Wow - this is amazing stuff! Thank you

[@GoodlyChandeep]

Glad you enjoyed it!

[@KuldeepSingh-nq1vi]

Really Awesome Video, Please start DAX and Power pivot from scratch..

[@michaelt312]

Great video. Thank you.

[@GoodlyChandeep]

Glad you liked it!

[@thebhaskarjoshi]

Wow, that's pretty awesome! I appreciate it!

[@GoodlyChandeep]

Glad you like it!

[@CreativelyChallenged]

These are awesome; I am pretty new to learning intermediate Excel. Anyway, to make a tutorial for inbound calls into a Call Center by the different queues?

[@WaymondJr]

Keeps me thinking, every one of your videos‼️ Fantastic ♥️😎

[@GoodlyChandeep]

Glad you like them!

[@MrAmcold]

loved ur video brother - always something new to learn - bravo

[@GoodlyChandeep]

Glad you enjoyed it Zeeshan!

[@tanujgupta9081]

Your content is always amazing 👍👍 and I really liked the concatenation part which you demonstrated

[@GoodlyChandeep]

Happy to hear that!

[@vidyabhushankumar5361]

Where were you till now...... "PRETTY AWESOME".....❣

[@GoodlyChandeep]

Thanks!

[@JJ_TheGreat]

18:51 Wow! I knew #9 beforehand! But most of the others are new to me!

[@JM-mb6tf]

GIVE US MOOOOOOORE !!!

[@arbazahmad7177]

Really Awesome 👌 trick 1 is.... thanks 😊

[@GoodlyChandeep]

Welcome!

[@logic3686]

Trick #1 can also be done by using power query.

[@aliakberza]

This two part viodeo is amazing. A lot of lesser known tips that can improve productivity. Thank you for sharing this. I had a follow-up question on the custom grouping trick. How can I make Excel remember the groupings and be able to use the groups in the field lists of multiple pivot tables?

[@JR3318.]

Bro, you are the man 🔥

[@williamarthur4801]

Great to see PT Wizard in action, great trick to unpiovt, really like your fisca quarter, my way has been a bit longer, divide by 3 roundup and if 1 then 4, else -1, and of course the concatenate was very clever.

[@JJ_TheGreat]

What about using the MOD() function?

[@shiffamohammed5818]

Thank you so much for sharing these informative pivot table tricks, Chandeep.

[@GoodlyChandeep]

Pleasure

[@brij9464]

Great boos 👍

[@GoodlyChandeep]

Thanks Brij!

[@evanbeedle5693]

Thank you so much for the first. I work as a financial manager and nearly every report is cross-tabular. Extracting data for analysis is usually tedious to reformat. I'm going to tell everyone about this.

[@mohammadalbizri2013]

So nice

[@GoodlyChandeep]

Thanks!

[@MrDhunpagla]

So cool

[@GoodlyChandeep]

Thanks!

[@HarshilPatel497]

Perfect just in time at when needed i realized the solution or the way i was working was wrong due to this cross table data concept... thanks for letting me know i was screwd upto now... no more obviously after this video

[@GoodlyChandeep]

Sure! 😁

[@donduck7845]

Another quick resolution to cross tabulated data is to select all data with Ctrl+A, copy, then Ctrl+alt+v to open Paste Special, hit E to select Transpose then Enter and you've corrected the format in the sheet without pivot tables or PQ

[@harishpaldhir5915]

Woovv so excited to see this video 😍😍😍

[@GoodlyChandeep]

Great to hear!

[@tnssajivasudevan1601]

Thank 😊 Sir

[@GoodlyChandeep]

welcome!

[@jayeshaw5705]

Cool tricks! For the text in the values area of the pivot table. How to get the grand totals to display a count rather than the grand totals showing the text?

[@knutkuhnhausen1531]

Amazing. The options of the pivot table could be set up as default in die excel options. Otherwise you have to adjust them each time you create a pivot table.

[@lucaschiavone8469]

For the first one: is the result the same if you use the unpivot function in power query?

[@bibybsa7921]

Foudn my new favourite youtuber! May I ask you a question? In pivot tables, can I group top 5 customers and the remaining ones as Others? Because I do not want to lose the total data when filtering, but summarize the table. I tried this, but the groups cross pivot tables, so when I reate groups in a different Pivot Table, it brakes the groups in the Pivot Tables before.

[@evilangel4136]

No Further Ado...👍

[@Tat-cx1lr]

Bravo

[@GoodlyChandeep]

Thanks Tat!

[@Elaine-nx4gv]

How do I split total hours for an employee worked for different locations onto a different worksheet, and combine all numbers associated with the 2nd worksheet?

[@parshuramnangare8577]

Thank you so much Chandeep sir, these are pretty awesome tips and tricks of pivot table, however regarding your trick # 2 : distributing data across different pages, I have tried to do this for my customer names but it look like if you have large sets of data it doesn't rename all the sheet selected under the filter in my case I have selected filter as "customer names" with 190 counts. when I had tried this tricks few of them rename with customer name but most of them generated with just normal sheets numbers without the names. Can you please guide me if I am doing anything wrong here.

[@vikasbhardwaj7885]

Is there any option when I select my one slicer than the second one slicer filter clear & again we select or click on the second one 1st one clear that means in 2 slicer click any one than second one auto filter out. because this is my practical requirements.thanks in advance

[@umairmomin8868]

Brst tutorial can you please suggest how everytime selected cell coloured?

[@puffdapaz]

Awesome maths

[@GoodlyChandeep]

Thank you!

[@renatovianello874]

Hi Chandeep. many thanks for the excellent content you share! I am italian and I have an Excel installation in italian language. For this reason I cannot use the shortcut Alt+d+p to show the legacy PivotTable Wizard. Since I was not able to find a translation via a Google search, is there a way to show the wizard by using a macro in VBA? Many thanks in advance for your kind answer and regards Renato

[@NicolaJelmorini]

The correct shortcut is ALT+D+B (in the video shows up the wrong one :-))

[@renatovianello874]

@@NicolaJelmorini Grazie Nicola, adesso funziona!

[@amitgupta5484]

How you do highlighting feature with red colour

[@marghubbenzema613]

Sir , How to set different colour in cell please tell me shortcut trick

[@JUHILLAPSIWALA007]

Sir, How to do Dr. Cr. Amount plus minus in running balance in pivot table?

[@brpputtur2201]

Wow

[@GoodlyChandeep]

Thanks!

[@GoodlyChandeep]

Which tricks are new to you? Previous Pivot Table Tricks Video - kzbin.info/www/bejne/mneZemtvaZdpeJo

[@shoaibrehman9988]

Gr8 stuff , In future can you discuss calculated field options.

[@nici1782]

Unfortunately some aren"t working on excel for Mac

[@dilip1able]

For 7 I am not getting the More Tables option in Pivot

[@JJ_TheGreat]

19:23 Never use the “Merge and center” setting in Excel… Use “Center across selection” instead… You should know better, Chandeep 😉

[@00oxymore]

I think it's a trick that works only for pivot tables :)

[@magicub]

Actually after learning power query i've been using pivot tables way less

[@vasudeomhadgut5524]

Dax traning

[@barttitulaerexcelbart9400]

Thanks Chandeep. Great trick to add this pivot to the datamodel and then use CONCATENATEX. If you have only one value (for example in a planning) I use PQ, pivot column and then advanced, don't aggegate....seems very strange but this works fine.

[@maggienguyen958]

Thanks!

[@GoodlyChandeep]

Thanks a lot Maggie! Cheers

[@hanyabushedeed]

Thanks

[@GoodlyChandeep]

Thank a ton! 🙂