Consolidate Data in Excel Across Multiple Worksheets

How-to basics · Updated November 18, 2026

Yes. In Excel for Microsoft 365 on Windows, use Data > Consolidate to summarize matching ranges from multiple worksheets, or use formulas or Power Query when you need a result that updates or combines records. The right method depends on whether your sheets share the same layout and whether you want totals, a live calculation, or one appended table.

What consolidating worksheet data means

Consolidating brings information from separate worksheet ranges together in a destination range. It can calculate a summary, such as total sales by product, from several sheets. It does not necessarily combine every source row into one long list: that is a different task, usually called appending or stacking data.

For example, imagine a workbook with one sales worksheet for each region. Each sheet has product names in column A and sales figures in column B. A consolidated summary can show the total for each product across the regions. If each sheet contains daily transactions and you need one table containing every transaction, use Power Query to append the rows instead.

Before choosing a method, decide what the output should represent. A total by category, a combined transaction table, and a formula that references corresponding cells are different results, even though all three involve multiple worksheets.

Choose a method for your goal

Excel offers several practical approaches. The main differences are how much setup they need and what happens when source data changes.

  • Data Consolidate: A built-in tool for summarizing ranges, especially when you need a quick total or other aggregate. It can match data by labels or by position.
  • Formulas: Useful for a small, stable set of sheets when you want visible calculations that can recalculate as source values change.
  • Power Query: A strong choice for repeatable work, especially when you need to append rows, clean data, or refresh a combined result after source tables change.
  • PivotTable: Useful for exploring and summarizing a combined data table. It is generally a reporting step after data has been brought into a consistent table, rather than a replacement for appending separate records.

For a one-time summary, start with Consolidate. For recurring reports with consistent source tables, consider Power Query. For just a few corresponding cells, a formula may be easiest to inspect and maintain.

Use Data Consolidate step by step

On Excel for Microsoft 365 for Windows, prepare a destination area and then open Data > Consolidate. The destination can be on an existing worksheet or a new one. Make sure it will not overwrite useful data.

  1. In the Function box, select the calculation you need, such as Sum, Average, Count, Max, or Min.
  2. Click in the Reference box and select the first source range. Include the relevant labels if you plan to match by labels, then click Add.
  3. Repeat for each worksheet range. Check that every source you intend to include appears in the All references list.
  4. Choose how Excel should match the ranges: select Top row for column headings, Left column for row labels, or both when the source ranges have labels in both places. If the ranges have the same structure but no matching labels, leave these options clear to consolidate by position.
  5. If you want the result to contain links to source data, select Create links to source data. Leave it clear for a value-based summary.
  6. Click OK and inspect the output, including any row and column labels and the totals.

The available functions include common summary calculations such as Sum, Average, Count, Max, and Min, along with other statistical and arithmetic options. Choose a function that fits the meaning of the source values; for example, adding monthly revenue is sensible, while adding monthly percentages may not be.

Excel for Mac has a Consolidate command in desktop Excel, though ribbon placement and dialog appearance can differ by release. Excel for the web has fewer desktop data tools; if the command is unavailable, open the workbook in the desktop app. On Windows, Alt+F8 is not relevant to this command, and there is no need for a keyboard shortcut: use the Data tab.

Match ranges by position or by category

Consolidation by position means Excel treats the first cell in every selected range as corresponding, the second cell as corresponding, and so on. It works when every source has the same layout, order, and range size. If one sheet lists products in a different order, a positional consolidation can attach figures to the wrong product.

Consolidation by category uses labels to match corresponding rows or columns. For example, if one worksheet lists Books before Games and another lists Games before Books, selecting Left column tells Excel to use those labels rather than assuming the first row label always means the same item. If headers are along the top, select Top row as well.

Use labels that are consistent across sheets. Small differences such as a trailing space, an abbreviation, or a changed category name can prevent labels from matching as intended or create separate categories. Review spelling and naming before consolidating, and check the output for unexpected blank or duplicate-looking labels.

Matching labels is not the same as identifying unique records. If two sheets both contain a row labeled East, Excel can aggregate those values under that category. It does not determine whether the underlying rows are duplicate transactions that should be removed.

Use formulas for a live calculation

For a few sheets with the same layout, a formula can make the relationship between the source cells and the result explicit. A three-dimensional reference can summarize the same cell across a consecutive group of worksheets. The worksheets at both ends of the range, and every worksheet between them in tab order, are included.

Assume the January, February, and March worksheets each store the relevant sales total in cell B2, with values of 120, 135, and 110 respectively. Enter this formula in the destination cell:

=SUM(January:March!B2)

The result is 365, because 120 + 135 + 110 = 365. This approach is convenient when the same cell has the same meaning on each sheet. It is not appropriate if the values are in different cells or if you intend to include only selected sheets between the endpoints.

