Analistable

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

Ways to combine sheets in Excel
MethodResultUpdates automaticallyExcel versions
VSTACKOne stacked table (formula)YesMicrosoft 365, Excel 2024, web
Power Query AppendOne stacked table (query)On Refresh2016+, Microsoft 365 (Mac too)
ConsolidateSummary (sum, count, average…)Optional linksAll
Pivot table (multiple ranges)Summary you can pivotOn RefreshWindows
Copy and pasteOne stacked table (static)NoAll

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:D500 and 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

  1. Turn each sheet's data into a table (Ctrl+T) and give each a clear name.
  2. On each table, choose Data → From Table/Range, then Close & Load To… → Only Create Connection.
  3. Choose Data → Get Data → Combine Queries → Append, pick Three or more tables, and add them all.
  4. 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
    Combined

Excel.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.

  1. Click the top-left cell of an empty area on a summary sheet.
  2. Choose Data → Consolidate and pick the function, usually Sum.
  3. Click in Reference, select the range on the first sheet including its labels, and click Add. Repeat for each sheet.
  4. 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:

Result of stacking Jan, Feb and Mar
MonthRegionProductUnits
JanNorthDesk12
JanSouthChair30
FebNorthDesk9
FebNorthLamp14
MarSouthDesk11

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.

Units by product and month from the stacked table
ProductJanFebMarTotal
Chair300030
Desk1291132
Lamp014014
Total42231176

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

Troubleshooting combined sheets
SymptomCauseFix
Rows of zeros at the bottom of each blockVSTACK returns 0 for the empty cells in a range that is larger than the dataWrap it in FILTER to keep only rows where the first column isn't blank
#SPILL! instead of a tableSomething is already in the cells the result needsClear the area below and to the right of the formula
#N/A in the last columns of some rowsOne range is narrower than the others, and VSTACK pads it with #N/AMake every range the same width, or wrap the formula in IFNA(…, "")
A new month's tab is missingThe tab sits outside the 3D reference, or its table isn't part of the AppendMove it between the first and last tabs, or add its query to the Append
Consolidate lists “Desk” twiceOne sheet has a trailing space or different capitalisation in the labelClean the labels with TRIM, then consolidate again
Totals come out as zero or too lowNumbers stored as text on one tab are ignored by SUMConvert 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:

  1. 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).
  2. 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.
  3. Click Data → Refresh All if you use Power Query. VSTACK and Google Sheets formulas update by themselves.
  4. 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?

Choosing a way to combine sheets
Your situationUseWhy
Microsoft 365, a few tabs, want it liveVSTACKUpdates as you type, no refresh
Tabs added every month, Excel 2016 or laterPower Query Append or Excel.CurrentWorkbookNew tables join the result on Refresh
Same layout, you need totals onlyConsolidate or 3D SUMGives the summary directly
A pivot table across tabsStack first, then one pivot tableKeeps your column names as fields
Excel 2013 or older, one-off jobCopy and pasteWorks in every version
Google SheetsVSTACK or {…; …} with FILTERNo 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

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.