How to combine Excel sheets into one
By the Analistable team · Updated · 3 min read
To combine several sheets into one in Excel 365, use =VSTACK(Sheet1:Sheet3!A2:D100) for a live stacked table. For a refreshable table in Excel 2016 or later, load each sheet with Data → From Table/Range and stack them with Power Query's Append Queries. In any version you can copy each sheet's rows under the first. To summarise rather than stack, use Data → Consolidate or a pivot table.
Stack, summarise or match?
Before choosing a method, look at what the sheets contain:
- Same columns, different rows (one tab per month or region) → stack them into one long table.
- Same layout, numbers to add up (one budget per department) → consolidate into one summary.
- Different columns about the same records (customers on one tab, orders on another) → match them by a key column. See joining spreadsheets.
Methods compared
| Method | Result | Updates automatically | Excel versions |
|---|---|---|---|
| VSTACK | One stacked table (formula) | Yes | Microsoft 365, Excel 2024, web |
| Power Query Append | One stacked table (query) | On Refresh | 2016+, Microsoft 365 (Mac too) |
| Consolidate | Summary (sum, count, average…) | Optional links | All |
| Pivot table (multiple ranges) | Summary you can pivot | On Refresh | Windows |
| Copy and paste | One stacked table (static) | No | All |
Option 1: VSTACK for a live combined sheet
Create a new sheet and type the header row once. In the cell below the first header, enter:
=VSTACK(Jan:Mar!A2:D500)
Jan:Mar is a 3D reference: every sheet from Jan to Mar in tab order. Blank rows at the bottom of each range will appear too, so wrap it in FILTER to drop them: =LET(d, VSTACK(Jan:Mar!A2:D500), FILTER(d, CHOOSECOLS(d,1)<>"")). Our dynamic arrays guide covers adding a sheet-name column with HSTACK.
Option 2: Power Query Append for a refreshable table
- Turn each sheet's data into a table (Ctrl+T) and give each a clear name.
- On each table, choose Data → From Table/Range, then Close & Load To… → Only Create Connection.
- Choose Data → Get Data → Combine Queries → Append, pick Three or more tables, and add them all.
- Click Close & Load. Power Query creates one table on a new sheet.
When the source tabs change, click Refresh All. Columns are matched by header name, so a column that exists on only one tab still comes through. Step-by-step screens are in Power Query Append Queries.
Option 3: Consolidate when you need totals
Data → Consolidate adds up the same cells or labels across sheets. Choose a function (Sum, Count, Average…), add each sheet's range, and tick Top row and Left column so Excel matches by labels rather than position. The result is a summary, not a list of rows. See the Consolidate function guide.
Worked example
Three monthly tabs with the same columns, stacked with a source column:
| Month | Region | Product | Units |
|---|---|---|---|
| Jan | North | Desk | 12 |
| Jan | South | Chair | 30 |
| Feb | North | Desk | 9 |
| Feb | North | Lamp | 14 |
| Mar | South | Desk | 11 |
With every row in one table, one pivot table answers questions such as units by product per month — which was impossible while the data sat on three tabs.
No formulas: combine sheets in your browser
If you only need the combined table, drop the workbook into our free sheet consolidator. It reads every tab, stacks the rows, adds a source column and shows a row count per sheet. Nothing is uploaded: the file is processed on your computer.
Every guide in this topic
- Combining data with VSTACK, HSTACK and other dynamic arrays
Use Excel's dynamic array functions to combine data: VSTACK and HSTACK across sheets, add a sheet-name column, drop blank rows, dedupe and sort in one formula.
- How to combine tables in Excel
Combine Excel tables by stacking them with VSTACK or Power Query Append, or by joining them on a key with Merge Queries. Structured references explained.
- How to combine two pivot tables in Excel
Excel can't merge two pivot tables directly. Combine their source data, relate them in the Data Model, or build a side-by-side report with GETPIVOTDATA.
- How to combine two sheets in Excel
Combine two sheets in one Excel workbook: stack them with VSTACK or copy-paste, or match them side by side with XLOOKUP. Examples and the pitfalls to avoid.
- How to create a pivot table from multiple sheets
Three ways to build one pivot table from several sheets: stack them first, use the Data Model with relationships, or the Multiple Consolidation Ranges wizard.
- How to merge every Excel table into one sheet
Use Power Query's Excel.CurrentWorkbook to stack every table in a workbook into one sheet, including tables added later, with a column naming each source.
Frequently asked questions
- How do I combine all sheets into one in Excel?
- In Microsoft 365, use =VSTACK(FirstSheet:LastSheet!A2:D500) on a new sheet. In Excel 2016 or later, use Power Query's Append Queries. In any version you can copy and paste each sheet's rows under the first.
- Can I combine sheets that have different columns?
- Yes, with Power Query Append, which matches columns by name and leaves blanks where a column is missing. VSTACK matches by position, so the columns must be in the same order.
- How do I combine sheets and keep the sheet name?
- In Power Query, add a custom column with the table name before appending. With VSTACK, stack a column that repeats each sheet's name, or use a browser tool that adds a source column automatically.
- How do I combine sheets without duplicates?
- Stack them first, then use Data → Remove Duplicates, or wrap the stacked range in UNIQUE: =UNIQUE(VSTACK(...)).