How to Consolidate Data in Excel: Step-by-Step Guide

To consolidate data in Excel for Microsoft 365 on Windows, select the destination cell, choose Data > Consolidate, select a function such as Sum, add each source range, and click OK. Use matching positions when the source tables have the same layout, or select the label options when their rows or columns are arranged differently.

What Excel consolidation does

Excel’s Consolidate command combines values from several ranges into one summary range. It can calculate sums, averages, counts, maximums, minimums, and other listed functions. For example, you might combine monthly sales worksheets into one annual summary, or bring together department totals from separate sheets.

The command is useful when the source data is already organized into ranges and you want a summary without writing a formula for every result. It is not the same as merging cells, joining text, or appending all source rows into one long table. Consolidate produces a summarized result: matching positions or labels are combined using the selected function.

Before starting, decide whether the source tables match by position or by category labels. This choice determines how Excel pairs the values. Also decide whether the summary should be a snapshot or linked to source cells, and whether the source data is likely to grow.

Prepare the source ranges

Good preparation prevents many consolidation errors. Keep each source range rectangular and make sure it contains only the intended data and, if needed, its headings. Avoid selecting entire worksheets: include the actual table area instead, so blank rows, notes, and unrelated figures are not treated as source data.

For consolidation by position, make the ranges the same shape and put corresponding items in the same row and column.
For consolidation by labels, use clear, consistent row or column headings. For instance, use Product rather than switching between Product, Products, and Item.
Check that the values to be summarized are numeric. Text-formatted numbers, blanks, and errors can produce results that differ from what you expect.
Keep a separate destination area or worksheet. The output should not overlap the source ranges.
If you want to compare sources later, retain their names and dates in the workbook; the Consolidate result does not automatically provide a detailed audit trail for each contributing value.

For example, suppose three worksheets each contain rows for East and West and columns for January and February. If each table uses the same arrangement, Excel can combine the values by position. If the rows appear in a different order on one sheet, use labels so East is paired with East rather than with whichever category happens to occupy the same row.

Consolidate data step by step

Choose the destination. Open the worksheet where the summary should appear and select the upper-left cell of the output area. Leave enough blank space for all expected rows and columns. If you are consolidating by labels, the result may include the labels from the source ranges.
Open the command. On the Windows ribbon, select Data > Consolidate. The Consolidate dialog box opens. If you do not see the command, check that you are using the desktop Excel app rather than Excel for the web.
Choose a function. In the Function list, choose how Excel should combine matching values. Sum is common for sales or expenses; Average can summarize measurements; Count counts numeric entries. Choose the function that fits the meaning of your data, not just the one that produces a convenient-looking result.
Add the first source range. Click in the Reference box, select the range on the first worksheet, and click Add. The range should appear in the All references list. Include its headings if you intend to use the label options.
Add the remaining ranges. Select each additional worksheet range and click Add after selecting it. Confirm that every intended source appears in All references and that no range has been added twice.
Choose how Excel matches entries. If all ranges have identical layouts and the same items in the same positions, leave Top row and Left column unchecked. If the source ranges include headings and categories may be in different positions, check Top row for column headings, Left column for row labels, or both when both kinds of labels identify the data.
Choose whether to link the result. Check Create links to source data if you want the summary to refer to the source cells and show an outline that can be expanded. Leave it unchecked for a straightforward consolidated result that does not link back to individual source cells.
Run and inspect the summary. Click OK. Check several totals against the source worksheets, confirm that labels and values landed in the expected rows and columns, and apply number formats or other presentation formatting as needed.

In Excel for Mac, the command is also available through the Data tab as Consolidate in supported desktop versions, though the ribbon arrangement can vary by release. Excel for the web does not provide the desktop Consolidate dialog; open the workbook in the desktop app to use this procedure. Windows shortcuts such as Alt-key ribbon sequences can vary by Excel build and language, so the ribbon path is the dependable route. On Mac, use the Data tab rather than assuming a Windows Alt shortcut.

Choose by position or by labels

Consolidate by position is appropriate when every source range has the same structure. If cell B2 represents East-region January sales on every sheet, Excel combines the values in those corresponding positions. The ranges should have matching dimensions, and their order must be consistent. If a category moves to another row on one sheet, a position-based summary may combine unrelated figures.

Consolidate by labels is better when the same categories appear in different orders or when a source omits a category. Select Top row and/or Left column according to where the labels are located. Excel uses the labels to match corresponding rows or columns, rather than relying only on their positions. Labels should be consistent and unambiguous; spelling differences or extra spaces can cause categories to be treated as separate items. Clean the headings before consolidating if they were entered by different people.

For example, one store sheet might list Apples, Pears, and Oranges, while another lists Oranges, Apples, and Pears. With Left column selected and the labels included in the references, Excel can align the fruit names and combine their values. Without that option, it could add the first row on one sheet to the first row on the other, even though those rows refer to different fruit.

Which function should you use?

