If you work with information spread across several sheets—such as budgets from different regions or reports created by multiple contributors—you may want to bring that data together in one place. Excel offers several ways to do this, depending on whether you want to summarize values or simply combine lists.
Before you start
Make sure your source data is well structured.
-
Use a list format (no entirely blank rows or columns).
-
Keep labels (column headers) consistent across sheets.
-
If your Excel version doesn’t have the Data > Consolidate feature, you may be using Excel for the web or a platform that doesn’t support the feature. In that case, see the section "Option 2: Combine or append data instead of summarizing it".
Option 1: Summarize data with the Consolidate feature
Use Consolidate when you want to calculate totals, averages, counts, or other summary results across sheets. You can consolidate by position (same layout) or by category (matching labels).
Consolidate by position
Use this when each sheet uses the same cell layout.
-
Open your source sheets and confirm data appears in the same location on each sheet.
-
Go to the sheet where you want the combined results.
-
Select the upper‑left cell of the range where the consolidated data should appear.
-
Make sure there’s room for the data to expand downward and right.
-
-
Select Data >
-
Choose a Function (such as Sum, Average, or Count).
-
In each source sheet:
-
Select your data range.
-
Select Add to include it in All references.
-
-
Select OK to generate the consolidated report.
Consolidate by category
Use this when sheets share the same labels, even if the data isn’t positioned identically. Note that if one sheet uses “Average” while another uses “Avg”, you’ll need to standardize labels first so Excel can match them correctly.
-
Confirm each sheet uses matching labels in the top row or left column.
-
In the destination sheet, select the upper‑left cell of the range where the consolidated data should appear.
-
Make sure there’s room for the data to expand downward and right.
-
-
Go to Data >
-
Choose a Function (such as Sum, Average, or Count).
-
Check the boxes under Use labels in (Top row, Left column, or both).
-
In each source sheet:
-
Select your data range.
-
Select Add to include it in All references.
-
-
Select OK to generate the consolidated report.
If a label appears in one sheet but not another, Excel still includes it. A new row or column is created in the result.
Option 2: Combine or append data instead of summarizing it
If you need to merge or stack rows from multiple sheets, not calculate totals, you’ll need a different approach.
Copy and paste
This is a quick, manual option for merging data. It works best when you only need to merge a few sheets.
-
Create a new sheet.
-
Copy your entire list from the first sheet and paste it.
-
Repeat for other sheets, pasting directly below the existing data.
-
Remove duplicate headers if needed.
Use the VSTACK formula to stack data
If your sheets have the same column structure, you can dynamically stack them with VSTACK function. The following example would combine data from three worksheets.
=VSTACK(Sheet1!A1:D50, Sheet2!A1:D50, Sheet3!A1:D50)
This creates one combined list that updates when the data in the source sheets change.
Use Power Query
Power Query lets you automatically import and combine data from multiple tables or sheets, even across workbooks. This is best for large data sets and continuous merging.
-
Select each data range and press Ctrl+T to convert it to a table.
-
Go to Data > Get Data > From Other Sources > Blank Query.
-
Use Excel.CurrentWorkbook() in the formula bar to view tables.
-
Expand and combine them using the double‑arrow icon.
-
Choose Close & Load to create a combined sheet.
This approach creates a dynamic combined dataset that can be refreshed whenever data changes.
Troubleshooting and tips
Based on user feedback, here are the most common stumbling blocks.
You can’t find “Consolidate”
You may be on Excel for the web or a version that doesn’t support it. Use Power Query or formulas instead.
The Consolidate dialog won’t allow selecting ranges
Make sure the dialog stays active. If it blocks clicking into other windows, try resizing or moving it.
Your consolidation results look wrong
Check that:
-
Labels match exactly (e.g., “Average”, not “Avg”).
-
There are no blank rows/columns breaking list structure.
-
You chose the correct function (Sum vs. Average).
Data appears in inconsistent rows or columns
If your sheets aren’t aligned, use Consolidate by category rather than by position.
You want to append data, not summarize it
Use VSTACK or Power Query instead. They’re better suited for merging.
