Excel Consolidate: How to Combine Data Across Sheets

How-to basics · Updated November 18, 2026

Excel has no worksheet function named CONSOLIDATE. To combine ranges, use Data > Data Tools > Consolidate; if you specifically need a formula, a 3-D SUM can add the same cell across adjacent worksheets.

What Excel Consolidate Does

Consolidate is a command for combining values from multiple ranges into one summary range. It is useful when, for example, separate worksheets contain monthly sales figures, branch expenses, or department totals and you want one report. The command can calculate a summary such as Sum, Average, Count, Max, or Min, depending on the Function selected in its dialog.

Consolidate is not a normal worksheet formula: you do not enter a function into a cell and supply several ranges as arguments. Instead, you select a destination cell, open the Consolidate dialog, add source ranges, choose a summary function, and tell Excel how to match the data. The result can be a set of values, or—if you select Create links to source data—a linked summary that refers back to the contributing cells.

This distinction matters when deciding how to build a workbook. A static Consolidate result is convenient for a snapshot; formulas are easier to inspect and update as part of a calculation model; and tools such as PivotTables or Power Query may suit recurring reports better.

Prepare the Source Ranges

Before consolidating, decide whether your ranges have the same layout or whether their labels should determine how values line up. A simple positional consolidation works best when every sheet uses the same row and column arrangement. For instance, if row 2 is January and column B is Sales on every sheet, Excel can combine the cells in corresponding positions.

When layouts differ, consistent labels are more important than matching positions. A category-based example might have one sheet listing Books, Furniture, and Supplies in that order and another listing Supplies, Books, and Furniture. Selecting the label options lets Excel align the categories by their names instead of treating the first row on each sheet as the same item.

Check your source data before opening the dialog:

  • Use clear, consistent row and column headings. Fix spelling differences and extra spaces that could make one category look like two.
  • Keep each source range focused on the actual data. Avoid including totals if you are also asking Excel to sum the detail, or the summary may count those totals again.
  • Confirm that values are numeric where you expect a mathematical summary. Numbers stored as text may not behave like ordinary numeric entries.
  • Choose a destination area with enough blank space. The summary can occupy multiple rows and columns, and should not overwrite source data.

How to Use the Consolidate Command

In Excel for Microsoft 365 on Windows, click the cell where the summary should begin, then select Data > Data Tools > Consolidate. In the dialog, choose a Function such as Sum. Add each source range using the Reference box and the Add button. You can select a range on another worksheet while the dialog is open; repeat the process until all required references appear in the list.

Next, choose how Excel should interpret the ranges. If the data has headings across its first row, select Top row. If it has labels in its first column, select Left column. Select both if both types of labels should guide matching. If the ranges have identical layouts and you want position-based matching, leave the label options unchecked. Check Create links to source data only if you want formulas linking the summary to its source cells rather than a standalone result. Select OK to create the summary.

  1. Make a blank destination area and select its upper-left cell.
  2. Open Data > Data Tools > Consolidate and pick the summary function.
  3. Add the source ranges one at a time, checking that each reference is correct.
  4. Set the label and link options to match your data and update needs.
  5. Review the resulting values and compare a few totals with the original sheets.

Excel for Mac also provides a Consolidate command on the Data tab, although the dialog and ribbon layout can differ from Windows. Excel for the web does not provide the same full desktop Consolidate workflow, so open the workbook in the desktop app when the command is unavailable. For platform-specific ribbon placement, use the labels visible in your installed version rather than assuming the Windows group layout is identical.

Use a Formula Instead: SUM Across Sheets

A formula is often simpler when every worksheet has the same structure and the ranges to add are known. A 3-D reference tells Excel to include the same cell or range on every worksheet between two named sheets in the workbook tab order.

With sheets named Jan, Feb, and Mar arranged consecutively in that order, and each sheet storing its monthly total in cell B2, enter this in the summary cell: =SUM(Jan:Mar!B2). If Jan!B2 is 125, Feb!B2 is 90, and Mar!B2 is 110, the formula returns 325 (125 + 90 + 110).

This formula updates when a value in one of those cells changes. It also depends on worksheet order: a sheet inserted between Jan and Mar becomes part of the 3-D reference. A sheet moved outside those endpoints is no longer included. Therefore, use clear boundary sheets and check the tab order when adding or rearranging worksheets. The formula works for ranges too, but the referenced cells need compatible layouts for the result to mean what you intend.

For worksheets that are not adjacent, list each sheet reference separately. Sheet names containing spaces need single quotation marks around the name inside the formula; for example, a reference to a sheet called West Region is written as 'West Region'!B2. This is a regular SUM formula, not the Consolidate command, and it does not match changing category labels across differently arranged tables.

Position Matching Versus Label Matching

