Consolidate in Excel: Example, Steps, and Common Issues

How-to basics · Updated November 18, 2026

To consolidate data in Excel, place each source range on a separate worksheet, then choose Data > Consolidate, select a function such as Sum, add each source range, and select Top row and Left column if the ranges have matching labels. For example, Excel can combine three regional sales tables into one summary by product, even when a product appears in a different row on each sheet.

What Consolidate Does in Excel

Consolidate is a built-in Excel tool for combining values from multiple ranges into one result range. It can calculate totals, averages, counts, minimums, maximums, and other supported summaries. It is useful when data is split across worksheets or workbooks but you want a single report.

The feature has two approaches. By position, Excel combines values occupying corresponding cells: the value in the first source’s B2 is combined with the value in the second source’s B2. This works when every source has the same structure and row order. By category, Excel matches row and column labels, so a product named Tea can be aligned with Tea even if it is in a different row on each sheet. This is more forgiving of reordered lists, but labels must be consistent.

Consolidate is not the same as stacking every source row into one long table. It summarizes matching positions or categories. If you need to append all records, preserve transaction-level detail, or refresh a repeatable transformation, Power Query is often a better fit.

A Worked Example: Combine Regional Sales

Suppose a workbook has three sheets named North, South, and West. Each sheet contains a small sales table, but the products are not in exactly the same order:

  • North: Product and Sales headings in A1:B1; Coffee 120 in row 2, Tea 80 in row 3, and Cocoa 40 in row 4.
  • South: the same headings; Tea 50 in row 2, Coffee 90 in row 3, and Cocoa 30 in row 4.
  • West: the same headings; Coffee 70 in row 2, Cocoa 20 in row 3, and Tea 60 in row 4.

The desired result is Coffee 280, Tea 190, and Cocoa 90, for a total of 560. Those totals come from adding each product’s regional values: Coffee is 120 + 90 + 70; Tea is 80 + 50 + 60; Cocoa is 40 + 30 + 20. Since the products appear in different row positions, consolidate by labels rather than by position.

For this example, create a blank worksheet named Summary and click cell A1. The source ranges are A1:B4 on all three regional sheets. The header row contains the column label Sales, and the first column contains the category labels Product names.

Steps to Consolidate the Worksheets

  1. On the Summary sheet, select the upper-left cell where the consolidated output should begin, such as A1. Leave enough empty space below and to the right for the output.
  2. Open Data > Consolidate. In Microsoft 365 for Windows, this command is on the Data tab in the Data Tools group. If the button is not visible, check the ribbon’s Data Tools area or the simplified ribbon layout.
  3. In the Function box, choose Sum, because the example combines sales amounts.
  4. Click in the Reference box, select A1:B4 on the North sheet, and click Add. Repeat for A1:B4 on South and A1:B4 on West. Each range should appear in the All references list.
  5. Select Top row and Left column. These options tell Excel to use the headings and product names to match categories across ranges.
  6. Leave Create links to source data cleared for a fixed summary, then click OK.

Excel creates a summary starting at A1. It should show Coffee 280, Tea 190, and Cocoa 90, with a grand total of 560 if the output includes the corresponding total. Check the labels and totals against the source sheets. If the results do not match, review the references and verify that each value is stored as a number rather than as text.

Choosing a Function and Matching Method

The Function list determines how Excel combines numeric values that match. Sum adds them; Average calculates their mean; Count counts numeric entries; and Max or Min returns the largest or smallest matching value. Choose the function that answers the report question, not simply the first option in the list. For instance, adding monthly expenses makes sense for a period total, whereas averaging monthly temperatures may be more informative.

Choose matching by position only when all ranges have the same layout and category order. If one sheet puts Coffee in row 2 and another puts it in row 3, position-based consolidation combines unrelated entries. Choose Top row and Left column when the source ranges contain clear, matching labels. You can select one or both options depending on whether your categories run across columns, down rows, or both.

Labels need to match closely. Coffee and coffee may be treated as the same label, but extra spaces, spelling differences, abbreviations, or different naming conventions can create separate categories. Clean the source labels first, and include the same header structure in each range. The Consolidate tool does not infer that North America and N. America refer to the same category.

Fixed Results or Links to Source Data?

With Create links to source data cleared, Excel puts consolidated values in the destination as results. The output is convenient to distribute and inspect, but it does not automatically recalculate when the source values change. Run Consolidate again to update it.

