Create One Pivot Table from Many Sheets ~ AWESOME Excel Trick

Ever wanted to make a pivot table, but your data is scattered across multiple worksheets? Like Jan, Feb, Mar...?
In this video, learn an AMAZING Excel trick to solve the problem easily.
Note: This works in Excel 2016 or above (or with Power Query add-in in Excel 2013)
Step by step instructions and sample file 👉chandoo.org/wp/how-to-make-a-...
⏱ Video Topics:
==============
0:00 - Many sheets, One Pivot - the problem
0:42 - Combining the data (using Power Query)
2:08 - Data cleansing with Power Query
4:00 - Creating the pivots
6:00 - How to add new data and update the pivots
7:00 - What if your data is not structured correctly?
📁sample data
=============
Try it yourself using the sample dataset here 👇
chandoo.org/wp/how-to-make-a-...
📺 Watch next
=============
How to combine data when the headers / structure is not matching?
• AWESOME Excel trick to...
Combine multiple sheets of data using Power Query
• AWESOME Excel trick to...
Have data in a folder instead? Use this method:
• Powerful trick to comb...
How to create Pivot Tables? (8 examples):
• How to use Pivot Table...
Full Advanced Excel Course by Chandoo:
chandoo.org/wp/excel-school-p...
📗💻 LEARN EXCEL - FULL COURSE by Chandoo
========================================
Want to learn how to use Excel for Data Analysis, MIS, Reporting or Project Management roles? Check out my Excel School program and sign-up today.
chandoo.org/wp/excel-school-p...
~
#excel #dataanalytics

[@catlord21]

The more I watch your videos, the more I discover the power of pivot tables

[@mahmoudmusah5963]

Duuuude, you are amazing! I consider myself a beginner and for that matter, intend to master excel through your channel. Thanks for the great work.

[@curiouscatlabincgetsworrie7755]

This dude can chop his onions in Excel haha, no doubt! Great skills ... again! :D

[@chandoo_]

😂

[@GoldenSlumber474]

AWESOME power Chandoo! Thanks for this wonderful tip 👏

[@chandoo_]

My pleasure!!

[@chrism9037]

Another great video, thanks Chandoo!

[@chandoo_]

Glad you enjoyed it!

[@redinactionbaby]

Rarely give a comment, but after watching tonnes of your videos, ought to say thanks a tonne and wish you best in life and hereafter. 🎉🎉🎉

[@nehazahra5800]

This is the first time i am watching your video your illustration on excel through prominent arrow is super different with other excel channel... keep it up 👍

[@chandoo_]

Glad it was helpful!

[@thambirajahjeyaseelan5905]

Hi Chandoo, You are awesome! I discover you way too late. Your educational videos in excel are very useful. Thank you for your input videos. By the way I like your chair. God bless you bro.

[@vargheseantony5677]

Thank You ❤

[@chandoo_]

You are welcome :)

[@unnikrishnansanthosh]

Your videos are my bible, thanks for sharing

[@chandoo_]

😊 THANK YOU

[@taizoondean689]

Thank you 😊

[@chandoo_]

You're welcome 😊

[@XCellenTruckerBellz]

absolutely brillian works,thanks !!

[@chandoo_]

You're welcome!

[@preetshah3793]

Make a video on statistics for data analysis.

[@kakalkairuchi495]

Thank you so much for all your educational videos. We get to learn new knowledge everyday. May god bless you and your family 😊🙏

[@chandoo_]

So nice of you

[@Tony_Montana659]

Thanks man

[@chandoo_]

You're welcome!

[@tanakornpromthep9025]

Thank you 🙏

[@Yo_Kelz]

Nice! Thanks

[@amitdhiman7307]

Great👍👍

[@chandoo_]

Thank you 👍

[@daXcel7448]

Thanks, Chandoo. Could you kindly let me know the mic you are using to record videos?

[@Dan_De_Man]

When you did your 2nd pivot table instead of going to table and inserting another pivot table is it better to copy the first pivot and paste and then redesign it so not having 2 pivot caches or is that only uf you have 2 source tables

[@chandoo_]

It doesn't matter. As long as the source data is "same" Excel only builds one PivotCache.

[@shri_420]

Whenever I get my pivot I change the Row labels to actual field name and you know how I do that The reason I hate showing it as Row labels and other is when I copy the same pivot and change the field or column it automatically takes that as header name in the pivot

[@DataVisulizations]

Thanks Chandoo G. Excel is great for analysis but what if we have data in numbers. For example, digit 1 represents Male and digit 2 represents Female in our data. How we can make it meaningful while we analyze such data in Excel

[@chandoo_]

You can use Power Query to add a conditional column that converts your numbers to dimension labels.

[@girishpadshala9689]

Thanks for sharing informative & productive ideas.

[@chandoo_]

You are so welcome!

[@anwarashraf143]

Bravo

[@user-ri7sw2wt6f]

Great video. Where did you get the July data? I couldn't find it anywhere to add.

