Excel Consolidate Command: Combine Data Across Sheets

How-to basics · Updated November 19, 2026

In Excel for Microsoft 365 on Windows, open Data > Consolidate to combine values from multiple ranges into one summary. Choose a function such as Sum, add each source range, select whether to match data by position or labels, and choose the destination cell.

What the Consolidate command does

Consolidate is a built-in tool for summarizing information held in separate ranges or worksheets. For example, a business might keep monthly sales figures on four sheets and use Consolidate to create one annual summary. The command can combine corresponding numeric cells with functions such as Sum or Average, or calculate other supported summaries.

It is not the same as joining entire tables into a single list. Consolidate is most useful when each source range already represents comparable information—such as the same products, departments, or periods—and you want a combined summary. For a database-style list of records that grows over time, Power Query or a PivotTable may be a better fit.

The command does not require the ranges to be arranged in exactly the same order if you choose to match by labels. If you choose to match by position, however, Excel combines values according to their row and column locations, so consistent layouts matter.

Where to find it

In the Windows desktop version of Excel for Microsoft 365, select the cell where the summary should begin, then choose Data > Consolidate. The Consolidate dialog box contains a Function list, a Reference field, an Add button, label-matching options, and a checkbox for creating links to source data.

In Excel for Mac, look for Data > Consolidate in the desktop app as well. The ribbon layout and dialog appearance can differ slightly from Windows, but the basic process is similar. Excel for the web does not provide the desktop Consolidate command; open the workbook in the desktop app to create a consolidation. There is no dedicated default keyboard shortcut for this command, so use the Data tab and its Consolidate option.

How to consolidate ranges step by step

  1. Prepare the sources. Check that each range contains the figures you want to combine and, when relevant, a consistent row or column of labels. Remove unrelated totals or notes from the selected ranges.
  2. Choose an output location. Select an empty cell where the top-left corner of the summary will go. Leaving space around the result makes it easier to inspect and reduces the risk of overwriting existing information.
  3. Open the command. Choose Data > Consolidate.
  4. Select a function. Choose Sum for totals, Average for a mean, or another function that fits the purpose. Sum is commonly used for combining sales or expense amounts.
  5. Add each source range. Click in the Reference box, select a range on a worksheet, and click Add. Repeat for every range. If a source is on another sheet, switch to that sheet while the dialog is open and select the cells. Use Browse when selecting a range in another workbook.
  6. Choose how to match the data. Use position when corresponding values occupy the same row and column locations. To align ranges by headings, select Top row, Left column, or both, depending on where the labels appear.
  7. Decide whether to create links. Select Create links to source data if you want the output connected to the source cells. Leave it unchecked for a standalone summary of the values at the time you consolidate.
  8. Run the consolidation. Check that the references and settings are correct, then click OK. Review the result and compare a few totals with the source ranges.

For instance, suppose January and February each have a small table with product names in the first column and sales in the second. Add both two-column ranges, choose Sum, and select Left column so Excel matches the product names. If January lists “Pencils” before “Folders” and February reverses that order, matching by label still combines the figures for the same product. Matching by position would instead combine the first row with the first row, which could mix products.

Position matching versus label matching

By position means Excel combines cells in corresponding locations. The value in the upper-left data cell of one source is combined with the value in the upper-left data cell of every other source, and the same applies to the other positions. This works well for identically structured ranges, such as weekly schedules with the same employees in the same rows and days in the same columns.

By labels means Excel uses the selected headings to identify corresponding categories. Choose Top row when column headings identify the categories, Left column when row headings do, or both when the layout uses labels in both directions. The heading wording needs to identify the same category clearly in every source. Different ordering is fine; inconsistent spelling or different names can prevent the intended match.

Label matching can include categories that appear in only some source ranges, which is useful when one department has a category another department does not. It cannot determine that two differently named headings mean the same thing. Standardize labels first—for example, use “Office supplies” consistently rather than alternating among “Office,” “Supplies,” and “Office costs.”

Choosing a function and understanding the result

The Function list controls what Excel calculates for corresponding source cells. Sum adds values; Average calculates their mean; Max and Min return the largest and smallest values. Other available choices include Count, Count Numbers, Product, StdDev, StdDevp, Var, and Varp. These statistical choices use different definitions, so select one only when it suits the data and the question.

