Hello all! This is Shelly Engelman and Tom Withee, and we love communicating stories with data and data visualizations.
Like many trying out data viz, we initially hopped on the bandwagon with several Business Intelligence platforms. However, we quickly ran into several of the following impediments:
- Too time-consuming to generate dashboards for one-off surveys
- Too expensive to download on multiple desktops
- Too large of a learning curve for new data analysts
After many frustrations with these high powered software packages, we discovered that the solution was within reach the whole time: Excel! With Excel, you can easily aggregate and analyze data, and now, with slicers you can develop interactive dashboards to effectively showcase major trends and findings. Microsoft Excel is affordable, easily shareable without the use of a private server, and just as effective in displaying visualizations that are drillable without the added layers of difficulty that come with other Business Intelligence tools.
Hot Tip:
The 5-Step PARCS method
We developed the PARCS method to help make designing a dashboard in Excel super easy. Here are the five steps:
- Pivot: First, generate a pivot table
- Analyze: Then, analyze data within your pivot table fields
- Rename: Rename your pivot table from “Pivot Table 1” to “Graduation Plans”
- Chart: Insert a Pivot Chart to visualize your data
- Slice: Finally, insert a Slicer to allow the user to slice and dice the data. Slicers make the dream work!
Cool Trick:
Creative Design Layouts
One of the most exciting things about designing an interactive dashboard in Excel is the ability to get creative with your design layout and overall look of your dashboards. For example, did you know that you can create your dashboards on top of an existing image? The background images provide instant context to your data!
Lessons Learned:
Use the PARCS Method with Google Sheets!
Not all our clients have access to up-to-date versions of Excel. We discovered that you can build the same amazing dashboards in Google Sheets. Interactive dashboards in Google Sheets are very easy to share and accessible by anyone with an internet browser. We have found that all of the functionality of Excel is available in Google Sheets; it just looks a bit different.
Rad Resources:
Free Interactive Dashboard Templates
We hope that the PARCS method will enable you to create interactive dashboards in Excel without spending a bundle on other Business Intelligence tools. You are welcome to download some of our free interactive dashboard templates here: https://www.shellyphd.com/sample-dashboards.html.
The American Evaluation Association is celebrating Data Visualization and Reporting (DVR) Week with our colleagues in the DVR Topical Interest Group. The contributions all this week to aea365 come from DVR TIG members. Do you have questions, concerns, kudos, or content to extend this aea365 contribution? Please add them in the comments section for this post on the aea365 webpage so that we may enrich our community of practice. Would you like to submit an aea365 Tip? Please send a note of interest to aea365@eval.org. aea365 is sponsored by the American Evaluation Association and provides a Tip-a-Day by and for evaluators. The views and opinions expressed on the AEA365 blog are solely those of the original authors and other contributors. These views and opinions do not necessarily represent those of the American Evaluation Association, and/or any/all contributors to this site.
Thank you so much for sharing your method and the templates! This would have been so useful two jobs ago when I tried and failed to make a dashboard that included slicers and visualizations. Filing this away for future use.