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
- 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.
| 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).
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
- Select the top-left cell of the existing result.
- Open Data → Consolidate again. Excel remembers the references and options used last time in this workbook.
- Add, delete or change references, then click OK. Without links, the result is overwritten with fresh values.
- 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
| Symptom | Cause | Fix |
|---|---|---|
| Totals are wrong but no error | Labels not ticked, so cells were added by position | Tick Top row and Left column |
| Duplicate rows for one item | Labels differ by spacing or spelling | Clean labels with TRIM and Find & Replace |
| Top-left label is missing | Excel leaves the corner cell blank | Type the heading back in |
| Result shows 0 for a product | Source numbers stored as text | Convert them to numbers before consolidating |
| Links show #REF! | Source workbook moved or renamed | Data → 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.