How To Merge Tabs in Excel

Merging tabs in Excel can help you achieve optimized workflow if you need to collect data that spread across different worksheets into one place. Though you can copy and paste information from one worksheet to another, this method will be too stressful and time-consuming to merge tabs in Excel.

Instead, it is best to use an automated feature for merging tabs, and you will learn about them in this post.

Use the Excel Consolidate Function

The consolidate function in Microsoft Excel can merge data on different tabs, but it can merge only numeric values like sums, averages, deviations, minimum and maximum values, etc. It implies that you cannot merge text-based data using this feature. 

Also, it will merge tabs in the same format and size accurately but will create new rows or columns if the sizes of the tabs do not match. Here are the steps to merge tabs in Excel using the consolidate function.

However, before you start, you should ensure that you create backup copies of the data before merging them. That way, you retain access to the original data and avoid data loss even if something goes wrong while merging.

Step 1: Open the Destination Tab

Open a new tab in the Excel app so that you can merge the data from the existing tabs into it.

Then select the upper-left cell in the worksheet.

Step 2: Select the Consolidation Option

• Click the data tab from the top ribbon of the page.

• Navigate to the Data Tools section and click on the Consolidate option.

• If you cannot find this option, you will see a small drop-down arrow in the section. Click this arrow and you will see the Consolidate option.

Step 3: Configure the Consolidate Function

A small window will pop up on the screen, select your desired function in this window. The default option is the Sum and it will add the data values together.

Step 4: Select the Data to Merge

You can select the data to be merged by position or category. If you want to select data by position, go to the source tab and click the Add button to add the data to the All references box.

You can also type in the cell name containing the data in the form “Sheet1!$A$1:$A$6” which implies that you want to merge the data in cells A1 to A6 in Sheet1. Sheet 1 here represents the tab name.

Alternatively, you can select the data you wish to merge by category, say, rows or columns. You can do this with the options in the ‘Use labels in’ section. Select the box next to the Top row or Left column options, depending on the category of data you want to merge, or you can also create links to the source data.

Step 5: Complete the Process

Click OK in the window to merge the selected data in the All references box.

Note that you will have the best result if the sheets you want to consolidate have the same layout, identical headings, and data types. Also, you cannot have empty rows or columns within the data set.

Merge Tabs Using Excel VBA

Visual Basic Applications, VBA, is a simple programming language for Microsoft Office programs. It helps to perform several functions within the Microsoft Office programs environment and you can use it for merging tabs in Excel. However, you may be required to have basic programming knowledge to use this application.

Let’s see the necessary steps to merge tabs in Excel using VBA.

• Open the Excel worksheet.
• Click View from the top ribbon of the page.

• In the View options, double-click Macros to open a small window.

• Enter a name and click Create at the right pane of the window.

• The Microsoft VBA will open and you should see a page similar to the page below.

• Edit the Macro code. You can follow the Macro code example below:
  

  Sub Merge_Multiple_Sheets_Row_Wise()
  
  Dim Work_Sheets() As String
  
  ReDim Work_Sheets(Sheets.Count)
  
  For i = 0 To Sheets.Count - 1
  
   Work_Sheets(i) = Sheets(i + 1).Name
  
  Next i
  
  Sheets.Add.Name = "Combined Sheet"
  
  Dim Row_Index As Integer
  
  Row_Index = Worksheets(1).UsedRange.Cells(1, 1).Row
  
  Dim Column_Index As Integer
  
  Column_Index = 0
  
  For i = 0 To Sheets.Count - 2
  
   Set Rng = Worksheets(Work_Sheets(i)).UsedRange
  
  Rng.Copy
  
   Worksheets("Combined Sheet").Cells(Row_Index, Column_Index + 1).PasteSpecial Paste:=xlPasteAllUsingSourceTheme
  
   Column_Index = Column_Index + Rng.Columns.Count + 1
  
  Next i
  
  Application.CutCopyMode = False
  
  End Sub

• Next, click the Run tab, and select the Run Sub/UserForm option or press F5.

• The system will run the code and create a combined sheet.

That's it.

You may also like to read:

How to Insert a Signature in Excel

How To Change Date Format In Excel To dd/mm/yyyy

Export Google Calendar To Excel

How To Reset Excel Settings

How To Remove Empty Cells In Excel