How To Merge Two Spreadsheets in Excel (Consolidate) – 2024

We frequently need to summarize data from multiple Excel files or Excel sheets.

It is difficult to use formulas to combine multiple Excel files into a single sheet.

Formulas are prone to mistakes 🚧

Excel provides clever techniques for combining data from multiple sheets or excel files into a single sheet.

One of them is Excel consolidation 😍

You can practice with me by downloading the Excel workbooks here.

Let’s get started. 👍

Combine Excel sheets with ‘Consolidate’

First, let’s learn how to combine data from multiple sheets.

Here we have regional sales data for 4 weeks.

Each week’s sales are in its own Excel sheet in the same workbook.

Product names and regions are not in the same order 🤔

Now we want to combine all the data and create the monthly sales table (for all 4 weeks).

We will add a new sheet as “Master”. This sheet will serve as the destination sheet for the consolidated data.

1. Select a cell in that master sheet to place our sales summary and go to the data tab.

2. Select “Consolidate” from the data tools group.

Then, you can see the “consolidate” dialog box.

3. Select the consolidate method from the “Function box”.

In this example, we want to get the total of all the sheets. So, we select “Sum”.

4. Click the collapse button of the reference box.

Then click on a single worksheet that contains merging data and select the data in that Microsoft Excel sheet. In this case, we first select sales data in the “Week 1” sheet.

5. Click the expand dialog button and click the “Add” button. Then the data range will be added to the “All references”.

Repeat this step to get all the data in all the worksheets.

Pro Tip!

When you click on the other Excel sheets to get merging data, look at the reference!

Microsoft Excel will apply the previous cell range for the new worksheet as the reference.

If the data in all the sheets are in the same structure and the same layout, you can just click the “Add” button after you select the next worksheet 🥳

Now, it should look like this.

6. Check the boxes for “Top row” and “Left column” to add labels to the merged data table. Then all column headers and row labels will be copied to the merged data table.

7. Check the box – “Create links to source data” and click “Ok”.

Microsoft Excel will generate a consolidated data report in a single sheet by combining all the data of the selected sheets of the Excel file.

Now you know how to combine data in one file.

Combine multiple Excel files with ‘Consolidate’

In reality, most of the time we collect data in multiple files.

So, we need to combine multiple excel files to get the consolidated data 🤨

In Microsoft Excel, we can combine data from multiple workbooks as well 🤗

1. To merge Excel files, first, open all the Excel files to be merged. Before you start the process, it is better to save all the files in the same folder.

2. Create a new workbook and follow steps 1- 3 and 6-7 to combine excel sheets in the new Excel workbook.

Now, the “consolidate” dialog box should look like this.

3. Click the collapse button in the reference box and select the data ranges in Excel files one by one.

If the Excel spreadsheets are closed, click “Browse…” to locate the workbooks. You can see the folder path to the Excel workbook in the reference box. Then, manually enter the cell range after the exclamation mark of the folder path.

When you click “Ok”, you can get the data from separate files to one workbook.

Pro Tip:

If you want to quickly go to the cell ranges of each workbook, you can apply name ranges to those ranges.

It is superfast and more accurate💪🏻

You can read the named ranges article to learn more about it.

That’s it – Now what?

You no longer need to manually enter formulas in the master spreadsheet to prepare a summary 🤩

This is only one of the many ways to merge data from several Excel sheets or files.

Do you know that you can combine multiple worksheets using VBA codes?

Yes, you can combine multiple excel files using VBA too.

You can access my free 30-minute online course on “Vigorous VBA” by clicking here 🧑🏻‍🎓

Other resources

The Consolidate tool in Microsoft Excel appears to be a Pivot Table.

However, Pivot Tables offer more flexibility than Excel Consolidate. Therefore, use Pivot Tables if you need more flexibility to make changes to the summary table.

You can read our articles about power query and VBA codes and use them to merge files in Microsoft excel 👍

Frequently asked questions