Consolidate Multiple Excel Sheets Using Power Query Append

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

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.

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