Excel Consolidation: Combine Data from Multiple Sheets

How-to basics · Updated November 21, 2026

To consolidate multiple Excel sheets, use Data > Data Tools > Consolidate to summarize matching ranges, or use Power Query to append rows into one combined table. Choose the Consolidate command when you want totals or other summaries; choose Power Query when you need to stack detailed records and refresh the combined result later.

What Excel consolidation means

Consolidation brings information from separate worksheet ranges together in a destination range. It can calculate a summary such as a sum, average, or count, or combine records into a longer list. Those are different tasks, even though both are often described as combining sheets.

For example, a workbook might have one worksheet per month, each with the same rows of product names and a sales figure. A summary sheet can add the sales for each product across the months. Alternatively, if each monthly sheet contains individual sales transactions, you may want every transaction in one table so you can filter, sort, or build a PivotTable. The first task is a summary; the second is a row append.

Excel’s Consolidate command is designed mainly for the summary task. It can use matching positions or matching labels to determine which source values belong together. Power Query is generally more suitable for repeatable data preparation, especially when the source sheets contain records that should be appended without aggregation.

Choose the right method

Before combining anything, decide what the finished result should contain. If each source sheet has the same layout and you want one calculated result per category, try Consolidate. If you need to preserve every source row, use Power Query or another method that appends rows. If there are only a few known sheets and the layout never changes, a formula may be the simplest solution.

  • Use Consolidate for a one-time or periodically repeated summary across similar ranges, such as total expenses by department.
  • Use Power Query to combine detailed tables, clean inconsistent data, and refresh the result after source data changes.
  • Use formulas when the source sheets are stable and you want a transparent calculation in specific summary cells.
  • Use a PivotTable after appending records when you want to explore totals by several fields, such as month, product, and region.

Do not choose a summary method if you need to audit each underlying transaction: a sum replaces the detail with an aggregate. Keep the original data intact and place the combined or summarized output on a separate worksheet.

Use the Consolidate command

In Excel for Microsoft 365 on Windows, select the destination cell, then choose Data > Data Tools > Consolidate. In the dialog, choose a function, add each source range to the All references list, and select OK. The destination area must have enough blank space for the resulting summary.

  1. Choose a destination worksheet and click the upper-left cell where the result should begin.
  2. Open Data > Data Tools > Consolidate and select the calculation, such as Sum or Average.
  3. Click in the Reference box, select a source range on a worksheet, and click Add. Repeat for every range or source sheet.
  4. If the source ranges contain headings, select Top row, Left column, or both, as appropriate.
  5. Select Create links to source data if you want linked detail beneath the summary; leave it unchecked for a result made of values.
  6. Check the references and options, then click OK. Inspect the output and compare a few results with the sources.

The available summary functions include Sum, Count, Average, Max, Min, Product, Count Numbers, StdDev, StdDevp, Var, and Varp. Choose the function based on the meaning of the data. Count counts nonblank entries, while Count Numbers counts numeric entries. For most business summaries, Sum is common, but summing percentages or averages may not produce a meaningful result.

On Excel for Mac, the command is also available from the Data tab, although ribbon grouping and dialog appearance can differ by release. Excel for the web does not offer the full desktop Consolidate workflow; open the workbook in the desktop app if you need that command. The precise ribbon layout can vary as Microsoft updates Office.

How labels and layouts affect the result

When Top row and Left column are not selected, Excel matches cells by their relative position. If the first source range has a value in its third row and second column, Excel combines it with the value in that same position in the other ranges. This works only when the ranges have a consistent arrangement. A row inserted in one sheet can shift the positions and produce a plausible-looking but incorrect result.

Select Top row when the first row contains category labels, Left column when the first column contains row labels, or both when labels appear in both places. Excel can then match items by their labels rather than relying only on position. The labels need to identify the same categories consistently. Differences such as an extra space, a spelling variation, or “West” versus “Western” can make what should be one category appear as separate entries.

For example, if each worksheet has product names in column A and monthly totals in column B, include both columns in the source ranges and select Left column. With matching product labels, Consolidate can align a product even if it appears on a different row in one source. It does not, however, resolve conflicting names or determine that two differently worded labels mean the same thing. Standardize labels first when necessary.

Static results and linked summaries

With Create links to source data unchecked, the output is a snapshot of the calculation at the time you run Consolidate. If a source value changes later, the consolidated result does not automatically recalculate from that change. Run the command again to rebuild the summary.

