Excel Consolidate Function: How to Combine Data

Formulas & functions · Updated November 19, 2026

To use Excel’s Consolidate function, select the top-left cell for the results, then choose Data > Consolidate, select a function such as Sum, add each source range, and click OK. Use matching positions when the ranges have the same layout, or enable row and column labels when you want Excel to match categories by name.

What Excel’s Consolidate tool does

Consolidate combines values from two or more ranges into one summary range. It is useful when figures are split across worksheets—for example, monthly reports from different branches—or across workbooks that use a common structure. Rather than manually copying totals together, you tell Excel which ranges to include and how to aggregate their values.

The tool works with several summary operations, including Sum, Average, Count, Max, Min, Product, and statistical functions such as StdDev and Var. It is not the same as merging cells, joining text, or stacking one table underneath another. Consolidate creates a summary; it does not automatically turn separate tables into one detailed, append-only table.

You can consolidate ranges in the current worksheet, other worksheets, or other workbooks. The source ranges can be arranged identically or can have different row and column orders if they share meaningful labels. Choosing the right matching method is central to getting a correct result.

Prepare the source ranges first

Before opening the dialog, check that each source range contains the values you intend to summarize and that the category labels are consistent. For example, labels such as North, South, and West should be spelled the same way in every range. Extra spaces, abbreviations, or slightly different wording can cause Excel to treat what you consider one category as separate categories.

  • Keep each source range rectangular, with a clear first row and/or first column if you plan to match by labels.
  • Decide whether headings are included in each range. If they are, enable the matching label options in the Consolidate dialog.
  • Leave clear space for the result. Consolidation writes into the destination range and may overwrite cells there.
  • Remove unwanted subtotals from source ranges if you do not want them included in the new summary.
  • For repeated use, keep source sheets consistently organized and note which ranges the summary should include.

Consolidate does not repair inconsistent data. If one sheet says East Region and another says East, label-based consolidation can produce two rows. Review the source labels before relying on the total.

How to consolidate ranges in Excel

These steps apply to desktop Excel for Microsoft 365 on Windows. The Consolidate command is available on the Data tab. Set up a destination cell on the worksheet where you want the summary to appear, then follow these steps:

  1. Select the cell that should become the top-left corner of the consolidated result. Make sure there is enough empty space below and to the right for the output.
  2. Choose Data > Consolidate. The Consolidate dialog opens.
  3. In the Function list, select the calculation you want, such as Sum or Average.
  4. Click in the Reference box. Select the first source range, including its labels if you want to use them, and click Add.
  5. Repeat the range-selection and Add step for every source range. Check the list in All references to confirm the intended ranges are present.
  6. Choose how Excel should identify matching data. Select Top row for headings across the top, Left column for labels down the left side, or both when appropriate.
  7. If you want the output linked to the source values, select Create links to source data. Leave it cleared for a summary that is not linked.
  8. Click OK and inspect the result, including a few totals you can verify manually.

On Excel for Mac, look for Data > Consolidate; the dialog and choices are broadly similar, though the ribbon layout can differ by version. Excel for the web does not offer the same desktop Consolidate command, so open the workbook in the desktop app to create or edit a consolidation.

Position matching versus label matching

Position matching is the simplest approach when every source range has the same shape and the same categories in the same order. Excel combines the values in corresponding cell positions. For example, if cell B2 means January sales on every sheet, the values in those B2 cells are combined. If one sheet has February in B2 instead, the result may be numerically valid but represent the wrong category.

Label matching is safer when category order varies. Select Top row and/or Left column in the dialog so Excel can align items using their labels instead of assuming that the same position means the same thing. If a category exists in one source but not another, Excel can still include that category in the summary. Labels must match sufficiently for Excel to recognize them as the same item, so standardize spelling and wording first.

For instance, imagine one worksheet lists product sales in the order Apples, Pears, Oranges, while another lists Oranges, Apples, Pears. Position matching would combine different products together. Selecting Left column makes Excel align the figures by the product names, which is the intended result when those names are consistent.

Example: combine branch sales

Suppose three worksheets—North, Central, and South—each contain a small table with product names in column A and sales in column B. One sheet lists products in a different order. To create a company-wide total, select an empty destination cell on a Summary sheet, open Data > Consolidate, choose Sum, and add the three two-column source ranges. Because the product names are in the first column, select Left column. If the table also has month headings across the top, select Top row as well.

