How to Consolidate Numbers in Excel by Sheet or Label

How-to basics · Updated November 21, 2026

To consolidate numbers in Excel, select a blank destination cell, then choose Data > Consolidate. Set the function to Sum, add each source range, and click OK. If the ranges have matching row or column labels, select the matching label options so Excel combines corresponding items instead of relying on their positions.

What You’ll Need

  • A workbook open in the Excel desktop app. These steps use Microsoft Excel for Microsoft 365 on Windows; Excel for Mac has a similar Consolidate command, though its ribbon layout can vary. The Consolidate tool may not be available in Excel for the web, so open the workbook in the desktop app if you cannot find it.
  • The number ranges you want to summarize, such as monthly sales tables on separate worksheets. The ranges can be on different sheets or in different workbooks.
  • A blank area for the result. Keep the destination away from source data so the summary does not overwrite numbers or labels.
  • A decision about how to match the data: by position when every range has the same layout, or by labels when rows or columns may be in a different order.

Step-by-Step

  1. Check and prepare the source ranges. Confirm which cells contain the values and whether the ranges include headings. For example, each monthly sheet might have department names in column A and sales figures in column B. Make sure labels that should refer to the same item use consistent wording, such as North Region on every sheet. Remove accidental blank rows or columns within the tables, and check that the figures are stored as numbers rather than text. You do not need to rearrange tables that have different row orders if you plan to consolidate by labels.
  2. Choose a blank destination cell. Go to the sheet where you want the summary and click the cell where its upper-left corner should appear. Leave enough empty space to the right and below for the full result. Do not choose a cell inside one of the source tables: the output can overwrite data, or Excel may not be able to use the source range reliably. If you want a separate summary sheet, add one and give it a clear name such as Consolidated.
  3. Open the Consolidate dialog. On Windows, select Data > Consolidate in the Data Tools group. If the command is not visible, maximize the Excel window or check the Data tab for a collapsed group or additional commands. In Excel for Mac, look on the Data tab for Consolidate; the location and dialog appearance can differ slightly by version. If you are using Excel for the web and cannot find the command, open the file in desktop Excel.
  4. Choose the summary calculation. In the Function list, select Sum to add corresponding values, which is the usual choice for combining sales, quantities, or expenses. The list also includes calculations such as Average, Count, Max, and Min. Choose the function that answers your question: Count counts numeric entries, while Count Numbers is the label used in some Excel dialogs for counting numbers. Averages across separate ranges are averages of the values in those ranges, not necessarily a weighted average of all underlying records.
  5. Add the first source range. Click in the Reference box, select the worksheet containing the first table, and drag over the complete range you want to include. Include row and column headings if you intend to match labels. Click Add to place the reference in the All references list. If selecting cells is awkward, you can type a sheet reference in the box, for example January!$A$1:$B$8, then click Add. The sheet name and cell addresses must match your workbook.
  6. Add every other source range. Select the next worksheet and range, then click Add again. Repeat until each intended source appears in All references. Check the list before continuing: one range accidentally left out will be absent from the summary, while an extra range can double-count figures. For ranges in another open workbook, select that workbook when choosing the cells. Keeping source sheets in the same workbook generally makes the setup easier to maintain.
  7. Choose how Excel should match the tables. If every range has the same items in the same row and column positions, leave Top row and Left column unchecked. Excel then combines values according to their relative positions. If ranges include headings and items might appear in a different order, select Top row for column headings and Left column for row labels, as applicable. For the department example, include the department names and figures in each selected range, then select Left column. Excel uses matching labels to line up items; it can also include an item that appears in only one source range.
  8. Decide whether to link to the source data. Leave Create links to source data unchecked for a one-time summary of the current figures. Select it if you want the consolidated result connected to the source cells. When linked, Excel creates an outline-style summary that can be expanded to show detail, and changes to source values can flow through to the result. This layout is different from a simple flat table and may not suit every report. If you need a clean, fixed snapshot, leave the option off.
  9. Run the consolidation and inspect the result. Click OK. Check the output headings, the total for a familiar item, and the overall range. Compare at least one result with a manual addition of its source values. If you selected Sum and the same label appears in several sheets, its consolidated amount should equal those source amounts added together. Save the workbook after confirming that the output is in the intended location.

Example: Suppose January has North sales of 120 and South sales of 80, while February has North sales of 150 and South sales of 90. With the labels in column A, values in column B, and Left column selected, the consolidated Sum should show North as 270 and South as 170. If you instead consolidate by position, the same results appear only when North and South occupy the same rows in both source ranges.

