Excel Consolidate Multiple Sheets: Steps and Options

How-to basics · Updated November 19, 2026

To consolidate multiple sheets in Excel for Microsoft 365 on Windows, go to Data > Consolidate, choose a summary function, add each source range, select whether labels are in the top row or left column, and click OK. This combines values into one result area; for data that should keep updating as new rows are added, Power Query or formulas may be a better fit.

What Excel consolidation does

Excel’s Consolidate tool brings together information from two or more ranges. It can add corresponding values, calculate an average, count entries, or apply other summary functions. The result appears in a destination range that you choose. For example, you can combine monthly department totals into one annual summary without manually copying each total into a new sheet.

There are two ways for Excel to decide which values correspond. If the source sheets have the same layout, it can consolidate by position: the value in the same row and column in each range is combined. If layouts differ, it can match rows or columns by their labels, such as product names or region names. Labels need to be consistent enough to identify the same category; small differences in spelling or extra spaces can lead to separate results.

Consolidation is not the same as stacking all the original records into one long list. It produces a summary of selected source ranges. If your goal is to combine transaction-level rows so you can filter or analyze them later, consider Power Query instead.

Prepare the sheets and choose a destination

Before opening the tool, check the source sheets and decide what the summary should represent. A little preparation makes the result easier to trust and maintain.

  • Check the data types. Values to be added should be numeric. Text numbers, blank cells, and error values may not behave as intended in a summary.
  • Standardize labels. Use the same wording for repeated categories, such as East, West, and Central. Check for spelling differences and accidental spaces.
  • Choose the ranges. Include the relevant data and, when you want label matching, the row or column labels. Avoid selecting unrelated notes or totals.
  • Pick an empty destination. A separate summary sheet is often easiest to review. Do not place the output over any source range.

For instance, suppose each regional sheet has product names in column A and quarterly sales in columns B through E. If every sheet uses the same row order, you can consolidate by position. If products appear in different orders or some regions omit products, include the product labels and use the left-column label option so Excel can match categories.

Steps to consolidate sheets in Excel

These instructions apply to Excel for Microsoft 365 on Windows. First create or select the destination sheet, then follow these steps:

  1. Click the cell where the upper-left corner of the summary should appear. Leave enough blank space for the output.
  2. Open Data > Consolidate. In the dialog, choose the function that should combine the source values, such as Sum.
  3. Click in the Reference box, select a source range on a worksheet, and click Add. Repeat for every source range. You can select ranges from other open workbooks as well, but keep those workbooks available while setting up the consolidation.
  4. If the selected ranges include labels, check Top row, Left column, or both, as appropriate. If the ranges have identical layouts and you want position-based results, leave these options unchecked.
  5. Optionally select Create links to source data if you want the summary to use formulas linked to the source cells rather than a one-time result.
  6. Review the listed references, then click OK. Check the output against a few source values before relying on the summary.

In desktop Excel for Mac, the Consolidate command is also available from the Data tab, though the dialog layout can vary by version. Excel for the web may not provide the same desktop consolidation workflow; if you cannot find the command, open the workbook in the desktop app.

Choose the right function and label options

The function in the Consolidate dialog determines what Excel calculates for each corresponding position or label. Sum is common for sales, expenses, or quantities. Average calculates an average, while Max and Min return the largest and smallest values. Count counts nonempty entries, whereas Count Numbers counts numeric entries. The dialog also offers Product, StdDev, StdDevp, Var, and Varp for cases where those calculations are appropriate.

For example, imagine two sheets contain sales by product. One records Widget A as 120 and Widget B as 80; the other records Widget A as 90 and Widget B as 110. With matching row order and Sum, the results are 210 for Widget A and 190 for Widget B. Choosing Average instead returns 105 and 95. Choose the function based on what the figures mean: averaging monthly totals is not necessarily the same as calculating an average per transaction.

Use Top row when column headings identify the values to combine, and Left column when the first column contains row labels. If you select both, Excel can use both sets of labels. When you do not select label options, Excel aligns ranges by their relative row and column positions. That can be correct for identically structured sheets, but it can silently combine unrelated items if rows have been sorted differently.

Static results, source links, and refresh behavior

Without Create links to source data, the consolidated output is a result rather than a live link to each source value. If a source number changes later, the summary does not automatically recalculate from that change. Run the consolidation again when you need an updated result.