Excel produces one row per recognized product and adds the values for that product across the branches. If Apples show 120, 95, and 110 in the three source tables, the consolidated Sum is 325. Before sharing the summary, compare a few output rows with the source figures and check that no product has been split into duplicate labels such as Pear and Pears.

This is a summary by category, not a record-level merge. The result will not contain a separate row for every transaction or preserve other details such as sale date and salesperson. If those details matter, use a method designed to combine tables rather than aggregate them.

Choose a function and understand the output

Pick the function that answers the question you actually have. Sum adds values, Average calculates their average, and Max or Min returns the largest or smallest value in each matched position or category. Product multiplies values. Count and Count Numbers are distinct choices: Count counts nonempty entries, while Count Numbers counts numeric entries. This difference matters when source ranges mix numbers with text.

Statistical options such as StdDev, StdDevp, Var, and Varp calculate measures of spread. The population versions treat the supplied values as the whole population; the non-population versions use a sample calculation. If you are unsure which statistical definition is appropriate, check the requirements of your analysis before choosing.

Consolidate calculates values from the references you add. It does not infer that a displayed subtotal should be excluded, so including both detail rows and a subtotal can double-count the same data. Similarly, check whether blank cells, text entries, or formulas returning empty text affect the operation you chose.

Linked results and refresh behavior

When Create links to source data is selected, Excel builds links from the output back to the source ranges and creates an outline that can show or hide detail. Changes to linked source values can flow through to the consolidated results. This is useful for a recurring report where the source sheets are updated but their general structure stays the same.

Without that option, the output is a consolidated result rather than a live set of linked calculations. If source data changes later, run the consolidation again to refresh the summary. Even with links, do not assume that new rows or newly added source ranges will automatically become part of the setup. Expand the references or add the new range, then verify the output. Keep the source worksheets or workbooks available and preserve their layout if the summary depends on those references.

Common problems and how to fix them

  • Categories appear twice: Look for spelling differences, trailing spaces, abbreviations, or inconsistent punctuation in the source labels. Standardize them and run the consolidation again.
  • Numbers are combined under the wrong headings: You may be using position matching on ranges with different category orders. Use Top row or Left column label matching where suitable.
  • Some data is missing: Check that every intended reference was added, that each selected range includes the relevant rows and columns, and that the correct labels option is enabled.
  • The total is too large: Inspect the source ranges for subtotals or grand totals that are already included. Consolidating those alongside the underlying detail counts the figures again.
  • The summary does not change after editing a source: Check whether Create links to source data was selected. If it was not, repeat the consolidation. If it was, check the reference and whether the edited cell lies within it.
  • The output overwrites other work: Undo immediately if possible, or restore a saved copy. Select a genuinely empty destination area before running the tool.

When to use another Excel method

Consolidate is a good fit for a straightforward summary of similarly structured ranges. It is less suitable when you need to combine complete tables, keep every transaction, clean inconsistent data during import, or repeat a complex process across many changing files.

For a one-time manual summary, formulas such as SUM or SUMIF may be easier to inspect and customize. For appending rows from multiple tables and repeating the transformation as files change, Power Query is often a better option: use Data > Get Data and its combine or append tools, then load the resulting table. PivotTables are useful when you already have one detailed dataset and want to group and summarize it interactively. These alternatives solve related but different problems; Consolidate remains convenient when the inputs are simple ranges and the desired output is an aggregate.

Frequently Asked Questions

Does Excel Consolidate update automatically?
Only when you choose Create links to source data do results link back to the referenced cells. Without links, run Consolidate again after source values change. In either case, check whether new rows or ranges need to be added to the references.

Can I consolidate data from multiple worksheets?
Yes. Add a reference for each source range, including ranges on other worksheets. You can also consolidate ranges from other workbooks; review the references carefully and keep the source files available if the result depends on links.

Do the source ranges need to be in the same order?
No, if you use label matching and the row or column labels are consistent. If you leave the label options off, Excel combines corresponding positions, so categories must be in the same order.

What is the difference between Count and Count Numbers?
Count includes nonempty entries, including text, while Count Numbers includes numeric entries. Choose according to what you intend to count, especially if the source ranges contain both text and numbers.

Can Consolidate combine tables into one detailed list?
No. It summarizes values using the selected function; it does not append every source row as a new record. Use Power Query to append tables when you need one combined detailed dataset.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.