Consolidate Data in Excel: Methods and Steps

How-to basics · Updated November 18, 2026

To consolidate data in Excel for Microsoft 365 on Windows, choose Data > Consolidate, select a summary function such as Sum, add each source range, and click OK. Match records by position when the sheets have the same layout, or by category labels when their rows or columns are arranged differently; use Power Query instead when you need a repeatable process for combining growing datasets.

What consolidating data in Excel means

Consolidating means bringing information from multiple ranges, worksheets, or workbooks into one summary. For example, a business might keep monthly sales on separate sheets and create one annual summary. A school might collect the same set of measures from several departments and combine them for a report. The right method depends on whether you want a one-time summary, a formula that updates from source cells, or a refreshable combined dataset.

Excel’s built-in Consolidate command can calculate a summary such as a sum, average, or count from several ranges. It is not the same as simply copying sheets together: it can aggregate values into a smaller result, and it can match labeled categories even if those categories appear in a different order. It does not, however, automatically clean inconsistent names, repair missing data, or turn every source range into a single detailed list of records.

Choose a method that fits the data

Before combining anything, decide what the output should look like and how it needs to change over time. These common cases point to different Excel tools:

  • Use Data > Consolidate for a quick summary of comparable ranges, such as total expenses by department across several sheets.
  • Use formulas when you want visible links to a small, stable set of source cells, or when the calculation must sit beside other worksheet logic.
  • Use Power Query to append detailed rows from multiple worksheets or files, standardize columns, and refresh the result when source data changes.
  • Use a PivotTable to summarize one combined table by fields such as month, product, or region. A PivotTable summarizes data; it does not by itself combine unrelated source ranges.

A useful distinction is between consolidating values and appending records. If three sheets each contain a row for Product A and you need one total for Product A, a category-based consolidation can work. If each sheet contains individual transactions and you need all transactions in one table, append the rows with Power Query, then summarize them if needed.

Prepare the source worksheets

Good source structure prevents many consolidation errors. Put a single header row at the top of each table, use one column for each field, and avoid blank rows or merged cells within the data. Check that corresponding values use compatible formats: dates should be dates, quantities should be numbers, and category names should be spelled consistently.

For a position-based consolidation, ranges need the same layout. If cell B2 means January sales on every source sheet, Excel can combine the matching cells. For a label-based consolidation, use consistent row or column labels, such as West, Central, and East. The labels must match exactly for Excel to recognize them as the same category. Extra spaces, alternate spellings, or different punctuation can produce separate entries.

It also helps to make a copy of the workbook before changing a report used by others. Decide whether the output should be a fixed snapshot or should keep a relationship to its sources. That choice affects which option you select in the Consolidate dialog and how you maintain the result later.

Use Excel’s Consolidate command step by step

In Excel for Microsoft 365 on Windows, create or select a blank destination area, then follow these steps:

  1. Open the worksheet where the summary should appear. Click the cell where the upper-left corner of the result should go, leaving enough empty space for the output.
  2. Choose Data > Consolidate. In the dialog, choose a function. Sum is common for totals; Average, Count, Max, and Min are useful when the question calls for those calculations.
  3. Click in the Reference box and select the first source range. If the range is on another worksheet, switch to that sheet and select the cells, then click Add to put the range in the All references list.
  4. Repeat for every source range. Confirm that the references include the intended data and, if applicable, the same header rows or columns.
  5. If the ranges use category names, select Top row and/or Left column to tell Excel which labels to match. Do not select these options when you intend to combine strictly by cell position and the selected ranges contain no labels.
  6. Select Create links to source data if you want the summary to link back to source cells, then click OK.

For example, suppose three sheets contain quarterly sales by product in rows, with product names in column A and sales in column B. Add each two-column range, select Left column, and choose Sum. Excel can total each matching product even if the product rows are in a different order on one sheet. If you instead select only the sales cells and consolidate by position, Excel combines row one with row one, row two with row two, and so on. That can give misleading totals if the products are not in the same order.

Position matching versus category matching

By position is straightforward when every source range has the same dimensions and the same meaning in every cell. It is often appropriate for repeated templates, such as weekly sheets that always list the same departments in the same rows. It is fragile if someone inserts a row, sorts one sheet differently, or changes the order of categories.

By category is safer when source ranges share reliable labels but not necessarily the same order. With Top row selected, Excel uses column headings to align data; with Left column selected, it uses row labels. You can use both when the ranges have labels along both edges. Labels are keys for matching, not a guarantee that the underlying records are clean. For instance, Sales and Sales  with an extra trailing space may not be treated as identical labels.

The command is designed for summary calculations, not for preserving every detail in a transaction ledger. If a label appears multiple times, or if there are different measures in adjacent columns, check the output carefully and test it against a small manual total. A summary can look plausible even when the chosen matching method is wrong.

Use formulas for a small, fixed consolidation

