If you want to combine data from multiple worksheets in excel, you can read through this article to find exactly how to do it. Read to learn more

Consolidation is a common task we perform in Excel. By consolidation I mean, we have multiple worksheets and, in each sheet, we have a certain list with the same column headers and row headers but, with different values on each sheet. We would like to combine (consolidate) the values from the multiple source sheets into one single list.

You Can download the Exercise File and follow along by clicking on the download button

We have 2 different scenarios, that require using 2 different tools in Excel.

Scenario one: Identical layout

Example:

We have a list of products in Column A and the sales per Region for each product in columns B:E then I have a Total column F and a Total Row # 9.

I have the same exact layout in the same range A3:F9 for 12 different months (January December).

Since my data is in the same relative position on each sheet, or in other terms my data is in a predictable position: for example, Cell C6 in any sheet shows “Bay Leaf” sales for the “West” region, then I can use a SUM function.

The basic technique for summing by clicking on the same cell on each Sheet is considered bad practice, and will result in a long confusing formula, prone to errors if you miss a sheet or even duplicate entries.

=Jan!B4+Feb!B4+Mar!B4+Apr!B4+May!B4+Jun!B4+Jul!B4+Aug!B4+Sep!B4+Oct!B4+Nov!B4+Dec!B4

A little better technique is to group sheets while creating a SUM function, by selecting the leftmost sheet >> Press SHIFT >> click on the rightmost.

But I want to show you the easiest method to consolidate multiple sheets without the hassle of grouping:

In the destination worksheet “Summary”, select the entire range where you want to see the consolidated values B4:F9

Without clicking anymore, start typing an equal Sign “=” , it appears in B4 since it is the active cell.

Type SUM, and hit Tab to select it from the intellisense list of Excel.

Now instead of grouping the sheets, and because I want to add cell B4 from the 12 source sheets, I will use a wild character asterisk “*” which is a replacement character that means All Sheets.

The asterisk should be included in single quotation and followed by an exclamation mark =SUM(‘*’!

After the exclamation mark we type the cell reference corresponding to the cell I want to consolidate from all the source sheets, then close the bracket for the SUM function.

=SUM(‘*’!B4)

To populate the function in the entire selected range >> Hit CTRL +Enter

That is spectacular!!

If you look at the formula Bar now it shows

SUM(Jan:Dec!B4)

As if I grouped the sheets but with a much simpler technique

Scenario 2: Different Layout on each sheet

This situation is way more complicated because the source data is different on each sheet. I’m not talking about different values (Like in Scenario One), I’m talking about different row/ column labels, different number of records and different locations in the worksheet.

In our example we have a list of products in Column A and the Quarterly sales per product in columns B:E then I have a Total column F and a Total Row # 9.

We received data from four regions: East, West, North, South and we would like to consolidate the sales from the 4 regions into one single summary sheet. The problem is, the products on the different sheets are not in the same order, sometime different products and maybe also different locations on the worksheet. Quarters are also arranged differently. This makes it impossible to consolidate the multiple sources by using a Formula or a Function.

We have a tool that comes for rescue, called the Consolidation Tool, and is available on the Data Tab of the Ribbon.

Select the destination worksheet where you want to see the consolidated result. You already noticed there is no setup created upfront, it’s the consolidation tool that’s going to create the setup for you (unlike Scenario one).

How does the consolidation tool work?

Unlike a Formula or a Function where numbers are added regardless of what they represent, the consolidation tool looks at the column header and the row header, when it finds the same exact combination, it grabs the number at the intersection and add it to the next match and so on. This explains why it can deal with different data sizes, order, and location.

Let’s use the consolidation tool

1.  In the destination worksheet “All Regions”, select the destination cell (say A4).
2.  On the Data Tab of the Ribbon >> Click on Consolidate >> a dialog box opens.
   
3.  The top drop list in the Consolidate dialog box, enables you to switch between 11 different functions. So you are not limited to Sum which is the default.
4. Put your blinking cursor in the Reference box then, click on the first worksheet “East” and select the entire range (5 products) to consolidate (including column headers and row headers) then, in the consolidate dialog box >> Click Add. 
5. Click on the next worksheet Tab “West”. This sheet shows 7 products and a different order of products. Excel expects that you will be selecting the same exact range as in “East” so, it highlights that range for you upfront. However, we need to select the entire range (9 rows) to consolidate (including column headers and row headers) then, in the consolidate dialog box >> Click Add
6.  Click on the next worksheet Tab “North”. This sheet shows only 3 products and a different order for the Quarters, some of the products are not in previous regions. Excel expects that you will be selecting the same exact range as in “West” so, it highlights that range for you upfront. However, this range is only 5 rows so, select the entire range to consolidate (including column headers and row headers) then, in the consolidate dialog box >> Click Add 
7. Click on the last worksheet Tab “South”. This sheet shows only 3 products and a different order for the Quarters and products. Excel expects that you will be selecting the same exact range as in “North” so, it highlights that range for you upfront. However, the required range is in a different location on the worksheet. Select the entire range to consolidate (including column headers and row headers) then, in the consolidate dialog box >> Click Add

8. The most important part of the project, is to ask the consolidate tool to compare the column headers and row headers prior to grabbing the number at the intersection, so, Check the 2 boxes for ‘Top Row” & “Left Column” >> hit OK

That’s magical…

If you understand better with video, we recommend you watch this video from our good friend to learn more on how to combine data from multiple worksheet in excel

The data from all sheets has been consolidated despite of all differences.

Note that in order to use this tool efficiently, all your Row/ Column Headers must be entered exactly the same without a minor difference (not even a space) otherwise the tool will perceive them as different items. That’s why, when using this tool, it’s better to enter your Row/ Column Headers by using Data Validation (i.e. A drop List)

Hope you find this article useful… spread the knowledge by sharing it to your social networks.

Write me in a comment which topics you want me to cover next.

Check the upcoming FREE webinars and book your seat as there is a limited availability. Click Here To Check