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.
Create Tabs Within Tabs in Excel: Step by Step Procedure
Creating tabs within tabs is a process of some sequential processes. Here, I have divided the whole procedure into 6 sections. They are:
- Organizing Required Datasets
- Creating Multiple Tabs
- Aligning the Tabs
- Creating a VBA Code
- Assigning VBA Code with Tabs
- 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.
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.
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.
- 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.
- 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.
- Now, add text to those shapes and arrange them in a line.
Read More: How to Create Tabs Automatically in Excel
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.
- 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.
Thus, the similar rectangles align with one another.
- Following the same procedure, align the rest rectangles.
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
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.
- 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.
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.
- 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.
- Followingly, right-click on the mouse 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
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.
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.
Download Practice Workbook
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.
