How to Consolidate Data Across Multiple Worksheets in Microsoft Excel

When you are managing a massive project, you might receive a dozen different Excel workbooks from different departments, each containing a sheet of identical expenses. Copying and pasting data from twelve different sheets into a master summary document is incredibly tedious and prone to human error. Instead, you can use the built-in Consolidate tool to automatically pull data from multiple sheets and instantly sum, average, or count the results in a single, unified table.

How the Consolidate Tool Works

For the Consolidate feature to work perfectly, your data should ideally share the same layout. For example, if Department A’s sheet lists “Travel” in row 2 and “Software” in row 3, Department B’s sheet should ideally follow the same structure, with matching column headers.

1. Create a brand new, empty worksheet. This will be your master summary sheet.
2. Click on the exact cell where you want the consolidated data table to begin (e.g., cell A1).
3. Click on the Data tab in the main ribbon menu at the top of the screen.
4. Locate the “Data Tools” group and click the Consolidate button. (It usually looks like two small blue tables merging into one large table).

Configuring the Consolidation Settings

A small dialog box will appear. You must now tell Excel exactly how to process the data and where to find it.

1. Function: Use the drop-down menu to choose what mathematical operation you want to perform. “Sum” is the default and will add all the numbers together. You can also select Average, Count, Max, or Min.
2. Reference: Click inside the blank “Reference” text box.
3. Navigate to your first data sheet (e.g., Department A), and use your mouse to highlight the entire table, including the headers and labels.
4. Click the Add button in the dialog box. The range will appear in the “All references” list below.
5. Click inside the “Reference” text box again, navigate to your second data sheet (e.g., Department B), highlight the table, and click Add. Repeat this process until every sheet is listed in the “All references” box.

If your original tables included column headers (like “January”, “February”) and row labels (like “Travel”, “Software”), look at the bottom of the dialog box and check the boxes for Top row and Left column under the “Use labels in” section.

Finally, click OK. Excel will instantly scan all the referenced sheets, match the row and column labels perfectly, perform the mathematical sum, and generate a pristine master table on your new sheet.