Bottom Line: In this episode of the Ultimate Excel Dashboard Tutorial Series you will learn how to create custom interactive dashboard tabs for your Excel dashboard.
Skill Level: Advanced
Video Tutorial
Disclosure: Some of the links below are affiliate links, meaning, at no additional cost to you, I will earn a commission if you click through and make a purchase.
The ultimate Excel Dashboard Tutorial Series
In our Ultimate Excel Dashboard tutorial series you will learn how to create a state-of-the-art interactive Excel Dashboard with many outstanding and custom-built features. This tutorial series will cover how to
- create a basic interactive Excel dashboard with pivot charts and slicers
- design a beautiful dashboard background and gradient tile design
- create a modern dashboard slicer design
- auto-refresh dashboard pivot charts when source data changes
- create interactive dashboard info buttons
- create interactive dashboard tabs [you are here]
- create a custom interactive settings menu with modern radio and toggle buttons
By completing this tutorial series you will learn how to visualise your data and bring it to life in a completely stunning way. The great variety of different topics covered during the process will develop and strongly improve your overall Excel skillset.
Info: In this dashboard we use features that are only available in the latest version of Excel (included in Microsoft 365). If you want to are still using an older version of Excel, we recommend to upgrade to Microsoft 365 to have full access to all features.
Different Ways to Create Interactive Dashboard Tabs
Tabs are a feature very well known from everyday applications and websites. They allow to display a big amount of content with very small space required, since all tab contents appear within the same area based on what tab is currently selected.
In this tutorial we will show you two ways to implement interactive tabs in Excel using our Ultimate Excel Dashboard as a foundation. The dimension of the tabs theoretically is not limited, you could use tab functionality for the dashboard a whole. However, in this tutorial we will focus on a single dashboard tile and provide different tab views within this tile. Theoretically, this tab functionality can be successfully applied in basically every Excel project and provide very much value in most cases.
The two ways for doing this differ from each other regarding the numbers of shapes used for one tab button. The first option is based on two button shapes (one for the active button state and one for the inactive button state) and the second option is based on only one button shape per tab button. The core functionality will be implemented in a VBA script for both options and we will show you the whole code that is required.
Option 1: Two Button Shapes per Tab Button
The first option that we will show you is the more intuitive option, as it is simply based on the visibility property of all shapes involved. That means, if we click on one tab button the assigned macro will make all elements appear that are relevant for this particular tab and everything else disappear.
For the following example code you need to know that we want to have two tabs, one displaying two Pivot Charts showing Sales Revenue and the other one displaying two Pivot Charts showing Sales Units (Quantity). For better a handling you should always give a name to the objects involved via the Selection Pane. In our case we give the tab content objects the following names:
- Tab Content 1 (Revenue): Line_Chart_Sales_Revenue & Map_Chart_Sales_Revenue
- Tab Content 2 (Units): Line_Chart_Sales_Units & Map_Chart_Sales_Units
For the tab buttons we create two button shapes per tab button, one for the active and one for the inactive button state. Our preferred shapes for this button is the Rounded Rectangle shape, but you are free to use any shape you would like for these buttons. For the design we recommend to make the active button shape filled with some color (e.g. white) and the inactive button shape transparent. In our case we then give the button shapes the following names:
- Tab Button 1 (Revenue): Tab_Button_Sales_Revenue_Active & Tab_Button_Sales_Revenue_Inactive
- Tab Button 2 (Units): Tab_Button_Sales_Units_Active & Tab_Button_Sales_Units_Inactive
‘Option 1: 2 Button Shapes per Tab | 2 Macros
Sub Display_Tab_Sales_Revenue()
With ActiveSheet
‘Visibility of Tab Buttons
.Shapes(“Tab_Button_Sales_Revenue_Inactive”).Visible = False
.Shapes(“Tab_Button_Sales_Revenue_Active”).Visible = True
.Shapes(“Tab_Button_Sales_Units_Inactivective”).Visible = False
.Shapes(“Tab_Button_Sales_Units_Active”).Visible = True
‘Visibility of Tab Contents
.Shapes(“Map_Chart_Sales_Revenue”).Visible = True
.Shapes(“Line_Chart_Sales_Revenue”).Visible = True
.Shapes(“Map_Chart_Sales_Units”).Visible = False
.Shapes(“Line_Chart_Sales_Units”).Visible = False
End With
End Sub
▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
Sub Display_Tab_Sales_Units()
With ActiveSheet
‘Visibility of Tab Buttons
.Shapes(“Tab_Button_Sales_Revenue_Inactive”).Visible = True
.Shapes(“Tab_Button_Sales_Revenue_Active”).Visible = False
.Shapes(“Tab_Button_Sales_Units_Inactivective”).Visible =False
.Shapes(“Tab_Button_Sales_Units_Active”).Visible = True
‘Visibility of Tab Contents
.Shapes(“Map_Chart_Sales_Revenue”).Visible = False
.Shapes(“Line_Chart_Sales_Revenue”).Visible = False
.Shapes(“Map_Chart_Sales_Units”).Visible = True
.Shapes(“Line_Chart_Sales_Units”).Visible = True
End With
End Sub
This script is really intuitive, will do the work required and is a solid solution. However, if you are interested in a solution that only requires one button shape per tab button, have a look at Option 2.
Option 2: One Button Shape per Tab Button
This option is bit more advanced and not as intuitive compared to Option 1. However, the big advantage is that it only requires one button shape per tab button. That means we can simply delete one of the current button shapes per tab button and rename the remaining button shape.
- Tab Button 1 (Revenue): Tab_Button_Sales_Revenue
- Tab Button 2 (Units): Tab_Button_Sales_Units
‘Option 2: 1 Button Shape per Tab | 2 Macros
Sub Display_Tab_Sales_Revenue()
With ActiveSheet
‘Visibility of Tab Buttons
With .Shapes(“Tab_Button_Sales_Revenue”)
.TextFrame.Characters.Font.Color = RGB(0,0,0)
.Fill.Transparency = 0#
End With
With .Shapes(“Tab_Button_Sales_Units”)
.TextFrame.Characters.Font.Color = RGB(255,255,255)
.Fill.Transparency = 1#
End With
‘Visibility of Tab Contents
.Shapes(“Map_Chart_Sales_Revenue”).Visible = True
.Shapes(“Line_Chart_Sales_Revenue”).Visible = True
.Shapes(“Map_Chart_Sales_Units”).Visible = False
.Shapes(“Line_Chart_Sales_Units”).Visible = False
End With
End Sub
▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
Sub Display_Tab_Sales_Units()
With ActiveSheet
‘Visibility of Tab Buttons
With .Shapes(“Tab_Button_Sales_Revenue”)
.TextFrame.Characters.Font.Color = RGB(255,255,255)
.Fill.Transparency = 1#
End With
With .Shapes(“Tab_Button_Sales_Units”)
.TextFrame.Characters.Font.Color = RGB(0,0,0)
.Fill.Transparency = 0#
End With
‘Visibility of Tab Contents
.Shapes(“Map_Chart_Sales_Revenue”).Visible = False
.Shapes(“Line_Chart_Sales_Revenue”).Visible = False
.Shapes(“Map_Chart_Sales_Units”).Visible = True
.Shapes(“Line_Chart_Sales_Units”).Visible = True
End With
End Sub
This option now tends to have a better performance which you will even more notice the more tabs you include.
Generally, we recommend to not overuse the tab feature and set a upper cap of 3 or 4 tabs as a maximum for the best possible user experience.
Conclusion
That’s already it for this sixth episode of the Ultimate Excel Dashboard tutorial series. We recommend to continue with the next episode of this Ultimate Excel Dashboard tutorial series in order to make this dashboard even more awesome than it already is.
You can download the Basic Dashboard Excel File in our Download Area.
