Yes. To append tables in Excel without Power Query, copy the data rows from one table and paste them beneath the last row of another table with the same columns; the destination table will usually expand to include them. For a repeatable or automatically updating result, use a VSTACK formula in Microsoft 365 or Excel 2024, or automate the task with VBA.
What “append” means in Excel
Appending means adding records from one dataset below records in another. For example, if January and February sales lists have the same fields—Date, Product, and Amount—appending puts February’s rows after January’s. It does not match records by a key, combine columns side by side, or remove duplicates automatically. Those are different operations.
An Excel Table is a formatted range with features such as filter buttons, structured references, and automatic formatting. Two separate Excel Tables do not become one table merely because they sit next to each other or have similar names. To create one combined list, append the source rows into a destination range or generate a combined result with a formula.
Before choosing a method, check that both sources have the same fields and compatible data. A date column should contain dates in both lists, for instance, and a sales amount should not be text in one table and numbers in the other. Matching column order is especially important when pasting or running a macro.
Method 1: Copy and paste rows into a destination table
For a one-time task, copying rows is the simplest option in Excel for Microsoft 365 on Windows:
- Confirm the destination table’s headers and column order match the source table’s. Resolve any differences before copying.
- Select only the source data rows, not the header row. Copy with Ctrl+C.
- Click the first blank cell directly below the destination table’s last data row and paste with Ctrl+V.
- Check that the table expanded, that the values landed in the correct columns, and that formulas or formats look right.
When you paste immediately below a table, Excel commonly expands the table to include the new rows. If it does not, click inside the destination table and use Table Design > Resize Table to include the pasted range. Table Design is a contextual ribbon tab, so it appears when a cell in a table is selected. You can also enter or paste data into the first row below a table and check whether Excel extends the table automatically; behavior can depend on workbook settings and how the data is pasted.
On a Mac, use Command+C and Command+V instead of Ctrl+C and Ctrl+V. Excel for the web also supports ordinary copy and paste, though the ribbon layout and available table controls can differ slightly from desktop Excel. If you are pasting between workbooks, make sure the destination workbook is open and that you are pasting values and formulas in the way you intend.
Method 2: Add rows directly to the Excel Table
If you are entering a small number of new records, you can type or paste them into the first blank row beneath the destination table. Excel normally expands the table, carries down calculated-column formulas, and applies the table’s formatting. You can also click in the final table cell and press Tab to create a new row, then enter the next record.
This is convenient when you are adding a few rows, but it is less reliable for a large paste if the columns are misaligned or the paste includes a header. A table’s header row should appear only once. If the source has its own header, omit that row when appending. Check that formulas in calculated columns filled correctly, especially if the pasted source included values in those same columns.
Method 3: Combine ranges with VSTACK
For an automatically updating combined result, VSTACK places one range below another. It is available in Microsoft 365 and Excel 2024; it is not available in every older Excel release. Keep the source tables intact and put the formula in a blank area, since the output needs room to spill into adjacent cells.
Assume the first table’s data is in A2:C4 and the second table’s data is in E2:G3, with the same three columns in the same order and no headers included in either range. Enter =VSTACK(A2:C4,E2:G3) in a blank cell. The result is five combined data rows and three columns: three rows from the first range followed by two from the second. The formula updates when values in those referenced cells change, but the fixed ranges will not automatically grow to include rows added beyond row 4 or row 3.
To include headers, put a single header row above the formula output or include a header range once in the stack. Do not stack both tables’ headers as if they were records. If you use actual Excel Tables, structured references can make source references easier to read, but the formula still needs to avoid repeating headers and must have space to spill. A spill error usually means something is occupying a cell in the required output area; clear those cells or move the formula.
A VSTACK result is a formula output, not a new editable Excel Table. You can use the result for analysis or copy it and paste values elsewhere if you need a fixed combined dataset. Dynamic array functions also may not be available to people opening the file in older Excel versions.
Method 4: Use formulas to link a small, fixed list
For a short list that must show values from another sheet, direct cell references are another option. For example, you can place a reference to a source cell in the corresponding destination position, then fill the formula across and down. This keeps the displayed result linked to the source, but it takes more setup than pasting and is awkward when the number of source rows changes frequently.
Linked formulas do not physically append source records into the destination table. They create a separate formula-driven view, and blank rows, changed source locations, or inserted records can require adjustments. For a larger or changing set of records, VSTACK is usually more practical when available; for a one-time fixed list, copy and paste values may be simpler.
Method 5: Automate repeated appends with VBA
If the same append must be repeated, VBA can copy data rows from one Excel Table into another. The example below assumes both tables are on a worksheet named Data, the source is named Table2, the destination is named Table1, and their data columns match in number and order. It appends values only, not source formatting.
Sub AppendTable2ToTable1()
Dim ws As Worksheet
Dim sourceTable As ListObject
Dim targetTable As ListObject
Dim newRow As ListRow
Dim rowNumber As Long
' Both tables are on the Data worksheet.
Set ws = ThisWorkbook.Worksheets("Data")
Set sourceTable = ws.ListObjects("Table2")
Set targetTable = ws.ListObjects("Table1")
' Stop if the source table has no data rows.
If sourceTable.DataBodyRange Is Nothing Then Exit Sub
' Add each source data row to the destination table.
For rowNumber = 1 To sourceTable.ListRows.Count
Set newRow = targetTable.ListRows.Add
newRow.Range.Value = sourceTable.ListRows(rowNumber).Range.Value
Next rowNumber
End Sub
To add the macro, use Developer > Visual Basic, insert a standard module, and paste the code into it. The Developer tab may need to be enabled in Excel options. Save the workbook in a macro-enabled format such as .xlsm and review your organization’s macro policies before running it. Save a copy of the workbook first: macro actions cannot be undone with Ctrl+Z. If the tables are on different worksheets, the code needs separate worksheet references for each table.
Choose a method based on how often the data changes
- One-time append: Copy and paste the source data rows beneath the destination table, then verify the result.
- A few new records: Type or paste them into the table’s next row and confirm Excel expanded the table.
- A live combined view: Use VSTACK if everyone who needs the workbook has a compatible Excel version.
- Repeated, controlled updates: Consider VBA when a fixed process and macro-enabled workbook are appropriate.
- Records with different columns: Reconcile the schemas first. Decide which fields belong in the output and how missing values should be represented.
For teams that share files across Excel versions, a pasted, static combined table is often easier to open than a workbook relying on newer functions or macros. Conversely, if the inputs change regularly and the combined view should refresh without manual copying, a formula-based approach may be a better fit. Consider who will maintain the workbook as well as how it works today.
Common problems and how to prevent them
Rows land in the wrong fields: The source and destination columns are in different orders. Rearrange the source columns or copy one field at a time; do not assume matching headers cause Excel to remap pasted values.
The table does not include pasted rows: The paste may be adjacent but outside the table. Use Table Design > Resize Table to include the added rows, then verify filters and calculated columns still cover the whole dataset.
Headers appear among the records: You copied the source header along with the data. Remove the extra header row and keep just one set of column names at the top of the combined table.
Dates or amounts look wrong: Formatting can change how values appear, and text that resembles a date or number may not behave like a real date or number. Check representative cells and, if necessary, convert the source data before appending. Formatting a text value as a date does not necessarily convert it into a usable date value.
Repeated records appear: Appending does not automatically remove duplicates. Decide what makes a record unique, then review or remove duplicates using Data > Remove Duplicates on a copy or after confirming the intended columns. Avoid deleting records based only on one field if that field can legitimately repeat.
VSTACK returns an error or is unavailable: Confirm the Excel version supports the function, that the source ranges have compatible column counts, and that the spill area is empty. If the workbook must work in an older version, use a static paste or another compatible workflow.
Frequently Asked Questions
Can I append two Excel Tables without Power Query?
Yes. Copy the source table’s data rows and paste them below the destination table, or use VSTACK for a formula-based combined result in a supported Excel version. The two tables do not merge just because they have matching headers.
Does Excel automatically expand a table when I paste rows?
It often expands when data is pasted directly beneath the table, but check the table boundary afterward. If the new rows remain outside it, select the table and use Table Design > Resize Table.
Can I append tables with different column orders?
Not safely by pasting whole rows as-is. Excel places pasted values by position, not by comparing header names. Reorder the columns to match, or build a formula or process that explicitly maps each field.
Will VSTACK work in older Excel versions?
VSTACK requires Microsoft 365 or Excel 2024. If the workbook will be opened in a version without VSTACK, use a static combined range or choose a workflow supported by that version.
Does appending remove duplicate rows?
No. It adds rows and leaves duplicate checking to you. Decide which fields define a duplicate, make a backup if the data matters, and use Data > Remove Duplicates only after confirming the selected columns and intended outcome.
Related
- Excel Not Responding and Unsaved? Recover Your Work
- Excel Insert Slicer: Why No Connections Are Found?
- Excel Insert X Rows: Fast Methods for Any Worksheet
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Remove Duplicates in Excel by Entire Row: A Safe GuideTo remove duplicate records in Excel, select the full data range, choose Data > Remove Duplicates, and check…
- Excel Next Sheet Shortcut for Windows, Mac and WebIn Excel for Microsoft 365 on Windows, press Ctrl+Page Down to move to the next worksheet in the…
- Disable Circular Reference Warnings in Excel SafelyTo stop Excel from showing a circular-reference warning for an intentional circular formula, enable iterative calculation: in Excel…
- How to Set Out of Office in Outlook on a Windows PCTo set an out-of-office reply in Outlook on a Windows PC, open Automatic Replies, switch them on, write…
- Shortcut Key to Insert a Line in Word: Quick GuideTo insert a horizontal line in Microsoft Word, type three hyphens (---) on a blank line and press…
- How to Unblock a Sender in Outlook MailTo unblock a sender in Outlook, remove their email address or domain from your blocked list: in new…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.