In this article, we have created a custom Toggle Button in Excel and Power BI to toggle the Chart and Table on click of this toggle button. This trick could be useful in a dashboard wherein you have less space.
Visit our YouTube channel to learn step-by-step video tutorials
Watch the step by step video tutorial for Custom Toggle Button in Excel and Power BI:
Custom Toggle Button in Excel and Power BI
We have used 2 images to create the toggle button in Excel. We have created the 2 macros to show the chart and Table and assigned the macros to these buttons.
Below are the images of the Toggle buttons. We will arrange them on top of each other.
We have created below given macros and assigned to the respective buttons-
Sub Show_Table()
Dim sh As Worksheet
Set sh = ActiveSheet
sh.Shapes("Btn_Table").Visible = msoFalse
sh.Shapes("Btn_Chart").Visible = msoCTrue
sh.Shapes("Chart").Visible = msoFalse
sh.Range("A:N").EntireColumn.Hidden = False
End Sub
Sub Show_Chart()
Dim sh As Worksheet
Set sh = ActiveSheet
sh.Shapes("Btn_Table").Visible = msoCTrue
sh.Shapes("Btn_Chart").Visible = msoFalse
sh.Shapes("Chart").Visible = msoCTrue
sh.Range("A:N").EntireColumn.Hidden = True
End Sub
If we will click on the Chart button then it will display chart view-
If will click on the Table button then it will display the Table view-
Custom Toggle Button in Microsoft Power BI
In the Power BI, we can create the Bookmarks to create the toggle buttons. First, import the toggle button images form Insert >> Image and create the chart and tables as per your requirements.
Open the selection pane from View >> Selection. To create the Table view bookmarks, hide the table button, and chart form selection pane.
Open the Bookmarks pane from View >> Bookmarks. Add a bookmark and rename with Table_Name.
Now hide the chart button and matrix and show the table button and chart from the selection pane. Add a bookmark and rename it with Chart_View.
Select the table button in the selection pane and add an action as a bookmark. Select the Table_Veiw bookmark.
Now select the chart button in the selection pane and add an action as a bookmark. Select the Chart_Veiw bookmark.
Now you can keep both buttons on top of each other. Now, these buttons will work like a toggle button to show the Chart and Table.
