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

[@briandennehy6380]

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

[@jayeshsuthar]

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

[@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.

[@ArmandoReportes]

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

[@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!

[@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. ❤❤❤

[@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

[@odallamico]

Amazing tricks, thank for sharing

[@finzblazin]

Tip #1 my jaw dropped .... You are my hero

[@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.

[@ExcelWithChris]

Once again - BRILLIANT STUFF! Thanks from South Africa.

[@maggienguyen958]

Thanks!

[@GoodlyChandeep]

Thanks a lot Maggie! Cheers

[@Everythingexcelpro]

You alway deliver productive information.

[@IvanCortinas_ES]

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

[@GoodlyChandeep]

Glad you like it Iván !

[@EslamAbdo1919]

that's really cool. Thank you.

[@AnimeshDas11]

9/10 Great going bro!

[@meniporat3527]

Amazing video. About tip no.1: If your interface isn't in English or if you don't remember the the shortcut: Alt+D+P it is better to add the "Pivot Table and Pivot Chart Wizard" to your QAT (Quick Access Toolbar). 1. right-click the QAT 2. select "customize the QAT" 3. in the "choose commande from" drop-down select "All commands" 4. scroll down in the list and choose "Pivot Table and Pivot Chart Wizard" 5. click the Add button to add this shortcut to the QAT 6. that's all. frorn now on, you can easily use the QAT instead of memorizing the shortcut 🙂

[@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!

[@karenboatwright]

Another great video. Thanks for sharing

[@GoodlyChandeep]

Pleasure Karen !

[@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!

[@SanaKhairi-o3p]

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!

[@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!

[@SanjayMehta-z4o]

nice, such a cool explaining

[@MrAmcold]

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

[@GoodlyChandeep]

Glad you enjoyed it Zeeshan!

[@andyadkins7593]

Dude off the chain

[@devangthakkar6847]

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

[@huytruong2026]

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

[@GoodlyChandeep]

Thanks Huy!

[@ChaosOrZen]

Flipping amazing. Very useful

[@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?

[@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.

[@hassab21]

Very nice, very hrlpful

[@KuldeepSingh-nq1vi]

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

[@fujinumagic]

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

[@GoodlyChandeep]

Glad you like them!

[@karimallahwala7022]

Very very good content thank you so much

[@GoodlyChandeep]

Glad you liked it!

[@JJ_TheGreat]

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

[@balrajvirdee1087]

Amazing 🙏🏽

[@suvarnadeshpande8007]

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

[@GoodlyChandeep]

You're so welcome!

[@Blueboy30]

Keep them coming

[@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! 🙂👍

[@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!

[@shiffamohammed5818]

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

[@GoodlyChandeep]

Pleasure

[@vidyabhushankumar5361]

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

[@GoodlyChandeep]

Thanks!

[@giovannizanocco5406]

Ecxellent job! Very useful. Thank you

[@GoodlyChandeep]

Glad you enjoyed it ,Giovanni !

[@cg24356]

You are legend. Thanks so much

[@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

[@bluebellhs]

Mind blowing.

[@brianchuck9264]

Good Job Sir!

[@GoodlyChandeep]

Thanks!

[@bijumohan2405]

Sure we did bro 👍👍👍🙌🙌

[@thebhaskarjoshi]

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

[@GoodlyChandeep]

Glad you like it!

[@DavidWarbeck]

Wow - this is amazing stuff! Thank you

[@GoodlyChandeep]

Glad you enjoyed it!

[@ruzzputin]

Pretty awesome indeed! Thank you!

[@GoodlyChandeep]

Glad you liked it!

[@bojanjovanovic756]

Really good ! thx

[@GoodlyChandeep]

Glad you like it!

[@WaymondJr]

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

[@GoodlyChandeep]

Glad you like them!

[@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.

[@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?

[@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?

[@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?

[@logic3686]

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

[@michaelt312]

Great video. Thank you.

[@GoodlyChandeep]

Glad you liked it!

[@nomatusi7514]

Love it, love it

[@GoodlyChandeep]

Thanks No Matusi!

[@alializadeh8195]

Thanks

[@ramarisonandry8571]

From Madagascar, thank you👌

[@GoodlyChandeep]

Pleasure!

[@JR3318.]

Bro, you are the man 🔥

[@michaeldavidson7]

Excellent content. Always so helpful!

[@GoodlyChandeep]

Glad to hear it!

[@AftabAhmed-uw4ki]

Awesome this video very nice

[@GoodlyChandeep]

Thanks!

[@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.

[@arbazahmad7177]

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

[@GoodlyChandeep]

Welcome!

[@gennarocimmino]

simply great :)

[@GoodlyChandeep]

Glad you think so!

[@lucaschiavone8469]

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

[@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

[@tnssajivasudevan1601]

Thank 😊 Sir

[@GoodlyChandeep]

welcome!

[@tanujgupta9081]

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

[@GoodlyChandeep]

Happy to hear that!

[@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.

[@brij9464]

Great boos 👍

[@GoodlyChandeep]

Thanks Brij!

[@mohammadalbizri2013]

So nice

[@GoodlyChandeep]

Thanks!

[@harishpaldhir5915]

Woovv so excited to see this video 😍😍😍

[@GoodlyChandeep]

Great to hear!

[@iantotheh]

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

[@GoodlyChandeep]

😂😂

[@umairmomin8868]

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

[@JM-mb6tf]

GIVE US MOOOOOOORE !!!

[@amitgupta5484]

How you do highlighting feature with red colour

[@MrDhunpagla]

So cool

[@GoodlyChandeep]

Thanks!

[@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!

[@Tat-cx1lr]

Bravo

[@GoodlyChandeep]

Thanks Tat!

[@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.

[@shoaibrehman9988]

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

[@evilangel4136]

No Further Ado...👍

[@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?

[@GoodlyChandeep]

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

[@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.

[@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?

[@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 :)

[@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.

[@brpputtur2201]

Wow

[@GoodlyChandeep]

Thanks!

[@nici1782]

Unfortunately some aren"t working on excel for Mac

[@dilip1able]

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

[@vasudeomhadgut5524]

Dax traning

[@magicub]

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

[@amitmorag9922]

Thanks!

[@GoodlyChandeep]

Appreciate the Tip Amit!