With Create links to source data selected, Excel creates formulas that refer to the source cells and may organize the summary with an outline that can be expanded or collapsed. Changes to values in the referenced cells can flow through to the linked summary. However, this does not make the setup a fully automatic data pipeline: adding a new source sheet, extending a selected range, or changing the source layout can require updating or rebuilding the consolidation.

That distinction matters for recurring reports. If the sheets are fixed and you need a quick summary, consolidation is straightforward. If data is added regularly, document the exact source ranges and refresh procedure, or use a method designed to combine changing tables. Always verify the output after changing the source structure; a summary can look plausible while omitting new rows.

When formulas or Power Query are a better fit

A formula is useful when sheets share the same cell layout and you want a direct calculation in a particular cell. For example, if the monthly totals are in cell B2 on sheets named January, February, and March, and those sheets sit next to one another in the workbook, a 3-D reference can add that cell across the sheet range.

With monthly totals in B2 on contiguous sheets named January, February, and March, the formula entered in the summary cell is =SUM(January:March!B2). It returns the sum of B2 on those three sheets, provided no other sheet between January and March should be excluded. A 3-D reference includes sheets between the named endpoints in workbook order, so moving a sheet into or out of that span can change which sheets are included. This approach is best for a known, consistent layout—not for matching changing category lists.

Power Query is usually more suitable when each sheet contains a table of records and you want to append those rows into one dataset. In Excel for Windows, you can turn each range into an Excel table, select a table, and use Data > From Table/Range to load it into Power Query. In the Power Query Editor, use Home > Append Queries to combine tables, then load the result back to Excel. A query can be refreshed after source data changes, and tables can help include additional rows added to a source table. Confirm that the query sources and table boundaries cover the data you intend to include.

Choose based on the shape of the task: Consolidate summarizes matching ranges; formulas calculate specific relationships between cells; Power Query combines and transforms records. A PivotTable can then summarize a combined dataset by fields such as month, product, and region.

Common problems and how to fix them

  • Unexpected extra categories: Similar-looking labels may differ in spelling, punctuation, or spaces. Standardize the source labels and run the consolidation again.
  • Values appear under the wrong heading: The ranges may have been combined by position even though rows or columns are in a different order. Include labels and select the relevant top-row or left-column option.
  • Some rows are missing: Check that every reference covers the full source range, including the intended labels and last data row. A consolidation only uses the ranges you added.
  • The result is too small or blank: Check that the chosen function fits the source data and that values are actually numeric where required. A number stored as text may not contribute to a sum.
  • Updates do not appear: If you did not create links to source data, the result is static. Rerun the consolidation or choose a refreshable approach such as Power Query.
  • The source references are wrong: Reopen the Consolidate dialog and review the reference list. Remove incorrect entries and add the right ranges before creating the output.

A useful check is to calculate a small example by hand. If you are consolidating sums, choose one category and add its source values independently, then compare that figure with the result. This can reveal a missed range or label mismatch before you share the full report.

Frequently Asked Questions

Can Excel consolidate sheets with different layouts?
Yes, if the ranges have useful row or column labels and you select the matching label options. Excel can match values by labels instead of requiring the same position. The labels should be consistent; consolidation is not a substitute for cleaning ambiguous or differently named categories.

Does Excel Consolidate update automatically?
Not in every setup. A consolidation without source links produces a static result. With Create links to source data, changes to referenced cells can update the linked calculations, but adding sheets or extending source ranges generally requires you to revise the references or run the process again.

Can I consolidate sheets from different workbooks?
You can add references to ranges in other open workbooks through the Consolidate dialog. Make sure you select the intended ranges and keep the source files accessible while setting up the references. For a repeatable process across files, Power Query may be easier to maintain.

What is the difference between Consolidate and Append?
Consolidate calculates a summary from corresponding positions or matching labels. Append, as used in Power Query, places rows from tables together in one longer dataset. Use Consolidate for summaries such as combined totals; use Append when you need the underlying records in a single table.

Why does Excel show duplicate labels in a consolidated result?
Excel may treat labels that look similar as different when their text is not actually identical, such as North and North with an extra space. Check the source text for inconsistent spelling or hidden spaces, standardize it, and run the consolidation again.

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.