In Excel for Microsoft 365 on Windows, use Data > Consolidate to summarize values from two or more ranges in one destination range. Choose a function such as Sum, add each source range, and select Top row or Left column if the ranges should be matched by labels rather than by cell position.
What the Consolidate option does
Consolidate is a built-in data tool for combining information from multiple worksheets or workbooks into a summary. Instead of copying and pasting separate tables together, you tell Excel which ranges to use and how to combine their values. The result is a new summary table at a location you choose.
For example, a business might keep sales figures on separate worksheets for each branch. Consolidate can create a single table showing total sales by product across all branches. It can also calculate averages, counts, minimums, maximums, and other summaries. It is most useful when your goal is a compact report, not a new table containing every original record.
The command summarizes corresponding cells or labels; it does not automatically clean inconsistent data, identify that two differently named products are the same, or preserve the full detail behind each total. Review the source ranges and labels before relying on the result.
Where to find Consolidate
In Excel for Microsoft 365 on Windows, open the Data tab and look in the Data Tools group for Consolidate. If the ribbon is narrow, the group may be compressed, so widen the Excel window or open the group to see the command. The dialog is also available in desktop Excel for Mac, although the ribbon layout can differ. If you do not see the command in Excel for the web, open the workbook in the desktop app.
Consolidate is not the same as the Consolidate or Group commands used in other applications. In Excel, this particular command appears on the Data tab and opens a dialog with a Function list, a Reference box, and options for matching labels and creating links to source data.
How consolidation matches data
Excel can combine ranges in two main ways. If you leave the label options unchecked, it matches cells by their relative positions. The value in the first cell of every selected range is combined with the value in the first cell of every other range, the second with the second, and so on. This works well when every source table has the same arrangement and rows appear in the same order.
If the tables have headings, select Top row, Left column, or both. Excel then uses those labels to align corresponding information even when the labels appear in a different order in different ranges. For instance, if one worksheet lists North before South and another lists South before North, selecting Left column lets Excel pair each region by its name instead of assuming that the first row means the same thing in both tables.
Label matching depends on the text being consistent. Product A and Product-A, for example, may be treated as different labels. Check spelling, extra spaces, and duplicate labels; use clear, consistent headings. When a label is missing from one source, the consolidated output can still include information from the other sources, but the missing source contributes no value for that label.
Steps to consolidate ranges
Prepare the source worksheets first. Decide where the summary should go and make sure the destination area is clear. Then follow these steps in the Windows desktop app:
- Click the upper-left cell where the consolidated summary should begin. Choose a destination that does not overlap any source data.
- Select Data > Consolidate.
- In the Function list, select the calculation you want, such as Sum or Average.
- Click in the Reference box, select a source range on a worksheet, and click Add. Repeat for every source range. You can select ranges on other sheets or, where accessible, in other open workbooks.
- If your ranges contain headings, select Top row and/or Left column as appropriate. Leave them unchecked when matching by position is intended.
- Select Create links to source data if you want an outline-linked summary rather than a standalone result. Leave it unchecked for a summary of the current source values.
- Check that the references and options are correct, then click OK. Review the output, including its row and column labels, before using it in a report.
When selecting a range with the mouse, include the heading row or column if you have selected the corresponding label option. If you have not selected label matching, include only the cells you intend to calculate. The ranges do not have to be adjacent, but all must represent the kind of data you want summarized.
Choosing a consolidation function
The Function list determines what Excel calculates for each corresponding cell or label. Sum is the usual choice for additive measures such as units sold or expenses. Average is useful for values where a mean is meaningful, while Max and Min show the largest and smallest contributing values. Product multiplies the values together and is less common for routine reporting.
Count and Count Numbers answer different questions. Count counts nonblank entries, while Count Numbers counts cells containing numbers. If a source range contains text notes as well as numeric values, those choices can produce different results. The list also includes standard deviation and variance options, with separate sample and population versions: StdDev and Var are sample measures, while StdDevp and Varp treat the data as a population. Choose the statistical measure that fits your data rather than assuming all count or variation options are interchangeable.
Do not use Sum just because it is the default-looking choice. Adding percentages, unit prices, or averages can be misleading unless the underlying values and the meaning of the result support addition. If each worksheet contains a monthly average, for example, summing those averages does not produce an annual average. A weighted calculation or a summary of the underlying records may be more appropriate.
Example: combining branch sales
Suppose three worksheets named East, Central, and West each have a table with product names in column A and sales amounts in column B. The products appear in different orders, and one branch does not sell every product. To build a total by product, choose a blank destination, set Function to Sum, add each table range including its headings, and select Left column. Excel uses the product names to line up the rows before adding the amounts.
If the tables also have month names across the top row, you can select Top row as well as Left column. Excel can then align both dimensions by their labels. A value for a particular product and month is combined with the corresponding product and month in the other sources, even if the row or column ordering differs.
By contrast, if every worksheet has the same products in exactly the same row order and months in exactly the same columns, matching by position may be simpler. In either case, the result is a summarized table: it does not append the three source tables into one detailed list of all transactions.
Static results and links to source data
With Create links to source data cleared, the output is a consolidated summary of the values available when you run the command. If source figures later change, the summary does not automatically recalculate from those ranges. Run Consolidate again when you want to refresh the result. This approach is useful for a snapshot or a report that should remain independent of its source tables.
Selecting Create links to source data makes Excel create an outline structure and formulas that link the summary to the source cells. The summary can reflect later changes when the linked formulas update and any external workbook links are available. The outline controls let you expand or collapse detail rows or columns. This is useful when readers need to inspect contributing values, but the output is more complex than a static summary.
Linked summaries can depend on workbook locations and link-update settings, particularly when sources are in other files. Moving or renaming a source workbook can disrupt an external link. Keep source files accessible, check Excel’s link prompts, and verify that the displayed totals update as expected. A linked result is not the same as a live database connection or an automatically refreshed Power Query query.
Common problems and practical checks
- The total is wrong: Check that every intended range was added and that its dimensions and headings are correct. A missed source range or an accidental extra row can change the result.
- Rows are combined incorrectly: If ranges are arranged differently, enable Left column or Top row as needed. If labels do not match exactly, standardize them before consolidating.
- Values are combined by position when labels were intended: Confirm the label checkboxes in the dialog. Without them, Excel does not infer that similar-looking rows represent the same category.
- Count seems too high or too low: Decide whether you need nonblank entries or numeric entries, then choose Count or Count Numbers accordingly. Inspect text, blanks, and formulas that return empty text in the source.
- The output does not change after editing a source: A static consolidation must be run again. If you chose links, check whether the output contains formulas and whether external links can reach their source workbooks.
- The summary overwrites data: Choose a clear destination outside all source ranges before running the command. Keep a copy of important source data and inspect the result before replacing any existing report.
It is also worth checking whether each source uses the same units and reporting period. Adding dollars to values recorded in thousands, or combining a partial month with full months, may produce a mathematically valid but misleading summary. Consolidate performs the chosen operation; it cannot judge whether the figures are comparable.
When another Excel tool is a better fit
Use Consolidate when you have a manageable set of worksheet ranges and want to summarize them by position or labels. For recurring imports, evolving tables, or repeatable cleanup steps, Power Query is often a better fit: it can combine tables and refresh the result after source data changes. A PivotTable is useful when you want to explore a detailed dataset by categories, rearrange fields, or filter the analysis interactively.
For a small number of identically structured worksheets, a formula can be simpler. With worksheets named Jan, Feb, and Mar in that tab order, and the amounts to combine in cell B2 on each sheet, enter =SUM(Jan:Mar!B2) in the destination cell. If Jan B2 contains 120, Feb B2 contains 135, and Mar B2 contains 145, the result is 400. This three-dimensional reference includes the named sheets from Jan through Mar in tab order; it is not a flexible substitute for matching rows by labels.
For data stored as individual transaction records, a PivotTable or Power Query usually preserves more analytical flexibility than a consolidated summary. Choose based on whether you need a one-time total, a formula that updates, a refreshable combination process, or a detailed report that can be rearranged.
Frequently Asked Questions
Where is Consolidate in Excel?
In desktop Excel for Microsoft 365 on Windows, select Data > Consolidate. On Mac, look for Consolidate on the Data tab; the ribbon arrangement can vary. If the command is not available in your Excel for the web session, open the workbook in the desktop app.
Can Excel consolidate data from multiple worksheets?
Yes. Add each worksheet range separately in the Reference box, then choose the function and matching options. The source ranges can be on different sheets, and label matching can align categories that are listed in different orders.
Does Consolidate update automatically when source data changes?
A consolidation made without Create links to source data is a static summary and must be run again to include later changes. With links selected, Excel creates linked formulas, but external workbook availability and link-update behavior still matter.
Can I combine tables with different row orders?
Yes, if the ranges have consistent labels, select Left column for row labels and Top row for column labels as needed. Standardize headings first. If you leave these options unchecked, Excel combines by relative cell position instead.
What is the difference between Consolidate and a PivotTable?
Consolidate summarizes selected ranges into a result based on labels or positions. A PivotTable summarizes a dataset while letting you rearrange fields, filter, and explore categories. For detailed records or interactive analysis, a PivotTable is often the more flexible choice.
Related
- Hide Columns in Excel: How to Hide and Unhide Them
- How to Import CSV to Excel: Steps and Common Fixes
- Consolidate Data in Excel: Methods and Steps
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel AVERAGE: What It Ignores and IncludesIn Excel, AVERAGE ignores empty cells, text, and logical values such as TRUE or FALSE when they are…
- How to Export a CSV File in Excel Without Losing DataTo export a CSV from Excel for Microsoft 365 on Windows, open the workbook, select the worksheet you…
- Google Sheets for Scheduling: Setup, Tips and LimitsYes. Google Sheets can work well for a straightforward personal, shift, project, or room schedule: put dates, times,…
- How to Delete a Random Page in WordTo delete a random page in Microsoft Word, click anywhere on that page, press Ctrl+G on Windows, enter…
- Excel Accounting Solver: What It Does and How to Use ItExcel’s Solver add-in can help with accounting models that require finding the best combination of numbers under stated…
- Can You Create a Dropdown List in OneNote?No—OneNote does not provide a native dropdown-list control that readers can open and use to choose one value…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.