How to Create a Spreadsheet Dashboard

Want to focus the attention of particular items of your spreadsheets? Dashboards may be exactly what you need.

Steps

  1. Image titled Create a Spreadsheet Dashboard Step 1
    1
    Determine that a dashboard is what you need. A dashboard:
    • Displays data graphically, using charts of some type.
    • Only displays relevant data
    • Contains predefined conclusions that are relevant to the intention or goal of the dashboard.
  2. Image titled Create a Spreadsheet Dashboard Step 2
    2
    Define the measures. Be sure who is making the initial request and talk to them about it. You want them to be as clear as possible. It may be that an Excel report is all that they need.
  3. Image titled Create a Spreadsheet Dashboard Step 3
    3
    Establish the audience. Find out who the end users will be. Talk to them and see how they plan to use it. This will help to align your thoughts as to what is needed and should hopefully avoid missed requirements at a later date.
  4. Image titled Create a Spreadsheet Dashboard Step 4
    4
    Define the performance measures. Most dashboards are designed around Key Performance Indicators (KPI). This helps to draw attention to problem areas.
    • When you are gathering the measures, try putting them in a sentence. This will help make it more clear in your head.
  5. Image titled Create a Spreadsheet Dashboard Step 5
    5
    List the required data sources. You need to know where to get the information from.
  6. Image titled Create a Spreadsheet Dashboard Step 6
    6
    Determine the dimensions and filters that you will need. The dimensions would be regional, branch, manager, employee, etc. Filters might be of a particular time period, or something similar.
  7. Image titled Create a Spreadsheet Dashboard Step 7
    7
    Determine the need for drill-down details.
  8. Image titled Create a Spreadsheet Dashboard Step 8
    8
    Ask yourself the following questions to ensure accuracy and understandability.
    • Does the dashboard present the correct info?
    • Does everything in the report have a purpose?
    • Does it prominently display the key message?
    • How easy will it be to maintain?
    • How well documented is it?
    • How user-friendly is it?
    • How accurate is it? Check and triple check.
  9. Image titled Create a Spreadsheet Dashboard Step 9
    9
    Establish the update schedule. The users of a dashboard need to know when it will be updated. It should also be on a regular schedule.

Tips

  • Avoid 'nice to know' information. If it isn't relevant to the goal at hand, don't use it just to fill white spaces.

Sources and Citations

  • Source of information - Microsoft Excel Dashboards & Reports - ISBN 978-1-118-49042-6

Article Info

Categories: Basic Computer Skills