Excel Consolidate Tool: How to Combine Data

How-to basics · Updated November 19, 2026

Excel’s Consolidate tool combines values from multiple ranges into one summary: in Excel for Microsoft 365 on Windows, select the destination cell, then choose Data > Data Tools > Consolidate. Choose a calculation, add the source ranges, and, when their layouts differ, use the label options so Excel can match corresponding categories.

What the Consolidate tool does

Consolidate is a built-in Excel feature for summarizing data held in separate worksheet ranges. It can, for example, combine sales figures from several regional worksheets into one total by product, or combine monthly expense figures into a single summary. It is useful when the source ranges represent comparable information but are stored separately.

The tool can combine values by position or by matching row and column labels. Position-based consolidation treats the first cell in each selected range as corresponding to the first cell in every other range, the second as corresponding to the second, and so on. Label-based consolidation looks for matching category names instead. That distinction matters if one sheet lists products in a different order from another.

Consolidate is not the same as stacking every source row into one long table. It produces a summary using a selected calculation, such as Sum or Average. If the goal is to append detailed records—for example, to collect every transaction from several worksheets—Power Query is usually a better fit.

When it is a good fit

Use Consolidate when separate ranges contain the same kind of measure and you want a combined summary, rather than a combined transaction-level dataset. A typical case is a workbook with one worksheet per branch. Each sheet has product names and sales amounts, and the summary should show total sales by product across all branches.

It can also help when older workbooks have separate worksheets for teams, periods, or locations and changing their layout into one normalized table is not practical. You can consolidate ranges from multiple worksheets in a workbook and, in some cases, from other workbooks. Keep the source data accessible while setting up the references, and check any external-workbook links before relying on them.

  • Good fit: combine comparable measures into a summary by category.
  • Less suitable: append detailed rows, refresh a changing collection of files, or build a report that needs interactive filtering.
  • Before starting: confirm what each source value represents and whether you want a static result or links back to source cells.

How to consolidate ranges in Excel for Windows

In Microsoft Excel for Microsoft 365 on Windows, make a blank area or worksheet for the summary, then follow these steps. The destination should not overlap any source range: otherwise, results may overwrite source data or make the setup confusing.

  1. Select the upper-left cell where the summary should begin.
  2. Choose Data > Data Tools > Consolidate. If the ribbon is narrow, the Data Tools group may be collapsed.
  3. In the Function list, choose the calculation you want, such as Sum.
  4. Click in the Reference box, select the first source range, and choose Add. Repeat for each additional range. You can select ranges on different worksheets; use the range-selection control in the dialog if you need to minimize it while selecting.
  5. If the ranges have labels, select Top row, Left column, or both, according to where the labels appear in the source ranges.
  6. Optionally select Create links to source data, then choose OK and inspect the resulting summary.

For example, suppose the North and South worksheets each have product names in column A and sales in column B, with headings in row 1. Add both ranges, such as A1:B4 on each sheet, select Top row and Left column, and choose Sum. Excel uses the product labels to align the values and adds the matching sales amounts. If the product order differs between sheets, label matching can still put each product’s combined result on the correct row.

Position matching and label matching

If you leave the label options unchecked, Excel combines values according to their positions in the selected ranges. This works when each range has the same structure and category order. For instance, if every worksheet puts January, February, and March in rows 2 through 4 in that order, position-based consolidation can produce the expected monthly totals.

For ranges with categories in a different order, select the relevant label options. Top row tells Excel that the first row contains labels; Left column says that the first column contains labels. Select both when a range has headings across the top and categories down the left. Excel can then align matching labels rather than assuming that every value at a particular position means the same thing.

Consistency still matters. If one sheet calls a category Office supplies and another calls it Office supplies with an extra space or different wording, Excel may treat them as different labels. Check spelling, spaces, and category meaning before consolidating. Label matching is not a substitute for cleaning inconsistent source data.

Choosing a function and understanding the result

The Function menu controls how Excel combines corresponding values. Sum is common for amounts such as sales or units. Other available choices include Average, Count, Count Numbers, Max, Min, Product, StdDev, StdDevp, Var, and Varp. Choose the calculation that fits the data, not merely the one that produces a plausible-looking number.

  • Sum: adds corresponding numeric values.
  • Average: calculates the average of corresponding values; consider whether a simple average is meaningful when source groups differ in size.
  • Count and Count Numbers: count entries or numeric entries, respectively. They are different from summing values.
  • Max and Min: return the largest or smallest corresponding value.
  • Statistical functions: standard deviation and variance options are available when those measures are appropriate for the data.

Text labels identify categories when label matching is selected; they are not added as numeric data. A category that appears in just one source can still appear in a label-based summary, while its consolidated value reflects the available source values. After running the tool, compare a few results with the source ranges and check that the chosen function handled blanks and nonnumeric cells as you intended.

