How to Use Excel Data Consolidation to Merge Worksheets

In business environments, data is rarely stored neatly in a single spreadsheet. A manager might receive separate monthly expense reports from four different departments, or regional sales data from five different offices. Manually copying and pasting this information into a master summary sheet is tedious and highly prone to error.

Instead of manually calculating totals or writing complex VLOOKUP formulas, you can use the Data Consolidation tool. This built-in Excel feature automatically pulls data from multiple different worksheets (or entirely different workbooks) and merges it into a single, clean summary table.

Requirements for Data Consolidation

Before you begin, ensure your data is structured properly. The Data Consolidation tool works best when:

• The data across all worksheets is formatted as standard lists or tables.
• Each column has a clear header (e.g., “Product Name”, “Q1 Sales”).
• The data does not necessarily need to be in the exact same order on every sheet, provided the column headers and row labels match. Excel will use the labels to intelligently match and merge the data.

How to Consolidate Data

For this example, imagine you have three separate worksheets named “London”, “New York”, and “Tokyo”, and you want to combine their sales figures onto a fourth sheet named “Global Summary”.

1. Open your Excel workbook.
2. Click on the destination worksheet where you want the final, merged data to appear (e.g., your blank “Global Summary” sheet).
3. Click on the specific cell where you want the upper-left corner of the consolidated table to begin.
4. Click the Data tab in the top ribbon menu.
5. In the “Data Tools” group, click the Consolidate button.

The Consolidate dialogue box will appear on your screen. This is where you tell Excel exactly what to merge.

Step 1: Choose the Function

By default, Excel will “Sum” (add) the numbers together. If you want to find the average sales across all regions instead of the total, click the Function dropdown and select Average (or Count, Max, Min, etc.).

Step 2: Add the Data Sources

1. Click inside the Reference box.
2. Click the up-arrow icon on the right side of the Reference box to collapse the dialogue window, making it easier to select cells.
3. Navigate to your first data source (e.g., click the “London” tab at the bottom of the screen).
4. Click and drag your mouse to highlight the entire table of data, including the column headers and row labels.
5. Click the down-arrow icon to expand the dialogue window again.
6. Click the Add button. The range will appear in the “All references” list.
7. Repeat this process for your other worksheets (e.g., navigate to “New York”, highlight the table, click Add).

Step 3: Tell Excel How to Merge

Look at the bottom of the dialogue box under the “Use labels in” section. This is the most crucial step.

• Check the Top row box so Excel knows not to add your column headers together.
• Check the Left column box so Excel knows to match the data based on the names of the items (e.g., ensuring “Apples” from London are added to “Apples” from Tokyo, even if they appear in different rows).
• Optional: Check Create links to source data. If you check this, your summary table will automatically update if someone changes the original data in the London or Tokyo sheets. If you leave it unchecked, the summary table will be static.

Finalising the Consolidation

Once you have added all your references and checked the label boxes, click OK.

Excel will instantly process the information from the separate worksheets and generate a brand new, merged table on your summary sheet containing the consolidated totals.