Excel Consolidate: Combine Data from Multiple Sheets

To use Consolidate in Excel for Microsoft 365 on Windows, choose a blank destination cell, go to Data > Data Tools > Consolidate, select a summary function such as Sum, add each source range, and click OK. You can combine ranges by matching their positions or by matching row and column labels, which is useful when similar data is arranged differently on different sheets.

What Consolidate Does

Consolidate is an Excel command for summarizing data from multiple ranges into one result area. It is commonly used to combine figures from department, branch, product, or monthly worksheets. For example, you can add sales totals from several regional sheets into a company-wide summary without copying each sheet’s numbers by hand.

The command applies a selected calculation to corresponding data. Sum is the default, but you can also choose Count, Average, Max, Min, Product, Count Numbers, StdDev, StdDevp, Var, or Varp. Consolidate is not the same as putting all source records into one long table: it produces a summary, not a combined list of every transaction.

Excel can consolidate ranges from different worksheets in the same workbook and can also use ranges from other workbooks. The source ranges may be different sizes. Whether that works well depends on whether Excel should match cells by position or identify them by their labels.

Prepare the Source Data

Before opening the dialog, check that each source range contains the values you want to summarize and that the destination area is clear. A dedicated summary sheet makes the result easier to review. Choose a destination outside the source ranges; writing the result over a source could overwrite data or create an unsuitable setup.

For a position-based consolidation, arrange each range in the same pattern. If cell B2 means January revenue on every sheet, Excel can combine B2 with B2, C2 with C2, and so on. Include or omit headings consistently, and make sure corresponding cells represent the same measure.

For a label-based consolidation, use clear, consistent row or column headings. For example, use the same spelling for each department name across worksheets. Avoid using the same label for two different items within a range, since repeated or ambiguous labels make a summary harder to interpret. Check that values intended for addition are numeric; numbers stored as text may not behave as expected in calculations.

Consolidate Ranges by Position

Use position matching when the source ranges have the same layout and the same items occupy the same relative cells. A typical example is three monthly sheets with identical templates: product names are in the same rows and sales figures are in the same columns.

Open the worksheet where you want the summary and select its top-left destination cell.
Go to Data > Data Tools > Consolidate.
In the Function list, select the calculation you need, such as Sum.
Click in the Reference box, select the first source range, and click Add. Repeat for every source range. You can select ranges on other sheets while the dialog is open.
Leave Top row and Left column unchecked if you want Excel to combine values strictly by their positions.
Click OK and inspect the output, including its labels and totals.

For instance, if each region worksheet has quarterly sales in the same four cells, Sum adds the corresponding quarter figures together. It does not know what a cell means; it assumes that matching positions represent matching data. If one source has an extra row inserted, the figures below it may no longer line up correctly even though the ranges look similar.

Consolidate by Matching Labels

Choose label-based consolidation when ranges contain the same categories but those categories do not necessarily appear in the same order or in every source. Suppose one sheet lists Hardware before Services and another lists Services before Hardware. Matching the labels lets Excel bring the figures together under the appropriate category instead of combining cells solely because they occupy the same row.

In the Consolidate dialog, add the source references as described above. Then select Top row if the ranges have column headings you want Excel to match, and select Left column if they have row labels. Select both when the categories appear in both the top row and left column. Click OK to create the summary.

Use the options that describe where the labels actually are. If a range has department names down its left edge but no headings across the top, select Left column only. Excel compares labels to find corresponding entries, so consistent wording matters. A label such as North Sales on one sheet and North on another may be treated as two categories rather than one. Label-based matching is useful for differently ordered lists, but it is not a substitute for cleaning inconsistent category names.

Choose a Function and Set the Output

The selected function determines what Excel calculates for each matching position or label. Sum is appropriate for additive measures such as units sold or expenses. Average can summarize repeated measurements, while Max and Min show the highest and lowest source values. Count counts cells containing entries; Count Numbers counts numeric entries. Those two options can therefore produce different results when a range includes text.

Choose a statistic that makes sense for the underlying data. Adding monthly totals may be appropriate, but adding percentages or average prices can be misleading. If each source reports an average, a simple average of those averages may not equal the overall average when the sources represent different numbers of observations. In that situation, consider combining the underlying totals and counts, or use a method designed for the weighting required.

