Excel Consolidate Button: Where It Is and How to Use It

How-to basics · Updated November 18, 2026

In Excel for Microsoft 365 on Windows, find the Consolidate button on the Data tab, in the Data Tools group. Select a blank destination cell, choose Data > Data Tools > Consolidate, add the source ranges, choose a function such as Sum, and click OK.

What Excel’s Consolidate Button Does

Consolidate combines values from multiple ranges into one summary. It is useful when figures are spread across worksheets or workbooks—for example, when each regional sales team keeps a similarly structured monthly report and you want a single company-wide total. Depending on your settings, Excel can match the ranges by their positions or by their row and column labels.

The command does not merge worksheets, append all source rows into one long table, or make a new copy of every source record. It calculates a summary from the selected ranges. That distinction matters: a consolidation is suited to totals and other summaries, while combining transaction lists generally calls for a different method, such as Power Query.

Excel’s Consolidate dialog offers functions including Sum, Count, Average, Max, Min, Product, Count Numbers, StdDev, StDevp, Var, and Varp. The selected function is applied to corresponding values or matching labels across the ranges. Sum is the default choice, but it is not always the right one: averaging separately calculated averages, for instance, may not give a correct overall average if the groups contain different numbers of records.

Where to Find It on Windows, Mac, and the Web

In the Windows desktop version of Microsoft 365, open the Data tab and look in the Data Tools group for Consolidate. The ribbon may display the command as a text button or a compact icon, depending on the window size. Widening the Excel window can reveal commands that are tucked into a group menu.

In Excel for Mac, look on the Data tab for Consolidate; ribbon layout and dialog appearance can differ from Windows. Excel for the web does not provide the same desktop Consolidate command in its ribbon. If you cannot find the command in the browser, open the workbook in the desktop app and use the desktop instructions. A feature being unavailable in the web ribbon does not mean that an existing workbook cannot contain results created with it.

If you do not see the button on Windows, first check that you are using the desktop app and that the ribbon is showing its full Data tab. If you are editing a cell, finish or cancel the edit before looking for a command. You can also use Excel’s ribbon search to look for Consolidate. Avoid assuming that a similarly named command in another spreadsheet program has the same options or behavior.

How to Consolidate Ranges Step by Step

Before opening the dialog, decide where the summary should go and make sure the area is empty. The output can expand beyond the first cell, so leave room for the expected rows and columns. Do not place it inside one of the source ranges: overlapping inputs and output can overwrite data or make the result confusing.

  1. Select the top-left cell where the consolidated result should begin.
  2. Choose Data > Data Tools > Consolidate.
  3. In the dialog, choose the calculation in the Function list, such as Sum.
  4. Click in the Reference box, select the first source range, and click Add. Repeat for every range you want included. A range is not included just because it appears in the Reference box; use Add to put it in the list of references.
  5. If the ranges have headings, select Top row and/or Left column as appropriate. Leave these options unchecked when matching by position alone.
  6. Optionally select Create links to source data, then click OK.

For example, suppose three worksheets contain quarterly reports, each with product names down the left and months across the top. Add the sales ranges from all three sheets, select both Top row and Left column, and use Sum. Excel can then add values associated with the same product and month even if the products appear in a different order on one worksheet. If the rows and columns already line up exactly, you can instead consolidate by position without using labels.

Consolidating by Position or by Labels

By position means that Excel combines cells according to where they sit in each selected range. The top-left value in one range is combined with the top-left value in another, the next cell with the next cell, and so on. Choose this approach when all ranges have the same layout and the same items in the same order. It can give a plausible-looking but incorrect summary if one sheet has an inserted row or a different item order.

By labels means Excel uses headings to identify corresponding categories. Select Top row if column headings identify the data, Left column if row labels identify it, or both if both dimensions have meaningful labels. The source labels need to agree for Excel to recognize them as the same category; check spelling, spacing, and wording when a category is missing or split into separate entries. Label-based consolidation is helpful when the same categories are arranged in different orders, but it is not a substitute for cleaning inconsistent labels.

Consider one sheet with rows for North, South, and West, and another with West, North, and South. Consolidating by position would pair North with West because both occupy the first row. Consolidating by the left-column labels instead matches North with North, South with South, and West with West. This is one of the most important choices in the dialog: decide based on how the source ranges are organized, not simply on which boxes are available.

What Create Links to Source Data Changes

With Create links to source data unchecked, Excel writes a summary of the selected values into the destination. The output is a snapshot: changing a source value later does not automatically recalculate that consolidation. Run the consolidation again when the source figures change and you need a refreshed snapshot.

