Long ago BPQ (before Power Query), I wrote a similar post on how to combine data from multiple sheets but using VBA. Now that we have Power Query, combining data from multiple sheets into a single sheet has become so much simple (& not to mention code free).

In this step by step tutorial I’ll share with you that how can you append data from multiple sheets to a single sheet using none other than Power Query

Let’s begin

 

Consider this Excel Workbook with Multiple Sheets

Combine Data from Multiple Sheets into a Single Sheet

For now I have the same data across all the sheets, only difference being the dates are different

 

Step 1 – Create a New Sheet called “Consolidated Data”

  1. I am sure I don’t have to explain how to create a new sheet, just rename it to “Consolidated Data” (you can name it whatever you like)
  2. Make sure to Save the file after you insert and rename the sheet

 

Step 2 – Connect the Excel file to Power Query

Combine Data from Multiple Sheets into a Single Sheet

  1. In the Data Tab
  2. Get Data Drop Down >> From File >> From Workbook
  3. Direct the navigation to the path where the workbook is saved
  4. Power Query will give you a list of all sheets in the workbook.
  5. Since we want to combine data from all sheets, click on the Excel file name (rather than choosing any particular sheet)
  6. The sheet names get loaded in Power Query

 

Step 3 – Combining Data from All Sheets into a Single Sheet

Combine Data from Multiple Sheets into a Single Sheet

  1. Since we want to combine data from only on the sheets, apply a filter to the “Kind” column and keep only “Sheet”
  2. Next Promote the Headers by adding a Custom Column
    1. Add Column Tab >> Custom Column
    2. Write following formula =Table.PromoteHeaders ( [Data] )
    3. A new column with “Table” values will be inserted
  3. Keep only the Custom column and the Name column and “Remove other Columns”
  4. Now we want to exclude the sheet “Consolidated Data” (where the data will be combined), filter that sheet out from the Name column
  5. Now expand the Custom column
  6. Change the data type of the columns if required
  7. Done!

 

Step 4 – Load the Data into Excel

  1. Make sure to click on Load to
  2. This will allow you to load the data into Consolidated Data sheet

 

DOWNLOAD THE EXCEL FILE HERE

 

Video Lover ?

More of such tricks..

  1. Combine Data from Multiple Excel Files into a Single File
  2. Change Pivot Table Fields Calculations with VBA
  3. Change Pivot Table Calculations using Slicers (using DAX Power Pivot)
  4. Three Smartest Ways to apply filters on Data
  5. Filter Data with Multiple Criteria using Power Query (and automate the process..)

 



Topics that I write about...






Download Smart Ebooks on
Excel and Power BI