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.
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:
Step 2 - Create Pivot Tables and Pivot Charts 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 For every Pivot Chart, we start by creating the Pivot Table. As a data reference we simply use the name that we defined for our source data table in Step 1. Once the Pivot Table is set up we can drag and drop the required data fields into the rows, columns, and values area of our Pivot Table section. We recommend to have a look at the video at the top to get a better idea and some inspiration on what values make sense for each chart time.
The Pivot Charts can then be easily created by selecting one cell inside the respective Pivot Table , going to the Insert Tab and click on the Pivot Chart Button. The default chart type is always a simple vertical bar chart, but the chart can be easily changed via the Chart Design Tab > Change Chart Type.
The only exception are the two most advanced of those five chart type: The Map Chart and the Waterfall Chart. These both currently require to copy the Pivot Table Data outside of the Pivot Table and then insert a regular chart that references the copied data. A dynamic copy of a Pivot Table outside of the Pivot Table can be easily generated by copying the row and column headers and then use the GETPIVOTDATA function to reference the specific values. With this function, all filtering within the Pivot Table will automatically be applied to that cell.
Furthermore, we recommend to remove all unnecessary chart elements already in this chart preparation phase, like e.g. the title which is not needed as we will insert well-designed titles for the tiles and charts in the dashboard later on.
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) That is already everything we need before we insert and redesign the prepared Pivot Charts.
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