How To Merge Tabs In Microsoft Excel

Excel provides the function of including a number of sheets below the identical workbook. When you add sheets to your Excel file, you may view them as tabs on the backside of your Excel program. You can add associated information in every of those tabs, such because the gross sales for various months in a 12 months. 

Even if coming into information in a number of tabs could make accessing particular person information simpler, in some circumstances, it’s possible you’ll have to merge these tabs right into a single worksheet. Excel provides varied methods to combine your worksheet by both stacking them on prime or manipulating the info set using the library Excel features.

In this text, we now have gathered 4 methods to merge Excel tabs. Keep studying and get your merged worksheet prepared very quickly!

How to Merge Tabs in Excel?

There are a number of methods to merge tabs in Excel. You may both merge tabs as it’s and even use library Excel features, reminiscent of SUM, COUNT, and VAR, to calculate the prevailing information in your sheet after which consolidate it right into a singular information. 

Before we start with the strategies of merging tabs in Excel, guarantee that these information are in the identical format. Not solely will it not look organized within the ultimate worksheet, however two of the below-mentioned steps won’t work except your information set follows a sample.

Copy-Paste Data

The best approach to merge a number of tabs in Excel is by copying the info from them and pasting it into a brand new worksheet. If you wouldn’t have many tabs in Excel to merge, this may very well be the proper, much less time-consuming methodology for you.

If you want to use the copy-paste methodology to merge tabs in Excel, comply with these steps:

1. Open your Excel file.
2. From the workbook, choose + on the underside to create a clean worksheet.
   
3. Select the primary worksheet and choose your information, then copy it (Ctrl + C).
   
4. Paste your information within the clean worksheet. 
   
5. Repeat steps 3 and 4 for all of the sheets you want to merge.

Reference Sheets

You can reference a unique sheet inside or outdoors the workbook using 3-D referencing in MS Excel. 3-D referencing is dynamic, that means that if you happen to change the info from the supply sheet, Excel routinely modifications the info within the vacation spot sheet. 

If you need to merge your cells using cell referencing to stack information units on prime of one another, make sure the cell content material is on a unique cell location. Here are the steps you may comply with to make use of 3-D referencing to merge tabs in Excel:

1. Open your Excel worksheet.
2. Create an empty worksheet and choose an empty cell.
3. Enter the method on an empty cell:
   =(sheet title)!(vary)
4. Use Flash Fill to fill in all information from the vary.
   
5. Repeat step 4 for all of the sheets.

Consolidate Tool

The consolidate device is a good way to merge information in a number of worksheets into singular information. The consolidate device makes use of the library Excel features to carry out the calculations to enter the end result in your worksheet. This device can create a sum of the info within the merged tab. Your information have to be in the identical sample for this device’s work.

If you want to use the Consolidate device, comply with these steps:

1. Open your worksheet and create a clean worksheet.
2. Head to the Data tab from the clean workbook.
3. In the Data part, choose the choice for Consolidate.
   
4. Enter the next information within the Consolidate window:
   • Function: Select the drop-down menu and choose the operate you need to use. For instance, if you wish to add the info, choose SUM.
   • Reference: Select the arrow icon and head to the primary sheet with the info you need to merge. Select the vary along with your information set. Click on the arrow to open the window once more, then choose Add. Repeat this for all worksheets.
     
   • Use labels in: If you will have headers within the prime row and left column, choose the field next to them.
     
5. Click OK.

Use Power Query

Using the ability question is presumably essentially the most handy approach to merge Excel tabs. Before you proceed to the steps, you’ll want to convert your information right into a desk, or else the measures won’t give you the results you want.

Although using energy question may be intimidating for newer Excel customers, you may simply consult with the steps we’ve talked about beneath to merge your worksheets.

1. Open your worksheet with the info. 
2. Head to the Data tab.
3. In the Get & Transform part, choose New Query.
   
4. From the fly-out menu, navigate to From Other Sources > Blank Query.
   
5. In the method bar, sort =Excel.CurrentWorkbook() and choose Enter.
   
6. Right-click on the Content header and choose Remove Other Columns.
   
7. Click on the icon on the top-right of the Content header.
8. Uncheck the field next to Use unique column title as prefix.
   
9. Click OK.
10. Head to the Transform tab the choose Detect Data Type.
    
11. Select Close and Load.
    
12. Convert the desk to vary (Table Design > Convert to Range).