How to merge tabs in Excel

Excel is an incredibly useful tool for storing, managing, and displaying large sets of data. Whether you’re handling repeatable results from scientific experiments, company employee information, product pricing surveys, or more, all of this can be displayed as spreadsheets in Excel.

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

This article will show how to merge two (or more) tabs in Excel, along with some advanced features and methods you can use.

Merge tabs in Excel: it’s easy

Before merging, make sure all tabs have backups. The source tabs will contain the raw data you are 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 consolidate function in Excel can merge data by position or by category (row or column name). However, the data must be the same format and size, or new rows or columns will be created. For example, if you use sales metrics for different offices, you should have the same number of categories you sort by and the same number of weeks/months you catalog.

Note that consolidation functions work with numerical data. Excel can calculate sums, averages, deviations, and minimum and maximum values, among other statistical points. However, it does not allow for more nuanced transformations of text-based data.

The steps to merge, by position or category, are shown below:

1. On the destination tab, decide the positions for the combined data and click on the top left cell of the selected positions.

   

2. Click the “Data” tab.

   

3. Gonna “Data tools” and select “Consolidate.” This opens a popup window.

   

4. In it “Function” Select a function from the drop-down list.

   

5. Select the data to merge:
   • Yes of course, go to “Source tabs” and click on the “Add” button to add data to “All references” box. The data to be added can be entered manually, such as “Sheet1!$B$2:$B$10“Refers to cells B2 through B10 on the tab named Sheet1 in the current document.
     
   • If by category, in the “User tags in” box, select “Top row” (by rows) or “Left column” (by columns), or “Create links to source data” (write in links).

     

6. Click on the “OK” and the data in “All references” or the selected rows/columns will be merged.

   

It is worth mentioning that you can always use copy and paste to transfer data from one tab to another. However, this can be time-consuming and error-prone. There are more elegant ways to achieve consolidation without repeating information.

Merge tabs in Excel VBA

VBA stands for Visual Basic for Applications, a simple but powerful programming language that you can use to extend Microsoft Office applications, including Excel. The main problem with 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 extreme right. This opens a pop-up macro window.

   

3. Type the name of the macro (for example, “test”) and click the “Create” on the right side and an encoding console will appear with the basic content that says something like:

   ###
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
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
###

   The sample macro code loops through 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 “Execute Sub/UserForm”, then the new tab called “Combined Sheet” will be generated. You can also modify the Framework code to edit the data range and names.

   

Merge sheets in Excel online

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

Note that merging sheets does not manipulate the data. These tools take two or more Excel workbooks and return one Excel workbook with one or more sheets with data copied to them.

Merge tabs in Excel using Power Query

Power Query is another way to combine tabs in Excel. For an Excel workbook with multiple tabs, follow these steps:

1. Go to the “Data” tab and the “Get and transform data” group and click the “Get information” button.

   

2. In the list, click “Blank query” in it “From other sources” and you will see a new Power Query Editor with the default name “Query 1”.

   

3. In the function (fx) bar, write the formula “=Excel.Current Workbook()”(note that the formula is case sensitive) and press the button “Get into” and a list of the tab names of the original workbook will appear.

   

4. Select the tab (column) names to merge (or check the option “Select all columns” if all columns are to be merged).

   

5. Click the double arrow button near the “Content” key.

   

6. Uncheck the “Use the original column name as a prefix” if you want the exact names of the original book to be used in the merged file instead of as prefixes.

   

7. Click “OK” and you will see a new tab with all the data combined, in which the tab names of the original workbook will appear in the right column.

   

Alternative methods

Excel is a useful tool for managing 2D array data. However, if you are a data scientist, you can access the data directly using programming languages ​​such as Python or R. The key is that the data structure of 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 opened directly for reading/editing using Microsoft Excel. Alternatively, you can also open Excel (.xlsx) files using these programming languages, such as the “readxl” package in R, which can open and handle data from different Excel spreadsheets. In these cases, simple but convenient functions such as cbind (bind columns with the same dimension) can be used to merge. All data from different Excel tabs can be combined into data frames for visualizations and statistical analysis, without using Microsoft Excel software.

Get more from less

From the processes summarized above, you will get a single integrated worksheet with data from multiple resources. This worksheet can serve as a backup file or database. Additionally, the combined tab allows you to gain insights that may be missing from a single resource’s data. We recommend that you test the consolidation functions and macros on sample data and make a backup beforehand.

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