The destination cell is the top-left corner of the consolidated output. Leave enough room for the resulting rows and columns, and avoid placing unrelated information where the summary may expand. After the command runs, use normal worksheet formatting to distinguish headings, totals, and source data. Formatting improves readability but does not change the underlying calculation.

Update the Summary After Source Changes

By default, Consolidate creates a snapshot of the source values at the time you run it. If the source figures later change, the summary does not automatically recalculate from those ranges. Run Data > Data Tools > Consolidate again, confirm the references, and click OK to rebuild or refresh the result.

The Create links to source data option in the dialog creates links back to the source cells instead of only placing fixed results. This can make the summary respond to changes in source values. Excel may also create an outline that lets you expand or collapse details. Linked output is useful when source values are revised regularly, but it is still important to verify that references point to the intended sheets and cells. Structural changes—such as moving or adding categories—may require you to review the source ranges and run the consolidation again.

For a workbook that changes frequently, consider whether a maintained table or PivotTable would be easier to refresh and audit. Whichever approach you choose, keep the source data organized and check a few totals against the original sheets after refreshing.

Common Problems and How to Fix Them

Numbers appear under the wrong category: The ranges may have been consolidated by position even though their layouts differ. Re-run the command with the appropriate Top row or Left column label options.
A category is missing or duplicated: Check spelling, spaces, and wording in the source labels. Standardize labels before consolidating, then run the command again.
The total is lower than expected: Confirm that every source reference was added and that the selected function is correct. Check whether apparent numbers are stored as text or whether a range leaves out rows.
The result does not change after editing a source: A standard consolidation is a snapshot. Re-run it, or use Create links to source data when linked values suit the workbook.
The output overwrites useful content: Undo if possible, or restore from a saved copy, then choose a clear destination on a summary sheet. Keep the destination separate from the source ranges.

Also check that the selected source ranges include the intended headings when you are matching labels, and do not include unrelated notes or totals. Including a source sheet’s existing grand total along with its detail rows can double-count the same data. When figures look unexpectedly large, inspect whether the ranges include both subtotals and the individual items that make up those subtotals.

Consolidate Compared with Other Excel Methods

Consolidate is convenient when you need a straightforward summary from a manageable set of ranges. It is less suited to repeatedly combining changing lists of transaction records. For that task, Power Query can append tables into one dataset, after which you can load the combined data and summarize it. A PivotTable is another option when you want to group, filter, and rearrange a combined dataset interactively.

Formulas can be preferable for a small, fixed workbook where you want to see exactly how a result is calculated. A simple SUM formula can add known cells, but maintaining many references by hand can be tedious. Consolidate offers a dialog-based way to register multiple ranges and apply one summary function. Choose based on the shape of the input: repeated fixed-layout blocks suit position matching; category summaries suit label matching; growing record lists often call for a table-based workflow.

Excel for Mac has a Consolidate command in the Data tab, though its dialog layout or ribbon placement can differ from Windows. Excel for the web does not provide the same desktop Consolidate command, so open the workbook in the Excel desktop app to use these steps. The resulting workbook can still be viewed or edited in a browser, subject to the features available there.

Frequently Asked Questions

Where is Consolidate in Excel?
In Excel for Microsoft 365 on Windows, go to Data > Data Tools > Consolidate. In Excel for Mac, look for Consolidate on the Data tab. If you do not see it in Excel for the web, open the workbook in the desktop app.

Does Consolidate update automatically?
Not when you use the default option: it creates a snapshot. Select Create links to source data for linked results, or run Consolidate again after changing the source data. Review the references if the source layout changes.

Can I consolidate sheets with different layouts?
Yes, when the ranges share identifiable row or column labels, use the Top row and/or Left column options to match categories. If you leave those options off, Excel matches by position, so differing layouts can combine unrelated values.

Can Consolidate add data from several workbooks?
Yes. Add each workbook range as a source reference in the Consolidate dialog. Check that you selected the correct ranges and that the workbook references remain valid, particularly if files are moved or renamed.

Does Consolidate combine every transaction into one list?
No. It summarizes corresponding cells or categories using a function such as Sum or Count. To append transaction rows into one list, use a data-combining workflow such as Power Query rather than Consolidate.

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.