Selecting Create links to source data tells Excel to create formulas that refer back to the source cells and build an outline-style summary. This can make changes in linked source cells flow through to the summary, but it creates a more complex result with grouped detail rows. It is useful when you want to inspect how a summarized value was assembled; it may be less suitable for a clean, compact report.

Neither choice is a complete refresh system for changing source structures. If ranges grow as new rows are added, or a recurring monthly process must pick up new files automatically, consider converting the inputs to Excel tables and using Power Query. Consolidate is most straightforward when the input ranges are known and the summary is an occasional or controlled task.

Consolidate Across Workbooks

You can add references from other open workbooks as well as from sheets in the current workbook. Open the source workbooks, select the destination cell, and use the Reference box to select each source range before clicking Add. Keep the source files available while setting up the references, and ensure that the selected ranges include the intended labels and values.

External references can be harder to maintain if files are moved, renamed, or unavailable. For a one-time report, a consolidated result without links can avoid a continuing dependency. For a repeatable report, store source files in a stable location and test the update process. Be especially careful not to include a previous summary as one of its own source ranges, which would double-count results or create misleading totals.

Common Problems and How to Fix Them

  • A category is missing: Check that its label is included in the selected reference and that the range was added to All references. Confirm that the label is not blank or misspelled.
  • Similar categories appear twice: Standardize names and remove unintended leading or trailing spaces before consolidating. Labels that look similar may not be identical.
  • Totals are too high or too low: Verify that each source was added once, that no range includes a total row, and that the selected function is appropriate. Including subtotals in source ranges can count amounts twice.
  • Values are ignored or results seem incomplete: Check for numbers stored as text, blank cells, or a range that stops before the last data row. Convert text-formatted numbers to numeric values where appropriate.
  • Output is not current: A fixed consolidation does not update itself when sources change. Run the tool again, use source links where suitable, or build a refreshable process with Power Query.
  • The command is unavailable or hard to find: Confirm that you are using the Excel desktop app and inspect the Data tab. Excel for the web has feature differences, and some desktop data tools may not be available there in the same way.

Consolidate Compared with Formulas and Power Query

Consolidate is a practical choice for a quick summary from a known set of ranges. It is visual, does not require writing a formula, and can match categories by labels. Its limitations are that it does not append detailed records, automatically detect every future row, or offer the full transformation and refresh controls of Power Query.

Formulas can be better when a report needs to remain transparent and update as source cells change. For example, with sheets named North, South, and West, where the Coffee sales amounts are all in B2, a three-dimensional reference can add that cell across those consecutive sheets: =SUM(North:West!B2). It returns 280 for the example only if Coffee is in B2 on every sheet and the three sheets are arranged consecutively in the workbook. If categories are in different rows, this formula is not a safe substitute unless you first align the data or use a label-based lookup approach.

Power Query is usually preferable when you need to combine many tables with the same column names, append rows, clean inconsistent data, or repeat the process with new files. In Microsoft 365 for Windows, open Data > Get Data to access Power Query options; available commands can vary by source and Excel build. On Mac, Power Query support and data-source options differ by version, so check the Data tab in the installed edition. For a small, stable set of sheets, the Consolidate dialog may be faster than building a query.

Frequently Asked Questions

Where is Consolidate in Excel?
In Microsoft 365 for Windows, look on the Data tab, in the Data Tools group, for Data > Consolidate. Ribbon layout can vary, so expand the ribbon or inspect the Data Tools group if the command is not immediately visible.

Can Excel consolidate data from multiple sheets with different row orders?
Yes. Add the ranges and select Left column so Excel matches row labels, such as product names. The labels must be consistent. If you consolidate by position instead, Excel combines corresponding cells regardless of what the rows mean.

Does Consolidate update automatically when source data changes?
Not when you create a fixed summary with Create links to source data cleared. Run Consolidate again to rebuild it. Selecting that option creates linked formulas for the included source cells, but it does not automatically solve changes to the shape or location of the source ranges.

Can I consolidate data from different workbooks?
Yes. Open the workbooks, then add the needed source ranges through the Reference box. External links may depend on the source files remaining available at their expected locations, so consider whether a fixed output or a refreshable query suits the task better.

What is the difference between Consolidate and merging cells?
Consolidate summarizes values from selected ranges. Merging cells changes the layout by combining cells into one larger cell; it does not add or summarize data. The similar wording can be confusing, but these are different Excel operations.

Can I use Consolidate in Excel for the web?
Excel for the web does not offer every desktop feature in the same way. If you cannot find the command or need the full Consolidate dialog, open the workbook in the Excel desktop app for Windows or Mac and check the available features in that version.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.