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)

⏱ 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 👇

📺 Watch next
=============

How to combine data when the headers / structure is not matching?

Combine multiple sheets of data using Power Query

Have data in a folder instead? Use this method:

How to create Pivot Tables? (8 examples):

Full Advanced Excel Course by Chandoo:

📗💻 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.

~

#excel #dataanalytics

[DRIXD]

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

[GoldenSlumber]

AWESOME power Chandoo! Thanks for this wonderful tip 👏

[curiouscatlabincgetsworrie]

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

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

[mahmoudmusah]

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.

[thambirajahjeyaseelan]

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.

[kakalkairuchi]

Thank you so much for all your educational videos.
We get to learn new knowledge everyday.

May god bless you and your family 😊🙏

[unnikrishnansanthosh]

Your videos are my bible, thanks for sharing

[nehazahra]

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 👍

[daXcel]

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

[subidhabasheer]

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.

[VisuLytics]

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

[riswwtf]

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

[girishpadshala]

Thanks for sharing informative & productive ideas.

[preetshah]

Make a video on statistics for data analysis.

[An_Indian_]

Long story cut short, you are simply the BEST!

[jerrydellasala]

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.

[lilliansibley]

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?

[ericchen]

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

[ghulamqadir]

Sir please share this file for paractice. Thank you