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


Watch Video – Create Tabs Within Tabs in Excel

Steps to Create Tabs Within Tabs in Excel

In this tutorial, we will create tabs within tabs to show detailed statistics of three football clubs. The dataset contains data about the English Premier League in Column B to Column G, Bundesliga in Column I to Column N, and Serie A  in Column P to Column U.

How to Create Tabs Within Tabs in E

Organizing Required Datasets

Step 1: Organize the Data Similarly for Each Tab

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

Step 2: Insert the Necessary Tabs Within the Sheet

  1. Go to the Insert tab.
  2. Select Shapes from the Illustrations section.
  3. Choose a shape to set as a tab.
    The shape selected here is the “Rectangle: Top Corners Rounded” shape.Creating Multiple Tabs
  4. 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.
  5. 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. In our example, six rectangles are created as the chosen dataset includes three tables.
  6. Separate the shapes into two groups with an equal number of shapes.
    On the image below, rectangles are separated into two groups of three.  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.
    Creating Multiple Tabs
  7. Select all the shapes in one group and right-click on them.
  8. Pick a format according to your preferences from the Style tab.Creating Multiple Tabs
  9. Format the other set of rectangles in the same manner.
    Choose different shades of colors to differentiate the two groups, as one will signify “On” and the other “Off”button.
  10. Add text to the shapes and arrange them in a line.
    Each set is named after the tables in the sheet.Creating Multiple Tabs

Step 3: Align All the Tabs Within the Sheet

  1. Add a name to each shape from the Name Box.Aligning the Tabs
  2. Select all the rectangles by pressing the CTRL key.
  3. Go to Page Layout > Arrange group > Align option > Align Bottom.
  4. Select the shapes of the same name and choose Align Left in the Align tab.
    As an example, we have selected the rectangles named English Premier League and aligned them with the Align Left option.Aligning the Tabs

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

Aligning the Tabs

Step 4: Insert the VBA Code

  1. Go to the Developer tab and click on the Visual Basic button in the Code section. Selecting Visual Basic
  2. Select Module from the Insert tab in the VBA window.Selecting Module from Insert Tab
  3. Copy and 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

Creating a VBA Code

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, which 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: Assign the VBA Code to Each Tab

  1. Go to the Page Layout tab and click on Selection Panel within the Arrange section.Assigning VBA Code with Tabs
    The Selection Panel will appear on the right side of the worksheet.
  2. Select the aligned tabs while pressing the CTRL key.
    Select the tabs of the same name, such as EplOff, EplOn.
  3. Right-click on the tabs and select the Assign Macro option.
  4. Select the related macro from the options in the Assign Macro box.
    Sheet1.TabEpl macro is selected for the English Premier League tabs.Assigning VBA Code with Tabs

Step 6: Compile All Tabs Together

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

The Output of Creating Tabs Within Tabs

The final outlook of tabs within tabs is displayed below.

Output of Creating Tabs Within Tabs

Download Practice Workbook

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, back up your workbook. Additionally, consider testing the functionality on a smaller dataset to ensure smooth performance and identify potential issues.

Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Naimul Hasan Arif
Naimul Hasan Arif

Naimul Hasan Arif, a BUET graduate in Naval Architecture and Marine Engineering, has been contributing to the ExcelDemy project for nearly two years. Currently serving as an Excel and VBA Content Developer, Arif has written more than 120 articles and has also provided user support through comments His expertise lies in Microsoft Office Suite, VBA and he thrives on learning new aspects of data analysis. Arif's dedication to the ExcelDemy project is reflected in his consistent contributions and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF