If you’ve imported or created multiple tables in Excel sheets, you may find the need to merge them together into one. Having all data in a single table is simply so convenient to analyze information and input formulas.

In most cases, some users would copy Table 2 and paste them into the adjacent cells of Table 1 to combine them together. This workaround, however, is ineffective if your table is extremely large or does not correspond.

So, the most easiest yet effective way to merge any tables is **using the VSTACK function**. With this function, you can combine numerous tables within sheets, from different sheets, or separate workbooks. However, for users who do not have access to this function, **you could use VLOOKUP, Power Query, or Consolidate tools**.

## Using VSTACK Function

Excel’s VSTACK function combines the number of vertical arrays into a single array. This function is perfect for merging tables within the same sheet, different sheets, and different workbooks.

**Syntax: **=VSTACK(array1,{array2],..)

As this function is relatively new, you must open Excel on **Office 365** or the **Web version **to use it.

### Case 1: Merge Tables in the Same Sheet

In the given example, we are combining three tables into a single one within the same sheet. For this, I entered the formula as

**=VSTACK(A1:B6, A9:B13, A16:B20)**

In the above formula,** A1:B6** is Table 1, **A9:B13 **is Table 2, and** A16:B20 **is Table 3**. **While selecting the values, make sure to skip the table heading for Tables 2 and 3.

### Case 2: Merge Tables in Different Sheets

VSTACK function also makes it easier to combine tables from different sheets of the same workbook.

Suppose, you need to integrate tables from **Sunday**, **Monday**, and **Tuesday **sheets. For this, we entered the given formula in the Cell **A1 **of a new sheet.

**=VSTACK(Table_1[#All], Table_2, Table_3)**

Note that before entering the formula, we first renamed all Tables from the Table Design tab. Now, let’s see how we took the array reference in the formula.

- Firstly, enter
**=VSTACK(**in an empty cell. - Then, go to
**Sunday**sheet and select the**Table reference**i.e.**A1:B6**. Put a comma and do the same for other sheets. When done, close the Parenthesis and press enter.

In the above example, we just took three sheets as a reference. But, you can take reference from as many references as you want.

### Case 3: Merge Tables in Different Workbooks

Say, you have tables in different workbooks and wish to integrate them into one. For this, we will use a similar formula as above. However, it is only effective if you have a corresponding table. If it isn’t, the formula will result in Spill 0 where there is no value.

As a reference, we will combine tables from **Merge Tables** and **Table **workbook. To do so, we first opened the workbook in side by side view. Then, we entered the formula below.

**=VSTACK(Table_1[#All], Table.xlsx!Table_2[#Data])**

Let’s see how we added this formula.

- Firstly, type in
**=VSTACK(**on an empty cell of your current sheet. Then, select the table from that sheet. - Now, enter a
**comma**in the formula. Go to another**open workbook**and select the**table**. Close the Parenthesis and hit Enter.

**NOTE:**This formula will result in a Circular Reference error if you have added a new sheet in your formula workbook.

## Using Power Query

In the above method, we merged two tables having identical data value with the VSTACK function. But, there can be instances when the table you wish to combine does not correspond with other tables like in the image below.

Suppose, you need to consolidate these two tables into one. For this, we will use the Power Query tool. Here, we will create a connection in all tables and merge them. To do so, your data must be in Table Format.

### Step 1: Create a Connection in Queries

- Click on any cell of
**Table 1**. - On Data tab, click on
**From Table/Range**. - On Power Query Editor window, go to
**File Tab**. Click on**Close & Load > Close & Load To**. - Under Select how you want to view this data in your workbook, pick
**Only Create Connection**. Then, hit**OK**. - Again, repeat
**Steps 1 to 4**to create Connections for the rest of the tables too. On the Queries & Connections menu, you should see all the tables.

### Step 2: Merge Queries

- On your worksheet, go to
**Data**Tab. - On Get & Transform Data group, click on
**Get Data**. - Hover Over
**Combine Queries**>**Merge**. - A merge window will appear on your screen. Expand the drop-down menu and select a
**Table**. Again, second select**Table**to merge. - Now, Select the
**identical columns**on your table. - Under Join Kind, leave the option to
**Left Outer(all from first, matching from second)**. Click**OK**.

### Step 3: Close & Load Table

- On Power Query Editor Tool, hover over the
**Merged Table**and click on**Expand**Icon. - Only tick on the
**Column**you want to expand and hit**OK**. - If needed, swap
**Column**position. - From File Tab, click on
**Close & Load**>**Close & Load.** - The merged table will appear on the new sheet.

**Quick Tip:**If you want to merge more tables, pick

**Close & Load > Close & Load to**on the File tab. On Import Data, click on

**Create Connection**. Then, repeat the above

**Steps 2 & 3**to combine queries and load them.

## Using VLOOKUP Function

Another way to join several tables into a single is by using the VLOOKUP function. I recommend you use this method if you are sharing your workbook with older Excel version users.

Excel’s VLOOKUP function is mostly used to locate the values in the table. You could even join the tables that do not correspond with each other. But, the tables must have at least one column in common for this function to work.

**Syntax: **VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

**Lookup_value:**value to look for in the**First column**of Table**Table_array:**range in a table VLOOKUP looks for the**lookup_value****Col_index_num:**Values to return from column number**[range_lookup]:**logical value which is either Approximate match or Exact match. (Optional)- Approximate match -1/TRUE searches the nearest value if VLOOKUP does not find the exact value.
- Exact match – 0/FALSE looks for the specified value/range in the column.

Let’s suppose, we need to merge the given two tables using VLOOKUP. For this, enter the formula as

**=VLOOKUP([@Product],Table2, 2, FALSE)**

In the above formula, VLOOKUP will return the value in the same row as** [@Product]** which is the first column of Table 1. By passing, the **FALSE** range_lookup argument, it will look for the precise value in **Table 2’s second column** which is the **Product ID**. As a result, you’ll get all **Product ID **values in Table 1.

## Using Consolidate Tool

Excel’s Consolidate Tool returns the summed-up value of ranges within the same sheet or different sheets. Meaning, it integrates all duplicate data of a table and consolidates the output in a single range. Use this method if you wish to extract only unique values from a table.

- Create a
**New sheet**in your workbook. - Head to Data Tab and click on
**Consolidate**. - Click the
**Collapse Icon**on Reference. Select the**Table Reference.** - Next to All References, click
**Add**. - Repeat step
**3**&**4**for other tables. - Under Use labels in, tick
**Top row**and**Left column**box. Click**OK**.