Mark Finney - IT Solutions Manager, TP3
Today I'm going to be exploring the topic of Dashboards.
Think about your car’s dashboard. It displays a lot of information about your car and it needs to be read at a glance — that is, your focus should be on the road ahead, not on the dashboard! It indicates your speed and mileage, fuel status, engine temperature and so on.
In a similar fashion, Excel Dashboards display up-to-date information about the status of your business or specific project. This information can then be used to present results more effectively during meetings and to give you the information you need to make appropriate decisions.
Just like car dashboards, there is no single or universal design and those in cars from different manufacturers and models will have different features. However, if you and I were to swap cars (why yes thank you, I’d love to take your Ferrari for a spin) we would instantly recognise the dashboard when we saw it. Your car’s dashboard might have some digital elements and pretty lights, while mine may be a bit more analogue, but we know one when we see one.
Excel Dashboards can also be different in their design, according to your particular business and reporting requirements, and provide an at-a-glance view of KPIs relevant to your project’s objective or business process.
Key elements of a good dashboard include:
- Meaningful and useful data
- Clear visual presentation
The Excel Dashboard will typically use one or more of the following elements:
- Clearly formatted data regions
- Dynamic charts
- Conditional formatting
- Sparklines, or small line charts in cells, typically drawn without axes or coordinates, that present a general representation of data,
In the simple example below, Sales of various products can be displayed using a drop-down list at the top of the Dashboard. When an item is selected, all of the charts update to display sales by Sales Person and by Region. The leader board on the top right also updates to show the person with the highest sales in green. Finally, the list on the bottom right displays the Total Units Sold for each product.
Of course, a good dashboard doesn’t just happen by accident. It needs to be properly planned. And depending on the specific requirements and complexity of the dashboard, you may be able to build it using the tools available directly in Excel — or you may need to use some VBA (Visual Basic for Applications) programming to achieve the result you are looking for.
Don’t worry, you can do an awful lot in Excel without ever having to write a single line of code, and if you ask nicely you can probably get your friendly in-house Excel guru to help out with the tricky bits.
If I break down the elements of the dashboard shown here, it comprises three essential components:
- Storage — raw data in the form of a list
- Analysis — a summary sheet used to collate the data
- Presentation — this is the dashboard itself.
Using my raw data, I took the list and formatted it as a table. Whilst not absolutely essential to the task, Excel Tables give me some extra functionality when working with the data, such as automatically freezing the headings in place, allowing simple appending of data, sorting and filtering, and more. Check out Tables when you have a moment, they’re pretty cool (or do I just need to get out more?)
Once I had my table, I used Excel’s Advanced Filtering functionality to extract the unique set of data I needed to create a summary. Advanced Filtering is kind of “old school” — in earlier versions of Excel you pretty much had to use it because the standard filtering options were so limited, but it now has a new lease of life in helping to build dashboards. That’s why I refuse to throw out my cassette recorder, much to my wife’s bemusement!
I also used a couple of other Excel functions such as SumIf and Sumifs to further extract the relevant data.
Now that I had the information I needed I was ready to setup my dashboard by building my charts and adding some conditional formatting. I paid particular attention to getting the layout and colours consistent. I also added some data validation in the form of drop-down lists to make it easy to select the items I wanted to see. (I find it useful to approach the design with the idea that I am building it for somebody else. This helps me consider ease of use.)
Below is the completed dashboard. I’ve added a pie chart to show sales as a percentage, and another to show Total Units Sold.
Looking ahead I will probably remove the Total Units Sold list on the right since this is now a double up. I will also add some Sparklines to help highlight sales trends over time.
I hope this has given you a small insight into the elements that go into building an Excel Dashboard.
No VBA code was harmed in the building of this simple dashboard :)
If you would like to learn more about this subject, try our 90-minute "Creating Excel Dashboards" virtual training session.
For more extensive Excel insights, you can take one of our popular Excel training courses - created for all skill levels from beginner to advanced.