How to combine Excel sheets into one
By the Analistable team · Updated · 12 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.
A few rules about 3D references are worth knowing before you rely on them:
- They follow tab order, not tab names. A sheet dragged between Jan and Mar is included automatically; a sheet dragged outside them silently drops out of the result.
- Sheet names with spaces or punctuation need single quotes around the whole range of sheets:
='Jan 2026:Mar 2026'!A2:D500. - A common trick for a growing set of tabs is two empty “bookend” sheets called Start and End. Refer to
Start:End!A2:D500and place every data tab between them; new months are picked up as soon as you drag them inside. - VSTACK matches columns by position. If one tab has Product and Region swapped, its values land under the wrong headers without any error.
VSTACK on a 3D reference can't tell you which sheet a row came from. When you need that, stack each sheet with a label column instead:
=LET(
j, Jan!A2:C4,
f, Feb!A2:C5,
VSTACK(
HSTACK(EXPAND("Jan", ROWS(j), 1, "Jan"), j),
HSTACK(EXPAND("Feb", ROWS(f), 1, "Feb"), f)
)
)EXPAND("Jan", ROWS(j), 1, "Jan") turns the single word into a column as tall as the Jan range, and HSTACK puts it in front of the data. The formula is longer than a 3D reference, so it suits a handful of tabs; for dozens, use Power Query.
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.
If the workbook has many tables with a naming pattern, you don't have to append them one by one. A blank query can pick up every table whose name starts with Sales_ and tag each row with its table name:
let
Source = Excel.CurrentWorkbook(),
SalesTables = Table.SelectRows(Source, each Text.StartsWith([Name], "Sales_")),
Tagged = Table.AddColumn(SalesTables, "T", each Table.AddColumn([Content], "Sheet", (r) => [Name])),
Combined = Table.Combine(Tagged[T])
in
CombinedExcel.CurrentWorkbook() lists the tables and named ranges in the open workbook, not the sheets themselves, which is why each tab's data needs to be a table first. Give the output query a name that doesn't start with Sales_; otherwise, after the first load, the combined table becomes one of its own inputs and every refresh doubles the rows. A new month's tab is included as soon as its table is named Sales_Nov or similar and you click Refresh All.
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.
- Click the top-left cell of an empty area on a summary sheet.
- Choose Data → Consolidate and pick the function, usually Sum.
- Click in Reference, select the range on the first sheet including its labels, and click Add. Repeat for each sheet.
- Tick Top row and Left column under Use labels in, then click OK.
Without the label options, Consolidate adds cells by position: the third row of every sheet is added to the third row of the summary, whatever product it holds. With labels, a product that appears on only one sheet still gets its own row. Ticking Create links to source data builds an outline with formulas pointing at each sheet, so the summary updates when the sources change; it can't be used when the source and summary are on the same sheet. If you consolidate the same layout every month, our consolidation worksheet template sets this up once.
For a single total across identically laid-out tabs, a 3D formula is quicker than the dialog: =SUM(Jan:Mar!D2) adds cell D2 on every sheet from Jan to Mar. SUMIF and SUMIFS don't accept 3D references, so conditional totals across tabs need a different approach, described in SUMIFS across multiple sheets.
Option 4: a pivot table built from several sheets
When the goal is a summary you can slice and rearrange, there are two routes. The older one is the PivotTable and PivotChart Wizard on Windows: press Alt, D, P in turn, choose Multiple consolidation ranges, and add each sheet's range. It works in every desktop version on Windows, but the result has generic Row, Column and Value fields, so you lose your own column names.
The better route is to stack the sheets first, with VSTACK or Power Query Append, and build one ordinary pivot table on the stacked table. You keep every column as a field, and adding a month means adding rows rather than rebuilding the pivot. Walkthroughs: a pivot table from multiple sheets and combining two pivot tables.
Option 5: copy and paste
In every version of Excel, on Windows, Mac or the web, you can copy each sheet's rows under the first. Copy the header once, then the data rows from each tab, and add a column with the tab name before pasting the next block. It is fine for three or four tabs you combine once. If you will repeat it, every repetition is a fresh chance to miss a row, so use one of the refreshable options instead. Step-by-step: combining two sheets in Excel.
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.
| Product | Jan | Feb | Mar | Total |
|---|---|---|---|---|
| Chair | 30 | 0 | 0 | 30 |
| Desk | 12 | 9 | 11 | 32 |
| Lamp | 0 | 14 | 0 | 14 |
| Total | 42 | 23 | 11 | 76 |
Check the summary against the source: the five stacked rows add up to 76 units (12 + 30 + 9 + 14 + 11), and so does the grand total. In recent Microsoft 365 builds, =GROUPBY(C2:C6, D2:D6, SUM) on the stacked range gives the same product totals as a formula that updates without a refresh.
Combining tabs in Google Sheets
Google Sheets has no 3D references, so you list each tab. Curly braces stack ranges, with a semicolon between them in English-language locales: ={Jan!A2:D; Feb!A2:D; Mar!A2:D}. Sheets also supports VSTACK, which does the same with commas between ranges. Open-ended ranges like A2:D include every empty row, so filter them out:
=LET(d, VSTACK(Jan!A2:D, Feb!A2:D, Mar!A2:D), FILTER(d, CHOOSECOLS(d, 1) <> ""))Because the ranges are open-ended, rows added to any tab appear in the combined sheet immediately. To summarise as you combine, wrap the stacked range in QUERY and refer to columns as Col1, Col2 and so on. More patterns, including combining tabs from separate spreadsheets with IMPORTRANGE, are in combining data in Google Sheets and QUERY with multiple ranges.
When the combined sheet looks wrong
| Symptom | Cause | Fix |
|---|---|---|
| Rows of zeros at the bottom of each block | VSTACK returns 0 for the empty cells in a range that is larger than the data | Wrap it in FILTER to keep only rows where the first column isn't blank |
| #SPILL! instead of a table | Something is already in the cells the result needs | Clear the area below and to the right of the formula |
| #N/A in the last columns of some rows | One range is narrower than the others, and VSTACK pads it with #N/A | Make every range the same width, or wrap the formula in IFNA(…, "") |
| A new month's tab is missing | The tab sits outside the 3D reference, or its table isn't part of the Append | Move it between the first and last tabs, or add its query to the Append |
| Consolidate lists “Desk” twice | One sheet has a trailing space or different capitalisation in the label | Clean the labels with TRIM, then consolidate again |
| Totals come out as zero or too low | Numbers stored as text on one tab are ignored by SUM | Convert them with Data → Text to Columns or VALUE |
When the tabs don't have the same columns
Real workbooks drift. March gains a Channel column, or someone puts Product before Region. Power Query Append handles this by matching headers: a column that exists on only some tabs is kept and left empty (null) for the others, and order doesn't matter. Check the column list after appending; a header typed as “Units ” with a trailing space shows up as a second Units column.
VSTACK needs help, because it only knows positions. In Microsoft 365 you can reorder a tab's columns by header name before stacking it:
=LET(h, Mar!A1:D1, d, Mar!A2:D50, CHOOSECOLS(d, XMATCH({"Region","Product","Units"}, h)))XMATCH finds where each wanted header sits on the Mar tab and CHOOSECOLS returns the columns in that order. Wrap each tab in the same pattern and stack the results with VSTACK. If XMATCH can't find a header, the formula returns #N/A, which is a useful warning that a tab has renamed a column. Our guide to combining Excel tables covers the same problem when the data is already in Excel tables.
Adding a new tab each month
Most combined sheets exist because a tab is added every week or month. A little set-up keeps that routine to a minute:
- Keep a blank template tab with the agreed headers, and create each new month by copying it (right-click the tab → Move or Copy → Create a copy).
- Convert the new data to a table and name it to the pattern your query expects, such as
Sales_Nov; for VSTACK, drag the tab between the Start and End bookends. - Click Data → Refresh All if you use Power Query. VSTACK and Google Sheets formulas update by themselves.
- Re-run the row-count and total checks below, and only then share the summary.
Avoid typing directly into the combined sheet. Any change made there is overwritten on the next refresh, or breaks the spill of a VSTACK formula; correct the source tab instead.
Check that nothing was lost
A combined sheet should contain exactly the rows of the source tabs. Two formulas tell you whether it does. =COUNTA(Jan:Mar!A2:A500) counts the filled ID cells across all tabs (COUNTA accepts 3D references), and =ROWS(F2#) counts the rows in a VSTACK result that starts in F2. If the numbers differ, either blank rows have slipped in or a tab is missing.
Then compare one numeric total, for example =SUM(Jan:Mar!D2:D500) against the sum of the Units column in the combined table. Matching counts and totals are the quickest evidence that the combination is complete. For Power Query results, the row count appears in the status bar of the Query Editor and in the Queries & Connections pane after loading.
Which option fits your workbook?
| Your situation | Use | Why |
|---|---|---|
| Microsoft 365, a few tabs, want it live | VSTACK | Updates as you type, no refresh |
| Tabs added every month, Excel 2016 or later | Power Query Append or Excel.CurrentWorkbook | New tables join the result on Refresh |
| Same layout, you need totals only | Consolidate or 3D SUM | Gives the summary directly |
| A pivot table across tabs | Stack first, then one pivot table | Keeps your column names as fields |
| Excel 2013 or older, one-off job | Copy and paste | Works in every version |
| Google Sheets | VSTACK or {…; …} with FILTER | No 3D references in Sheets |
If the sheets you need are in separate workbooks rather than tabs of one file, the job is merging files instead: see how to merge Excel files. To pull a few columns from several tabs into a report rather than stacking whole tables, see pulling data from multiple sheets into one.
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
- #SPILL! when stacking sheets with VSTACK: causes and fixes
VSTACK returns #SPILL! when its output range is blocked, sits in a table or merged cells, or runs off the sheet. Find the cause and fix each one.
- Combined sheet has blank rows: why it happens and how to fix it
Blank rows or rows of zeros after combining sheets come from oversized ranges, formatted empty cells and VSTACK padding. Filter them out at the source.
- 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.
- Combining files with merged header rows: flatten first, then stack
Report exports with two-row or merged headers don't stack cleanly. Flatten them into single, unique column names first in Excel, Power Query or pandas.
- Dates change format when combining Excel files: find and fix them
Fix day and month swapping, dates stored as text and wrong sort order after combining Excel or CSV files from UK and US sources, with formulas and Power Query.
- How to add a source column when combining files or sheets
Add a column showing which file or sheet each row came from when you combine Excel data, using Power Query, VSTACK, Python or VBA.
- 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.
- How to pull data from multiple sheets into one summary sheet
Pull a value or row from many Excel sheets into one summary sheet with INDIRECT and a sheet list, 3D references or VSTACK — and the trade-offs of each.
- Why and how to unpivot wide reports before merging
Month-per-column reports can't be stacked or joined reliably. Unpivot them to long format in Power Query, pandas or SQL, then merge on clean keys.
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(...)).
- Why does VSTACK show zeros under my data?
- The ranges you stacked are taller than the data, and VSTACK returns 0 for each empty cell. Wrap it in FILTER to keep only rows whose first column is not blank, as shown in the LET example above.
- Can I combine sheets from different workbooks?
- VSTACK and 3D references only work reliably within one workbook. For separate files, use Power Query's From Folder, copy the sheets into one workbook with Move or Copy first, or use a browser combiner that reads several files at once.