Pinterest Pixel

Consolidate Multiple Excel Sheets Using Power Query Append

Power Query is awesome! You will see why after viewing this tutorial. I get lots of queries... read more

Download Excel Workbook
John Michaloudis
Posted on

Overview

Power 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 (e.g. January Sales List, February Sales List, March Sales-List, etc), then we can easily use Power Query combine Multiple Sheets feature.

Follow the step by step tutorial guide on power query multiple sheets with free downloadable Excel workbook to practice.

Download excel workbookAppend-Multiple-Sheetsv2.xlsx

STEP 1: Make sure that each worksheet´s data is in an Excel Table by clicking in the data and pressing CTRL+T

Consolidate Multiple Excel Sheets Using Power Query Append

 

STEP 2: Click in each of the worksheets data that you want to consolidate and select:

Power Query > From Table

Consolidate Multiple Excel Sheets Using Power Query Append

 

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

Consolidate Multiple Excel Sheets Using Power Query Append

 

STEP 4: Select Power Query > Append 

Consolidate Multiple Excel Sheets Using Power Query Append

 

STEP 5: Choose the Three or more tables option

Consolidate Multiple Excel Sheets Using Power Query Append

 

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!

Consolidate Multiple Excel Sheets Using Power Query Append

 

STEP 7:This will open up the Query Editor once again.  Choose Close & Load.

Consolidate Multiple Excel Sheets Using Power Query Append

 

STEP 8: This will open up a brand new worksheet which will consolidate all the worksheets into one big Table:

Consolidate Multiple Excel Sheets Using Power Query Append

STEP 9: From this consolidate worksheet you can Insert a Pivot Table and do your analysis:

Consolidate Multiple Excel Sheets Using Power Query Append

This is how you can combine tables and use Power Query consolidate multiple worksheets feature.

Power Query is an extremely useful tool in Excel. You can learn more about it here.

Further Learning:

👉 Click Here To Join Our FREE Excel Pivot Tables & Excel Dashboards Webinar That Will SAVE YOU HOURS At Work & INCREASE Your Excel SKILLS!

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Founder & Chief Inspirational Officer at MyExcelOnline.com

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 Academy Online Course.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!