Create One Pivot Table from Many Sheets ~ AWESOME Excel Trick
Vložit
- čas přidán 10. 07. 2024
- 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 - Věda a technologie
The more I watch your videos, the more I discover the power of pivot tables
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.
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. 🎉🎉🎉
AWESOME power Chandoo! Thanks for this wonderful tip 👏
My pleasure!!
Another great video, thanks Chandoo!
Glad you enjoyed it!
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.
Awesome thanks for posting
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 👍
Glad it was helpful!
This dude can chop his onions in Excel haha, no doubt! Great skills ... again! :D
😂
Thank you 🙏
Thank You ❤
You are welcome :)
Thank you 😊
You're welcome 😊
Nice! Thanks
Thanks man
You're welcome!
Your videos are my bible, thanks for sharing
😊 THANK YOU
Thank you so much for all your educational videos.
We get to learn new knowledge everyday.
May god bless you and your family 😊🙏
So nice of you
Bravo
absolutely brillian works,thanks !!
You're welcome!
Great👍👍
Thank you 👍
good
🔥🔥🔥🔥🔥🔥🔥🔥🔥
😎
Thanks, Chandoo. Could you kindly let me know the mic you are using to record videos?
Make a video on statistics for data analysis.
❤
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
It doesn't matter. As long as the source data is "same" Excel only builds one PivotCache.
Thanks for sharing informative & productive ideas.
You are so welcome!
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
You can use Power Query to add a conditional column that converts your numbers to dimension labels.
Long story cut short, you are simply the BEST!
😍
Great video Chandoo!
Glad you enjoyed it
Great video. Where did you get the July data? I couldn't find it anywhere to add.
You can make some random values or copy paste the same sheet and change the dates to test.
@@chandoo_ Thanks.
Truly genius 👏
Power Query is the real magic. I just wave the wand.
Can we have this summary sheet in the same workbook of the data sheets?
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?
Great video, awesome. Thanks Sir
My pleasure.
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.
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
1:10 But can’t you pick the folder - to select all worksheets and all future worksheets?
Super explain as usual SIR C
I am glad you enjoyed this.
Very nice 👍
My pleasure.
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.
Great point @jerrydellasala7643
Oof, Chandoo delivering the WISDOM yet again.
I am glad you enjoyed this.
Sir please share this file for paractice. Thank you
How to create fever account kindly guide
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?
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 - czcams.com/video/5u7bpysO3FQ/video.html
@@chandoo_ thank you so much for reply...means alot. Surely I will watch video.
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?
@1:05 Can you not select the folder and it will load all the file?
You can.
Many thanks for the information, quick question please, what happens if you use the "select multiple items" before "tranform data "? Thanks
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.
@@chandoo_ thankyou so much for the explanation and the time.
Sir I want ms office 365, how to download and install please do video.
Also, can’t you use a PowerPivot for this task?
If the data in each tab is near 1M rows then how can we combine all these tabs in single table ?
Awesome video like awesome chocolates👍👍
Thank you 🤗
I want to get online class sir
How to add July Month in Excel i didn't understand
How to add Next Month In Excel
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
Please use the channel plalylists and other CZcams results to self-learn. All the best.
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 😢
Please see the introduction to Excel video and make a self-study plan to learn more. czcams.com/video/F7aPazuS8QY/video.html
big fan pls meet me i live in india
Hello Sir. I work as a financial analyst and a fresher. I wanna move to other countries for job purposes. Any advice please.😅
Try going for eduction or work for a few years and look for transfer / onsite opportunities.
Pls Am waiting for your
Qualitative reply
Please use Google / CZcams to learn.