The selected function applies to the corresponding values in all added ranges. Sum is usually suitable for quantities that can be added, such as units sold. Average is appropriate only when averaging the underlying observations makes sense. If the worksheets contain averages calculated from different numbers of records, averaging those averages may not equal the true overall average. In that case, consolidate the underlying totals and counts separately, then calculate the overall average from those totals.

Sum: adds matching numeric values.
Average: calculates the average of matching values; check how blanks and missing categories should be interpreted.
Count: counts numeric entries, which is not the same as counting every nonblank text or numeric cell.
Max or Min: returns the largest or smallest matching value.
Other listed functions: choices such as Product, Count Numbers, StdDev, and Var are available in the dialog; use them only when their statistical meaning matches the question you are answering.

A common mistake is using Average on pre-calculated departmental averages and treating the result as an organization-wide average. If one department averaged 10 units across 2 records and another averaged 20 across 100 records, a simple average of those two figures gives 15, while the weighted overall average is much closer to 20. Consolidation does not infer weights or business rules for you.

What linked consolidation means

Create links to source data changes how Excel builds the output. Instead of only placing consolidated results in the destination, Excel creates a summary with outline controls and links back to source cells. You can expand or collapse levels to inspect detail. This is helpful when someone needs to trace a total to its contributing worksheets.

Linked results can make a workbook more complex, and the outline may initially hide details. Expand the outline using its controls beside the row or column headings. If you do not need source-level drill-down, an unlinked summary is often simpler to distribute. Treat either output as something to verify after source edits: do not assume the command automatically incorporates newly added rows or newly created worksheets. Review the references and run or update the consolidation as appropriate for the workbook.

Neither option turns separate worksheets into a normalized database or a refreshable query by itself. If your source tables will keep growing, consider whether a structured table, PivotTable, or Power Query process is a better fit.

Common problems and how to fix them

A category is missing or split into two rows: inspect the source labels for spelling variations, trailing spaces, or inconsistent wording. Make the labels consistent, then rebuild the consolidation.
Values seem to be combined incorrectly: check whether the ranges were consolidated by position when they should have been matched by labels. Also verify that Top row and Left column correspond to the headings you included.
A total is too large: confirm that no source range was added twice and that the selected ranges do not include subtotals as well as the underlying detail. Adding both can double-count figures.
Some figures are not included: check that the source range covers the full table and that numeric entries are stored as numbers rather than text. Review blanks, errors, and any rows excluded from the reference.
The output overwrites other data: undo immediately if possible, or close without saving if necessary, then choose a clear destination with sufficient empty space. Back up important workbooks before changing their structure.
New source data does not appear: the original reference may cover only the old range. Add or revise references and run the consolidation again, or choose a tool designed to refresh from expanding data sources.

Formatting can also be misleading. A displayed number may look rounded even though the stored value has more decimal places, and currency formats do not change the underlying calculation. Compare actual values or adjust the destination number format before deciding that the arithmetic is wrong.

Consolidate compared with other Excel tools

Consolidate suits a small set of clearly defined ranges when the goal is a compact summary. A PivotTable is often preferable when you need to filter, rearrange categories, drill into records, or summarize one consistent data table. Power Query is a stronger option when you repeatedly append similarly structured files or worksheets and need a refreshable preparation process.

Formulas can be clearer for a small, fixed workbook where you want each calculation visible in a cell. A formula that sums the same cell across consecutive worksheets can use a 3-D reference; for example, with monthly worksheets named January, February, and March in that tab order, and each month’s value in B2, the formula =SUM(January:March!B2) returns the sum of those three B2 cells. If they contain 120, 150, and 130, the result is 400. This method depends on the sheet names and their order, and it does not match categories by labels.

For combining full records rather than summarizing them, use an append workflow such as Power Query or carefully stack the tables, then analyze the combined data. Consolidate is not a substitute for appending rows: it reduces matching values to a function result and may not preserve the individual transactions.

Frequently Asked Questions

Can I consolidate data from different worksheets?
Yes. Add a reference for each range, including ranges on different worksheets in the same workbook. You can also use ranges from other workbooks through the Reference box, but check each reference carefully and keep source files accessible if the workbook depends on them.

Can I consolidate worksheets with different row orders?
Yes, when the ranges include consistent labels. Add the headings or row labels to the references and select Top row, Left column, or both as appropriate. If you leave the label options unchecked, Excel matches by position, so different row orders can produce incorrect combinations.

Does Consolidate update automatically when I add rows?
Do not assume it will. A consolidation uses the references you added, so data outside those referenced ranges is not automatically included. Update the source references and run the command again, or use a refresh-oriented tool if new records arrive regularly.

Why are my categories appearing more than once?
Excel may be seeing different labels as distinct, even if they look similar. Compare the text for spelling, extra spaces, punctuation, and singular or plural forms. Standardize the labels in the source ranges before consolidating.

Can I undo a consolidation?
Immediately after running the command, Ctrl+Z on Windows or Command+Z on Mac generally undoes the last worksheet action. If you have made other changes since then, undo may affect those later actions instead. Save a backup before making a major change, especially when the destination contains existing work.

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.