Analistable

How to use the Consolidate function in Excel

By the Analistable team · Updated · 5 min read

Data → Consolidate summarises ranges from several sheets into one table. Choose a function (Sum, Count, Average…), add each source range under Reference, and tick Top row and Left column to match by labels, so the sources don't need the same order. Tick Create links to source data to keep the summary connected to sources in other sheets or workbooks.

Part of our guide: How to consolidate data in Excel

Step by step

  1. Click the top-left cell where the summary should go (on its own sheet).
  2. Choose Data → Data Tools → Consolidate.
  3. In Function, pick Sum (or Count, Average, Max, Min, Product, StdDev, Var…).
  4. Click in Reference, select the first source range including its row and column labels, and click Add.
  5. Repeat for each sheet. For other workbooks, open them first or use Browse.
  6. Tick Top row and Left column.
  7. Optionally tick Create links to source data. Click OK.

By position vs by label

Without Top row and Left column, Consolidate adds up cells by position: the first cell of each range, the second, and so on. That's only correct if every range has exactly the same layout. With labels ticked, it matches rows and columns by their text, so ranges can have different products in a different order.

Three regions consolidated by label (Sum)
ProductNorthSouthWestTotal
Chair210340550
Desk12095160375
Lamp6045105

Each region's sheet listed only the products it sold; consolidation by label produced one row per product.

Create links to source data

With this option, Excel builds an outline: each product row expands to show the linked value from every source, and the summary updates when the sources change. It only works when the sources are on other sheets or in other workbooks, and adding a new source still means running Consolidate again.

Limits

  • Numbers only: text can't be consolidated. Use TEXTJOIN or Power Query for text.
  • New sources aren't picked up automatically; rerun Consolidate or switch to Power Query.
  • Without links, the result is static values.
  • Labels must match exactly, including spaces: “Desk” and “Desk ” become two rows.

For a summary that refreshes and accepts new sheets automatically, stack the sheets with Power Query and use a pivot table — see consolidating data in Excel.

Consolidate across workbooks

Open each source workbook before you add its range, or use Browse and type the range address. References look like '[North.xlsx]Sales'!$A$1:$D$40. With Create links to source data ticked, the summary updates when the source files change — but if a source file is moved or renamed, the links break and Excel asks you to update them (Data → Edit Links).

Worked example: three regional sheets

North, South and West each have products in column A and quarters in row 1, but list different products in a different order. North has Chair 210 and Desk 120; South has Lamp 60, Chair 340 and Desk 95; West has Desk 160 and Lamp 45. With Sum, Top row and Left column ticked, Consolidate returns the totals already shown above: Chair 550, Desk 375 and Lamp 105, a grand total of 1,030.

Check it: North totals 330, South 495 and West 205, which also add up to 1,030. Comparing the grand total with the sum of the source totals catches the most common error — a source range that stopped one row short.

Use named ranges so sources can grow

Consolidate stores fixed addresses such as North!$A$1:$E$20. If North gains a row 21, it is left out the next time you run it. Give each source a name instead (select the range, type a name such as North_Data in the Name Box) and add the name under Reference. Redefine the name when the data grows, or make it a dynamic name with OFFSET or INDEX so it expands by itself. Excel tables can't be referenced by structured reference in the dialog in every version, so named ranges are the more reliable option.

Re-running and editing an existing consolidation

  1. Select the top-left cell of the existing result.
  2. Open Data → Consolidate again. Excel remembers the references and options used last time in this workbook.
  3. Add, delete or change references, then click OK. Without links, the result is overwritten with fresh values.
  4. With links, delete the old outline first (select the rows and Data → Ungroup → Clear Outline, then clear the cells), otherwise new rows can land in the middle of old ones.

Consolidate on a Mac and in Excel for the web

Excel for Mac has Data → Consolidate with the same Function, Reference, Top row and Left column options. Excel for the web may not offer Consolidate, or may offer fewer options; if the command is missing, open the file in the desktop app. Google Sheets has no Consolidate command; use QUERY or a pivot table over stacked ranges instead.

Troubleshooting

Consolidate problems
SymptomCauseFix
Totals are wrong but no errorLabels not ticked, so cells were added by positionTick Top row and Left column
Duplicate rows for one itemLabels differ by spacing or spellingClean labels with TRIM and Find & Replace
Top-left label is missingExcel leaves the corner cell blankType the heading back in
Result shows 0 for a productSource numbers stored as textConvert them to numbers before consolidating
Links show #REF!Source workbook moved or renamedData → Edit Links → Change Source

Consolidate, pivot or formulas?

Consolidate suits a quick, occasional summary of a few grids that already have labels down the side and across the top. If the summary is needed every month, a pivot on stacked data refreshes with one click; if the layout must stay fixed for a report, SUMIFS formulas pointing at a stacked table are easier to audit because each figure shows where it comes from. See SUMIFS across multiple sheets.

Frequently asked questions

Where is Consolidate in Excel?
On the Data tab, in the Data Tools group.
Can Consolidate combine data from different workbooks?
Yes. Open the workbooks (or use Browse) and add their ranges as references. Tick Create links to source data to keep the result updated.
What's the difference between Consolidate and a pivot table?
Consolidate summarises several separate ranges at once. A pivot table summarises one table (or related tables in the Data Model) and is easier to rearrange.

Related guides