With the option checked, Excel creates links from the output back to the source cells and organizes the result with outline levels. The linked summary can reflect changes to included source cells as Excel recalculates. This is useful for a workbook that is updated over time and where readers may want to expand the outline to inspect contributing detail. It also creates more formulas and a more structured output than a simple snapshot.

Links do not remove the need to maintain the source references. If a new month or category is added outside the ranges originally included, that new data is not automatically part of the consolidation just because links were created. Review the references when the source layout changes, and be cautious with links to other workbooks that may be moved, renamed, or unavailable.

Choosing the Right Function and Checking the Result

Choose a function that answers the question your summary is meant to answer. Sum adds values, Average calculates an average, and Max or Min returns the largest or smallest value. Count counts entries, while Count Numbers counts numeric entries. These distinctions matter when source ranges include text, blanks, or a mix of numbers and labels. If the summary is meant to count sales transactions, for example, decide whether you mean all nonblank records or only cells containing numbers.

Be especially careful with Average. If one team reports an average order value based on 10 orders and another reports an average based on 100 orders, averaging those two averages gives each team equal weight. That is not necessarily the overall average across all 110 orders. For a reliable overall average, consolidate the relevant totals and counts separately, then calculate total value divided by total count, or use a method that works from the underlying records.

After clicking OK, compare a few output cells with their source values. Check that the expected categories appear, that the function is appropriate, and that no headings were accidentally treated as data. A quick independent check—such as adding a small number of known figures on a calculator—can catch a misplaced range or a mistaken position-based match before the summary is shared.

Common Problems and How to Fix Them

  • A source range is missing: Confirm that you clicked Add after selecting it and that the range appears in the references list. Reopen the dialog to inspect the included references.
  • Categories appear in the wrong rows: The ranges may have been consolidated by position. Use the label checkboxes when headings identify the categories, and check that the labels match consistently.
  • Headings are included in the calculation: Select the relevant Top row or Left column option if those cells are labels, not values. Check the source selection as well; do not include extra title rows unless they are meant to be part of the range.
  • The result does not change after editing a source: The consolidation may be a snapshot because Create links to source data was not selected. Run Consolidate again, or rebuild the result with links if linked detail is appropriate.
  • New rows are not represented: The original references may not cover them. Update the source ranges and run the consolidation again; do not assume Excel expands every manually selected reference.
  • The summary is larger or messier than expected: Check for inconsistent labels, extra blank rows, or ranges that include unwanted columns. Use a clear destination area and review the chosen function.

Consolidate Compared with Other Excel Tools

Use Consolidate when you have a manageable set of ranges and want a summary across them, particularly when they share a layout or meaningful labels. It can be quick for monthly or departmental worksheets that are maintained separately. Its limitation is that it relies on the ranges and choices you specify; it is not designed as a robust pipeline for repeatedly importing changing datasets.

A PivotTable is often a better choice when you want to explore one well-organized dataset by fields such as region, product, and month, or change the view interactively. Power Query is generally more suitable for appending rows from multiple similarly structured tables, cleaning inconsistent data, and repeating an import-and-transform process. Ordinary formulas such as SUMIF or SUMIFS can be preferable when you need a tailored calculation in a fixed report. These approaches solve related but different problems: a list of records to append is not the same thing as matching summary ranges to consolidate.

For an ongoing report, consider whether the source sheets can be replaced by one structured table with a column identifying the month or department. A single table can make filtering, PivotTables, and refreshable Power Query workflows easier to manage. Consolidate remains useful when that redesign is not practical, but the more frequently the source layout changes, the more important it is to document and verify the selected ranges.

Frequently Asked Questions

Where is Consolidate in Excel?
In Excel for Microsoft 365 on Windows, go to Data > Data Tools > Consolidate. On Mac, look for Consolidate on the Data tab. The desktop command is not available in the same way in Excel for the web.

Why is the Consolidate button missing or unavailable?
Check that you are using desktop Excel, viewing the Data tab, and not currently editing a cell. If the ribbon is compressed, expand it or use ribbon search. If you are in Excel for the web, open the workbook in the desktop app to use this command.

Can Excel Consolidate combine data from different worksheets?
Yes. Add each worksheet range as a separate reference in the dialog. The ranges can be consolidated by position when their layouts match, or by labels when their row or column headings identify matching categories.

Does Consolidate update automatically?
A consolidation made without Create links to source data is a snapshot and does not update when source values change. Selecting that option creates linked output that can reflect changes to included source cells, but newly added rows or categories may still require updating the references.

Is Consolidate the same as combining or merging worksheets?
No. Consolidate calculates a summary across selected ranges; it does not append all source records into one list or merge worksheets. For combining rows from multiple tables, consider Power Query instead.

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.