# How to merge Excel worksheets

Microsoft’s spreadsheet program Excel offers various functions to merge contents from different worksheets. This guide explains step-by-step how to combine worksheets with entries that are identical or different.

## How to merge Excel worksheets quick guide

1. Select the cell where you want to merge the data.
2. Click on **Data** > **Consolidate** in the ribbon.
3. In the **Reference** field, select the cells you want to merge and click **Add** to include them in the references.
4. Click on **OK** to merge the values.

Note The following instructions apply to Excel in Microsoft 365 and Excel versions 2021, 2019 and 2016.

## What are the benefits of merging data in Excel?

The advantage of combining data from different worksheets is that you can [create new Excel tables](https://www.ionos.co.uk/digitalguide/online-marketing/online-sales/creating-an-excel-table/) to work with customer or company data. In contrast to the features for [merging cells](https://www.ionos.co.uk/digitalguide/online-marketing/online-sales/merging-cells-in-excel/) and [moving cells](https://www.ionos.co.uk/digitalguide/online-marketing/online-sales/moving-cells-in-excel/), merging data with the **Consolidate** function or the **Power Query Editor** allows you to combine data from separate worksheets into one worksheet.

## How to merge data in Excel with Consolidate

If you want to merge separate worksheets in Excel, use the Consolidate feature. To use this feature, your Excel file need to have **at least two tabs**. In the following example, we’ll combine customer data from NYC and LA.

**Step 1**: Open the file with the worksheets that you want to merge. Click on **+** next to the worksheet names at the bottom of the window to create the worksheet where the data should be merged. Name the worksheet. A name like ‘Consolidated’ will make it easy to find.

[![Image: Creating a new worksheet for merged data](https://www.ionos.co.uk/digitalguide/fileadmin/_processed_/2/7/csm_combine-worksheets-new-worksheet-uk_392822b717.webp "Creating a new worksheet for merged data")](https://www.ionos.co.uk/digitalguide/fileadmin/DigitalGuide/Screenshots_2024/combine-worksheets-new-worksheet-uk.png) Open the Excel file containing the data you want to merge and click on the plus sign to create a new worksheet. **Step 2**: In the new worksheet, select the cell where you want to merge the data. In this example, we’ve chosen cell A1. Now click on **Data** in the menu at the top of the window. In the **Data Tools** section, select the symbol for **Consolidate**.

[![Image: Go to ‘Data’ in the ribbon and click on ‘Consolidate’ in the Data Tools field](https://www.ionos.co.uk/digitalguide/fileadmin/_processed_/2/c/csm_combine-worksheets-data-tools-consolidate_6e46f9007b.webp "Go to ‘Data’ in the ribbon and click on ‘Consolidate’ in the Data Tools field")](https://www.ionos.co.uk/digitalguide/fileadmin/DigitalGuide/Screenshots_2024/combine-worksheets-data-tools-consolidate.png) Click on Data and then select Consolidate from the Data Tools field. **Step 3**: The **Consolidate menu** will open. This is where you can choose how Excel should merge the data (i.e., sum, average, max). In this example, we’ll choose the **Sum** option so that the values are added together.

[![Image: Consolidate menu in Excel](https://www.ionos.co.uk/digitalguide/fileadmin/_processed_/5/1/csm_combine-worksheets-consolidate-window_702909bf37.webp "Consolidate menu in Excel")](https://www.ionos.co.uk/digitalguide/fileadmin/DigitalGuide/Screenshots_2024/combine-worksheets-consolidate-window.png) In the Consolidate menu, you can choose how the data is merged in Excel. **Step 4**: Minimise the Consolidate menu by clicking on the arrow under **Reference**. You’ll now see the menu labelled **Consolidate – Reference** in its collapsed form.

**Step 5**: Go to the first worksheet and select the data that you want to merge. You’ll now see the cells you selected in the **Consolidate – Reference** window. Next, click on the small arrow in Consolidate - Reference.

[![Image: Selection of worksheet cells that should be merged](https://www.ionos.co.uk/digitalguide/fileadmin/_processed_/b/f/csm_combine-worksheets-consolidate-cells-uk_ea2a253b19.webp "Selection of worksheet cells that should be merged")](https://www.ionos.co.uk/digitalguide/fileadmin/DigitalGuide/Screenshots_2024/combine-worksheets-consolidate-cells-uk.png) Select the data and then click on the arrow in Consolidate - Reference. **Step 6**: Add the selected reference to **All references** using the **Add** button. Repeat the process for the second worksheet.

[![Image: Add table contents to all references](https://www.ionos.co.uk/digitalguide/fileadmin/_processed_/a/f/csm_combine-worksheets-consolidate-both-sheets_cb932b112f.webp "Add table contents to all references")](https://www.ionos.co.uk/digitalguide/fileadmin/DigitalGuide/Screenshots_2024/combine-worksheets-consolidate-both-sheets.png) Go to Add to transfer the reference to ‘All references’ and then repeat the same process for the second worksheet. **Step 7**: Go to the worksheet where you plan to merge the sheets. Click on the tick boxes next to **Top row** and **Left column** to ensure the sheet is properly formatted. Then click on **OK**.

**Step 8**: You’ll now see the merged data from the other Excel sheets in the new worksheet.

[![Image: Screenshot of the merged data in a new worksheet](https://www.ionos.co.uk/digitalguide/fileadmin/_processed_/b/e/csm_combine-worksheets-consolidate-finished-uk_7bbf92d131.webp "Screenshot of the merged data in a new worksheet")](https://www.ionos.co.uk/digitalguide/fileadmin/DigitalGuide/Screenshots_2024/combine-worksheets-consolidate-finished-uk.png) You can now see the merged data in a new worksheet. ## How to merge data in Excel with the Power Query Editor

For simple merging operations where both worksheets have the same formatting and information, the consolidation feature will suffice. However, if you want to merge sheets that contain, for example, different values for the same customer group, the **Power Query Editor** is your best bet.

**Step 1**: Go to the first worksheet and select the contents. Then click on the **Data** menu and afterwards on **From Table/Range**. After the **Create Table** window pops up, click **OK**.

[![Image: Click on ‘Data’ and ‘From Table/Range’ to open the Power Query Editor](https://www.ionos.co.uk/digitalguide/fileadmin/_processed_/2/0/csm_combine-worksheets-from-table-uk_87b0dd6f32.webp "Click on ‘Data’ and ‘From Table/Range’ to open the Power Query Editor")](https://www.ionos.co.uk/digitalguide/fileadmin/DigitalGuide/Screenshots_2024/combine-worksheets-from-table-uk.png) Select the table in the first worksheet, click on Data and From Table/Range and then on OK. **Step 2**: The Power Query Editor will now open with the contents of the table you selected. To add the contents of the second table, click on **New Source** in the upper right-hand corner of the Excel window. Select **File** and then **Excel Workbook**.

[![Image: Import an Excel table via ‘New source’, ‘File’ and ‘Excel’](https://www.ionos.co.uk/digitalguide/fileadmin/_processed_/5/5/csm_combine-tables-pqe-source_e0dfedce72.webp "Import an Excel table via ‘New source’, ‘File’ and ‘Excel’")](https://www.ionos.co.uk/digitalguide/fileadmin/DigitalGuide/Screenshots_2024/combine-tables-pqe-source.png) Go to ‘New source’ in the Power Query Editor and import the second table via File > Excel. **Step 3**: Import the Excel file containing the second table and click **OK** in the navigator that opens.

**Step 4**: Click on **Merge Queries** and then again in the dropdown menu.

[![Image: Go to ‘Combine’ and ‘Merge Queries’ in the menu](https://www.ionos.co.uk/digitalguide/fileadmin/_processed_/6/d/csm_combine-tables-pqe-combine_b3f35661f0.webp "Go to ‘Combine’ and ‘Merge Queries’ in the menu")](https://www.ionos.co.uk/digitalguide/fileadmin/DigitalGuide/Screenshots_2024/combine-tables-pqe-combine.png) Select Combine in the menu and then Merge Queries. **Step 5**: A window labelled **Merge** will open. Select the two tables and choose the columns with the same data to ensure consistent formatting.

[![Image: Select matching column(s)](https://www.ionos.co.uk/digitalguide/fileadmin/_processed_/6/6/csm_combine-tables-corresponding-columns_b562a723f0.webp "Select matching column(s)")](https://www.ionos.co.uk/digitalguide/fileadmin/DigitalGuide/Screenshots_2024/combine-tables-corresponding-columns.png) Select the matching columns in the Merge menu and click on OK. **Step 6**: To make the contents of the table visible, click on the **arrow symbol** under **Table 2** and untick the boxes next to the columns with the same data (in this case Column1). Tick the boxes for different data that should be added.

[![Image: Remove the tick for the corresponding columns](https://www.ionos.co.uk/digitalguide/fileadmin/_processed_/8/5/csm_combine-tables-mark-columns_8286092997.webp "Remove the tick for the corresponding columns")](https://www.ionos.co.uk/digitalguide/fileadmin/DigitalGuide/Screenshots_2024/combine-tables-mark-columns.png) Make the content you want to add to the table visible by clicking on the arrow symbol and highlighting the desired information. **Step 7**: The editor will then merge the contents you selected into a single table. Click on **Close & Load** to place the merged table in a new Excel worksheet.

[![Image: Complete the merge using the ‘Close & Load’ button](https://www.ionos.co.uk/digitalguide/fileadmin/_processed_/a/9/csm_combine-tables-powerquery-finished_4317186c27.webp "Complete the merge using the ‘Close & Load’ button")](https://www.ionos.co.uk/digitalguide/fileadmin/DigitalGuide/Screenshots_2024/combine-tables-powerquery-finished.png) Now insert the merged tables into a new Excel worksheet under Close & Load.


This is a markdown version of: [https://www.ionos.co.uk/digitalguide/online-marketing/online-sales/merge-data-in-excel/](https://www.ionos.co.uk/digitalguide/online-marketing/online-sales/merge-data-in-excel/) for AI/LLM consumption.