This blog post is a collaboration between Elizabeth Grim and Laura Sundstrom (posted on both of our sites), based on our presentation at the 2016 American Evaluation Association conference in Atlanta, GA. The presentation, entitled “Low Cost, High Impact: How to Create Dashboards on a Budget,” focused on basic dashboard design principles and provided detailed examples of how to create meaningful dashboards using Microsoft Excel and PowerPoint. This is the second part of a pair of blog posts about the presentation. The first part focused on basic dashboard design principles.
Now that you’ve familiarized yourself with our three-step process for dashboard design (Discuss, Develop, Refine), you’re ready to put your dashboard skills to the test. This post will provide example dashboards that we’ve created, and explain how the dashboard design principles were used. Want to learn more about what questions to ask during each phase of the dashboard design process? Download our Dashboard Design Checklist!
This is an example of a basic static dashboard that was created using PowerPoint, but the same design elements are available in Excel if you prefer that software. The dashboard was developed with a nonprofit organization to monitor attendance for a multi-site program. Let’s review some of the dashboard design principles and questions.
- Who is the audience? Organization staff.
- What is the purpose? To understand if attendance is remaining consistent through the growth of the program and addition of new program sites.
- Is the dashboard static or interactive? Static.
- Which platform was used? PowerPoint was chosen because staff feel more comfortable using and editing in PowerPoint vs. Excel. Additionally, PowerPoint is good for formatting and aligning in a static dashboard.
- What is the best visual display for each metric? Bullet charts are great for showing progress towards a goal. Column charts allow us to show comparisons across different groups of participants and to easily display a benchmark.
- Is color used intentionally? The colors used align with the organization’s branding guide. Purple was used for overall program metrics and goals, teal is used for individual program sites.
- Has the clutter been removed? Yes, grid lines, tick marks, and other distracting elements have been removed.
- Who needs to provide feedback? Program staff, program directors, and the executive director all provided feedback to ensure that that dashboard was useful and meaningful at all levels.
- Can users easily determine what action needs to be taken after reviewing the dashboard? Yes, benchmark lines were added so that users can see areas of success and areas for improvement.
Tip: This beginner dashboard was designed in a way such that it has to be updated manually. You can link an Excel file to the dashboard (or use Excel to create the dashboard in general) so that the data is directly linked to the visuals, rather than having to manually update each graph.
This is an example of a more advanced interactive dashboard that was created in Excel using data from the Fatality Analysis Reporting System (FARS). The dashboard was developed primarily as a demonstration for the AEA 2016 presentation and is considered to be more advanced because it includes slicers (available in Excel 2010+), Pivot Tables, and Pivot Charts. If you want to learn more about how to create a dashboard with slicers, check out the great work happening at MyOnlineTrainingHub, which served as inspiration and guidance for this example.
Alright, let’s review some of the other dashboard design principles.
- What metrics will be included? Total fatalities and DUI fatalities by month and by state.
- Are the metrics easy to understand? Yes, but could also (or alternatively) include percentages for comparisons.
- How often does this information need to be updated? Monthly or annually, depending on the level of analysis.
- Which platform will be used? Excel 2016 (have to have Excel 2010+ to use slicers).
- Will the dashboard be static or interactive? Interactive (when you click on the states in the slicer on the left sidebar, the blue lines in the bottom two charts move to represent data from that specific state).
- Does each color represent only one component? Yes, gray is used for the total and blue is used for the states.
- Can users easily determine what action needs to be taken after reviewing the dashboard? Not really, might consider adding some text about potential action steps.
- Does the dashboard continue to be responsive to the ongoing changing and monitoring needs? Yes, the dashboard was created using Pivot Tables and Pivot Charts so it’s easy to update the dashboard simply by adding new data to the existing table.
Tip: You can also embed your Excel dashboard on a website using Microsoft OneDrive. It’s helpful to test out this method using fake data first while you practice choosing different viewing and sharing options, and to make sure all your functionality works.
So there you have it, you’re now equipped with the knowledge and tools you need to become a dashboard superhero! Want to learn more about what questions to ask during each phase of the dashboard design process? Download our Dashboard Design Checklist!
We want to hear from you! What’s working well during your dashboard design process? What are you struggling with? What questions do you need answered that aren’t included in the checklist?