Creating Useful Dashboards: Part 2 - From Cleaning to Going Live

By Elise Bajohr, CPHQ

Do you ever spend large amounts of time working on a dashboard, only to have it sit on a shelf? Welcome back to this two-part series on creating useful dashboards! This article will walk you through tips on cleaning data, synthesizing the information, and going live with the dashboard.

Cleaning Data

Often one of the most time-consuming tasks, cleaning data is integral for accurate and efficient dashboards. This is where the conversations you had to determine the data sources and processes for data entry come into play. A dashboard might integrate multiple data sources across multiple departments, or it might be as concise as one program and one spreadsheet. These tips translate across most scenarios.

Separate Data

Simplicity rules. The more complex a spreadsheet, the more risks not only for errors in entry, but also in use. Many direct service providers are rushed, therefore scrolling across spreadsheets to find column XX is not efficient nor effective. This leads to incomplete data entry and use of the data. Providing another reason those key conversations from the previous article are so important.

• Evaluate Use & Need: Now that you have your data, work directly with those who enter and use the information to determine its necessity. Often, there are extraneous categories of information that were added by past staff. Think of this information as a human appendix, historically useful but with no clear, current purpose. Ask staff what is required or important and prune what isn’t needed.

• Reduce Open-Text Fields: Open text fields are harder to report on and visualize. While you can build dashboard components that allow you to scroll through open text, it doesn’t translate easily into charts and graphs. Whenever possible, go through the common answers that are listed and turn them into their own columns. For example, if you are tracking unmet needs, instead of an open field for note entry, this could be a drop down of common answers, with an open field for “other.” The new column is now data that can be analyzed and while it adds a column for the few other answers, it reduces the need for open fields and becomes simple click-and-tab workflows. Reducing open-text fields creates easier and more useful data.

Error-Proofing: To err is human, and punishing staff for a foundational human characteristic will not prevent it from happening again, and it creates a punitive work environment instead of a positive organizational culture. However, even a singular, tiny human error can throw off an entire data set and cause cascading inaccuracies across a dashboard. Adding a space before entering a date in a column or a typo in the name of a service or provider skews results. If an error is small enough, it might go unnoticed and even impact decisions, allocation of resources, and reporting accuracy.

• Solution Poka-Yoke: Poka-yoke is the Japanese term in quality improvement for “mistake-proofing.” Common examples of this in action are the function in a microwave where it will not run when the door opens or the small overflow hole towards the top of your bathtub to help prevent it from overflowing if the drain is plugged. The key is to apply this to your spreadsheets. In typical spreadsheets, such as Excel or Google Sheets, you prevent a lot of errors through basic design changes.

  • Drop Downs: Whenever possible, utilize drop downs. If people can select the option instead of typing it, there is no risk for typos, additional spaces, or other similar errors.

    Color Code: Beyond the basic red, yellow, green light ideas (which are not the best for accessibility reasons), you can utilize colors to reduce the risk of errors. For example, if everything from 2026 is orange and 2025 is blue, it is easier to spot an error and select the correct year.

  • Data Visualization: In both Excel and Google Sheets you can change the cells in a row or column to only accept dates in the specific format you set. If someone enters a leading space or enters too many digits, the cell will flag as invalid. You can also embed a calendar to allow the user to select the date entirely through clicking.

  • Data Dictionaries: Build agreement about definitions. If people understand a word, task, or phrase differently, the data will be inconsistent. Create a data dictionary and have staff review it to confirm a shared understanding and lexicon. This becomes more complex when multiple departments use the same word but define it differently, which requires a more intensive, though not insurmountable, effort to navigate than what we are discussing today.

Sustaining the Process:  It is critical for the accuracy and continuity of the process to ensure that the new workflows are effective for those who will use them. To help people avoid slipping into old habits or resisting the change altogether, you need to make a system that works reliably and consistently.

• Trail and Error: Listen to those who enter the data and make changes. Test out a new process for a week, then check back in. It can take a while to become proficient with any new workflow, so be patient and ask for patience in return.

  Create a Protocol: Create a user-guide. This allows for future staff and supervisors to understand the steps and their order. Explaining the purposes of changes can be especially important for building buy-in and compliance. Have non-involved staff follow the guide, if possible, to confirm clarity and accuracy.

Synthesizing Information

You now have clean data and are ready to build your dashboard, right?!? Not quite yet. Remember how simplicity is key for data? It’s also true for selecting the information for your dashboard. When building a dashboard for multiple levels of roles and use, your job involves synthesizing and selecting direction. Here are a few common problems and solutions.

Too Many Cooks in the Kitchen: Let’s say you are building a dashboard for a community services organization. Your exploratory conversations yielded 20 different ideas about what would be useful to see, track, and the number of goals. Incorporating everyone’s priorities into one dashboard can yield a disjointed and overwhelming, even if impressive, mess.

• Break Dashboard Down: Instead of one huge dashboard, you can make multiple tabs, enabling people to access what is relevant to them through the same link. This creates user-friendly dashboards that provide the required audiences with the information they need, without convoluting it with everyone else’s needs.

Idea Stew: No matter how many “cooks” are in the kitchen, you can end up with too many ideas for the dashboard. There can be a trap of not wanting to disappoint by not fulfilling each request, especially if those requests come from leadership. However, this again can lead to a long, messy, and cumbersome result. You did exactly what was asked, but it created a dashboard that is not used as much as a simpler version would.

• Have Another Conversation: If you want to winnow down to determine more clear goals and functions, other clarifying conversations can help. You can ask the individual(s) where they want to start or to rank the importance of the ideas.

• Prioritize: Use your general idea of what matters to which stakeholders and select what your expertise and previous conversations led you to believe is most important. This is not the same as going in cold or receiving a short order and building without more information. You have done your due diligence at this point and should have built your understanding through interviews and conversations. Do not hold yourself back if you have the power to make decisions on the most important items to include. This builds directly into the next step, going live.

Going Live!

Congratulations! You built the dashboard! Here are a few more tips for ensuring usefulness and a happy result.

Training: This should be done when introducing the dashboard to new staff but also consider creating a short video or guide for future users. Key components of the training can include:

• A Walkthrough of where to click to drill down or reset the tiles and dashboard

• Tabs, if used

• Purposes and definitions

• X and Y axis

• Basic visualization explanations (e.g., what a stacker bar shows)

Appreciate Feedback: Especially for complex dashboards, this is likely not one-and-done. Introduce the roll-out meeting by framing that this is the first version. Be open to building additional versions, which links back to prioritizing what you put on this version to reduce idea stew. Providing a first version, or even draft, can be immensely helpful for a group or individual to hone what they actually want. Given the goal of wanting a useful dashboard, there is a balance between your expertise in data visualization and the system you use for building dashboards with the expertise and requests of the end users.  Being open to feedback and creating additional versions are helpful to reaching the end goal.

• Revisit: Depending on the team, complexity, and time, schedule follow-up meetings to ensure the dashboard is fulfilling its purpose, data entry is smooth, and users questions are answered.

• Continuity and Empowerment: Whenever possible, work with supervisors and leadership to identify any staff who can learn some of the basic skills, whether within the dashboard or spreadsheet. Efficiently, this limits the amount of time they need to hire you (if you are a contractor) or call your department whenever a small change needs to occur. Additionally, this provides professional development and empowers staff to make appropriate, internal adjustments as needed for a more efficient system.

Closing Thoughts

Thank you for walking through my tips on creating useful dashboards. Hopefully some of this will help you in your work or consider giving us a call at Bynes Consulting Group, where we integrate data visualization to inform our projects and support the great work that you all do!