How to Create Tabs Within Tabs in Excel (with Simple Steps)

By creating tabs within tabs in Excel, you can structure your lengthy dataset in a way that enables you to display a designated portion of the dataset with just a click.

In this Excel tutorial, you will learn step-by-step procedures to create tabs within tabs in Excel.

The media below showcases detailed statistics of three distinct football clubs, displayed within a compact space by creating tabs within tabs in Excel.

Watch Video – Create Tabs Within Tabs in Excel

Why Do We Create Tabs Within Tabs in Excel?

Tabs within tabs streamline navigation and alleviate the inconvenience. By simply clicking on a tab, we can effortlessly access related information within a specific area. This eliminates the need to search for data in worksheets, making it more efficient to manage extensive amounts of similar data.

Steps to Create Tabs Within Tabs in Excel

Creating tabs within tabs requires several steps starting with the organization of data, inserting several tabs, assigning VBA code to the tabs, and finally ending with the compilation of all tabs. You need to follow several steps at each of the steps, which will be discussed below.

In this tutorial, we will create tabs within tabs to show detailed statistics of 3 football clubs. The dataset contains data about the English Premier League in Column B to Column G, Bundesliga in Col I to N, and Serie A in Col P to Col U. Now we will compile these datasets within a short template by creating tabs within tabs in Excel

To create tabs within tabs in Excel, follow the steps below:

Step 1: Organizing the Data Similarly for Each Tab

To efficiently compile various datasets into a concise template, begin by organizing the datasets.

Arrange all the tables in a sheet with consistent column headers.
Ensure enough space above tables for tab insertion.

The dataset used in this article is presented below. Initially, the English Premier League points table is displayed, with columns B to G assigned to Clubs, Match Played, Win, Draw, Loss, and Points, respectively.

The Bundesliga points table is arranged similarly, using columns I to N.

The Serie A points table is also placed, allocating columns P to U for this assignment.

Step 2: Inserting the Necessary Tabs Within the Sheet

In this segment, you’ll learn to create personalized shapes as tabs. By assigning VBA to these shapes, you can access the relevant data simply by clicking on them.

Go to the Insert tab.
Select Shapes from the Illustrations group.
Choose a shape to set as a tab.
The shape selected here is the “Rectangle: Top Corners Rounded” shape.
Click and drag the cursor to create the shape.

Define the size of the inserted shape from the Size section in the Shape Format tab if you need it.
Create the required number of duplicates of the shape by pressing the CTRL + D buttons.
While duplicating tabs, always create twice as many tabs as tables. Here, 6 rectangles are created as the dataset chosen includes 3 tables.
Separated the shapes into two groups with an equal number of shapes.
In the image below, rectangles are separated into two groups of 3. One set of rectangles will be used for the “On” button associated with each table, while the remaining three rectangles will be designated for the “Off” button.
Select all the shapes in one group and right-click on the mouse.
From the Style option, pick a format according to your choice.
Similarly, format the other set of rectangles too.
Try to choose different shades of colors for differentiating two groups later as one will be for the tab being “On” and the other set for“Off”.
Add text to the shapes and arrange them in a line.
Each set is named after the tables in the sheet.

Step 3: Aligning All the Tabs Within the Sheet

In this part, we will align the same league-named shapes together so that one tab works as the “On” button of one league and the other as the “Off” button.

Add a name to each shape from the Name Box.
Then, select all the rectangles by pressing the CTRL key.
Go to Page Layout > Arrange group > Align option > Align Bottom.
Select the same table named shapes and press Align Left from the Align option.
Here, we have selected the rectangles named English Premier League and applied the feature Align Left option to align them with one another.

Following the same procedure, align the rest of the rectangles.

Step 4: Inserting VBA Code

