Delineate the Measures for Your Excel Dashboard
Most Excel dashboards are designed around a set of measures, or key performance indicators (KPIs). A KPI is an indicator of the performance of a task deemed to be essential to daily operations or processes. The idea is that a KPI reveals performance that is outside the normal range for a particular measure, so it therefore often signals the need for attention and intervention.
Although the measures you place into your dashboards may not officially be called KPIs, they undoubtedly serve the same purpose — to draw attention to problem areas.
The measures used on a dashboard should absolutely support the initial purpose of that dashboard. For example, if you’re creating a dashboard focused on supply chain processes, it may not make sense to have human resources head-count data incorporated. It’s generally good practice to avoid nice-to-know data in your dashboards simply to fill white space or because the data is available. If the data doesn’t support the core purpose of the dashboard, leave it out.
Here’s another tip: When gathering the measures required for the dashboard, it often helps to write a sentence to describe the measure needed. For example, rather than simply add the word Revenue into user requirements, write a component question, such as “What is the overall revenue trend for the past two years?”
This is called a component question because you will create a single component, such as a chart or a table, to answer the question. For instance, if the component question is “What is the overall revenue trend for the past two years?” you can imagine a chart component answering this question by showing the two-year revenue trend.
You can take this a step further and actually incorporate the component questions into a mock layout of the dashboard to get a high-level sense of the data the dashboard will require.
Each box in this dashboard layout mockup represents a component on the dashboard and its approximate position. The questions within each box provide a sense of the types of data required to create the measures for the dashboard.