One of the common headaches I have come across in the course of my career as a Trainer in Excel Dashboards is the question of how to combine data from multiple worksheets into one master worksheet, so that you can analyze smartly.
Many general users of Excel retire to the time consuming method of copy and pasting, which works, if you have few worksheets. It doesn’t have to be that way! Learn at least 3 ways at your disposal to combine multiple worksheets into a consolidated position.
The Consolidate feature in Excel comes in handy when the goal is to aggregate the values in individual worksheets, but often falls short when the data contains dimensional, text-based columns.
I think the next version of Excel needs to take into account this pain point of many Excel users, by including a simplified way to combine worksheets.
Consider the following scenarios that can necessitate combining various worksheets include:
- For a teacher, this would mean merging data for various streams of the same grade into one, to help in ranking.
- You have received budgets from various business units and you want to have a consolidated position.
- Vehicle sales data coming from different outlets needs to be consolidated into a single worksheet.
- You have extracted 5 P&Ls from your financial reporting system and your task is to generate a summarized income statement from these individual profit centers.
- The ministry of health would want to consolidate the number of patients served from various dispensaries from different counties.
The individual worksheets need to be identically structured, at least with respect to the column headings.
Please note that the challenge at hand is not to perform a lookup of additional columns that are stored in different mapping tables.
This is a 3 part series that will equip you with all that you need to combine data coming from multiple worksheets or external sources into a single worksheet. You can use the links below to jump to your preferred starting point.
- Using Excel Macros/VBA to Combine Worksheets
- 3 ways to consolidate data in Excel
- How to combine data using Power Query in Excel 2013/2016
Learn 3 ways to combine data from multiple worksheets: Use Power Query, Use consolidate feature, Use a macro (VBA) #DataCleansing
How to combine data from multiple worksheets using Power Query: Query Editor in Excel 2013/2016
Power Query is a powerful way to combine data from multiple sheets into a master list for further analysis. It enhances Business Intelligence for Excel by helping you combine, shape analyze and visualize data coming from a wide variety of sources.
With MS Excel 2010, Power Query is not inbuilt but comes as a free add-in that you download from the Microsoft website. For MS Excel 2013 Microsoft Office Professional Plus, the Power Query comes inbuilt but you need to enable it among the Add-ins.
Power Query in MS Excel 2016
Excel 2o16 comes pre-installed with Power Query. With this version of Excel, it has been named as Get & Transform >> New Query.
Combine Data Using Power Query
In the example at hand, we have sales data for a fictitious company that operates in two regions: Nairobi and Mombasa. As seen in figure 2 below, the data is stored in 2 worksheets for each region.
In a third worksheet, the Finance Manager of the company seeks to analyze the data but first, he has to combine the data sets.
The method discussed in this post uses the Get & Transform feature in MS Excel 2016. The steps (similar to Excel 2013/2010) are as follows…
Step #1: Format the data as an Excel Table
First, ensure the source worksheets are formatted as Excel tables -that is, click any cell inside the data, then press CTRL+T (or CTRL+L). Similarly, you can select a cell in the data, and from the Home tab, select Format as Table and choose your preferred style.
Having formatted the data as an Excel table, you will notice an extra contextual Design menu appears on the Excel Ribbon. From this menu, you can rename each of the tables to make it easy to work with.
We shall name the Mombasa and Nairobi tables as MombasaSales and NairobiSales respectively.
Step #2: Using Get & Transform
From the Data menu, in the Get & Transform group, click From Table.
Step #3: Create Custom Column in Query Editor
Step 2 above will open up the Query Editor window. In this step we shall create a custom column that will be used to identify the dataset you have loaded. The custom column is added from the “Add Column” tab in the Query Editor.
If you need to do further manipulation on the data, you can take advantage of the rich features in the Query Editor.
For this example, adding the region column is all we needed to do.
Step #4: Next, press Close & Load To…
The Close & Load To… option opens the Load To dialog box shown below. In this window, we will choose Only Create Connection. The “Table” option would load a duplicate table in your Excel worksheet. We do not want this!
Press Load to complete the process.
Step #5: Repeat steps 2, 3 & 4 for each individual worksheet
In this example, we shall repeat the steps above for the Mombasa sales data.
In step 3 though, the formula to create the custom column will be = “Mombasa”. The column heading need to be similar, i.e. Region.
Step #6: Append the Queries
This is the step that helps us combine the individual worksheet datasets into a single worksheet.
Go to Data menu and select New Query. In the short-cut menu, choose Combine Queries >> Append.
This opens the dialog box shown below. Choose the Three or more tables option.
Select each of the tables and press Add to move the table to the right side of the dialog box. Once through click OK.
When you press OK, Excel opens the Query Editor window to allow you make further adjustments.
Step #7: Rename Query and Close & Load To…
On the Query Editor, we shall rename the newly created table from the default “Append1” to an appropriate name. In this example we have called this “Combined Sales”.
Next, press Close & Load To… option from the Home tab on the ribbon.
The dialog box below will pop up.
This time round, we shall load the combined dataset into a new Table, and in a New Worksheet. You can choose existing worksheet option if you already had created a blank worksheet where your data should go.
When you press Load, Excel will create a new worksheet with the original datasets combined.
You can now go on and do your analysis on based on this consolidated worksheet.
Step #8: Bonus Step
One of the advantages of using the Power Query approach to data consolidation is that you can access the Query Editor and make adjustments that you may have omitted as you did the first process.
For instance, you may realize in this example that the sales date column that was loaded into the combined worksheet comes with the time component which you may not need.
To avoid displaying the time component, select a cell in the combined worksheet.
On the Excel ribbon, you will see a contextual Query menu. Click Edit.
This will open the Query Editor window. On the Query Editor, ensure you have selected the Combined Sales query and the Sales Date column is selected.
Select Transform menu >> Date >> Date only.
Having done this, press Close & Load from the Home menu of the Query Editor window.
The sales date column is updated accordingly.
Further, when you make changes on the original worksheets and click Refresh from the Data menu, the changes are immediately reflected on the combined worksheet.
There is a revolution in the self-service Business Intelligence that is coming as a result of the Power BI Suite (Power Query, Power Pivot, Power View, Power BI Desktop, Power BI Web version) provided by Microsoft. I personally believe these features are a must know for all Excel Analysts as well as anyone interested in making smarter business decisions. There are a lot of resources to learn these tools and it pays off. Mynda Treacy’s article is such a resource that will solidify your understanding of the Append feature.
The beauty of Power Query is that once set, you only need to refresh and not repeat the process! #DataCleansing #ExcelDashboards
To me, the Power Query combined with VBA form powerful data shaping tools.
Do it yourself
The best way to learn is to practice yourself, so download the workbook used to for this post to test your understanding.
Share this with your friends!