I just finished updating my Excel Dashboards course, Drive Your Dashboard with Excel, and thought I would share an excerpt.
Dashboard Design
Creating a dashboard is NOT the same things as creating an Excel spreadsheet. By this, I mean that people tend to open an Excel spreadsheet and just start entering data. They worry about formatting and layout later – if at all. (No, I am not talking about you.)
This is not true of creating a dashboard. The design of the dashboard is key. Now, don’t roll your eyes. If you don’t know who your audience is and what the purpose of the dashboard is, it will not be successful. You will have simply wasted your time and probably confused your audience.
To design a dashboard properly you need to know:
- Who will be viewing the dashboard?
- Who will be using the dashboard?
- Viewers and Users may have different purposes
- What they want to dashboard for – what measurements?
- What metrics do you want to track?
- What do users want to learn from dashboard/ how do they plan to use it?
- High Level overview or detailed operational needs
Once you understand who your audience is and why you are designing it, you need to consider:
- Where is the data located?
- How is the data going to be retrieved?
- Who will maintain and update the original data?
- Who is going to update the dashboard’s data once it is created?
- How should the dashboard be structured?
Design Layout
KISS- Everyone knows the Rule – Keep It Simple….
Dashboards, with their colors and graphics, usually resemble a magazine layout. Appearance is important but don’t get so focused on the graphics and layout that you lose the data. This is becoming more and more important as dashboards gain in popularity and everyone is trying to make them look so pretty.
Edward Tufte, the acknowledged “founder” of dashboards, discusses basic techniques for improving the display of quantitative information. He has an interesting website which can be found at http://www.edwardtufte.
Below are some basic rules to keep in mind as you design a dashboard:
- Plan and design a mockup so that you know what you are doing with your space.
- If there is too much data, it is difficult to see what is really going on so keep it clean and simple.
- Do not use a lot of different colors.
- Do not use a lot of different fonts.
- Format consistently – For example, do not use accounting format in one section and currency format in another section.
- Select a single background color for charts or make the chart areas transparent.
- Use custom formats for large numbers and/or abbreviate where possible.
- Use white space to indicate sections to create the idea of more space (no borders).
- Only include needed graphics such as charts.
- Don’t get cute- it is not a PowerPoint presentation.
- Generally, the top of your dashboard will contain overview information and more specific information should be found underneath it.
Dashboard Structure
Basically, you need to break your dashboard area into sections.
At an absolute minimum, you should have 3 sections in your workbook/files:
1.Data/Input Section
- The Data section is where the raw data is. If you are importing from other programs, such as Access or QuickBooks, this is where you would want the data to reside
2. Analysis Section
- The Analysis section contains the formulas, which pull the data from the Input section, and organizes it so that it becomes information. This is the section that will feed the Presentation section.
3.Dashboard (Presentation) Section
- The Presentation section pulls and/or reshapes the key information from the Analysis section into the actual Dashboard.
Other sections to consider adding include:
- Control Section
- Many people recommend having a Control section or sheet that would contain informative that would be used multiple times in a dashboardsuch as listing of dates, listing of products or sales regions. In other words, data that you could reference or link to multiple times.
- Help Section
- Some people recommend having a Help section or sheet, which can be very useful, if one or more people are maintaining the dashboard You can also include comments and explanations of calculations there as well. You may think you will remember why you put a formula together a certain way, however, there is a good chance that you may forget what your reasoning was a year later.
- Table of Contents or Reports Page
- A Table of Contents can be very useful if you are using a lot of worksheets. You may also want to consider having a Reports Page and then pull the Dashboard from that page. Make sure your set-up allows for flexibility.
For example, your raw data section may contain Sales by month by sales person by product for last year and this year as well as budget numbers. In the Analysis section, you may have created a pivot table that summarizes sales by product for the month and quarter. The presentation (dashboard) section may display the quarterly sales by product as compared to budget or last year.
Keeping all the data together in one workbook is obviously the easiest method; however, these sections can all be in separate workbooks depending upon your preference and the volume of data and analysis. This is one of the reasons that preplanning and layout are so important. You don’t want to be in the middle of your creation and suddenly realize that it won’t work or that you forgot an important component that now needs to be incorporated in somehow.
When I reread this section, I realized that most of the information included here was also included in a course I wrote earlier on designing a database. I cannot emphasize enough that preplanning and design are the most important aspects of a dashboard. If you don’t know who your audience is and what they want the information for then you have just wasted a lot of your time as well as theirs.