Common Mistakes

  • Using positional matching on tables in different orders. If North is in row 2 on one sheet and row 3 on another, an unchecked Left column option can add unlike items together. Match by labels when order varies.
  • Including labels without selecting label matching. If a range includes text headings but you leave Top row and Left column unchecked, Excel treats data by position. Include the relevant label option when you want headings to identify corresponding items.
  • Using inconsistent names. Excel may treat labels such as West, West Region, and Western as different items. Standardize the wording in the source tables before consolidating if those names mean the same thing.
  • Selecting a range that is too large or too small. An extra total row can cause figures to be counted twice; a missing final row leaves data out. Check each reference and include the same intended types of cells in every source range.
  • Assuming a consolidation is a live table by default. Without Create links to source data, the result is a snapshot. If source values later change, run the consolidation again or use a linked approach.
  • Choosing the wrong function. Average or Count will not produce a total. Confirm the Function setting says Sum when the goal is to add amounts.

Tips

  • Give source sheets descriptive names, such as Jan and Feb, and keep a short note on the summary sheet listing which sheets are included. That makes it easier to review the scope later.
  • For recurring work, use the same label spelling and table structure each time. Consistent source data makes it faster to add ranges and reduces the chance of splitting one category into multiple labels.
  • Use the Consolidate tool when you want to combine separate ranges into a summary. If you need to append all source rows into one long table for filtering or pivoting, Power Query is often a better fit than Consolidate.
  • If the source ranges are consistently laid out and you want a formula that recalculates, a direct SUM formula can be simpler. With East sales in B2 and West sales in B2, enter this in the summary cell: =SUM(East!B2,West!B2). If the two source values are 120 and 150, the result is 270. This adds those two specific cells; it does not automatically add new sheets or find matching labels.
  • For a linked consolidation, expand the outline controls beside the result when you need to inspect source detail. Keep the source sheets in place and avoid casually renaming or moving workbooks that contain the referenced data.

Troubleshooting

The Consolidate command is missing: Make sure you are using desktop Excel and have selected the Data tab. In Excel for the web, open the workbook in the desktop app if the command is unavailable. On a narrow window, ribbon groups may be compressed, so maximize Excel or inspect the Data tab carefully.

The result is blank or smaller than expected: Reopen Data > Consolidate and review All references. Confirm that each source range covers the intended cells and that you clicked Add after selecting each one. Check that the destination cell is where you expect the output to begin, and confirm you chose the correct function.

Numbers from different categories are combined: The tables may have been consolidated by position. Run the operation again with ranges that include their labels, then select Left column and/or Top row as appropriate. Also check for inconsistent labels; matching by labels cannot infer that two differently named categories mean the same thing.

Some values do not contribute to the Sum: Check whether the cells contain numeric values. Numbers imported as text, cells containing errors, and unexpected blank cells can affect the result. Convert text-formatted numbers to numbers in the source data, then run the consolidation again. Do not assume a value that merely looks numeric is stored as a number.

The result does not change after editing a source: If you left Create links to source data unchecked, the output is a snapshot; run the consolidation again to refresh it. If you selected links, check that the source cell is included in a listed reference and that the outline is showing the summary level you expect. For a straightforward formula-based total, use a SUM formula that directly references the source cells.

Frequently Asked Questions

Can I consolidate numbers from different worksheets?

Yes. Add a reference from each worksheet to the All references list. Select matching by position if the layouts are identical, or include labels and select the relevant label options if the order differs.

Can I consolidate data from different workbooks?

Yes, you can add ranges from other open workbooks. Keep those workbooks accessible while setting up the references, and verify the references carefully. For a report that must remain easy to update, storing related source sheets in one workbook may be simpler.

Does Consolidate update automatically when the source changes?

Only if you selected Create links to source data, which creates a linked outline-style result. Without that option, the result is a snapshot and you need to run the consolidation again to include later edits.

Can the source ranges have different sizes?

They can, especially when you match by labels. Excel can align matching row or column labels and include labels found in only some ranges. Review the result for missing, unexpected, or inconsistently named categories.

What is the difference between Sum and Count?

Sum adds numeric values together. Count counts numeric entries rather than adding their values. Choose Sum for totals and Count when you need to know how many numeric cells are included.

Can I use Consolidate for a table that grows every month?

You can add each new range and run the tool again, but this requires maintaining the references. If monthly data is appended regularly and you need one combined detail table, consider Power Query or a structured data workflow 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.