[@chandoo_]

You can make some random values or copy paste the same sheet and change the dates to test.

[@user-ri7sw2wt6f]

@@chandoo_ Thanks.

[@shahidwarsi_]

❤

[@An_Indian_2000]

Long story cut short, you are simply the BEST!

[@chandoo_]

😍

[@934rex]

🔥🔥🔥🔥🔥🔥🔥🔥🔥

[@chandoo_]

😎

[@brendamg7298]

good

[@subidhabasheer860]

Hai.. great video.👨‍🏫 Chandoo, help, please. Is it possible to have this combined sheet in the same workbook as the source data.? Sorry if the question does not make sense.

[@namy1557]

Truly genius 👏

[@chandoo_]

Power Query is the real magic. I just wave the wand.

[@knowledgeworld1506]

How to create fever account kindly guide

[@Azhar_Khan383]

Great video, awesome. Thanks Sir

[@chandoo_]

My pleasure.

[@a.b.8444]

Great video Chandoo!

[@chandoo_]

Glad you enjoyed it

[@BhaskarReddy-bv3ls]

Sir I want ms office 365, how to download and install please do video.

[@ericchen1615]

Can we have this summary sheet in the same workbook of the data sheets?

[@vikaspawar6494]

In a my case, I usually have 70K to 100K rows in each month data, So by combining each month data with power query, will the file size be as big as individual excels or less...Also will excel perform with so much data rows?

[@chandoo_]

I strongly recommend using a database to hold your values. Excel is not safe or right place to maintain such large datasets. But you can still use Power Query to combine, clean, and manipulate the data. You can even analyze the larger combined dataset with pivot tables, even if the total size is more than 1mn rows. See this video for more - kzbin.info/www/bejne/a6aak6OvqLRmfLM

[@vikaspawar6494]

@@chandoo_ thank you so much for reply...means alot. Surely I will watch video.

[@lilliansibley8418]

I imported the original data from csv files into excel format. When I look at the data file for the Power query, it includes both the csv file and the excel sheet so data is doubled. Is there a way to get rid of that in the source workbook?

[@narasimhabalas1993]

@1:05 Can you not select the folder and it will load all the file?

[@chandoo_]

You can.

[@rabin3333]

Very nice 👍

[@chandoo_]

My pleasure.

[@Alpha408]

Many thanks for the information, quick question please, what happens if you use the "select multiple items" before "tranform data "? Thanks

[@chandoo_]

You are welcome. Each item you select in the "navigator" creates one query. So, when you select "multiple items" you end up with different queries inside PQ. You will need to either append them all or load them separately to Excel.

[@Alpha408]

@@chandoo_ thankyou so much for the explanation and the time.

[@ghulamqadir3248]

Sir please share this file for paractice. Thank you

[@jerrydellasala7643]

Unless the full table is needed for other output (printed?), it would be easier to load the data as a connection only to the Data Model. Once in the data model, the Amount and Boxes number format could be set, and not have to be set for each pivot table.

[@chandoo_]

Great point @jerrydellasala7643

[@JJ_TheGreat]

1:10 But can’t you pick the folder - to select all worksheets and all future worksheets?

[@Oliver77019]

I am a fresher , i didnot know anything well regarding excel , could you please advice me how can i start 🙏 , because the local institutes will nicely charge thousands of rupees and they dont cjvee advanve pprtion properly 😢

[@chandoo_]

Please see the introduction to Excel video and make a self-study plan to learn more. kzbin.info/www/bejne/fGjEgZSwqrhrh7s

[@JJ_TheGreat]

Also, can’t you use a PowerPivot for this task?

[@piyushnautiyal6271]

Awesome video like awesome chocolates👍👍

[@chandoo_]

Thank you 🤗

[@abhisquest]

If the data in each tab is near 1M rows then how can we combine all these tabs in single table ?

[@sonugupta6831]

sir, when i made this pivot table and when I selected date then it didn't came with date month wise and I want data with month wise so how can i do this pls sir help?

[@sandhyajalagam8128]

Hi sir Done mba marketing 2023 Here wish to learn sql +power bi Trying for job search Pls give guidance and links For free learning I don't have fees

[@chandoo_]

Please use the channel plalylists and other KZbin results to self-learn. All the best.

[@pickelbarrelofficial1256]

Oof, Chandoo delivering the WISDOM yet again.

[@chandoo_]

I am glad you enjoyed this.

[@mamalik1088]

Super explain as usual SIR C

[@chandoo_]

I am glad you enjoyed this.

[@SaidbabaSaid]

I want to get online class sir

[@sandhyajalagam8128]

Pls Am waiting for your Qualitative reply

[@chandoo_]

Please use Google / KZbin to learn.

[@dukkaravindranath2892]

Hello Sir. I work as a financial analyst and a fresher. I wanna move to other countries for job purposes. Any advice please.😅

[@chandoo_]

Try going for eduction or work for a few years and look for transfer / onsite opportunities.