Another option is to add individual sheet references. Assume the same values are in B2 on the January, February, and March worksheets, and the destination is elsewhere in the workbook. This formula adds only those three specified cells:

=SUM(January!B2,February!B2,March!B2)

Its result is also 365. Individual references can be easier to control when the relevant sheets are not consecutive, but you must update the formula if you add another source sheet. Formulas recalculate as values change, subject to normal workbook calculation settings and any broken references.

Append and refresh data with Power Query

Choose Power Query when the goal is one combined list of records, or when you repeat the same cleanup and combination process. It is especially useful when several source tables have the same column headings but different rows. Unlike a simple summary, an appended result retains the records so you can filter or analyze them later.

For worksheets in the same workbook, first turn each source range into an Excel table, with a header row and consistent column names. Select a cell in a source range and use Insert > Table, confirming whether the table has headers. Give the tables clear names so you can distinguish them. Then use Data > From Table/Range to create a query for each table. In the Power Query Editor, use Home > Append Queries > Append Queries as New, select the tables to append, and load the resulting query to a worksheet or the Data Model.

For data in another workbook, the Windows desktop path is Data > Get Data > From File > From Excel Workbook. Select the workbook, choose the relevant tables or sheets in the Navigator, and use Transform Data if you need to review or clean them before combining. The exact available choices can depend on the workbook structure and Excel build.

Power Query appends columns by their names, not by assuming that the first column in every source has the same meaning. If a source table lacks a column found in another, the combined result can contain blank values for that field in those rows. After loading, use Data > Refresh All to run the query again and bring in source changes. A query is refreshable, but it does not necessarily update the output at the instant a source cell changes.

Common errors and checks

A correct-looking total can still be wrong if the source ranges or matching rules are wrong. Before relying on a consolidated result, check both the inputs and the output.

  • Unequal layouts: Position-based consolidation depends on consistent order and shape. Use label matching or standardize the sheets first.
  • Missing sheets: Confirm that each source range was added. A worksheet that was created later is not automatically included in a previously selected list of references.
  • Inconsistent labels: Standardize category names, remove accidental spaces, and check for abbreviations or spelling variations.
  • Wrong calculation: Sum is appropriate for additive quantities, but not every metric should be added. An average of averages, for example, may not equal the average of all underlying observations when the groups have different sizes.
  • Text stored as numbers: Values imported as text may not behave as expected in calculations. Check cell formats and the underlying values, rather than relying only on how they look.
  • Unexpected duplicates: Consolidate summarizes matching labels; it is not a duplicate-removal tool. Use a record-level workflow if you need to identify repeated transactions.
  • Stale query output: If Power Query results do not reflect edits, run Refresh All and confirm that the query points to the intended tables or files.

A useful validation is to compare the consolidated grand total with a separate check of the source totals. Also inspect a few individual categories from the output and trace them back to their source sheets. This catches omitted references and mismatched labels more reliably than checking only whether the final number seems plausible.

Limits, links, and workbook maintenance

Data Consolidate is useful for compact summaries, but a summary usually loses row-level detail. A total of 1,000 for a product does not show which transactions produced it. Keep the original source data if you may need to audit or filter individual records later.

By default, the Consolidate result is a set of values rather than a continuously updating formula connection. Selecting Create links to source data asks Excel to create linked results and an outline structure. Those linked results can reflect source changes when the workbook recalculates and the references remain available, but this is not the same refresh workflow as Power Query. If you move, rename, or delete source sheets or workbooks, verify the links.

For maintainable workbooks, use clear worksheet and table names, keep headers consistent, and document which sheets are included. If new worksheets are added every month, a manually maintained Consolidate reference list or a formula with a fixed list of sheet names can be easy to forget. A query based on consistently named tables may be a better recurring process.

Frequently Asked Questions

Can I consolidate data from multiple worksheets into one sheet?
Yes. Use Data > Consolidate to create a summary on a destination sheet. If you want to place all source records one after another, use Power Query to append the tables instead.

Does Excel Consolidate update automatically?
By default, it creates a value-based result that does not automatically refresh when a source changes. Create links to source data can make linked results respond to source changes, while Power Query results are updated through a refresh.

Can I consolidate worksheets with different layouts?
Sometimes. Label-based consolidation can match categories that appear in different positions, but it relies on usable, consistent labels. If the columns or fields differ substantially, standardize the data or use Power Query to transform it before combining.

What is the difference between consolidating and appending?
Consolidating usually calculates a summary, such as a sum by product. Appending places the rows from several tables into one longer table, preserving individual records for later analysis.

Can I consolidate data from separate workbooks?
Yes. Data Consolidate can use references from other workbooks, but links and file locations need maintenance. For repeatable imports, Power Query can connect to workbook files and refresh the combined result.

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.