Analistable

How to combine tables in Excel

By the Analistable team · Updated · 5 min read

If your tables have the same columns, stack them with =VSTACK(Table1, Table2) (structured references leave headers out) or with Power Query Append Queries. If they share a key column but hold different fields, join them with Power Query Merge Queries or an XLOOKUP column. Using real Excel tables (Ctrl+T) makes every method grow automatically as rows are added.

Part of our guide: How to combine Excel sheets into one

Why use Excel tables first

Select the data and press Ctrl+T to make it a table, then give it a name in Table Design → Table Name. Tables expand when you add rows, and formulas refer to them by name — Sales[Amount] instead of C2:C500 — so a combined result never misses new rows. This is the main advantage over plain ranges.

Structured references you'll use when combining
ReferenceMeans
SalesAll data rows of the Sales table (no header)
Sales[#Headers]The header row
Sales[#All]Header and data rows
Sales[Amount]The Amount column's data

Stack tables with VSTACK

=VSTACK(Jan[#Headers], Jan, Feb, Mar)

This returns the header once followed by every row from three tables. Because the references are table names, rows added to Feb appear automatically. VSTACK places columns by position, so all tables need the same column order.

Stack tables with Power Query Append

  1. Click in the first table and choose Data → From Table/Range, then Close & Load To → Only Create Connection. Repeat for each table.
  2. Choose Data → Get Data → Combine Queries → Append.
  3. Pick Three or more tables, add each one, click OK, then Close & Load.

Append matches columns by name, so order doesn't matter. Screens and options: Power Query Append Queries.

Join tables on a key

When one table has Customers and another has Orders, combining means adding columns, not rows. Use Data → Get Data → Combine Queries → Merge, pick the key column in each table and a join kind, then expand the columns you want. A step-by-step example is in joining two tables in Excel.

Worked example: three monthly tables stacked

=VSTACK(Jan[#Headers], Jan, Feb, Mar)
OrderProductAmount
1001Desk420
1002Lamp65
1003Chair180
1004Desk420

Need every table in the workbook without listing them? Power Query can collect them all at once — see merging all Excel tables into one sheet.

Tables with different columns

VSTACK stacks by position, so =VSTACK(Jan, Feb) gives nonsense if Feb has an extra Discount column in the middle. In Microsoft 365 you can pick the columns you want from each table with CHOOSECOLS before stacking:

=VSTACK(Jan[#Headers], Jan, CHOOSECOLS(Feb, 1, 2, 4))

Here Feb's columns 1, 2 and 4 are Order, Product and Amount, skipping Discount. Power Query Append handles this without any extra work: it matches columns by header and fills a column that is missing from one table with null. That makes Append the safer choice when tables come from different people. More on mismatched layouts: combining Excel files with different columns.

Add a column that says which table a row came from

Once rows are stacked you usually want to know their origin. With formulas, add a source column to each table before stacking, for example a Month column filled with "Jan". In Power Query, select each query, choose Add Column → Custom Column, and enter a constant such as "Jan" before you append. Do it before the append, not after, because afterwards you can no longer tell the rows apart.

Join example: Orders and Products

An Orders table holds Order, SKU and Qty; a Products table holds SKU, Product and Price. To get a line value you need both. With a formula column inside Orders:

=[@Qty] * XLOOKUP([@SKU], Products[SKU], Products[Price], 0)
Orders after the lookup
OrderSKUQtyPriceLine value
1001D-012210420
1002L-0716565
1003C-03360180

The three lines total 665. If a SKU is missing from Products, the formula returns 0, so filter Line value for 0 to find gaps. In Power Query the equivalent is a Merge with a Left Outer join; a Left Anti join lists the orders whose SKU has no product, which is a useful check. See Merge Queries join kinds.

VSTACK, Append or Merge: which to use

Decision guide
SituationUse
Same columns, same order, Microsoft 365VSTACK with table names
Same columns but order or names varyPower Query Append
Excel 2016–2021Power Query Append or Merge
Different fields about the same recordsXLOOKUP column or Merge Queries
Tables over a few hundred thousand rowsPower Query, loaded to the Data Model

Common mistakes

  • Forgetting [#Headers]: =VSTACK(Jan, Feb) has no header row, because a table name alone means data rows only.
  • Using Jan[#All] for every table, which repeats the header between each block.
  • Leaving a total row switched on in a source table — structured references to the table exclude it, but a pasted range would include it.
  • Loading every Power Query step to a sheet instead of Only Create Connection, which leaves copies of each source in the workbook.
  • Mixing text and numbers in the key column, so XLOOKUP or Merge finds no match for values that look identical.

Check the combined table

Compare row counts first: =ROWS(Jan)+ROWS(Feb)+ROWS(Mar) should equal the number of data rows in the result. Then compare a total, for example =SUM(Jan[Amount],Feb[Amount],Mar[Amount]) against the sum of the combined Amount column. If the counts agree but the totals don't, a column has shifted; if the counts differ, a table was missed or a header row was stacked as data. Sorting the result by each column and looking at the first few values is a quick way to spot text sitting in a number column.

Frequently asked questions

How do I combine multiple tables into one in Excel?
Stack them with =VSTACK(Table1, Table2, Table3) in Microsoft 365, or with Power Query's Append Queries in Excel 2016 and later.
Can I combine tables with different columns?
Power Query Append matches columns by name and fills missing ones with null. VSTACK needs the same column order.
How do I join two tables in Excel by a common column?
Use Power Query Merge Queries and choose the key column in each table, or add an XLOOKUP column.

Related guides