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

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.

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.

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

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.
Select a cell containing a tab’s name.
Cell C5, containing the name “Samantha Carlson,” is selected here.
Press Ctrl+K keys.
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.
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.

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.

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.

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:

In the master tab, go to the Developer tab > Insert > Button(Form Controls).
Press OK in the Assign Macro box that appears.A button will appear.
Double-click the button (form control) and type a tab’s name.
Go to the Developer tab > Visual Basic from Code group.
Select Module from the Insert tab in the VBA window.
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.
Go back to the master tab.
Right-click on the button.
Choose Assign Macro option from the context menu.
In the Assign Macro box, choose the 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.

Click on the image to get a detailed view.

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

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.

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.

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.

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.

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.

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.

Group Tabs Under a Master Tab.xlsm

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.

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

Get FREE Advanced Excel Exercises with Solutions!