Count and Count Numbers are not interchangeable: one counts non-empty entries, while the other counts numeric entries. That distinction matters if a source range contains text labels or notes along with figures. For ordinary financial or quantity totals, Sum is usually the clearest choice. For averages, confirm that each source contributes the intended observations; a simple average of subtotals is not always the same as an average across all underlying records.

Consolidate produces a summary range, not a normal Excel Table that automatically expands when new source rows are added. If the source structure changes, you may need to adjust the references and run the command again. Decide whether the result should be a one-time snapshot or a connected summary before choosing the link option.

What Create links to source data changes

When Create links to source data is selected, Excel builds links from the summary back to the source cells and creates an outline structure. The outline lets you expand or collapse detail beneath summary figures. Changes to linked source values can flow through to the corresponding summary, which is useful when the source ranges remain in place.

Links are not a substitute for a refreshable data pipeline. They do not automatically make a newly added row part of a previously selected fixed range, and moving or renaming source files can complicate external references. If you need to include new records regularly, use a process designed for refresh and expansion, such as Power Query with well-structured source tables.

Without the link option, the output is a value-based result rather than a set of ongoing connections to each source cell. If the original figures change later, rerun the consolidation to update that summary. This can be useful for a report that should preserve the values used at a particular point in time.

Common problems and practical checks

  • The total is wrong: Confirm that every intended range was added and that the selected function is appropriate. Check whether the ranges include existing subtotals; adding both detail and subtotals can double-count amounts.
  • Categories are combined incorrectly: Check whether the dialog is matching by position or by labels. If the source rows are in different orders, choose the relevant label option instead of relying on position.
  • A category is missing or split into separate entries: Compare the labels for spelling, extra spaces, and inconsistent naming. Make the headings consistent and run the consolidation again.
  • The output does not change after editing a source: If the result was created without links, rerun Consolidate. If it was linked, verify that the edited cell is within a listed source range and that the link still points to the correct workbook.
  • External references are unreliable: Keep source workbooks in a stable location and check the references after files are moved or renamed. If the information is updated often, consider importing it through Power Query instead.

Before consolidating, save a copy of the workbook if the output will replace or sit near existing work. The command itself is not a data-cleaning tool: it will not resolve duplicate records, decide which of two conflicting values is correct, or infer relationships between differently structured tables. Review the source data and the final summary rather than assuming that a successful dialog run guarantees a meaningful result.

Consolidate compared with other Excel tools

Use Consolidate when you have several comparable ranges and want a compact summary by position or labels. It is straightforward for a monthly or departmental roll-up, especially when the source ranges are already prepared and the consolidation is occasional.

Use Power Query when you need to combine repeated tables, append new records, clean inconsistent data, or refresh an import. Power Query is designed for repeatable data-preparation steps, whereas Consolidate is a direct summary command. Use a PivotTable when you want to group a single well-formed table by fields and explore totals interactively. Use formulas such as SUMIFS when you need a purpose-built calculation that updates as the referenced data changes and you are comfortable managing the formulas.

These tools solve related but different problems. Consolidate summarizes corresponding ranges; Power Query transforms and combines data; a PivotTable explores a list of records; formulas calculate specified results. Choosing based on the shape of the source data and how often it changes makes the workbook easier to maintain.

Frequently Asked Questions

What is the Excel Consolidate command used for?
It combines values from multiple ranges into one summary using a selected function, such as Sum or Average. You can match cells by their positions or use row and column labels to align categories.

How do I consolidate data from multiple sheets?
Choose a destination cell, open Data > Consolidate, select a function, and add each source range from its worksheet. Then choose the appropriate label options and click OK.

Does Excel Consolidate update automatically?
Only linked results can reflect later changes to cells in the selected source ranges. A consolidation without links is a snapshot, and neither method automatically includes new rows outside the ranges you originally selected.

Can Consolidate combine ranges with different layouts?
It can match categories in different orders if you select the relevant label options and the headings identify the same categories. Position-based consolidation requires corresponding values to occupy matching locations.

Where is Consolidate in Excel for the web?
The desktop Consolidate command is not available in Excel for the web. Open the workbook in Excel for Windows or Mac to create a consolidation, then use the workbook in the browser as appropriate.

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.