In this blog post, I am explaining the Power Query Method to combine data from multiple worksheets. If you are a person who deals with data in Multiple Worksheets, You should definitely try this one.
Following is the screenshot a workbook which contains 7 worksheets called Violet, Indigo, Green, Blue, Orange, Yellow and Red.
These worksheets contains the Scorecards of more than 70 candidates who are divided into 7 Groups called Violet, Indigo, Green, Blue, Orange, Yellow and Red.
Now, I want to combine the data from these 7 worksheets into a single worksheet for further analysis. I will be using Power Query for this task and let’s see how to do that.
Please note that the method explained in this post is having a Static nature and won’t update for addition of new sheets. If you are expecting addition of new sheets into the same workbook, Click on the following link to read about a more dynamic method.
How to Combine Data from Multiple Worksheets (Tables) using Power Query
Step 1:
Go to the Data tab of the Excel Ribbon, Get Data > From File > From Workbook
Step 2:
Using the Import Data dialog, select the Excel file containing the worksheets to be combine and Click on Import.
Step 4:
A new dialog called Navigator will appear and the list of worksheets present in the specified the file will be listed in the left side bar.
Mark the checkbox against the label, Select Multiple Items > Mark the checkboxes against worksheets to combine > Click on Transform Data
Data from 7 different sheets will be loaded into the Power Query Editor as 7 different queries.
As you can the sheets are loaded queries in the Alphabetical order of sheet names, but if required we can rearrange them manually. Here I have rearranged the Queries in the order Violet, Indigo, Green, Blue, Orange, Yellow and Red.
Before combining these queries into a single query, in other words, before combining these data present in different queries, we have to load these queries as connections. For that,
Step 5:
Click on the split button called Close & Load in the Home tab > Close & Load To…
In the Import Data dialog, Select Only Create Connection and Click OK
In the Queries & Connections pane, you can see seven Queries loaded as Connection only.
Step 6:
Right-Click on any of the query and select Edit to activate the Power Query Editor
Step 7:
Now, to combine the 7 queries,
Click on the split button for Append Queries in the Home tab of the Power Query Editor > Append Queries as New
In the dialog for Append, Select Three or more tables > Select the tables to append from the list of Available tables > Add it to the list of Tables to append > Click OK
A new Query called Append1 is created and data from 7 sheets are combined into this single query.
Also note the following formula shown in the formula bar. This single formula can combine 7 Queries called Violet, Indigo, Blue, Green, Yellow, Orange, Red into a single query
= Table.Combine({Violet, Indigo, Blue, Green, Yellow, Orange, Red})
Step 8:
To load this data into an Excel sheet, Click on the split button called Close & Load in the Home tab > Close & Load To…
In the dialog called Import Data > Select New worksheet
A new worksheet is created which contains the data from 7 different worksheets present in the selected workbook.
Whenever you make any change in the sheets referred in the Query called Append1, Click on the Refresh All button in the Data tab for the Query (output table) to update.