Splitting data over multiple sheets is perhaps one of the worst Excel crimes I see. It’s a crime because it breaks the rule that source data should be in a tabular format. Tabular data is what we need for PivotTables and many functions like SUMIFS, COUNTIFS, INDEX, VLOOKUP etc.
To have your data in any other format is just going to make your Excel life difficult. Anyhow, that’s enough ranting about data layouts. I do enough of that here.
Thankfully we can easily consolidate Excel sheets with Power Query in just a few clicks of the mouse.
Enter your email address below to download the sample workbook.
Watch the Video
Power Query Consolidate Excel Sheets
Let’s take the file below that has a separate sheet for each salesperson’s order data (Buxton, Maxwell, Jarvis and Everton), and another sheet containing ‘Other Stuff’ 1:
1. The ‘Other Stuff’ sheet simply represents a typical file that has another sheet(s) containing information that isn’t source data2. I included this sheet so that I can address how to handle these sheets when consolidating source data in Power Query.
2. Source Data is data that I want to include in my consolidated data set. It’s the source of your analysis.
Key Points to Using Power Query Consolidate Excel Sheets
Ideally, we want the source data on the four salesperson’s sheets merged into one sheet because that’s going to allow me to summarise it with a PivotTable or easily analyse it with any of the built in Excel functions, which is not possible when the data is spread across multiple sheets.
The technique I’m going to show you here requires:
- The data on the sheets I want to consolidate are formatted in an Excel Table or has a been given a Named Range. This is required for Power Query to find the data.
- The table structure (column names) on each sheet you want to consolidate are the same.
- The name of the Tables or Named Ranges use distinct nomenclature that is different to any tables/ranges you don’t want to include. E.g. my salesperson tables all begin with ‘Orders…’, as you can see in the Name Manager below:
Note: The ‘TotalOrders’ table is on the ‘Other Stuff’ sheet and the name purposely doesn’t begin with ‘Orders’ because it isn't part of my source data. This will allow me to filter the tables and consolidate only those that have names beginning with ‘Orders’.
Consolidating Excel Sheets using Power Query
Ok, now the housekeeping tasks are out of the way, let's look at how we can use Power Query to grab the data off the salesperson sheets and merge it into one table:
Step 1:Create a new blank query in the file containing the sheets you want to consolidate. For Excel 2016 or Office 365 take the following steps:
In Excel 2010 or 2013 take the following steps:
Note: If you don’t see the Power Query tab in Excel 2010 or 2013 you can download it here.
This opens the Power Query Editor window.
Step 2:In the formula bar type:
As shown below:
Note: Power Query functions are case sensitive.
Press ENTER. This returns a list of the Excel Tables, Named Ranges and Filtered Lists in your file:
Step 4:Filter the ‘Name’ column for items that ‘Begins With…’:
In the ‘begins with’ field enter ‘Orders’:
Using a consistent nomenclature allows me to add more ‘Orders…’ tables to my file in future and Power Query will automatically include them upon refresh. Of course, if I’m sure I’ll never add any more sheets that I want to include in the consolidated table then I could simply use the check boxes in the Filter list to select the tables I want.
Step 5:Now I only have the tables I want to consolidate, I can click the double headed arrow on the ‘content’ column to expand the data:
Note: If you don’t deselect ‘Use original column name as prefix’ each column header will be prefixed with ‘Content’. E.g. Content.Country, Content.Salesperson etc.
And with that I have my data consolidated into one table:
Notice that the last column contains the name of the source Table. You can click on this column header and DELETE it if you don’t want it.
Step 6:Set the Data Types. It’s important to tell Power Query what type of data you have in each column; dates, text, decimal numbers etc. To do this, click on the icons in the top left of each column and select the data type from the list:
Note: This isn’t formatting, as that’s done in the Excel sheet. These are data types required by Power Pivot (if you’re using it) and will help Excel identify what cell formatting it can automatically apply if any.
Step 7:We’re almost done. Give your query a name. This name will be inherited by the Excel or Power Pivot Table, so choose carefully avoiding the nomenclature for the tables you are consolidating. i.e. don’t begin with ‘Orders’, otherwise this table will be included in the query and you’ll double count your data!
Step 8:Now we’re ready to load the data into Excel or Power Pivot. On the Power Query Editor Home tab > Close & Load > Close & Load To…:
Step 9:Choose the destination for your data:
Note: Excel 2010 and 2013 will not have PivotTable Report or PivotChart, as listed above. Excel 2010 will not have the ‘Add his data to the Data Model’ option.
I chose to load my data into a Table in a new worksheet, as you can see below:
It’s important to note that Power Query does not alter the original tables. It merely takes a copy of the data and creates a new table for you to work with. If data in the original tables gets updated, you can simply refresh the query (Data tab > Refresh all) and it will update the consolidated table.
You can also get the data from other Excel files and create a new file for the consolidated data. This helps keep your file size manageable if you’re working with a lot of data.
Learn More Power Query
Everyone should learn Power Query. It really is a game changer for automating tasks and making light work of laborious jobs.
So, if you’d like to learn Power Query take a moment to check out my course: