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

Q: 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.

Q: 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.

In this tutorial, we will group tabs under a master tab in Excel using hyperlinks and VBA.

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.

Method 1 – Using Hyperlinks

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.

The “Employee Dataset” sheet below lists the employees’ names under column C. The sales records of each employee are stored in separate tabs. We will hyperlink to these tabs from the list of names.

STEPS:

Enter the names of all tabs to group in the master tab.
As in the image below, 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 the Ctrl+K keys.
In the Insert Hyperlink box that opens:
- - 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 for a detailed view.

The selected cell now 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.

After 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.

Tip:

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.

Method 2 – Using a VBA Macro

This VBA code is linked to a form control. Selecting that form control will navigate you to the corresponding tab based on its name.

STEPS:

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

After inserting buttons for one department’s employees’ names, our sheet looks like this:

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.

Grouping tabs is useful to create standardized tables or perform calculations across multiple sheets at once. However, it only works efficiently when the sheets all 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 for grouping tabs in Excel:

Case 1 – Grouping Adjacent Tabs

To group several adjacent tabs, click on the first tab and the last tab while pressing the 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

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 not all the sheets are grouped, you can ungroup selected tabs by clicking on any group outside of that particular group.

Download Practice Workbook

Group Tabs Under a Master Tab.xlsm

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 their tab names, then drag them to the desired location.

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

Yes. 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?

It 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!