Bottom Line: In this first episode of the Ultimate Excel Dashboard Tutorial Series you will learn how to create a modern basic interactive Excel Dashboard from scratch. Besides Pivot Tables, Pivot Charts and Slicers, this is an amazing lesson on how to use progressive charts types and create state-of-the-art dashboard designs.
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 [you are here]
- 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
- 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.
Before we start building the Ultimate Excel Dashboard
In this tutorial we are using Pivot Tables, Pivot Charts and Slicers. If you are already familiar with Excel Pivot Tables, head right on to the first step. If you think a quick and compact introduction to Excel Pivot Tables might be helpful, check out our tutorial on how to use Excel Pivot Tables the right way.
Now, that we are set and done, let’s get right into it!
Step 1 - Source Data Preparation
For our Ultimate Excel Dashboard we will use a huge amount of transactional sample data. We generated a dataset about a hypothetical online store’s customer success, measuring everything along the whole sales, delivery and customer feedback process. We auto-generated that dataset using an advanced VBA script (the last tutorial in this tutorial series). For know, we recommend to simply download that dataset from our Download Area.
Once you have the data, the first thing you should always do is to put that data into a simple table. There are two very good reasons why to do that. At first, you can give that table a specific name and every time you want to make use of that data (e.g. referencing it as the source data of a Pivot Table), all you have to do is write that defined name into the reference. And second, it then allows you to easily add new data below the data table which will be automatically included into the data table. So if you use the name in your data references, you will never have to update any data reference again if new data is added.
Step 2 - Create Pivot Tables and Pivot Charts
Now that you have your data set prepared, let’s start creating and preparing the Pivot Tables and Pivot Charts that we need to fill our Excel dashboard with life. For this dashboard we create six charts by using five advanced chart types:
- Smoothed Line Chart
- Map Chart (only available in Microsoft 365 or Office 2019)
- Binary Doughnut Chart
- Waterfall Chart (only available in Microsoft 365, Office 2019 and Office 2016)
- Normalised Horizontal Bar Chart
Step 3 - Raw Dashboard Design
A good dashboard design has the ability to make the audience easily focus on what is really important. That already starts with visually setting apart the background noise and the actual dashboard area. Our approach to this is to use a darkened image as a background. It should be scaled to a size that covers the whole visible area. And then, as a contrast, we use a modern tile design that differentiates in its colors, either by being even darker or brighter.
For this Ultimate Excel Dashboard we decided to use dark background with a brighter, semi-transparent gradient-style tile design. In the next episode of this tutorial series we will cover in detail how to prepare beautiful background and tile-designs within minutes.
For now, we take that foundation as given and insert a big dashboard title and one additional title for every single tile. Each single tile title is composed of three essential components:
- a rounded rectangle as semi-transparent white background box
- the title itself in white
- a beautiful flat white icon (from the huge icon library exclusively included in Microsoft 365)
Step 4 - Insert and Redesign Pivot Charts
Before you start doing this, just keep in mind an important concept for dashboard designs: For every chart, you want to send the key message within seconds. So don’t overload the dashboard tiles and charts and design them in a way that is visually appealing, minimalistic but still able to send the message!
If you need to include additional information to explain a chart I have the perfect solution for in episode 5 of this tutorial series, where I introduce a great way to implement custom info buttons to display and hide beautiful info boxes.
To include the prepared Pivot Charts in our dashboard, we simply cut them out of the respective data sheet and insert them inside the Dashboard Data sheet. The redesign process than includes to make them fit inside our prepared tiles, to adjust the proportions in a way that maximised the actual chart content area, and adjust the coloring. For all charts, we recommend to use not more than two or three basic harmonic colors and maybe some slight variations of these. More colors easily lead to a visual overload and make it much harder and less pleasant to look the charts. As base content colors for this dashboard we chose white, red, and green (with white as the dominant one).
To get a good idea on how a beautiful redesign of these charts could look like, have a look at the video above.
Step 5 - Add Slicers for Interactivity
Slicers are the magic component that will make our charts interactive and cross-dimensionally filterable. We insert a slicer for every dimension that we want to filter in and connect all slicers to every chart.
To top the overall dashboard design off, we recommend to create a custom slicer design as the default slicer designs just look old-fashioned and would destroy the whole dashboard appearance. In episode 3 of this tutorial series, we will go into detail on how to create modern slicer designs with beautiful hover effect and button states that adhere to modern UI standards.
Conclusion
That’s already it for this first episode of the Ultimate Excel Dashboard tutorial series. We recommend to go through the whole series step-by-step if want to take deep look into specific design topics and of course if you want to learn how to make this dashboard even more awesome than it already it.
The Dashboard Excel File we created in this tutorial is available for download in our Download Area.