Now, we will insert three subroutines. These subroutines make assigned columns visible when you click on any tab. To insert the VBA code:

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 TabEpl()
With Sheet1
.Shapes("EplOn").Visible = msoCTrue
.Shapes("EplOff").Visible = msoFalse
.Shapes("BundOn").Visible = msoFalse
.Shapes("BundOff").Visible = msoCTrue
.Shapes("SerieOn").Visible = msoFalse
.Shapes("SerieOff").Visible = msoCTrue
.Range("B:H").EntireColumn.Hidden = False
.Range("I:U").EntireColumn.Hidden = True
End With
End Sub
Sub TabBundesliga()
With Sheet1
.Shapes("EplOn").Visible = msoFalse
.Shapes("EplOff").Visible = msoCTrue
.Shapes("BundOn").Visible = msoCTrue
.Shapes("BundOff").Visible = msoFalse
.Shapes("SerieOn").Visible = msoFalse
.Shapes("SerieOff").Visible = msoCTrue
.Range("I:O").EntireColumn.Hidden = False
.Range("B:H,P:U").EntireColumn.Hidden = True
End With
End Sub
Sub TabSeieA()
With Sheet1
.Shapes("EplOn").Visible = msoFalse
.Shapes("EplOff").Visible = msoCTrue
.Shapes("BundOn").Visible = msoFalse
.Shapes("BundOff").Visible = msoCTrue
.Shapes("SerieOn").Visible = msoCTrue
.Shapes("SerieOff").Visible = msoFalse
.Range("P:V").EntireColumn.Hidden = False
.Range("B:O").EntireColumn.Hidden = True
End With
End Sub

The VBA code given consists of three sub-routines: TabEpl(), TabBundesliga(), and TabSerieA(). These subroutines manage the visibility of shapes using the Shape.Visible property determines the tabs’ color based on whether they are selected or not. The Range.EntireColumn property controls the visibility of columns on “Sheet1”. When a specific tab is selected, any data not related to that tab will be hidden. For instance, in TabEpl(), columns B to H are visible, while columns I to U are hidden.

Step 5: Assigning VBA Code to Each Tab

To assign the Codes with tabs:

Go to Page Layout tab > Arrange group > click Selection Panel.
The Selection Panel will appear on the right side of the worksheet.
Select the aligned tabs while pressing the CTRL key.
Select the same table named tabs together like EplOff, EplOn.
Right-click on the tabs and select Assign Macro.
Select the related macro from the options in the Assign Macro box.
Sheet1.TabEpl macro is selected for the English Premier League tabs.

Thus, the macro will be assigned to the connected tabs.

Step 6: Compiling All Tabs Together

We will compile all tabs together to give the whole set of procedures a final outlook. To compile all tabs:

Go to Shapes from the Insert tab.
Choose a shape from the available options.
Enclose the entire dataset within the selected shape.
Select all the shapes from the Selection Panel by pressing the CTRL key.
Right-click on the selection and choose the Size and Properties.
A Format Shape box will appear on the right side.
From the Properties section, click on the “Don’t move or size with cells” box.

This command will compile the whole work.

Output of Creating Tabs Within Tabs

The final outlook of tabs within tabs is displayed below. You can click on any tab, and the related dataset will appear just like the way it is shown in the following media file.

Download Practice Workbook

Tabs Within Tabs.xlsm

Conclusion

In this tutorial, you learned the steps of creating tabs within tabs in Excel. By following this step-by-step guide, you can effectively categorize and group related data, making it easier to navigate and analyze large amounts of information. For any further queries, comment below. You can visit our site for more articles about using Excel.

Frequently Asked Questions

Are tabs within tabs suitable for creating interactive dashboards?

Yes, they are excellent for creating interactive dashboards. By assigning VBA code to tabs, you can make your Excel workbook dynamic, allowing users to interact with specific data sets seamlessly.

Can I use images or icons instead of shapes for the tabs?

Yes, you can use images or icons as tabs. Insert the images, assign appropriate names, and use VBA code to control their visibility. This can add a visual flair to your nested tabs setup.

What precautions should I take before creating tabs within tabs in a large dataset?

Before implementing nested tabs in a large dataset, backup your workbook. Additionally, consider testing the functionality on a smaller dataset to ensure smooth performance and identify potential issues.

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

Get FREE Advanced Excel Exercises with Solutions!