Create a Sleek and Interactive Business Dashboard in Excel [PART 3]

Dashboard PART 3

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.

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.

new look

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.
data model slicer
  • Click on Tables in Workbook Data Model because we have added the database in the current workbook’s data model.
  • Click Open.
slicer options
  • Check the dimensions as per the charts in the dashboard.
  • We will select Date (Month), Item, Salesperson, and Region.
  • Click OK to create slicers.
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.
slicer stacked
  • Click on a slicer and go to the Slicer tab.
  • Adjust the number of columns for each slicer in the Columns field under Buttons.
slicer columns

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.
slicer final

We have completed creating and designing the slicer.

Conclusion

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!