Combine Data from Multiple Sheets in a Single Sheet

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

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

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

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

Video Lover ?

⬇️ Pop in your Name & Email to get the file!

Subscribe to Newsletter

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..)