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

We love to have our data sorted and decorated. For this purpose, we can use tabs within tabs. This will sort the data within a small space and also enhance the appearance. This article might be very helpful for you if you are trying to create tabs within tabs. In this article, I am going to explain the whole procedure of how to create tabs within tabs in Excel step by step. I hope it will be very helpful for you.


Download Practice Workbook


Step by Step Procedures to Create Tabs Within Tabs in Excel

Creating tabs within tabs is a process of some sequential processes. Here, I have divided the whole procedure into 6 sections. They are:

  1. Organizing Required Datasets
  2. Creating Multiple Tabs
  3. Aligning the Tabs
  4. Creating a VBA Code
  5. Assigning VBA Code with Tabs
  6. Compiling All Tabs

1. Organizing Required Datasets

In order to compile multiple datasets within a short template, we organize the datasets first.

Here, I have arranged the English Premier League points table within column B to column G.

How to Create Tabs Within Tabs in E

Next, I have arranged the Bundesliga points table within column I to column N.

Similarly, decorated the Serie A points table within column P to column U.

Organizing Required Datasets


2. Creating Multiple Tabs

In order to lessen the hassle, I have created multiple tabs within tab. Just by clicking on the tab, we can have the related information in the template.

Steps:

  • Go to the Insert tab first.
  • Select Shapes from the ribbon.
  • Pick a shape to set as tab. I have selected here Rectangle: Top Corners Rounded shape.

Creating Multiple Tabs

  • Then, insert the selected shape and define the size according to your choice.

  • Use CTRL + D buttons to create duplicates. I have created a total of 6 rectangles and separated them into two groups.

Creating Multiple Tabs

  • Select all the shapes in one group and right-click on the mouse.
  • From the Style option, pick a format according to your choice.

Creating Multiple Tabs

  • Similarly, format the other set of rectangles too.

  • Now, add text to those shapes and arrange them in a line.

Creating Multiple Tabs

Read More: How to Create Tabs Automatically in Excel (3 Easy Ways)


3. Aligning the Tabs

In order to define the behavior of the tabs whether it is working or not, we need to align the similar tabs.

Steps:

  • Add a name to each rectangle from the Name Box.

Aligning the Tabs

  • Then, select all the rectangles by pressing the CTRL key.
  • Go to Page Layout.
  • Click on the Align option from the ribbon.
  • Pick the Align Bottom option to align the rectangles in a line.

  • Now, align the similar box. Here, I have selected the rectangles named English Premier League and applied the feature Align Left from the Align option.

Aligning the Tabs

Thus, the similar rectangles align with one another.

  • Following the same procedure, align the rest rectangles.

Aligning the Tabs


Similar Readings


4. Creating a VBA Code

In order to determine the function of the tabs, we need to set some conditions. For this,

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

Here, I have used TabEpl(), TabBundesliga(), and TabSerieA() as Sub Procedures. I have also used the Shape.Visible property to define the color of the tabs according to their status of being selected or not selected, and the Range.EntireColumn property to define the hidden columns (when a particular tab is selected anything other than the data related to that tab will be hidden).


 5. Assigning VBA Code with Tabs

In the above section, I have written down a VBA code. Now, I am going to assign that code to the tabs in this section.

Steps:

  • Select the Page Layout tab.
  • Pick Selection Panel from the ribbon to have the Selection Panel on the right side of the worksheet.

Assigning VBA Code with Tabs

  • Now, select the aligned cells (i.e. EplOff, EplOn) by keeping pressing the CTRL
  • Right-click on the mouse and click on Assign Macro… 

An Assign Macro wizard will appear.

  • Now, pick the related macro from the options.

Assigning VBA Code with Tabs

Thus, the Macro will be assigned with the connected tabs.


6. Compiling All Tabs

In this section, we are going to compile all the tabs. This will give our whole above procedures a final outlook.

Steps:

  • Go to Shapes from the Insert tab.
  • Pick a shape.

Compiling All Tabs

  • Encompass the whole dataset with that shape.

  • Now, select all the shapes from the Selection Panel. Remember, you need to hold it by pressing the CTRL button during the selection.

Compiling All Tabs

  • Followingly, right-click on the mouse and choose the Size and Properties

Compiling All Tabs

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.

Compiling All Tabs

This command will compile the whole work.


Output of Creating Tabs Within Tabs

I have shown the output here. You can click on any tab and the related dataset will appear just like the way it is shown in the following media file.

Output of Creating Tabs Within Tabs


Benefits of Creating Tabs Within Tabs

Creating tabs within tabs lessens the hassle of navigation. In this way, we can have the related information within a certain area just by clicking on the tab. We do not have to take the burden of finding those data in worksheets. It becomes handier in terms of dealing with a vast amount of similar data.


Conclusion

That’s all for today. I have tried my best to describe the whole procedure of how to create tabs within tabs in Excel. It will be a matter of great pleasure for me if this article could help any Excel user even a little. For any further queries, comment below. You can visit our site for more articles about using Excel.


Related Articles

Naimul Hasan Arif

Naimul Hasan Arif

Hello, I am Arif. I am an Engineer who graduated from BUET. I want to pursue an enterprising career in a progressive environment where my skills & knowledge can be enhanced to their maximum potential to contribute to the overall success and growth of the organization.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo