Tech News Today
  • Hardware
    • Motherboards
    • CPUs
    • Graphic Cards
    • RAM
    • SSDs
    • Computer Cases
    • Monitors
    • Peripherals
    • Power Supply Unit
    • PC Builds
    • Computer Tips
  • Software
  • Operating System
    • Windows
    • Mac
    • Linux
  • Gaming
  • Mobile
  • Console
  • More
    • Internet
    • Networking
    • Security
    • Buyer’s Guide
    • Gadgets
    • Laptops
    • Reviews
    • How To
    • News
Facebook Twitter Instagram
Tech News Today
  • Hardware
    • Motherboards
    • CPUs
    • Graphic Cards
    • RAM
    • SSDs
    • Computer Cases
    • Monitors
    • Peripherals
    • Power Supply Unit
    • PC Builds
    • Computer Tips
  • Software
  • Operating System
    • Windows
    • Mac
    • Linux
  • Gaming
  • Mobile
  • Console
  • More
    • Internet
    • Networking
    • Security
    • Buyer’s Guide
    • Gadgets
    • Laptops
    • Reviews
    • How To
    • News
Tech News Today
Home»Software»Excel»How to Merge Tabs in Microsoft Excel

How to Merge Tabs in Microsoft Excel

Asmi DhakalBy Asmi DhakalNovember 16, 2022
how to merge tabs in 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!

Table of Contents

  • How to Merge Tabs in Excel?
    • Copy-Paste Data
    • Reference Sheets
    • Consolidate Tool
    • Use Power Query

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.
    plus icon in excel
  3. Select the first worksheet and select your data, then copy it (Ctrl + C).
    Select Table Excel
  4. Paste your data in the blank worksheet. 
    Paste Table Excel
  5. Repeat steps 3 and 4 for all the sheets you wish to merge.

NOTE: Do not use Select All (Ctrl + A) to select your data. This will also select the empty cells, and Excel will not be able to paste it in the new worksheet as it doesn’t have enough cells to fit it in its grid.

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)Reference Cell Excel
  4. Use Flash Fill to fill in all data from the range.
    Use Flash Fill Excel
  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.
    Consolidate Tool Excel
  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.
      Refer a Cell Consolidate
    • Use labels in: If you have headers in the top row and left column, select the box next to them.
      Consolidate Excel
  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.
    New Query Excel
  4. From the fly-out menu, navigate to From Other Sources > Blank Query.
    Open Blank Query Excel
  5. In the formula bar, type =Excel.CurrentWorkbook() and select Enter.
    Excel Current Workbook Power Query
  6. Right-click on the Content header and select Remove Other Columns.
    Remove Other Columns Excel Power Query
  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.
    Use original column name as prefix excel
  9. Click OK.
  10. Head to the Transform tab the select Detect Data Type.
    Detect Data Type
  11. Select Close and Load.
    CloseandLoadExcelPowerQuery
  12. Convert the table to range (Table Design > Convert to Range).
    convert-to-range-in-excel
how-to
Asmi Dhakal

Asmi Dhakal is a Tech Writer specializing in Microsoft Office-based programs at TechNewsToday. She aims to use her experience to help others learn their way around its programs. Asmi’s interest in Office products grew when she first entered the marketing industry. While she initially struggled to understand their application, she eventually understood her way around them. In her articles, she exhibits a strong understanding of related issues and attempts to push only relevant fixes. Over time, she has grown a particular fondness for MS Excel and spends her free time learning Visual Basic Analysis (VBA). She’s always on the lookout for productivity tools that can get the job done quicker in Excel. Her approach to related problems is very humane, helping new users not only fix the issue but learn more about its nature. Asmi also heavily indulges in personal writing. She is a disciplined journal writer and has maintained a regular journal for 8 years! When her schedule allows, she works to rehabilitate street animals.

Related Posts

check mark in excel

How to Put Check Mark in Excel

January 20, 2023
best excel shortcuts

Best 52 Excel Shortcuts Only Experts Know

January 19, 2023
How to Find and edit Hyperlinks on Excel

How to Find and Edit Hyperlinks on Excel

January 23, 2023
how to find unique values in excel

How to Find Unique Values in Excel

January 15, 2023
how to find links in excel

How to Find and Edit External Links in Excel

January 13, 2023
reference isn't valid excel

Reference Isn’t Valid Excel Error – 6 Ways to Fix it

January 6, 2023
Add A Comment

Leave A Reply Cancel Reply

Latest Posts
how long does a cmos battery last

How Long Does a CMOS Battery Last

January 25, 2023
thunderbolt vs usb c

Thunderbolt Vs USB C: What’s the Difference

January 25, 2023
how to find hidden folder in laptop

How to Find Hidden Folder in Laptop

January 24, 2023
You may also like
4k-tv-as-monitor

How to Use a 4K TV as a Monitor? (Step-by-Step Guide)

January 26, 2023
how-to-install-cinema-on-firestick

How to Install Cinema on Firestick (Step-By-Step Guide)

January 26, 2023
how to adblock on chrome

How to Block Ads on Google Chrome

January 26, 2023
Recommended
Cookie Clicker Garden Guide

Cookie Clicker Garden Guide to Unlocking Every Seed

September 26, 2021
monitor no signal

Computer Turns On But Monitor Says No Signal (9 Ways To Fix)

November 10, 2022
Facebook Twitter Pinterest
  • Home
  • About Us
  • Our Team
  • Editorial Guidelines
  • Privacy Policy
  • Affiliate Disclosure
© 2023 TechNewsToday.

Type above and press Enter to search. Press Esc to cancel.