How to use “Consolidate” to Merge Excel-files & Combine Workbook Data

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

Data consolidation is useful in cases where you want to summarize data from more than one source.

This source data could be within the same Excel workbook on different worksheets.

It could also be in one or more different workbooks altogether.

This is super easy to do in Excel! We simply use the ‘Consolidate’ feature to merge data. ‘Consolidate’ allows us to merge data from several worksheets and files.

Kasper Langmann, Co-founder of Spreadsheeto

Get your FREE exercise files

Before you start:

Throughout this guide, you need a data set to practice. In fact, you need 4 different data sets – and a main file.

I’ve included everything for you (for free).

Download it all right below!

Download the FREE Exercise File

There are 5 different exercise files you need for this tutorial.

Here’s each one:

Main Exercise File

Download it here

Regional Files

  1. North Region Data Example file
  2. East Region Data Example file
  3. South Region Data Example file
  4. West Region Data Example file

Consolidate data in Excel
in the same workbook

Let’s say we have a workbook that contains multiple sheets of sales data for some products by region.

Now we would like to consolidate each region into one main summary table.

This table gives us the sum total of all the products.

So, let’s take a closer look at our project!

Kasper Langmann, Co-founder of Spreadsheeto

We have sales numbers for the first three months of the year.

These numbers are for 4 different products, A, B, C, and D.

Each of four regions, North, South, East, and West have sales figures for all the products. Each region’s table is in its own worksheet.

North Region example
South region example
East region example
West region example

Now we can add a new worksheet tab and select the cell in which we would like our new consolidated table to start.

We will select cell ‘B2’ for the sake of consistency with the regional tables.

Paste in the template for the table to match the column headers and row labels of the regional tables. This is ultimately what we want when using the ‘consolidate’ tool.

Kasper Langmann, Co-founder of Spreadsheeto
Consolidate template

So, this is our template.

The next step is to click into cell ‘C4’, which is where we want the ‘Consolidate’ tool to insert our data.

Once cell ‘C4’ is highlighted, we go to ‘Consolidate’ in the ‘Data Tools’ section of the ‘Data’ tab in the ribbon.

Consolidate in Data Tools

We then click on ‘Consolidate’.

When we do so, the ‘Consolidate’ dialog box will appear.

This is where it all happens!

Consolidate dialog box

The first thing we notice is the ‘Function:’ drop-down menu.

Here we select how we want to consolidate our data.

There are several functions to choose from providing a lot of flexibility. Select ‘Sum’ since we need to combine the sales for all our regions to get a national total.

Kasper Langmann, Co-founder of Spreadsheeto
Sum from consolidate dialog box

The next order of business is to add our references for our consolidated table.

So, we now click on the ‘Reference:’ box. Once there, we will go to our worksheet that contains the data for our North division.

Select only the range of sales data not including column headers or row labels.

Kasper Langmann, Co-founder of Spreadsheeto
Range selection
Reference in Consolidate dialog box

Once the range is selected, its location now appears in the ‘Reference:’ input box in the ‘Consolidate’ dialog box.

Once we verify this is the correct range, we now click on ‘Add’ to the right of the ‘All references:’ field. Then our selection will appear in that field.

Kasper Langmann, Co-founder of Spreadsheeto
Selection appears

Simply repeat the same process until the data ranges for all four regions appear in the ‘All references:’ field.

One important thing to note at this point. Since we are working with data ranges/tables of the exact same size and shape, Excel does not require us to physically repeat the process.

Kasper Langmann, Co-founder of Spreadsheeto

So, you don’t need to select the data range for subsequent reference selections.

It automatically selects the same range so long as each subsequent worksheet has the same layout (i.e., the table begins at the same cell as ours at ‘B2’).

So, the next three steps for us are quite simple…

Navigate to the next worksheet and click on ‘Add’. At this point, it should look like the following figure:

Automatic selection

Now we click ‘OK’ and we have our consolidated table with the sum totals for each product by Month.

Consolidated table with the sum totals for each product by month

This is the simple method for consolidating tables of data.

In this, we got a single table that shows the sum for all tables being consolidated.

Note, that this consolidated table only contains static values.

The drawback to this static table is that when data in the regional tables changes, this table will not be affected.

Kasper Langmann, Co-founder of Spreadsheeto

If you want a consolidated table that updates if any changes are made to the reference table ranges, we must make one change to our approach…

Dynamic consolidation:
Creating links to source data

If you want our consolidated table to dynamically change, it’s actually quite easy!

Simply check the ‘Create links to source data’ box at the bottom of the ‘Consolidate’ dialog box.

Create links to source data

Notice that the consolidated table has some grouped rows of data that can be expanded.

These are the actual figures for each product. They have been imported into our new worksheet.

Kasper Langmann, Co-founder of Spreadsheeto

