Free Practice WorkbookPower Query is awesome! You will see why after viewing this tutorial. I get lots of queries from my blog readers asking me if there is a way to easily combine tables from multiple sheets in the same workbook with Power Query With Power Query, the answer is YES! If you have multiple Excel worksheets that are in the same format and their underlying differences are their values and dates (Examples are January Sales List, February Sales List, March Sales-List, etc), then we can easily use Power Query combine Multiple Sheets feature.
Key Takeaways:
- Seamless Data Consolidation: Power Query allows you to combine data from multiple sheets into a single table effortlessly using the Append Queries feature, eliminating manual copy-pasting.
- Dynamic Updates: Once set up, Power Query automatically updates the consolidated data when changes are made to the source sheets, saving time and ensuring accuracy.
- Handling Large Datasets: Power Query is optimized to process large amounts of data across multiple sheets efficiently, making it ideal for consolidating complex datasets into a single, unified view.
Table of Contents
How to Consolidate Multiple Excel Sheets
STEP 1: Make sure that each worksheet´s data is in an Excel Table by clicking in the data and pressing CTRL+T
STEP 2: Click in each of the worksheets data that you want to consolidate and select:
Power Query > From Table
STEP 3: This will open up the Query Editor and all you have to do here is press Close & Load.
NB: Make sure to do Step 2 & 3 for each worksheet you want to consolidate
STEP 4: Select Power Query > Append
STEP 5: Choose the Three or more tables option
STEP 6: Add the tables to append from the Available Tables (from the left) to the Tables to Append (to the right) by selecting and pressing the Add button.
You can also organize the order that you want your consolidated table to appear by moving the Tables up or down
Press the OK button!
STEP 7:This will open up the Query Editor once again. Choose Close & Load.
STEP 8: This will open up a brand new worksheet which will consolidate all the worksheets into one big Table:
STEP 9: From this consolidate worksheet you can Insert a Pivot Table and do your analysis:
This is how you can combine tables and use Power Query consolidate multiple worksheets feature.
Frequently Asked Questions
How do I use Power Query to consolidate data from multiple sheets?
To consolidate data, go to the Data tab and select Get Data > From Workbook. Import your Excel file, and Power Query will display all sheets. Load each sheet into Power Query, then use Append Queries to combine the data into one table. Finally, click Close & Load to export the consolidated table back to Excel.
Can I consolidate sheets with different column names?
Yes, but Power Query aligns data based on column headers. If the column names differ, Power Query will place the unmatched data into separate columns. To resolve this, rename the columns in each sheet before appending, ensuring consistency.
What happens if I add new data to the source sheets?
Once the Power Query Append setup is complete, any new data added to the source sheets will automatically reflect in the consolidated table when you refresh the query. To refresh, simply click Data > Refresh All.
Is it possible to consolidate sheets from multiple workbooks using Power Query?
Yes, Power Query can append data from multiple workbooks. Use the Get Data > From Folder option, select the folder containing the Excel files, and Power Query will combine all sheets with similar structures.
Can I filter or clean the data while consolidating sheets in Power Query?
Absolutely! Power Query allows you to clean, filter, and transform data before consolidating. You can remove duplicates, filter rows, or apply formulas to standardize the data, ensuring the consolidated output meets your requirements.
John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship MyExcelOnline Academy Online Course.
30 Days - Full Access 