Static summaries and links to source data

By default, Consolidate writes a summary result rather than a live formula-driven connection to every source cell. If the source data changes later, a standard consolidated result does not automatically recalculate from those changes. Run the consolidation again when you need an updated result.

Selecting Create links to source data changes that behavior: Excel creates links from the summary back to the source cells and arranges the result with outline levels. This can let you expand or collapse the detail behind summary values. Linked consolidation is helpful when you want to trace totals to source entries, but it does not turn the source ranges into a flexible database. New rows added outside the original references may not be included, so verify the references and refresh or rebuild the consolidation when the source structure changes.

Choose the linked option only if you want that outline and source-cell relationship. For a simple handoff, export, or presentation, a static summary may be easier to manage. Either way, keep a copy of the original sheets and document which ranges were included, particularly if the workbook is used for recurring reports.

Common problems and checks

A result that looks incomplete or unexpectedly split into several categories usually points to a range or label issue. Check that every intended range was added, that the selected cell is the correct destination, and that the top-row and left-column options reflect the actual source layout. A heading accidentally included as data, or a label column omitted from the selection, can make the result misleading.

  • Categories appear more than once: compare their labels for spelling, extra spaces, abbreviations, or inconsistent naming.
  • Numbers seem misaligned: confirm that you used label matching when category order differs. If you used position matching, verify that every range has the same order and dimensions.
  • Some results are missing: reopen the dialog and inspect the Reference list. A newly added row may sit outside a previously selected range.
  • The result does not change after editing a source: rerun a static consolidation, or check whether you selected Create links to source data.
  • Totals do not make business sense: verify that all ranges use the same units, period, and definition, and that Sum is the intended function.

It is especially important to validate units and definitions. Adding dollars to units sold can produce a number but not a meaningful total. Similarly, combining monthly values with year-to-date figures double-counts information. Excel consolidates according to the ranges and function you give it; it cannot determine whether those figures are conceptually comparable.

Consolidate compared with other Excel methods

Consolidate is a quick option for combining a fixed set of similarly structured ranges. Its strength is that it can summarize ranges by position or labels without first rearranging the worksheets. Its limitations become more noticeable when the source data changes often, comes from many files, or needs ongoing analysis.

Power Query is generally better for importing, cleaning, and appending records from multiple worksheets or files. It can shape data into a consistent table and refresh the query when sources change, subject to how the query and source locations are configured. It takes more setup than a one-time Consolidate operation but is usually easier to repeat reliably.

PivotTables are useful when the data is already in a single well-structured table and you want to summarize it by fields, rearrange categories, or filter the report. A PivotTable does not itself solve every problem of combining separate ranges; first bring the data into a suitable source table or use a data model or query workflow.

Formulas can make a summary transparent and tailored, especially when the source layout is stable and you need specific calculations. However, formulas require careful range management and can become difficult to maintain across many sheets. The best choice depends on whether the task is a one-off summary, a repeatable data-preparation process, or an interactive report.

Availability on Mac and Excel for the web

Excel for Mac includes a Consolidate command in the Data tab, though the ribbon arrangement and dialog presentation can differ from Windows. The Windows path described above is Data > Data Tools > Consolidate; on Mac, look for Consolidate on the Data tab rather than assuming the Windows group layout is identical.

Excel for the web does not provide the same full desktop Consolidate dialog. If you cannot find the command in a browser, open the workbook in the Excel desktop app to use this workflow. Excel’s web features can change over time, so for a workbook that must be edited only in a browser, consider designing the summary with supported tables, formulas, or another available workflow and confirm it behaves as needed there.

Frequently Asked Questions

Where is the Consolidate tool in Excel?
In Excel for Microsoft 365 on Windows, select the destination cell and choose Data > Data Tools > Consolidate. On a Mac, look for Consolidate on the Data tab.

Does Excel Consolidate update automatically?
A standard consolidation is a static summary, so edits to source data do not automatically update it. Create links to source data can link the summary to source cells, but added rows outside the original references still require attention.

Can Consolidate combine data from multiple worksheets?
Yes. Add a reference for each worksheet range in the Consolidate dialog. Make sure the ranges contain comparable information and choose label matching when categories are not in the same order.

What is the difference between Consolidate and merge?
Consolidate summarizes corresponding values using a function such as Sum or Average. It does not simply join every row into one long list. To append detailed rows, use a table-based workflow such as Power Query.

Why does Consolidate show the same category more than once?
Excel may be seeing different labels as distinct categories. Check for spelling differences, extra spaces, abbreviations, or inconsistent names in the source ranges, then standardize them and run the consolidation again.

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.