Our cells for the consolidation now contain ‘SUM’ formulas for those rows.

SUM inserted via consolidation
Look at specific SUM function in table

So that is the big difference between a static and a dynamic consolidation table!

There are some other features in the ‘Consolidation’ dialog box worth mentioning…

Kasper Langmann, Co-founder of Spreadsheeto

Let’s say you want to insert a consolidated table in a blank worksheet…

In that case, make sure to check the boxes for ‘Top row’ and ‘Left column’ in the ‘Use labels in’ section below ‘All references:’.

This inserts the column headers and row labels for us. This is super handy when you need some quick figures in a hurry and don’t want to fuss over the formatting.

Kasper Langmann, Co-founder of Spreadsheeto

It is as simple as adding a new worksheet and selecting a cell, say ‘B2’.

Like before, click on ‘Consolidate’ in the ‘Data Tools’ selection of the ‘Data’ tab.

We select our same source data.

Now you need to select the range that includes the column headers and row labels.

Range selection
Reference selection in consolidation dialog box

Once you have selected all four ranges of source data, make sure the checkboxes in the ‘Use labels in’ section are selected.

Then, click ‘OK’.

We now have a consolidated table with row and column labels!


Consolidate data in Excel
from different workbooks

You now know how to consolidate data from different worksheets within a single workbook.

There’s, even more, you can do!

Kasper Langmann, Co-founder of Spreadsheeto

Excel also gives you the opportunity to combine Excel files (or consolidate data) from other workbooks.

To merge multiple Excel files, first, open the relevant files.

Multiple Excel files

Once you have the files opened, go to a blank worksheet.

Select the cell where you want to insert the consolidated data table.

Here, I select ‘B3’ and then open the ‘Consolidate’ dialog box.

This new worksheet is completely blank and without formatting. Therefore, check both boxes for ‘Top row’ and ‘Left column’.

Kasper Langmann, Co-founder of Spreadsheeto

Also, go ahead and make this a dynamic summary table by checking the ‘Create links to source data’ box.

The next step to merge multiple Excel files is a bit different than before.

This time you need to click on ‘Browse…’ to locate our files that we want to merge. We then select the first file that we want to merge.

Kasper Langmann, Co-founder of Spreadsheeto

However, we are not done with our ‘Reference:’ selection because it has no cell range reference yet.

Our range of data that includes the column and row labels is ‘B3:E7’.

Select that range in our destination worksheet and then click ‘Add’ – and you’re done!

That said, there is a more efficient way to select our ranges. Hang on, we’re about to go deep!

Kasper Langmann, Co-founder of Spreadsheeto

Advanced tip: using Named Ranges

Now is the time to suggest a bit of extra skill to make our quest to merge multiple Excel files a bit simpler.

Instead of selecting the range of cells we need from every workbook we are drawing our source data – we can do this one time in each file and name the range.

Kasper Langmann, Co-founder of Spreadsheeto

Pro-tip: If you want to go even more advanced, read my guide to dynamic named ranges on Lifehacker.

Furthermore, since each is a separate file, we can use the same name in each to make things even simpler.

So, let’s go to our ‘North’ region file and select the range that includes the column and row labels.

North region range selection

We now want to click into the ‘Name Box’ to the left of the ‘Formula Bar’ where we currently see ‘B3’. ‘B3’ is now highlighted.

Then we type in our new range name. We will call it ‘Range1’.

Once we click enter, ‘Range1’ has now replaced ‘B3’. Click on the drop-down arrow on the right-hand side of the ‘Name Box’.

Kasper Langmann, Co-founder of Spreadsheeto

Now you see ‘Range1’ as the only available selection. When you select it, the range is highlighted on our worksheet.

A1 is Range1
Range selected on click

Repeat the exact same process in the other three source files naming the exact same range as ‘Range1’.

Now that we have named ranges for our source files, we can combine Excel files much more efficiently.

Kasper Langmann, Co-founder of Spreadsheeto

Back to our ‘Consolidate’ dialog box.

Recall that we need to select our first source data file, the ‘North’ region workbook. Do that now.

Once the file is shown in the ‘Reference:’ input box, we type ‘Range1’ after the exclamation point.

Reference path to file

We then click ‘Add’ and repeat the process for the other three workbooks.

Multiple workbooks selected

Once we click ‘OK’ we have a new summary table that has our data from the four separate workbooks now combined 🙂

Four workbooks' data combined

Wrapping up…
Here’re your next steps!

The ‘Consolidate’ feature is one of many methods to merge data – or files –  in Excel.

The previous examples have shown you how to consolidate data in Excel with a very straight forward method.

Now, go check out how to merge cells in Excel right here.

Consolidation is quick and easy to use when summarizing data is the need at hand. With a little practice, this method of data consolidation becomes second-nature to you very soon!

Kasper Langmann, Co-founder of Spreadsheeto