To create a consolidated sum in Excel for Microsoft 365 on Windows, select the destination cell, choose Data > Consolidate, set Function to Sum, add the source ranges, and select OK. If the sheets have matching row or column labels, select the matching label options so Excel combines like categories rather than simply adding cells in the same positions.
What a consolidated sum does
Excel’s Consolidate command combines data from one or more ranges into a summary range. Choosing Sum adds the values in corresponding categories or positions. It is useful when figures are split across worksheets, such as monthly sales on separate tabs, or stored in multiple tables that need one summary.
Consolidation is different from placing a simple total beneath one list. It can bring together a set of categories from several sources, even when the categories are not in the same order. Depending on your settings, it can either create a snapshot of the source values or create formulas linked to the source cells.
The key decision is how Excel should identify matching data. With labels enabled, it matches equivalent row or column headings. Without labels, it matches by position: the first cell in each source is combined with the first cell in every other source, the second with the second, and so on. That positional behavior can produce misleading totals if the source layouts differ.
Prepare the source ranges
Before consolidating, check that each source contains a clean, rectangular range. A typical table has a heading row, a label column, and numeric values. Remove subtotals and grand totals from the source ranges; otherwise, those totals may be added again. Also check for blank rows, misspelled labels, inconsistent names, and numbers stored as text.
For example, a department might keep a small table on each regional worksheet with product names in column A and sales in column B. The regional tables can list products in different orders if you use the left-column label option, but “Desk” on one sheet and “Desks” on another will be treated as different labels. Standardize spelling and wording before running the command.
- Use a distinct, consistent label for every category you want Excel to match.
- Keep headings in the same row or column orientation in every source.
- Select only the data and its labels, not unrelated notes or existing totals.
- Make sure values intended for addition are numeric, not text that merely looks like a number.
- Choose an empty destination area with enough room for the combined categories and headings.
Consolidate can work with ranges on different worksheets, and the dialog lets you add references one at a time. It is also possible to use ranges from other workbooks. For a dependable result, make sure the referenced files and ranges are available and that the data structure is understood before you add them.
How to use Data > Consolidate
In the Windows desktop version of Excel for Microsoft 365, use these steps:
- Open the workbook and select the top-left cell where the summary should appear. Leave enough blank space below and to the right for the result.
- Open Data > Consolidate. In the dialog, choose Sum from the Function list.
- Click in the Reference box, select the first source range, and click Add. Repeat for each additional range. The references appear in the All references list.
- If the source ranges include headings, select Top row and/or Left column, according to where the labels appear. If there are no labels, leave these options clear.
- Select Create links to source data if you want the output to use formulas linked to the original ranges. Leave it clear for a static consolidation.
- Review the references and options, then click OK. Check the output against the source data, especially if labels or totals were involved.
The dialog’s reference list matters: selecting a range does not include it until you click Add. If a reference is wrong, select it in the list and remove or correct it before confirming. Use the range selector button beside the Reference box if the dialog makes it difficult to select cells directly.
Example: combine monthly category totals
Suppose January and February worksheets each contain a two-column table with product names in A2:A4 and sales in B2:B4. January lists Chairs, Desks, and Lamps; February lists the same three products in a different order. Select an empty destination, add both A1:B4 ranges, and select Top row and Left column if row 1 contains the headings and column A contains the product labels. Excel can then combine sales by product name rather than by row position.
For example, if January has Chairs 120, Desks 200, and Lamps 80, while February has Lamps 90, Chairs 130, and Desks 150, the consolidated totals should be Chairs 250, Desks 350, and Lamps 170. The grand total is 770. This gives you a quick check on the result: it should equal the sum of the source sales, provided each source range includes each sale exactly once and excludes any existing subtotals.
If you leave the label options unchecked in this example, Excel adds the first data row from each range together, then the second rows, and then the third. Because February’s products are in a different order, the resulting figures would combine different products. That is why checking labels is not a cosmetic setting; it changes how Excel pairs the data.
Matching by labels versus matching by position
Select Top row when the source ranges have column headings that should be matched, such as years or departments. Select Left column when the first column contains row labels, such as product names or regions. You can select both when both kinds of labels are present. Excel uses the selected labels to organize the result, so consistent names are essential.
When labels are not selected, consolidation is position-based. This is appropriate when every source has the same shape and the same meaning in every cell—for example, a fixed grid where row 2 is always “North” and column B is always “Units.” It is risky when a source has been sorted, when categories are missing, or when someone inserted a row in only one worksheet.
Labels that look similar but are not identical may not match. Extra spaces, punctuation differences, and abbreviations can create separate entries. If the summary contains unexpected duplicate categories, compare the source labels carefully and normalize them before running the consolidation again.
Static results and links to source data
With Create links to source data cleared, the output is a snapshot: it contains the consolidated results at the time you ran the command. If a source value changes later, the result does not automatically update. Run Consolidate again when you need a fresh snapshot.
With the link option selected, Excel creates formulas connected to the source data and displays an outline structure that can show or hide detail. This is helpful when you want a summary that responds to changes in source cells and need to inspect the contributing values. It also means the summary depends on those source references continuing to point to the intended cells. Review the links if ranges move, worksheets are renamed, or workbooks are reorganized.
A linked consolidation is not the same as a live, automatically expanding database connection. If the source ranges grow, do not assume newly added rows are included; verify the references and update them as needed. For recurring reporting, a consistently structured Excel Table and a PivotTable, or a Power Query process, may be easier to refresh and maintain.
Formula alternatives for simpler cases
For a few worksheets with identical layouts, a formula can be more transparent than the Consolidate dialog. With worksheets named Jan, Feb, and Mar, and the same item total in cell B2 on each, enter this in the summary cell:
With the corresponding value in B2 on each worksheet named Jan, Feb, and Mar, the formula =SUM(Jan:Mar!B2) returns the sum of those three B2 cells. For instance, values of 120, 130, and 140 return 390. This three-dimensional reference includes worksheets between Jan and Mar in the workbook’s tab order, so keep the intended sheets within those bounds.
If categories are in the same positions on each sheet, you can instead sum matching ranges directly. With values in B2:B10 on worksheets Jan and Feb, enter =SUM(Jan:Feb!B2:B10) in the summary cell. This adds the cells in that range across the sheets; it does not match category names. It is suitable only when the layouts and row meanings align.
For a single source table where you want to total values matching one category, use SUMIF. With product names in A2:A5, amounts in B2:B5, and the product to find in D2, enter =SUMIF(A2:A5,D2,B2:B5). If D2 contains “Chairs” and the matching amounts are 120 and 130, the result is 250. Unlike Consolidate, this formula summarizes one criteria range and one sum range; it does not build a complete multi-sheet consolidation by itself.
Common problems and checks
- The result is unexpectedly too large: Check whether the selected source ranges include subtotals, grand totals, or overlapping rows. Consolidation adds everything in its selected ranges.
- Categories appear more than once: Look for spelling, spacing, or punctuation differences in the labels. Correct the source names and consolidate again.
- Numbers seem to be ignored: Check whether values are stored as text. Convert them to numbers and confirm that the cells are not blank or otherwise outside the selected range.
- Totals are paired with the wrong categories: Confirm that the appropriate top-row or left-column label option is selected. If the data has no matching labels, verify that every range has the same layout before using positional matching.
- Changes do not appear in the result: A consolidation without source links is a snapshot. Re-run it, or use linked output or an appropriate formula-based method.
- New rows are missing: Recheck the references. A range that originally ended at row 20 will not necessarily include a new row 21.
When a total matters, verify it independently. Add the source totals separately or compare the consolidated grand total with a known overall figure. This simple check can reveal an omitted sheet, a duplicated range, or an included subtotal before the summary is shared.
Choosing the right method
Use Data > Consolidate when you want a one-time summary from multiple ranges and need Excel to combine categories by labels. Use a 3-D SUM formula when worksheets have the same structure and you want a concise formula across a run of tabs. Use SUMIF when you need a criteria-based total from one table. For larger recurring reports with changing source data, consider a PivotTable or Power Query rather than repeatedly rebuilding a manual consolidation.
These approaches are not interchangeable. Consolidate is convenient for assembling a summary, but it does not automatically make inconsistent source data consistent. A formula is easier to inspect and recalculate, but it relies on references and matching layouts you define. A PivotTable is useful for interactive grouping and filtering, while Power Query is designed for importing and reshaping data before loading a result. Choose based on how often the sources change and how much repeatability the report needs.
On Excel for Mac, the available ribbon layout can differ from Windows, so the exact location or appearance of the Consolidate command may vary by release. Look on the Data tab for Consolidate; the core choices—function, references, labels, and links—serve the same purpose. Excel for the web may not expose every desktop data tool in the same way. If the command is unavailable in your web session, open the workbook in the Excel desktop app to use the Consolidate dialog.
Frequently Asked Questions
How do I sum the same cell across multiple Excel sheets?
Use a 3-D SUM formula when the relevant worksheets are together in tab order and share the same layout. For example, =SUM(Jan:Mar!B2) adds B2 on every sheet from Jan through Mar, inclusive.
Does Excel Consolidate update automatically?
Not when you leave Create links to source data unchecked; that creates a snapshot. Selecting the link option makes a formula-linked result, but you should still check whether the source references include any newly added rows or ranges.
Can I consolidate data when the categories are in a different order?
Yes. Add the source ranges and select Left column for row labels, or Top row for column labels. The labels must be consistent for Excel to recognize categories as the same.
Why does my consolidated total count some figures twice?
The selected ranges may overlap or include subtotals and grand totals. Inspect every reference in the Consolidate dialog and select only the underlying data that should be added.
Can I consolidate data from different workbooks?
Yes, you can add references to ranges in other workbooks. Confirm that each reference points to the intended sheet and cells, and verify the result. If you need a repeatable process for changing files or growing tables, a refreshable data workflow may be more suitable than manually maintained references.
Related
- Excel Delimited Text to Columns: How to Split Data
- Defining Print Area in Excel: Setup and Printing Tips
- DDSolver Excel Add-in: What It Does and How to Use It
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Set Up Automatic Email Forwarding in OutlookTo automatically send incoming Outlook messages to another address, open Outlook on the web or new Outlook, go…
- Remove Duplicates in Excel: Keyboard ShortcutIn Excel for Microsoft 365 on Windows, select a cell in your data and press Alt, then A,…
- Excel File Data: What It Contains and How to Use ItExcel file data is the information stored in a workbook: values, text, dates, formulas, tables, and sometimes charts,…
- Excel Online: How to Insert, Format, and Manage TablesTo insert a table in Excel Online, select the range of cells containing your data, then choose Insert…
- Excel Dropdown List: Create, Edit, and Troubleshoot OneTo make a dropdown list in Excel for Microsoft 365 on Windows, select the cell or cells, choose…
- How to Archive Old Emails in Outlook 365 and Find ThemTo archive old emails in Outlook 365, select the messages and choose Archive; Outlook moves them to the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.