Position matching is appropriate for stable templates. If cell B3 always means “Units sold” for every source, Excel can combine B3 from each range without needing to interpret row or column names. The advantage is speed and simplicity. The risk is that a misplaced row or column can silently combine unrelated values while still producing a plausible-looking total.

Label matching is more forgiving when categories appear in different orders or when a source contains only some categories. Selecting Top row and/or Left column tells Excel to use headings to align the data. This is useful for branch reports where one location may have no entry for a particular product category. It still relies on clean, consistent labels: “Office supplies” and “Office Supplies ” may not be treated as the same label if punctuation or trailing spaces differ.

Do not assume that label matching repairs messy data or understands a hierarchy. If one source calls an item “Hardware” and another calls it “Tools,” Excel cannot know whether those labels are equivalent. Standardize names in the source sheets or transform the data before summarizing.

Static Results, Linked Results, and Refreshing

Without Create links to source data, Consolidate writes a result that is effectively a snapshot of the source values at the time the command runs. If a source number later changes, do not assume the summary will recalculate; run the consolidation again to rebuild the result. This can be suitable for a one-time report or a deliberately fixed period-end extract.

With Create links to source data selected, Excel creates a linked outline of the consolidated values. It is designed to show the source detail behind summary results and to reflect changes in linked cells. The output is more complex than a simple static table, and it may not have the same shape as a formula you would design yourself. Inspect the generated rows and links before distributing the workbook, particularly if you will move, rename, or remove source sheets.

If your sources are in other workbooks, links may depend on those files remaining available at their referenced locations. Moving or renaming a source file can break external links or prompt Excel to locate the file. For a report that must travel independently, consider whether a static snapshot is more appropriate; for a report that must update, document where the source files live and test the update process.

Consolidate Compared With Other Excel Tools

Choose the tool based on the shape of the data and how often it changes. Consolidate is handy for a finite set of ranges and straightforward summaries. A formula is transparent when the calculation is small and stable. A PivotTable is generally a better fit for exploring a list of records by category, date, or another field, because you can rearrange its summary fields without building separate formulas for every view.

Power Query is worth considering when the same combining task repeats, when there are many source files, or when the data needs cleaning and reshaping. It can append tables into one dataset and load the result for analysis. Unlike a basic Consolidate result, a Power Query workflow can be refreshed after the sources change, but it requires setting up the query and ensuring its source files remain accessible.

For example, three monthly worksheets with identical cells and a handful of totals may be easiest to combine with Consolidate or a 3-D SUM. Hundreds of rows of transactions from several monthly files are usually better treated as one structured dataset and summarized with a PivotTable or Power Query. Avoid choosing a tool solely because it produces a total; consider how someone will verify, refresh, and extend the report.

Common Problems and Checks

A result that is unexpectedly high often means the source selection included an existing subtotal or grand total in addition to the underlying rows. Recheck every reference in the dialog and ensure that each source range contains the same level of detail. A result that is unexpectedly low may point to a missing reference, text-formatted numbers, or a source range that stops before the last row.

When categories appear twice, compare their labels carefully for spelling, spaces, punctuation, and inconsistent abbreviations. When numbers appear under the wrong heading, check whether you chose positional matching even though the source layouts differ, or whether the label options do not cover the headings you need.

If you use a 3-D SUM and a sheet seems to be missing, inspect the order of the tabs and verify that the sheet lies between the two endpoint sheets. If a linked Consolidate output does not reflect a change, confirm that Create links to source data was selected and that the source reference remains valid. For any method, validate a few individual categories against the underlying figures before relying on the final total.

Frequently Asked Questions

Is there a CONSOLIDATE formula in Excel?
No. Consolidate is a command on the Data tab, not a worksheet function. Use the Consolidate dialog for its range-based summary, or use functions such as SUM when a cell formula is the better fit.

How do I sum the same cell across multiple sheets?
If the worksheets are next to one another in tab order, use a 3-D SUM such as =SUM(Jan:Mar!B2), with the actual first and last sheet names and the desired cell reference. Make sure no unintended sheets lie between those endpoints.

Does Consolidate update automatically?
A consolidation without source links is a snapshot and should be run again after the source changes. Selecting Create links to source data creates linked results intended to reflect source-cell changes, but check the generated links and any external workbook dependencies.

Can I consolidate data with different row orders?
Yes, when the ranges have usable matching labels. Select Top row or Left column, as appropriate, so Excel matches categories by headings rather than by position. Standardize the labels first to prevent similar categories from being treated separately.

Why is my consolidated total wrong?
Check that you added every intended source, selected the correct function and label options, and excluded any subtotals that would be counted twice. Also verify that the source values are numeric and that the selected ranges include the complete data.

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.