How to Group Tabs Under a Master Tab in Excel (2 Ways)

Get FREE Advanced Excel Exercises with Solutions!

While working with lots of tabs, locating a particular tab can be tiresome and time-consuming. Let’s take the example of an employee database, where sales records for each employee are stored in individual tabs or worksheets. To avoid this problem, you can create a master tab and group all the tabs in the worksheet using hyperlinks or VBA. This will allow you to easily navigate to any tab you need, saving you time and effort.

In this Excel tutorial, you will learn how to group tabs under a master tab in Excel.

In the master sheet provided below, the sales records of each employee are conveniently linked to their respective names in column B. By simply clicking on an employee’s name, you will be directed to their individual sales record sheet.

Group tabs under a master tab in Excel


2 Ways to Group Tabs Under a Master Tab in Excel

In Excel, there are two methods to group tabs under a master tab. The first method involves using hyperlinks, while the second involves using VBA macros. Implementing these techniques allows you to create a master tab as an organizational hub, providing a structured view of multiple sheets within a workbook.

Here are 2 ways to group tabs under a master tab in Excel:

Using Hyperlink

In Excel, you can easily group all tabs in a master tab by creating hyperlinks to them. Hyperlinks create shortcuts to navigate within the workbook or open documents on a network server, intranet, or the Internet with a simple click on a cell. By organizing your tabs within the master tab, you can readily access them whenever necessary.

The “Employee Dataset” sheet below lists the employees’ names under column C. The sales records of each employee are stored in separate tabs, which you can hyperlink to the name list.

Showing Data Tabs

To group tabs under a master tab using hyperlinks, follow the steps below:

  1. Type the names of all tabs to group in the master tab.
    As in the image, all the employee names are listed in the range C6:C18.Listing all the tab names
  2. Select a cell containing a tab’s name.
    Cell C5, containing the name “Samantha Carlson,” is selected here.
  3. Press Ctrl+K keys.
  4. In the Insert Hyperlink box:
      • From “Link to” options, choose “Place in This Document”.
      • Choose the tab name from “Or select a place in this document” section.
      • Press OK.
    inserting hyperlink in a cell

    Click on the image to get a detailed view.

You can see that the selected cell contains a hyperlink to the sales record tab of that employee. Also, the hyperlinked text is underlined and blue-colored, which makes it easier to differentiate from other texts.

hyperlink inserted into the selected cell

Finally, by hyperlinking all the names with designated tabs, the list will appear as shown in the image below. This will allow you to easily navigate to the desired tab by clicking on the respective name.

All tab names hyperlinked

Tips:
To make your work more accessible, you can add a hyperlink in each tab by linking it with the master tab. This way, when you finish working on a tab, you can quickly return to the master tab. master tab added

Using VBA Macro

Tabs can be grouped under the master tab by assigning a VBA code. This VBA code is linked to a form control. Therefore, selecting that form control will navigate you to the corresponding tab based on its name.

Follow the steps below to group tabs under the master tab using the VBA macro:

  1. In the master tab, go to the Developer tab > Insert > Button(Form Controls).Selecting Button Form Control
  2. Press OK in the Assign Macro box that appears.Press OKA button will appear.Button Form Control Appeared
  3. Double-click the button (form control) and type a tab’s name.Rename the button
  4. Go to the Developer tab > Visual Basic from Code group.Selecting Visual Basic
  5. Select Module from the Insert tab in the VBA window.Selecting Module from Insert Tab
  6. Paste the following VBA code into the Module.
    Sub GoToSheet1()
        Sheets("Samantha Carson").Activate
    End Sub

    Change the sheet name according to the targeted tab name.

  7. Go back to the master tab.
  8. Right-click on the button.
  9. Choose Assign Macro option from the context menu.Select Assign Macro
  10. In the Assign Macro box, choose the macro and press OK.Select Macro and press OK

The macro will be assigned to the button. Now, if you click on the button, it will take you to the assigned tab.

Clicking Button Taking to Desired Tab

Click on the image to get a detailed view.

Lastly, we inserted buttons for one department’s employees’ name, shown in the image below.

Button Created for One Group

Read More: How to Create Tabs Within Tabs in Excel


How to Group Tabs in Excel

You can work on multiple sheets at once by grouping tabs in Excel. After grouping tabs, if you make any changes to one sheet, it will be reflected in all sheets in that group.

To create standardized tables or perform calculations across multiple sheets at once grouping tab is useful. However, it only works efficiently when the sheets have the same structure.

In the image below, we have 5 tabs with the sales record of 5 company employees. Each tab’s structure is similar to the one shown below.

Tabs with Employee Names

Here are 3 cases to group tabs in Excel:

Case 1: Grouping Adjacent Tabs

To group several adjacent tabs, click on the first tab and click on the last tab while pressing Shift key.

Any changes made in the first tab will be visible in other selected sheets, too.

Adjacent Tabs Grouped

Case 2: Grouping Non-Adjacent Tabs

To group non-adjacent tabs, select the first tab and then select other sheets while holding the Ctrl key.

Non Adjacent Tabs Grouped

Case 3: Grouping All Tabs

If you want to select all tabs in a worksheet, right-click on any tab and click Select All Sheets from the context menu.

Choosing Select All Sheets Option


How to Ungroup Tabs in Excel

To remove the grouping of tabs after making changes, simply right-click on the tab name and choose the option Ungroup Sheets from the context menu.

Ungrouping tabs

If all sheets are grouped together, you can easily ungroup them by simply clicking on any tab name. However, if all the sheets are not grouped, you can ungroup selected tabs by clicking on any group outside of that particular group.


Download Practice Workbook

Download the practice workbook.


Conclusion

In this tutorial, you have learned two ways to group tabs under a master tab in Excel. You can create hyperlinks for tabs or assign VBA to buttons with each tab in the master tab. Using these two methods, you can navigate large datasets much more quickly. Please feel free to leave a comment below if you have any queries.


Frequently Asked Questions

How do I move a group of tabs in Excel?

To move a group of tabs, simply group them by holding down the Ctrl key and clicking, then drag them to the desired location to move.

Can I color-code tabs within the master tab for better visualization?

Yes, color-coding tab within the master tab is a useful practice. Right-click on a tab, choose “Tab Color,” and select a color to visually group related sheets.

How many tabs can you have in Excel?

The number of tabs you can have in Excel varies depending on the version you are using. In most versions of Excel, including Excel 2019 and Excel 365, you can have a maximum of 1,048,576 tabs in a single workbook.


Related Articles


<< Go Back to Sheets Tab in Excel | Excel Parts | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Durjoy Paul
Durjoy Paul

Durjoy Kumar, with a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, is a dedicated contributor to the ExcelDemy project. His substantial contributions include crafting numerous articles and demonstrating expertise in Excel and VBA. Durjoy adeptly automates Excel challenges using VBA macros, offering valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, SPSS, C, C++, C#, JavaScript, Python Web Scraping, Data Entry... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo