How To Merge Tabs in Excel

The natural form of data is the two-dimensional (2D) array or a table. Whether you’re handling repeatable results of scientific experiments, company employee information, surveys of product prices, or more, these all come in naturally as work tables such as spreadsheets, or tabs, in Excel.

How To Merge Tabs in Excel

An Excel file, or workbook, may contain multiple tabs. While most Excel sheets have different uses, some tabs might contain duplicated or related information. Merging, or consolidating, related tabs into a single Excel tab will help you read, analyze, and organize the data for further processing.

Here we will show how to merge two (or more) tabs in Excel alongside some advanced features and methods you can use.

Merging Tabs in Excel – It’s Simple

It should be mentioned that you can always use copy-and-paste to transfer data from one tab to the other. However, this can be time-consuming and prone to errors. There are also many elegant ways to achieve consolidation without repeating information.

Before merging, make sure all tabs have backup copies. Your source tabs will contain the raw data you’re using, while the destination tab will have the final result. Depending on your project requirements, these may or may not be the same tab.

The default consolidation function in Excel can merge data by position or by category (row or column name). However, the data needs to be in the same format and size, or it will create new rows or columns. For example, if you’re using sales metrics for different offices, you need to have the same number of categories you’re sorting by, and the same number of weeks/months you’re cataloging.

Keep in mind that the consolidation functions work with numeric data. Excel can calculate sums, averages, deviations, and minimum and maximum values, among other statistical points. However, it doesn’t allow for more nuanced transformations of text-based data.

The steps for merging, by position or category are shown below:

  1. On the destination tab, decide the positions for the merged data and click the upper-left cell of the selected positions.
  2. Click on the “Data” tab.
  3. Go to “Data Tools” and select “Consolidate.” This opens a pop-up window.
  4. In the “Function” box, select a function from the dropdown list.
  5. Select the data to be merged:
    • If by position, go to “Source Tabs” and click the “Add” button to add the data into the “All references” box. The data to be added can be manually typed in, such as “Sheet1!$B$2:$B$10” refers to the cells from B2 to B10 of the tab named Sheet1 in the current document.
    • If by category, in the “User labels in” box, select “Top row” (by rows) or “Left column” (by columns), or “Create links to source data” (write in links).
  6. Click the “OK” button, and the data in the “All References” box or selected rows/columns will be merged.

Merging Tabs in Excel VBA

VBA stands for Visual Basic for Applications, a simple yet powerful programming language you can use to extend Microsoft Office applications, including Excel. The main issue in using VBA is that you will need to understand and use code to create applications or macros.

To create and edit a VBA Macro, do the following:

  1. Select “View” in the toolbar.
  2. Click on “Macros” on the far right. This opens a pop-up Macro window.
  3. Type in the macro name (for example, “test”) and click the “Create” button at the right side and a coding console will pop up with the basic contents which read something along the lines of:
    Sub test()
    End Sub

  4. You can edit the Macro code in the console. This is an example that combines tables from sheets:
    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
     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

    The sample macro code loops over the total number of Tabs and creates a new sheet “Combined Sheet.”
  5. To run the code, in the Macro console, in “Run” tab, click “Run Sub/UserForm”, then the new Tab named “Combined Sheet” will be generated. You can also modify the Marco code to edit the data range and names.

Merging Sheets in Excel Online

Multiple free online tools allow you to merge Excel sheets. In these tools, you simply need to select and upload the workbooks (either a muti-Tab workbook or different workbooks). Examples are Aspose Cell Merger and DocSoSo Excel Combiner.

Keep in mind that merging sheets doesn’t manipulate the data. These tools take two or more Excel workbooks and return an Excel workbook with one or more sheets with data copied into them.

Combining Tabs in Excel Using Power Query

Power Query is another way to combine Tabs in Excel. For an Excel workbook with multiple tabs, use the following steps:

  1. Go to the “Data” tab and the “Get & Transform Data” group, and click on the “Get Data” button.
  2. From the list, click “Blank Query” in the “From Other Sources” option, and you will see a new Power Query Editor with the default name “Query 1.”
  3. In the function (fx) bar, type the formula “=Excel.CurrentWorkbook()” (note the formula is case sensitive), and hit the “Enter” key and a list of the Tab names of the original workbook will appear.
  4. Select the tab names (columns) for merging (or mark the option “Select All Columns” if all columns are to be merged).
  5. Click the double-arrowed button nearby the “Content” key.
  6. Unmark the “Use original column name as prefix” if you want the exact names in the original workbook to be used in the merged file instead of as prefixes.
  7. Click “OK” and you will see a new tab with all merged data, in which the tab names of the original workbook will be listed in the right-most column.

Alternative Methods

Excel is a useful tool for handling 2D array data. If you are a data scientist, however, you may access the data directly using programming languages such as Python or R. The key is that the data structure of the 2D arrays is common: using separators (space, tab, comma, etc.) to split all data points. The .csv format, for example, uses comma separators and can be directly opened for reading/editing by Microsoft Excel. Alternatively, you can also open the Excel files (.xlsx) by these programming languages, such as the “readxl” package in R, which can open and handle the data from different Excel worksheets. In these cases, simple yet convenient functions, such as cbind (binding columns with the same dimension) can be used for merging. All data from different Excel Tabs can be merged into data frames for statistical analyses and visualizations, without using the Microsoft Excel software.

Get More Out of Less

From the processes summarized above, you will get a single integrated worksheet with data from multiple resources. This worksheet can serve as an archive or backup database. Moreover, the merged tab allows you to acquire perspectives that may be missing from the data from a single resource. We encourage you to test the consolidation functions and macros on example data and back it up beforehand.

Do you have a better way to consolidate tabs in Excel? Let us know in the comments below.

Leave a Reply

Your email address will not be published.

Disclaimer: Some pages on this site may include an affiliate link. This does not effect our editorial in any way.

Todays Highlights
How to See Google Search History
how to download photos from google photos