A formula can be simpler than opening the Consolidate dialog when a few source cells always occupy the same locations. For example, with the values in cell B2 on worksheets named Jan, Feb, and Mar, enter =SUM(Jan!B2,Feb!B2,Mar!B2) in the destination cell. If those cells contain 120, 95, and 175, the result is 390. The formula updates when one of those source values changes, but it will not automatically include a new month that you have not added to the formula.

For larger tables, formula approaches can use functions such as SUMIF or SUMIFS to total rows that meet criteria. They are useful when the source data is already arranged consistently and the reporting rules are clear. But formulas that refer to many separate worksheets can become difficult to audit and extend. If new sheets arrive regularly, a repeatable Power Query process is usually easier to maintain than manually editing a long formula or adding another reference each month.

Remember that an ordinary formula displays a calculated result, while a label-based consolidation builds a summary from the ranges you add to the dialog. Neither approach automatically imports and cleans a changing collection of files.

Combine tables with Power Query

Power Query is generally the better choice when you want to stack detailed rows from multiple sources into one dataset. Appending puts rows from one table beneath rows from another; matching columns are aligned by their column names. This is different from a Consolidate summary, which calculates totals or other functions over ranges.

For workbook files in Excel for Microsoft 365 on Windows, use Data > Get Data > From File > From Workbook to connect to a workbook, select the required table or sheet in the Navigator, and choose to transform or load the data. In Power Query Editor, use Home > Append Queries or Home > Append Queries as New to combine two or more queries. For a collection of similarly structured files in a folder, start with Data > Get Data > From File > From Folder and follow the combine prompts.

Power Query lets you remove unwanted columns, set data types, filter rows, and rename fields before loading the result. After the query is set up, use Data > Refresh All to refresh connected results when the source files change, subject to access and connection settings. A refresh does not fix inconsistent source schemas by magic: files with missing or renamed columns may need adjustments to the query. Excel for Mac has Power Query features, but availability and connectors can differ by version; Excel for the web does not have the same full Windows desktop editing experience.

Refresh behavior, source links, and maintenance

Without Create links to source data, the Consolidate result is a snapshot: later edits to the source values do not automatically recalculate that result. Selecting the link option creates an outlined summary with formulas linked to source cells. The outline can be expanded to inspect detail, which is handy for tracing a total back to its contributing values.

Source links are not the same as a fully automated data pipeline. If you move or rename source workbooks, change the structure, or add a new source range, you may need to update references or run consolidation again. Keep source sheets and ranges organized, document what the summary includes, and check the reported totals after substantial source changes. If a monthly process must include new files or rows repeatedly, Power Query is usually more suitable.

Excel for Mac also includes consolidation capabilities, though the dialog and available features may vary by release. On Mac, look for Consolidate on the Data tab. If you cannot find it in your edition, check that you are using the desktop app and consult the version-specific ribbon; Excel for the web does not provide the same desktop Consolidate workflow.

Common problems and practical checks

  • Totals do not match expectations: Confirm that the selected function is correct, all required source ranges were added, and the selection does not include subtotals that would be counted again.
  • Similar labels appear as separate results: Check spelling, punctuation, leading or trailing spaces, and whether one source uses a different label. Normalize the source data before consolidating.
  • Values combine with the wrong category: Check whether the dialog is matching by position or by labels. If the row order varies, select the appropriate label option rather than relying on position.
  • Numbers seem to be ignored: A value that looks like a number may be stored as text. Convert it to a numeric value in the source data and verify that the selected function treats the cells as intended.
  • New source data is missing: The original references may cover only the old range. Extend the ranges or use a refreshable query designed to include the new rows or files.
  • The output is hard to trace: Consider Create links to source data for a linked outline, or use Power Query and a PivotTable when the goal is a repeatable, auditable reporting workflow.

Before sharing a consolidated report, compare at least one category with its source values and check an overall total. For important reports, preserve the source data and note the selected function, date range, and included sheets. These simple checks help distinguish a valid result from one that merely looks tidy.

Frequently Asked Questions

How do I combine multiple Excel sheets into one total?
Use Data > Consolidate, choose Sum, add each source range, and select Top row or Left column if the ranges should match by labels. If you need all detailed rows rather than totals, append the tables with Power Query.

Does Excel Consolidate update automatically?
A consolidation without source links is a snapshot. Selecting Create links to source data links the output to the selected source cells, but adding new ranges or files still requires updating the setup. Power Query results can be refreshed after their sources change.

Can I consolidate sheets with different row orders?
Yes, if the ranges have consistent category labels. Add the ranges and select Left column for row labels, or Top row for column labels. If labels differ, standardize them first.

What is the difference between Consolidate and Append in Excel?
Consolidate calculates summary values across selected ranges. Append in Power Query stacks records into a longer table. Use Consolidate for totals by category and Append when you need the underlying rows together.

Can I consolidate data from separate workbooks?
Yes. In the desktop Consolidate dialog, add references from the other open workbooks, or import workbook data through Power Query. For recurring files, Power Query can provide a more maintainable refresh process.

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.