Consolidate & Clean Multiple Excel Sheets in One Pivot Table

In this video you'll learn how to dynamically combine data from multiple Excel sheets into a single PivotTable using Power Query! This guide is perfect for those who need to consolidate information from various sources in Excel. We'll demonstrate how to append data from multiple sheets, apply necessary transformations, and create a PivotTable report that updates automatically with new data.

Excel's Power Query can easily solve a common problem in Excel: Consolidating information from multiple Excel worksheets into one Pivot Table. This also works if you'd like to append data from multiple sheets into a single Excel table.

🔑 Key Concepts Explained:
▪️ Sending data from multiple sheets into Power Query in one step.
▪️ Excluding irrelevant data, like grand totals, and filling gaps in datasets.
▪️ Dynamically pulling in data from all sheets named with a specific pattern.
▪️ Creating a PivotTable directly from the Power Query Editor.
▪️ Ensuring the final report excludes itself as a data source to avoid duplication.
▪️ Updating the PivotTable report automatically when new data or sheets are added.

In this video we'll combine the data from multiple Excel sheets into a single Pivot table in the same workbook. And we'll do it in a dynamic way so that when new sheets are added, we just need to press Refresh on our report. We will also apply Power Query's convenient transformations to clean and prepare the data for our report (fill up empty cells in a table)

00:00 Combine Data from Multiple Sheets in one PivotTable
01:48 Send data to Power Query
02:25 Send data from all sheets in one go
03:45 Apply Power Query's Data Transformations
04:58 Create Excel Pivot Table on Appended Data
05:38 Exclude End-Report as Data Source in Power Query
07:46 Refresh Report with New Data

🚩Let’s connect on social:

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#excel #powerquery

[EyiBillion]

The absence of skill makes a man sweat when faced with tasks. Thank you for adding value Leila.

[vincentmorissette]

I'm following the complete Power Query Course and it litterally changed everything. Life is so much easier at work when I have to manipulate data !

[shaunwthompson]

Leila Gharani is the absolute best. 50% of my YouTube suggestions are her videos at this point and I watch almost all of them.

[michebernier]

So often using the old ways becomes habit, wasting time and energy, these tips honestly have been an incredible eye opener, and are very appreciated. Thank you for making the instruction so easy to understand, and meaningful. I am looking forward to some time off, where I can purchase a course or two and finding out what new things I've missed. Thank you again.

[prioritymaleman]

I've watched half a dozen pivot table videos, and yours is by far the easiest and most complete. Great job thank you! Jim in FL

[asetbilibaev]

I’ve been heavily using power queries for two years in my job as data analyst. In 9 minutes i have just learned several things which has already drastically changed forever my ways of doing excel.

[cor]

Cant't wait for the company I work for to install Power Query, it is making things so easy.

[jacquelines]

You are a terrific teacher, thank you. The content is always relevant and you make it easy to understand with just the right amount of details and explanation.

[jmartin]

You are nothing short of a wizard, thank you!!

[R-nk]

it never fails.. The Queen make it looks easy

[titoutitouh]

Leila strikes back again...You just answered a ton of questions I had in one simple video. I am starting to think that you're somehow reading my mind! Thank you very much, you are the best!

[s.o.]

Thank you Leila! Not having a lot of knowledge in Excel, and English not being my mother tongue, I can easily follow your explanations using the “pause” button (thank God 😊!). Thanks to you, Excel doesn’t seem inaccessible. So, thank you very very much, Leila 😊!! S. 🌸

[mdtechpk]

That is amazing Leila. You always bring something new, mostly excel users are unaware of this. Powerquery 👍

[brad_hedinger]

I can listen to Leila say the word "data" all day. Am I alone on this? Seriously though, Leila, your tutorials are amazing and tremendously helpful. Thank you for all you do!

[dogwal]

I keep learning something New Every Day with you. You are making me change the way the World looks Daily by data. You deserve the 5 🌟 starts. You are Awesome

[khalidmajeed]

WAOO---QUEEN IS ALWAYS QUEEN---distribute your knowledge to others get the result..

[luissilvacolindres]

This helped me so much on a report that I was breaking my head with and not being able to get around to what I was trying to accomplish. But since I learning and watching this short video, I managed and resolved my issue and nailed what I was trying to complete. Thank you so much for this!

[SakthivelS]

Madam, thank you very much for explaining in details the steps involved to create an automated report. After watching the above video, I started using the above method and I got very appreciated by our colleagues for my speed in reporting. All thank to you only. Thank you once again.

[nikiasf]

Hi! I have been watching your videos since 2020 and it's amazing how my excel skills advanced since!

[Juliesongbirdjournals]

I have purchased all of Leila's courses on Udemy. She is an amazing teacher and teaches with scenarios that I can use in my work.