Bottom Line: Learn how to create a fantastic looking and interactive Excel dashboard that can be applied to any sort of transactional data. Learn how to start from scratch by building up all required Pivot Tables and Pivot Chars and some awesome design.
Skill Level: Advanced
Background Image: Found on Pexels
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 Power of an Interactive Excel Dashboard
Knowing how to visualise your data in Excel in a beautiful way is already a huge skill. But building an Interactive Excel Dashboard that allows anybody to take a look at the visualised data from any angle they want is considered the supreme discipline. We want to enable everyone to create the most appealing and functional, interactive Excel Dashboards with minimum expenditure of time. You will learn how to use Excel Pivot Tables, Pivot Charts and Slicers to create a beautiful and powerful result.
Info: For this Dashboard we use native features that are only available in the latest version of Excel (included in Microsoft 365). So if you are still using an older version of Excel we recommend to upgrade to the latest version available.
Before we Start Building the Interactive 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!
Table of Contents
Building Interactive Excel Dashboard in 5 Steps
Get Your Data Right
Now, what we need to get started is some sort of transactional data. Transactional data is any kind of data that has one row for every event or transaction. An event can be everything, e.g. a sale, a hiring of new employee or a click on your website. If you don’t have any data available, feel free to download our sample data.
Create Pivot Tables and Pivot Charts
Once you have your data set, you can start to create a Pivot Table and the according Pivot Chart for every Chart you want to have in your Dashboard. We recommend to use on sheet (each sheet has his own tab in the bottom area of your Excel worksheet) for each Pivot Table.
For the Pivot Table, simply select ‘Insert Pivot Table’, select the whole data range you specified in Step 1 and select all dimensions you want to see in that Pivot Table. On a site node, to get more familiar with Pivot Tables check out our detailed Introduction on Pivot Tables.
To insert a Pivot Chart, simply click into one cell of the Pivot Table, select ‘Insert Pivot Chart’ in the top tool bar and select the Pivot Chart Type you prefer. That should be relatively simple for most chart types. However, for the Map Chart you have to apply a workaround since Map Charts cannot be generated directly from Pivot Tables: You have to build a dynamic copy of the Pivot Table outside of the pivot table by referencing to all Pivot Table value cells. After that, you can insert a Map Chart that references to the dynamic copy table.
Basic Dashboard Design
For our Dashboard Design we want to introduce the concept of tiles: Tiles are rectangular shapes and something pretty popular in modern web and frontend design. But simply inserting rectangles won’t serve us well. On the converse, an outstanding visual design requires the perfect interplay of background, tile symmetry and tile transparency. To get a better idea you should definitely take a look at the video above.
To create the best possible contrast between background and dashboard we insert a background image. Since I got many requests about the image I used in the video, here is the image link.
We insert five tiles for our chart contents and one tile for the interactive area below that. After placing the tiles, colouring them and setting their transparency for the optimal look, we add titles and icons to every single tile.
Fusion of Dashboard and Prepared Charts
Now that we have prepared the Dashboard Foundation, let’s copy the prepared charts over to the Dashboard Sheet, make them fit into the respective tiles and style them up. The most important slogan for our design is: Simplicity is king. We want to display as little elements as possible but also enough to not lose the message of the charts. Furthermore, we carefully select a colour scheme that is visually appealing. In our case, we decide to only use the base colours white, blue and green.
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. After that, we adjust the design of the slicer to make it visually fit into the interactive area below our content area.
Conclusion
And that is pretty much it: Within a pretty short amount of time we created an awesome looking interactive Excel Dashboard that will leave you colleagues and peers stunned.
If you don’t want to start from scratch, you can download the final Dashboard in our Download Area.
