Before you send out your finished Excel dashboard, it's worth your time to step back and ask some key questions. Use these questions as a checklist before distributing your dashboard.
Does your dashboard present the right information?
Look at the information you are presenting and determine whether it meets the purpose of the dashboard identified during requirements gathering. Don't be timid about clarifying the purpose of the dashboard again with your core users. You want to avoid building the dashboard in a vacuum. Allow a few test users to see iterations as you develop it. This way, communication remains open, and you won't go too far in the wrong direction.
Does everything on your dashboard have a purpose?
Take an honest look at how much information on your dashboard doesn't support its main purpose. To keep your dashboard as valuable as possible, you don't want to dilute it with nice-to-know data that's interesting but not actionable.
If the data does not support the core purpose of the dashboard, leave it out. Nothing says you have to fill every bit of white space on the page.
Does your dashboard prominently display the key message?
Every dashboard has one or more key messages. You want to ensure that these messages are prominently displayed. To test whether the key messages in a dashboard are prominent, stand back and squint while you look at the dashboard. Look away and then look at the dashboard several times. What jumps out at you first? If it's not the key components you want to display, you'll have to change something.
Here are a few actions you can take to ensure that your key components have prominence.
Place the key components of your dashboard in the upper left or middle left of the page. Studies show that these areas attract the most attention for longer periods.
De-emphasize borders, backgrounds, and other elements that define dashboard areas. Try to use the natural white space between your components to partition your dashboard. If borders are necessary, format them to lighter hues than your data.
Format labels and other text to lighter hues than your data. Lightly colored labels give your users the information they need without distracting them from the information displayed.
Can you maintain this dashboard?
There is a big difference between updating a dashboard and rebuilding a dashboard. Before you excitedly send out the sweet-looking dashboard you just built, take a moment to think about the maintenance of such a dashboard. You want to think about the frequency of updates and what processes you need to go through each time you update the data. If it's a one-time reporting event, set that expectation with your users. If you know it will become a recurring report, you'll want to really negotiate development time, refresh intervals, and phasing before agreeing to any timetable.
Does your dashboard clearly display its scope and shelf life?
A dashboard should clearly specify its scope and shelf life. That is to say, anyone should be able to look at your dashboard and know the period it's relevant to and the scope of the information on the dashboard. This comes down to a few simple things you can do to effectively label your dashboards and reports.
Always include a timestamp on your dashboard. This minimizes confusion when distributing the same dashboard or report in monthly or weekly installments.
Always include some text indicating when the data for the measures was retrieved. In many cases, timing of the data is a critical piece of information when analyzing a measure.
Use descriptive titles for each component on your dashboard. Be sure to avoid cryptic titles with lots of acronyms and symbols.
Is your dashboard well documented?
It's important to document your dashboard and the data model behind it. Anyone who has ever inherited an Excel worksheet knows how difficult it can be to translate the various analytical gyrations that go into a report. If you're lucky, the data model will be small enough to piece together in a week or so. If you're not so lucky, you'll have to ditch the entire model and start from scratch.
The documentation doesn't even have to be hifalutin fancy stuff. A few simple things can help in documenting your dashboard.
Add a Model Map tab to your data model. The Model Map tab is a separate sheet you can use to summarize the key ranges in the data model and spell out how each range interacts with the reporting components in the final presentation layer.
Use comments and labels liberally. It's amazing how a few explanatory comments and labels can help clarify your model even after you've been away from your data model for a long time.
Use colors to identify the ranges in your data model. Using colors in your data model enables you to quickly look at a range of cells and get a basic indication of what that range does. Each color can represent a range type. For example, yellow could represent staging tables, gray could represent formulas, and purple could represent reference tables.
Is your dashboard user-friendly?
Before you distribute your dashboard, you want to ensure that it's user-friendly. It's not difficult to guess what user-friendly means:
Intuitive: Your dashboard should be intuitive to someone who has never seen it. Test it out on someone and ask that person whether it makes sense. If you have to start explaining what the dashboard says, something is wrong. Does the dashboard need more labels, less complicated charts, a better layout, more data, less data? It's a good idea to get feedback from several users.
Easy to navigate: If your dashboard is dynamic, allowing for interactivity with macros or pivot tables, make sure that the navigation works well. Does the user have to click in several places to get to her data? Is the number of drill-downs appropriate? Does it take too long to switch from one view to another? Again, test your dashboard on several users. And be sure to test any interactive dashboard features on several computers other than yours.
Prints properly: Nothing is more annoying than printing a dashboard only to find that the person who created the dashboard didn't take the time to ensure that it prints correctly. Be sure to set the print options on your Excel files so that your dashboards print properly.
Is your dashboard accurate?
Nothing kills a dashboard or report faster than the perception that the data in it is inaccurate. It's not within my capabilities to tell you how to determine whether your data is accurate. You can, however, highlight three factors establishing the perception that a dashboard is accurate:
Consistency with authoritative sources: It's obvious that if your data does not match other reporting sources, you'll have a data credibility issue — especially if those other sources are deemed to be the authoritative sources. Be aware of the data sources that are considered to be gospel in your organization. If your dashboard contains data associated with an authoritative source, compare your data with that source to ensure consistency.
Internal consistency: It's never fun to explain why one part of your dashboard doesn't jibe with other parts of the same dashboard. You want to ensure some level of internal consistency within your dashboard. Be sure that comparable components in different areas of your dashboard are consistent with each other. If there is a reason for inconsistency, clearly notate those reasons. It's amazing how well a simple notation clears up questions about the data.
Personal experience: Have you ever seen someone look at a report and say "That doesn't look right?" They are using what some people call "gut feel" to evaluate the soundness of the data. Nobody looks at numbers in a vacuum. When you look at any analysis, you bring with you years of personal knowledge, interaction, and experience. You subconsciously use these experiences in your evaluation of information. When determining the accuracy of your dashboard, take into consideration organizational "anecdotal knowledge." If possible, show your dashboard to a few content experts in your company.