How to Use Excel Data Consolidation to Summarize Worksheets

Imagine you manage four regional sales teams: North, South, East, and West. At the end of the month, each team sends you their own Excel workbook. Every workbook is formatted identically, with January sales in column B, February in column C, and so on. Your job is to create a master “Company Total” sheet that sums the numbers from all four regional sheets. While you could write a massive formula (e.g., =North!B2 + South!B2 + East!B2 + West!B2) and drag it across the grid, this is incredibly tedious and prone to error. The faster, built-in solution is the Data Consolidate tool.

Step 1: Understand the Requirements

Data Consolidation works best when your source data is organized identically.

• Consolidation by Position: If every sheet has exactly the same layout (e.g., “Apples” is always in row 2, “Oranges” is always in row 3), Excel simply stacks the sheets on top of each other and adds the cells that physically align.
• Consolidation by Category: If the sheets contain the same labels but in different orders (e.g., the North team lists “Apples” in row 2, but the South team lists “Apples” in row 5), Excel can look at the row headers, match the words, and add them together automatically.

Step 2: Prepare the Master Sheet

1. Open a new, blank worksheet. This will be your Master Summary sheet.
2. Click on the cell where you want the consolidated data to begin (e.g., cell A1).

Step 3: Launch the Consolidate Tool

1. Navigate to the Data tab on the ribbon.
2. In the “Data Tools” group, click the Consolidate button (it often looks like two blue grids combining into one).

Step 4: Configure the Consolidation

The Consolidate dialog box will appear.

1. Function: Choose how you want to combine the numbers. “Sum” is the most common, but you can also choose Average, Count, Max, or Min.
2. Reference: This is where you point Excel to the data. Click the small arrow icon next to the Reference box.
3. Navigate to your first regional sheet (e.g., “North”) and highlight the entire block of data you want to include, including the row and column headers.
4. Click the Add button in the dialog box. The range will appear in the “All references” list.
5. Repeat this process for the South, East, and West sheets. Highlight the data, then click Add.

Step 5: Consolidate by Category (The Magic Step)

If you just click OK now, Excel will consolidate by position, ignoring the text labels and just adding cell B2 to cell B2.

To make Excel match the data using the text labels, look at the bottom of the dialog box under Use labels in:

1. Check the box for Top row (this tells Excel to match the month names, like “January” and “February”).
2. Check the box for Left column (this tells Excel to match the product names, like “Apples” and “Oranges”).

Step 6: Execute and Link

Before clicking OK, consider the Create links to source data checkbox.

• Unchecked: Excel will calculate the math once and paste static numbers into your Master sheet.
• Checked: Excel will build an intricate outline with live formulas linking back to the regional sheets. If the North team later updates their spreadsheet, your Master sheet will automatically recalculate.

Click OK.

Excel will instantly scan all four sheets, align the row and column headers, perform the math, and generate a perfectly formatted summary table on your master sheet in seconds.