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 consolidate multiple Excel worksheets into one.

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 consolidate all the worksheets into one.

Here is how…

DOWNLOAD EXCEL WORKBOOK

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

excel table

 

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

Power Query > From Table

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

close & load

 

STEP 4: Select Power Query > Append 

Append

 

STEP 5: Choose the Three or more tables option

three or more table

 

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 organise the order that you want your consolidated table to appear by moving the Tables up or down

Press the OK button!

append2

 

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

close & load2

 

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

consolidated table

 

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

pivot table

Append Multiple Sheets

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

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Share on Google+

Related Posts

Advanced Editor In Power Query Power Query can let us perform a lot of complex steps with our data.However wouldn't it be fun if you could understand better what is happening under the hood? Come join me as we take a look into the Advanced Editor!We will use the Index Column Post as our starting point....
Unpivot Data Using Excel Power Query Power Query is a free add-in created by Microsoft for Excel 2010 (or later) and you can download and install it for Excel 2010 and 2013:Click to see tutorial on how to install Power Query in Excel 2013Click to see tutorial on how to install Power Query in Excel 2010In...
Import Data from XML Using Power Query or Get ... Power Query or Get & Transform (In Excel 2016) lets you perform a series of steps to transform your Excel data.But what if your data source is not in your Excel spreadsheet?It's very common nowadays to get data imported from a company's accounting or sales system in t...
Replace Values Using Power Query  Power Query lets you perform a series of steps to transform your Excel data.  One of the steps it allows you to take is to replace values easily.DOWNLOAD EXCEL WORKBOOK Let’s go through the steps in detail:STEP 1: Select your data and turn it into a...