How To Merge Tabs In Microsoft Excel

Excel offers the feature of adding multiple sheets under the same workbook. When you add sheets to your Excel file, you can view them as tabs at the bottom of your Excel program. You can add related data in each of these tabs, such as the sales for different months in a year. 

Even if entering data in multiple tabs may make accessing individual data easier, in some cases, you may need to merge these tabs into a single worksheet. Excel offers various ways to integrate your worksheet by either stacking them on top or manipulating the data set using the library Excel functions.

In this article, we have gathered four ways to merge Excel tabs. Keep reading and get your merged worksheet ready in no time!

How to Merge Tabs in Excel?

There are multiple ways to merge tabs in Excel. You could either merge tabs as it is or even use library Excel functions, such as SUM, COUNT, and VAR, to calculate the existing data in your sheet and then consolidate it into a singular data. 

Before we begin with the methods of merging tabs in Excel, make sure that these data are in the same format. Not only will it not look organized in the final worksheet, but two of the below-mentioned steps will not work unless your data set follows a pattern.

Copy-Paste Data

The easiest way to merge multiple tabs in Excel is by copying the data from them and pasting it into a new worksheet. If you do not have many tabs in Excel to merge, this could be the perfect, less time-consuming method for you.

If you wish to use the copy-paste method to merge tabs in Excel, follow these steps:

1. Open your Excel file.
2. From the workbook, select + on the bottom to create a blank worksheet.
   
3. Select the first worksheet and select your data, then copy it (Ctrl + C).
   
4. Paste your data in the blank worksheet. 
   
5. Repeat steps 3 and 4 for all the sheets you wish to merge.

Reference Sheets

You can reference a different sheet within or outside the workbook using 3-D referencing in MS Excel. 3-D referencing is dynamic, meaning that if you change the data from the source sheet, Excel automatically changes the data in the destination sheet. 

If you want to merge your cells using cell referencing to stack data sets on top of each other, ensure the cell content is on a different cell location. Here are the steps you can follow to use 3-D referencing to merge tabs in Excel:

1. Open your Excel worksheet.
2. Create an empty worksheet and select an empty cell.
3. Enter the formula on an empty cell:
   =(sheet name)!(range)
4. Use Flash Fill to fill in all data from the range.
   
5. Repeat step 4 for all the sheets.

Consolidate Tool

The consolidate tool is a great way to merge data in multiple worksheets into singular data. The consolidate tool uses the library Excel functions to perform the calculations to enter the result on your worksheet. This tool can create a sum of the data in the merged tab. Your data must be in the same pattern for this tool’s work.

If you wish to use the Consolidate tool, follow these steps:

1. Open your worksheet and create a blank worksheet.
2. Head to the Data tab from the blank workbook.
3. In the Data section, select the option for Consolidate.
   
4. Enter the following data in the Consolidate window:
   • Function: Select the drop-down menu and select the function you want to use. For example, if you want to add the data, select SUM.
   • Reference: Select the arrow icon and head to the first sheet with the data you want to merge. Select the range with your data set. Click on the arrow to open the window again, then select Add. Repeat this for all worksheets.
     
   • Use labels in: If you have headers in the top row and left column, select the box next to them.
     
5. Click OK.

Use Power Query

Using the power query is possibly the most convenient way to merge Excel tabs. Before you proceed to the steps, be sure to convert your data into a table, or else the measures will not work for you.

Although using power query can be intimidating for newer Excel users, you can easily refer to the steps we’ve mentioned below to merge your worksheets.

1. Open your worksheet with the data. 
2. Head to the Data tab.
3. In the Get & Transform section, select New Query.
   
4. From the fly-out menu, navigate to From Other Sources > Blank Query.
   
5. In the formula bar, type =Excel.CurrentWorkbook() and select Enter.
   
6. Right-click on the Content header and select Remove Other Columns.
   
7. Click on the icon on the top-right of the Content header.
8. Uncheck the box next to Use original column name as prefix.
   
9. Click OK.
10. Head to the Transform tab the select Detect Data Type.
    
11. Select Close and Load.
    
12. Convert the table to range (Table Design > Convert to Range).