Selecting Create links to source data asks Excel to create links back to the source cells and arrange the result in an outline. This can make the summary update when linked source values change, but it also adds detail rows and formulas, so the output may look different from a simple flat table. If source workbooks are external, links can depend on file locations and may need to be updated when files move or are renamed.

Linked output is not the same as a refreshable data-import process. If you want to repeat the same combination after adding rows or sheets, Power Query is usually easier to manage. Whichever method you use, document where the source data lives and check whether the final workbook contains external links before sharing it.

Combine sheets with a formula

A formula can be convenient when every worksheet uses the same cell layout and the set of sheets is known. A three-dimensional reference can summarize the same cell across a continuous group of worksheets. It refers to the sheets between the two named sheet tabs, including those endpoints, so sheet order matters.

With sheets named January, February, and March, and each month’s sales for one item in cell B2, enter this formula in the summary cell: =SUM(January:March!B2). If B2 contains 120, 150, and 130 on those sheets, the result is 400. The worksheets must be consecutive in the tab order for this reference to include exactly those three months; inserting another sheet between January and March includes it too.

For selected, nonconsecutive sheets, add separate references instead. With the same B2 layout on January and March only, use =SUM(January!B2,March!B2). Both formulas update when referenced cells change, but neither automatically expands a summary to include new rows of records. Formula approaches are therefore best for small, controlled workbooks rather than recurring imports with changing structure.

Append worksheets with Power Query

Power Query is Excel’s data-import and transformation tool. Its append operation stacks tables or queries vertically, aligning columns by column name rather than requiring every field to remain in the same position. It is a better fit when each sheet holds rows of transactions with shared fields, such as Date, Customer, and Amount.

  1. Turn each source range into a table using Insert > Table, and make sure the tables have consistent, meaningful column headings.
  2. For each table, choose Data > From Table/Range to open it in the Power Query Editor. Create a query for each source and use Close & Load To to keep it as a connection if you do not need a separate output for each one.
  3. In the Power Query Editor, choose Home > Append Queries > Append Queries as New. Select the tables or queries to append.
  4. Review the combined preview, check data types and column names, then choose Home > Close & Load to place the result in Excel.
  5. When source table data changes, refresh the output with Data > Refresh All. Confirm that any newly added data is inside the source tables and that the query still points to the right sources.

Append keeps individual records; it does not total them. If you want one row per product after appending monthly transactions, use Power Query’s grouping and aggregation options or create a PivotTable from the combined table. For sheets in other workbooks, Power Query can also import from files, which is useful when the files follow a consistent structure.

Common problems and checks

  • Missing categories: Check whether a label is spelled differently or contains leading or trailing spaces. Standardize the source labels and consolidate again.
  • Unexpected totals: Verify that all intended references are in the list, that the correct function is selected, and that ranges do not overlap or include unwanted total rows.
  • Wrong items combined: If matching by position, compare the row and column order across sheets. Use label matching where suitable, or clean and align the source data first.
  • New data is absent: Consolidate may use fixed source ranges. Update the references, or use tables and a refreshable Power Query workflow for changing datasets.
  • Values are not included: Check for numbers stored as text, blank cells, and formulas that return text. Confirm whether the chosen function handles the entries as intended.
  • External links break: Keep linked files in stable locations and review workbook links after moving or renaming a source. A static result or Power Query import may be preferable if link maintenance is a concern.

Always validate the output rather than assuming that a successful dialog means the result is right. Compare a few categories against their source values, check a grand total where one makes sense, and look for duplicate or missing labels. Save a copy before making broad structural changes, especially if the workbook is used for reporting.

Frequently Asked Questions

Can Excel combine multiple sheets into one sheet?
Yes. Use Power Query’s append operation to stack rows into one table. Use Data > Data Tools > Consolidate when you want calculations such as totals across similar ranges rather than every source record.

Does Excel Consolidate update automatically?
A basic consolidation produces a static result. Selecting Create links to source data makes linked output respond to source-cell changes, but it is not the same as a query that refreshes and incorporates changing source tables. Power Query results can be refreshed.

Can Consolidate match rows with different orders?
It can match using row and column labels when you select Left column and/or Top row. Labels must be consistent. Without those options, Excel combines values by their relative position in the selected ranges.

How do I combine sheets with different columns?
Power Query can append tables with different column sets, placing fields with matching names in the same columns and leaving missing fields blank for records that do not contain them. Review the headings and data types before loading the result.

Is Consolidate the same as a PivotTable?
No. Consolidate combines selected ranges using a chosen function. A PivotTable summarizes a structured data list and lets you rearrange fields to analyze different groupings. For recurring analysis, first append records into one table, then build a PivotTable if you need flexible summaries.

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.