Welcome to Part 3 of creating a sleek and interactive business dashboard in Microsoft Excel!
Before we begin, we recommend you to read Part 1 and then Part 2 of this tutorial, if you’re learning for the first time.
- Read here: Creating a Sleek and Interactive Business Dashboard [PART 1]
- Read here: Creating a Sleek and Interactive Business Dashboard [PART 2]
This tutorial is Part 3 of creating a sleek and interactive business dashboard in which we will elucidate on making a dashboard interactive using filters and slicers in the charts.
Take a look at the new look of our dashboard. We made a few changes to it.
Let’s begin now!
Making the dashboard interactive for users
In this section, we will learn to apply filters to our dashboard and create and insert slicers to make the dashboard charts interactive.
Let’s begin with creating slicers for all the charts in the dashboard.
- Go to the Insert tab.
- Click on Slicer under Filters.
- Go to the Data Model tab.
- Click on Tables in Workbook Data Model because we have added the database in the current workbook’s data model.
- Click Open.
- Check the dimensions as per the charts in the dashboard.
- We will select Date (Month), Item, Salesperson, and Region.
- Click OK to create slicers.
You will see all slicers are created at once in the dashboard.
Now, we must create a reference of every slicer with every chart present in the dashboard. Here’s how you can do it.
- Stack all slicers in one place first.
- Right-click on a slicer and press Report Connections.
- Check all the pivot tables in the list.
- Click OK.
- Repeat this for all slicers in the dashboard to create a reference.
Now that all slicers have a reference with every chart in the dashboard, try applying filters to the slicers and watch how the dashboard interacts.
Creating a filter tile in the dashboard
Now that the slicers are ready, we will create a filter tile for the users to apply those slicers in the tile and let them interact with the dashboard. Follow the steps below.
- Select an existing tile and copy and paste it into the dashboard.
- Copy an existing text box and paste it on the new tile.
- Widen the tile as we want to fit the slicers in this tile.
- Click on a slicer and go to the Slicer tab.
- Adjust the number of columns for each slicer in the Columns field under Buttons.
We have adjusted the slicer columns for each slicer manually as in the screenshot above.
- Select all slicers by pressing and holding CTRL.
- Go to the Slicer tab.
- Choose a color combination from the Slicer styles group based on your dashboard’s theme.
To create add a custom color to your slicers.
- Open the Slicer Styles box.
- Click on New Slicer Style.
- Choose Selected Item with Data.
- Click Format.
- Select a custom background color in the Fill tab.
- Select a custom background color in the Font tab.
- Click OK.
- Choose Header.
- Choose a fill color and font color for the header.
- Click OK and select a custom color for the whole slicer and other elements like hovered slicer and unselected and selected slicer colors in the same way.
We have completed creating and designing the slicer.
This article is a comprehensive how-to guide to create beautiful and responsive dashboards for your organization.
In Part 1, we learned to create metric charts from a database represented by the map, bar, doughnut charts, and more using pivot tables, pivot charts, maps charts, etc.
In Part 2, the most exciting part of this guide, we learned to create a stunning, sleek, and systematic design for the dashboard using the data charts we prepared in Part 1.
The final Part 3 was about adding filters and slicers to make the business dashboard interactive with the users!