What merging tables means and when you need it

Merging tables in Excel means combining data from two or more separate tables into one. This is different from merging cells (which combines cells into a single box). When you merge tables, you are stacking rows from one table onto another, or joining them side by side based on a shared column.

You need this when you have customer data split across two worksheets, sales figures from different months in separate tables, or inventory lists from different locations that you want to see together. Excel does not have a single "merge tables" button — instead, you use a few different methods depending on whether your tables are in the same worksheet, different worksheets, or need to match up based on a common value like a customer ID.

The method you choose depends on your situation. If one table sits directly below the other with the same column headers, you can straightforward copy and paste. If the tables are in different worksheets or need to line up by a matching column, you will use either the Consolidate tool or a VLOOKUP formula.

Key Takeaways

  • Stacking tables with identical columns is fastest with copy and paste — select the data rows (not headers) from the second table and paste below the first.
  • The Consolidate tool in the Data menu combines tables from different worksheets and can sum, average, or count values automatically.
  • VLOOKUP formulas let you pull data from one table into another based on a matching column, like customer ID or product code.
  • Before merging, check that column headers match exactly and that you do not have duplicate rows in either table.

Stacking tables with copy and paste

If your two tables have the same column headers and you want to combine all rows into one table, copy and paste is the fastest method. This works when both tables are in the same worksheet or different worksheets, and when one table sits below the other with no data in between.

First, click on the first cell of the data you want to copy in the second table — this is the first data row, not the header. Then hold Shift and click on the last cell of that table to select all the data rows. Do not include the header row. Press Ctrl+C (or Cmd+C on Mac) to copy.

Now click on the first empty cell below your first table. This should be directly below the last row of data in the first table. Press Ctrl+V to paste. Excel will paste all the rows from the second table. If the columns line up correctly, the data will fall into the right place automatically.

After pasting, check the data to make sure nothing shifted. If a column in the second table was narrower or wider than the first, the pasted data might have landed in the wrong columns. If this happened, undo with Ctrl+Z and manually adjust the column widths in the second table to match the first before copying again.

Using the Consolidate tool for tables in different worksheets

The Consolidate tool is built into Excel and works well when your tables are on different worksheets or when you want Excel to automatically sum, average, or count the values as it combines them. This is useful for monthly sales data, regional totals, or any situation where you want a calculation done during the merge.

First, create a new worksheet where you want the consolidated table to appear, or use an empty area of an existing worksheet. Click on the top-left cell where you want the merged table to start. Then go to the Data menu and click Consolidate (in some versions of Excel, this is under Data > Consolidate).

A dialog box will open. In the Function dropdown, choose what you want Excel to do — Sum is the default, but you can also choose Average, Count, Min, or Max. Then click in the Reference field and navigate to your first table. Click and drag to select all the data including headers, then release. The reference will appear in the field as something like Sheet1!A1:D50.

Click Add to add that reference to the list below. Repeat for each table you want to consolidate. Once all references are added, check the boxes for "Top row" and "Left column" if your tables have headers — this tells Excel to use those as labels. Click OK. Excel will create a new table with all the data combined and the calculation applied.

Joining tables based on a matching column with VLOOKUP

When you have two tables that share a common column — like customer ID, product code, or date — and you want to pull information from one table into the other based on that match, use a VLOOKUP formula. This is different from stacking tables; it is more like adding new columns to an existing table by looking up values in another table.

Set up your main table first. This is the table that will stay in place and receive new columns. Next to it, you will add a formula that looks up a value from the matching column and pulls in data from the second table.

Click on the first empty cell to the right of your main table, in the same row as your first data row. Type a formula like =VLOOKUP(A2,Sheet2!A:D,3,FALSE). Break this down: A2 is the matching value in your main table (the customer ID or product code). Sheet2!A:D is the range in the other table where you want to search. The 3 means "pull the value from the third column of that range." FALSE means "find an exact match only."

