To consolidate values in Excel, select the destination cell, choose Data > Consolidate, select a summary function such as Sum, add each source range, and click OK. The Consolidate command combines data from one or more ranges into a summary; if you mean joining text or removing duplicate entries, those are different tasks with different tools.
What Excel consolidation does
Excel’s Consolidate feature summarizes related data in a single destination range. It is useful when figures are spread across worksheets or workbooks—for example, monthly sales on separate tabs or the same budget layout for several departments. You choose a calculation, such as Sum or Average, and tell Excel which ranges to include.
There are two ways Excel can match source data. Consolidation by position compares cells in the same relative locations, so the ranges should have the same layout. Consolidation by category uses labels, such as product names or regions, to match corresponding rows or columns even when they appear in a different order. The labels need to be consistent enough for Excel to recognize the same category.
Consolidate is not a general-purpose command for stacking entire tables into one long list. It produces a summary, not a copy of every source row. Nor does it automatically clean spelling differences, standardize dates, or decide whether two differently named categories mean the same thing.
Prepare the source ranges
Before opening the dialog, decide what result you want and make the source ranges comparable. For a monthly sales summary, each source might contain product names in the first column and sales figures in the next. If you plan to consolidate by category, include the category labels in every selected range. If you plan to consolidate by position, make sure each range places equivalent data in equivalent cells.
- Use clear, consistent row and column labels. A trailing space or a spelling variation can cause a category to be treated separately.
- Keep headers out of the numeric data unless you intend to use them as labels with the top-row option.
- Choose an empty destination area so the summary does not overwrite source data or other results.
- Check that the values are actually numeric when you expect a total. Numbers stored as text may not behave like ordinary numeric values.
- Save a copy before changing a workbook with important source data, especially if you are still learning the dialog.
The source ranges do not have to be the same size when matching by category. For position-based consolidation, however, different shapes or shifted rows can pair unrelated cells and produce a misleading summary.
Consolidate ranges step by step
These instructions are for Excel for Microsoft 365 on Windows. The command is on the Data tab. In a desktop build where the ribbon is simplified, look for Consolidate in the Data Tools group or expand the ribbon to locate it.
- Open the worksheet where the summary should appear and select the upper-left destination cell. Leave enough empty space for the output.
- Select Data > Consolidate.
- In the Function list, choose the calculation you need. Sum is common for sales or expenses; Average, Count, Max, and Min are useful for other kinds of summaries.
- Click in the Reference box. Select the first source range, including labels if you plan to match by labels, and click Add.
- Repeat the selection and Add steps for every additional source range. Ranges can be on other sheets in the workbook. If you are selecting data from another workbook, open it first so you can select the relevant cells.
- If the source ranges include labels, select Top row, Left column, or both to tell Excel which labels to use for matching. Do not select these options if the ranges contain only position-based data without labels.
- Optionally select Create links to source data if you want the summary linked to its source cells. Review the references and options, then click OK.
Excel writes the result starting at the destination cell. With a category-based summary, it brings together the labels it finds and applies the selected function to matching categories. With a position-based summary, it combines corresponding cell positions. Inspect the output and compare a few totals against the source ranges before relying on it.
Choose a function and matching method
Pick the function that answers the question you are asking. Sum adds corresponding values; Average calculates their mean; Count counts nonempty entries; Count Numbers counts numeric entries; and Max or Min returns the largest or smallest value. Other available statistical functions include Product, StdDev, StdDevp, Var, and Varp. The exact names shown in the dialog may use spaces, but they refer to the corresponding summary calculations.
For example, suppose three department sheets each list the same product names in column A and sales in column B. If every list has the same order and structure, position-based consolidation can summarize corresponding cells. If products appear in different orders, include the product labels and select Left column so Excel matches by product name instead. The category approach is generally safer when rows may move, provided labels are consistent.
The top-row and left-column choices are instructions about where Excel should find matching labels; they are not merely formatting options. If a range has a title row above the actual column headers, including that title can make the output confusing. Select the data and its real labels, not decorative headings.
Linked results and refresh expectations
The Create links to source data option changes how Excel builds the result. When selected, Excel creates links back to the source cells and adds an outline structure so you can expand or collapse details. This can be useful when readers need to trace a summary to contributing values. When it is cleared, the output is a summary rather than a linked detail structure.
A linked consolidation can reflect changes to existing source values through its formulas. It should not be treated as a fully automatic data pipeline: adding rows, changing the shape of a source range, or moving data may require you to revisit the references. Confirm that the ranges still cover the intended data after the source layout changes. If you need a repeatable import and transformation process, Power Query may be a better fit.
Links also make a workbook more dependent on its source locations. If source workbooks are moved, renamed, or unavailable, links may need attention. Keep related files in stable locations and test the summary after opening the workbook on another computer.
Common problems and how to fix them
- A category appears twice: Check for inconsistent spelling, spaces, punctuation, or different label wording. Standardize the source labels before consolidating.
- The total is too small or missing: Verify that every intended range was added, that the destination does not overlap a source, and that the values are numeric rather than text.
- Numbers combine with the wrong rows: You may have used position-based matching on ranges with different orders. Rebuild the consolidation using labels, or align the ranges first.
- Headers show up as results: Check the selected ranges and label options. A title or extra heading row may have been included by mistake.
- The summary does not include newly added data: Check whether the references include the new rows. A consolidation does not necessarily expand its source ranges just because new data is entered nearby.
- The result overwrites something: Undo immediately if possible, or restore from a saved copy. Select a clear destination area before running the command next time.
For a quick check, pick one category and calculate its expected result independently from the source sheets. If that check does not agree with the consolidation, inspect the ranges and labels before sharing the workbook.
When another Excel tool is a better choice
Use the tool that matches the shape of the job. Consolidate is a reasonable choice for a compact summary of ranges that already exist. Other features are better when you need a different output:
- PivotTable: Use a PivotTable when you want to explore a table by category, change row and column groupings, or filter and summarize a structured dataset interactively.
- Power Query: Use Power Query when you regularly append or transform data from multiple files or tables. It is designed for repeatable import and cleanup steps; the process can be refreshed after the source data changes.
- SUMIF: Use a formula for a small, transparent total based on one condition. With regions in A2:A5, amounts in B2:B5, and the criterion East in D2, enter this in E2:
=SUMIF($A$2:$A$5,D2,$B$2:$B$5). If the rows are East 120, West 80, East 30, and North 50, E2 returns 150. - Remove Duplicates: Use Data > Remove Duplicates only when you intend to delete repeated rows from a selected range. It does not summarize their values, and removing entries can discard information.
- Text joining: If by consolidate you mean joining names or notes into one text string, use a text-joining method such as TEXTJOIN where available rather than the Consolidate dialog.
Excel for the web may not expose the same desktop Consolidate workflow. If you cannot find the command in your browser, open the workbook in the desktop app. On Mac, the desktop ribbon also has a Data tab; ribbon layout can vary by release, so look for Consolidate among the data tools. The dialog’s core choices are similar, though the exact interface can differ.
Frequently Asked Questions
Does Consolidate combine worksheets into one worksheet?
It creates a summary from selected ranges; it does not append every source row into one complete table. To stack records, consider Power Query or copy the rows into a properly structured table.
Can I consolidate data from different worksheets?
Yes. Add a reference for each range you want to include. Select ranges with compatible layouts, or use category labels when the rows or columns are not in the same order.
Does Consolidate update when source values change?
With Create links to source data selected, the result is linked to source cells and can reflect edits to those cells. Without links, treat the result as a summary that may need to be rebuilt when the source changes. Structural changes can require updating references either way.
Can I consolidate duplicate names or categories?
Yes, when the ranges include matching labels and you select the relevant label option. Excel can summarize values under a shared label, but it will not infer that different spellings or near-matches refer to the same category.
Why is my Consolidate command unavailable or missing?
Check that you are using desktop Excel and that the Data tab is visible; ribbon layouts vary. If you are working in a browser or cannot locate the command in your Excel build, open the workbook in the desktop app or use a PivotTable, Power Query, or a formula-based summary instead.
Related
- Basic Conditional Formatting in Excel: A Practical Guide
- Basic Excel Table With Data: How to Build and Use One
- Excel Flash Fill: Automatically Fill Repeating Patterns
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Outlook 2010 Rules by Subject: Setup and TroubleshootingYes. In Outlook 2010, create a rule that checks incoming messages for words in the subject, then choose…
- Excel Rows Keep Hiding? How to Find and Unhide ThemExcel rows that seem to keep hiding are usually filtered, grouped, set to zero height, or hidden manually.…
- Create a Box Plot in Excel: Steps and InterpretationTo create a box plot in Excel for Microsoft 365 on Windows, put each group of numeric observations…
- Excel Power Query: Turn a JSON List Into a TableIn Excel for Microsoft 365 on Windows, choose Data > Get Data > From File > From JSON,…
- Excel Refresh All Shortcut for Windows and MacIn Excel for Microsoft 365 on Windows, press Ctrl+Alt+F5 to refresh all data connections in the workbook. On…
- Outlook Distribution Lists: How to Create and Manage ThemYes. You can create a personal distribution list in Outlook—called a contact list in new Outlook and Outlook…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.