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.

Week 1 sales
Week 2 sales
Week 3 sales
Week 4 sales

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

Product names and regions are not in the same order 🤔

Make sure that no data list contains any blank rows or columns⚠️

Kasper Langmann, Microsoft Office Specialist

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.
Merge sheets - Go to the data tab
  1. Select “Consolidate” from the data tools group.

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

Dialog box to consolidate data
  1. 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”.

There are several functions to combine Excel sheets such as SUM, COUNT, AVERAGE, MAX, MIN, PRODUCT, etc.

As we want to combine data to get the total of multiple worksheets, we select the “SUM” function.

Kasper Langmann, Microsoft Office Specialist
Functions to merge data in Microsoft Excel
  1. 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.

Click collapse button to select source data to combine sheets
Selecting sales data in Week 1 sheet
  1. Click the expand dialog button and click the “Add” button. Then the data range will be added to the “All references”.
Click the Expand button

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.

All references from multiple sheets
  1. 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.
Adding row and column labels

You must make sure the unique labels are the same in merge sheets.

Microsoft Excel will show the unique labels as separate column headings if they contain any typos or differences⚠️

Kasper Langmann, Microsoft Office Specialist
  1. Check the box – “Create links to source data” and click “Ok”.
Combine data in Microsoft Excel - create link to source data

Do you want to manually update consolidated data in the master spreadsheet?

Then do not check the box – “Create links to source data”.

Kasper Langmann, Microsoft Office Specialist

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.

Summary 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.
Open all the Excel workbooks
  1. 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.

Following steps 1-3 and 6-7 that are used to combine data from multiple sheets in the same Excel workbook.
  1. 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.

Browse and select the folder path
All references show file path to each workbook.

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

Combined workbook - Merge workbooks

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

Yes, you can combine multiple files.

  1. Open all the files.
  2. Create a new Excel spreadsheet and select a cell where you want to have the upper left cell of the merged data.
  3. Click the “Consolidate” in the “data tab”.
  4. Select the function to combine multiple files.
  5. Pull data from multiple excel files by clicking the collapse button in the reference box. Click “Add” each time you select data from a new Excel spreadsheet.
  6. Click “Ok”.

It is one of the Microsoft Excel features to merge data into one file from multiple sheets or multiple files.

It can merge several sheets or several Excel spreadsheets easily and accurately into a new sheet.

  1. Copy the data from multiple sheets into one sheet.
  2. Select the data in the entire sheet.
  3. Go to the “Remove duplicates” in the Data tab.
  4. If you have copied headers from all the files, do not check the “My data has headers”.
  5. Click “Ok”. Now, data in separate sheets are combined into one sheet without duplicates.