Press Enter. If the match exists, the formula will pull the corresponding value into your cell. If it does not find a match, it will show #N/A. Copy this formula down to all rows in your main table by clicking the cell, copying it, selecting the range below, and pasting. The formula will adjust automatically for each row.

Removing duplicate rows after merging

After you merge tables, you may have duplicate rows — the same customer, product, or record appearing twice. Excel has a built-in tool to find and remove these. Select all the data in your merged table, including headers. Go to the Data menu and click Remove Duplicates.

A dialog will open showing all your column headers with checkboxes. By default, all columns are checked, which means Excel will look for rows where every column matches. If you only want to remove duplicates based on one column — for example, remove duplicate customer IDs but keep different transactions — uncheck the columns you do not care about and leave only the column you want checked.

Click OK. Excel will remove any rows where the checked columns match a previous row and show you how many duplicates it found. The removed rows are gone permanently, so if you are unsure, copy your table to a new worksheet first and test the removal there.

Checking that headers and data types match

Before you merge any tables, spend a minute checking that the columns line up correctly. Open both tables side by side if possible. Look at the header row in each table and make sure the names are spelled exactly the same — "Customer ID" and "CustomerID" are different to Excel, even though they mean the same thing to you.

Also check the data type in each column. If one table has dates formatted as "01/15/2024" and the other has them as "January 15, 2024," they will not match in a VLOOKUP formula. Similarly, if one column has numbers stored as text (like "00123") and another has them as actual numbers (123), they will not match either. If the formats do not match, you may need to reformat one table before merging.

Look for extra spaces at the beginning or end of values, especially in ID columns. A space before or after a customer ID will prevent a match. If you suspect this, use Find and Replace (Ctrl+H) to search for the value with a space and replace it without one.

Merging tables with different numbers of columns

If your two tables have different columns — one has customer name and address, the other has customer ID and purchase history — you can still merge them, but the method depends on what you want the result to look like.

If you want all columns from both tables in the final result, use VLOOKUP to pull columns from one table into the other based on a matching ID. This keeps your main table intact and adds new columns alongside it. If you want to stack the tables vertically and some columns are missing from one table, copy and paste as usual — the missing columns will straightforward be blank in those rows.

If the tables have completely different structures and you are not sure how to combine them, step back and think about what question you are trying to answer. Do you want to see all transactions for each customer? Do you want a total of all sales across both tables? Do you want to compare two different datasets side by side? The answer to that question will tell you which merge method to use.

Frequently Asked Questions

Can I merge tables that are in different Excel files?

Yes. Open both files, or copy the data from one file and paste it into the other. If you use VLOOKUP or Consolidate, you can reference another file by including the file path in the formula or reference — for example, [C:\Users\Documents\Sales.xlsx]Sheet1!A:D. Keep both files open while you work, or the reference may break.

What does the FALSE in a VLOOKUP formula mean?

FALSE means "find an exact match only." If you use TRUE instead, VLOOKUP will find the closest match if an exact match does not exist, which usually gives you wrong results. Always use FALSE unless you have a specific reason not to. Some versions of Excel accept 0 instead of FALSE and 1 instead of TRUE — they mean the same thing.

If I merge tables and then add new data to one of the original tables, does the merged table update automatically?

Not if you used copy and paste — the merged table is a separate copy and will not change. If you used VLOOKUP or Consolidate, the merged table will update automatically if you change the source data. This is one reason to use formulas instead of copy and paste when you expect the data to change.

How do I merge tables that have different row orders?

If one table has customers listed A to Z and another has them in the order they signed up, use VLOOKUP to match them by ID rather than stacking them. Copy and paste only works when the rows are already in the same order or when order does not matter.

What if my tables have headers in different rows?

Standardize them first. Make sure both tables have headers in row 1 and data starting in row 2. If one table has a title row above the headers, delete it or move it elsewhere. Excel's merge tools expect headers to be in the same place in both tables.