Ultimate Excel Dashboard | Interactive Dashboard Tabs

Share on pinterest
Pinterest
Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn
Share on reddit
Reddit
Share on whatsapp
WhatsApp
Share on email
Email
Share on pinterest
Share on facebook
Share on twitter
Share on linkedin
Share on reddit
Share on whatsapp
Share on email

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

  1. create a basic interactive Excel dashboard with pivot charts and slicers
  2. design a beautiful dashboard background and gradient tile design 
  3. create a modern dashboard slicer design
  4. auto-refresh dashboard pivot charts when source data changes
  5. create interactive dashboard info buttons
  6. create interactive dashboard tabs [you are here]
  7. 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.

Dashboard Tabs Demo

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
Once we have these shapes created and named, we can start writing the VBA script.

‘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
This reduction of shapes will save us some line of codes and improves the performance of the tab functionality in the Excel worksheet in general as less objects are involved. The question is, how do we reproduce the change of appearance of the tab button without having two differently designed shapes? We will use a simple trick in our VBA script!
 
As a starting point we design the button like the active state button shape before (with a white fill). For the inactive state (we want to have no fill and a white text color here), we then simply change the transparency to 100% and change the text color to white. And for the active state we simply set the transparency back to 0% and change the text color back to black. Let’s have a look at the adjusted VBA code for Option 2.

‘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.

You may also like the following Excel Dashboard tutorials:

Share on pinterest
Pinterest
Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn
Share on reddit
Reddit
Share on whatsapp
WhatsApp
Share on email
Email
Share on pinterest
Share on facebook
Share on twitter
Share on linkedin
Share on reddit
Share on whatsapp
Share on email