You are reading the article Power Query Consolidate Excel Sheets updated in September 2023 on the website Lifecanntwaitvn.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested October 2023 Power Query Consolidate Excel Sheets
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.
Workbook Download
Enter your email address below to download the sample workbook.
By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.
Download the Excel Workbook . Note: This is a .xlsx file please ensure your browser doesn’t change the file extension on download.
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:
Written Instructions
Step 1:
Create a new blank query in the file containing the sheets you want to consolidate. For Excel 2023 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:
=Excel.CurrentWorkbook()
In the formula bar type:
As shown below:
Note: Power Query functions are case sensitive.
Step 3:
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.
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:
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 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:
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 QueryEveryone 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:
It’s available for accountants and other professionals who require CPE credits through Excel University with 12 CPE credits.
Or if you don’t need CPE credits you can get it for less here on my site here.
You're reading Power Query Consolidate Excel Sheets
Update the detailed information about Power Query Consolidate Excel Sheets on the Lifecanntwaitvn.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!