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.

How to use the Consolidate function in Excel

By the Analistable team · Updated 2026-10-07 · 2 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

Click the top-left cell where the summary should go (on its own sheet).
Choose Data → Data Tools → Consolidate.
In Function, pick Sum (or Count, Average, Max, Min, Product, StdDev, Var…).
Click in Reference, select the first source range including its row and column labels, and click Add.
Repeat for each sheet. For other workbooks, open them first or use Browse.
Tick Top row and Left column.
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)
Product	North	South	West	Total
Chair	210	340		550
Desk	120	95	160	375
Lamp		60	45	105

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).

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.