Some metaphors are really stupid. Convincing people that running a company is like driving a car and you need a similar dashboard is one of them. If you are naive enough to buy it, most dashboard vendors will happily fill up your precious screen real estate with irrelevant gauges and and speedometers designed to look “cool”.
Futuristic aesthetics sells very well, but what happens when the future arrives and doesn’t look exactly like the image you bought? Will you buy a new one?
Look at that eye-catching, high-tech gauges in your dashboard and now compare them to this dashboard in the Lamborghini Reventon. Suddenly you feel tired and lost in the past… Your dashboard looks as old as an old Flash Gordon movie… How embarrassing will be to show it again…
Now that you’ve learned your lesson, put the your dashboard in design mode and make it clean, elegant and timeless. Remove textures and add data. Keep it simple and to the point. When people asked you for those cool gauges don’t laugh in their faces. Help them. You know better now.
The other day I was reading Dundas’ whitepaper “Best Practices for Building Digital Dashboards” (PDF) and you know what? What they say makes a lot of sense. I agree with at least 75% of what is written. For example:
… developers must be careful that the visuals do not interfere with the usability and efficiency of the digital dashboard…
or
Context is an item which in most dashboards is completely forgotten. This is baffling, as without context, KPIs are completely useless.
This really took me off guard. I was prepared for the worst and and what I get is this six-page whitepaper full of good advices that even Tufte would follow.
But wait… My skeptical mind wants to know if they practice what they preach. So, for example, what kind of context could we provide in a typical Dundas dashboard?
Here it is: add a little green stripe to a gauge, call it “context” et voilà: best practices at their best. Take a look at the report for similar insightful concepts “regarding usability and efficiency of the digital dashboard”.
Corollary: if you follow best practices and use common sense, you’ll be lost soon.
Excel dashboards and executive reports are powerful, fairly easy to design and a great ways to improve your Excel and data visualization skills. Because of its flexibility, you can virtually design any dashboard in Excel exactly the way you, or the users, imagined (and then send project to IT for implementation).
Once you know what will the Excel dashboard be used for and what kind of metrics users expect, there are three major areas that you must address when creating it:
how to bring data into your spreadsheets;
how to manage the data and link it to the dashboard objects, like charts and tables;
how to design the dashboard report.
Let’s take a look at each of them.
How to bring data into your Excel dashboard
Yes, Excel is a very flexible tool, but to create an Excel dashboard you can’t just paste some data and add a few charts, can you? You must maintain and update it, and if you want to decrease the cost associated with that tasks you must impose some structure to your data.
Usually the data will (should) not be entered directly into the spreadsheet. This means that you must copy /paste the data from the source (not wise, really) or connect the spreadsheet to the data source. There is a standard way to communicate with external databases called ODBC. You can use it to connect your dashboard to a table in Access or Oracle, for example.
Once you have that connection established, every time the data changes at the source it also changes in the spreadsheet, after refreshing. Keep in mind that a good practice is to minimize the amount of data you bring into your spreadsheet. If you don’t need it, don’t use it. However, if there are calculations to be performed, try to do them at the source.
Data can be imported using two basic structures: a flat file and a pivot table. As a rule of thumb, I would say that a pivot table tends to create larger files but the calculations will be faster. The flat file will be smaller but, if you need complex formulas to select the data, performance will suffer (you must test this in your own project).
How to manage the data and link it to the dashboard objects
If you use pivot tables you can extract the data using the GETPIVOTDATA function. If you use a flat file, there is a vast array of formulas you can use, like database formulas (DSUM, DGET), lookup (VLOOKUP, MATCH, INDEX) or math formulas (SUM, SUMIF, SUMPRODUCT).
Having many formulas and calculations is dangerous for your dashboard integrity. You can decrease this by using pivot tables. That’s one of the reasons I choose pivot tables by default.
Named Ranges
Do I have to tell you that you should document your work? Ok, I’ll tell you: yes, you should. But you can simplify that boring task by using named ranges. I am sure this is a familiar example: “=Sales-Expenses” is better than “=$G$4-$H$5”.
Named ranges not only help you to make your formulas cleaner and easier to read, but they also play a key role in data management for Excel dashboards. Example: let’s say that the next update adds a new column to your table. If you don’t use named ranges you’ll have to change every single formula and add that extra column. With dynamic named ranges, the extra column is immediately available. This means less, but more reliable work.
Dynamic Charts
I am a truly believer in dynamic charts, and dashboards that the user can’t interact with doesn’t make much sense to me. All my dashboards have controls to change markets, change products, change sales territories, whatever. What is important is that the user must be able to interact with the dashboard and build his/her knowledge by exploring the available data.
If you are creating interactive charts you will probably need dynamic ranges. You can do it using the OFFSET() function.
Macros
OK, you are not a programmer (I am not) but you should consider using some macros to speed up some repetitive tasks. Also, some cool things that you can add to your dashboards can only be done using macros, like greeting the users and selecting the right profile when they open the dashboard (you may want to read the discussion around the use of VBA in Excel dashboards).
How to design the dashboard report
If you like the sort of charts that you find in marketing-oriented tools like Crystal Xcelsius let me tell you this: they don’t work. There is a first wow! and then their uselessness starts creeping in.
This is not only a matter of opinion: I tried (and failed) to replicate my Demographic Dashboard (see below) using Xcelsius and detailed the process in a series of posts (1, 2, 3, 4, 5), so I know why these tools don’t work. Don’t try to use Excel to mimic them.
Excel 2003 chart defaults are ugly and you should avoid them like the plague (and make sure you select the acceptable formats). Throughout this blog you’ll find manychartingtips, and tips to improve your Excel dashboard, so I will not elaborate much on this. The basic premise is this: a chart is used to discover actionable patterns in the data, and you should do your best to show those patterns.
This means that you should remove everything that has no added value (3D effects, backgrounds, etc), create a hierarchy of focus and context data (make relevant data stand out, using color carefully) and add at least a basic form of interaction (let users select the series, for example).
An Excel Dashboard Example: The Demographic Dashboard
This is an example of dashboard reporting using many advanced functions:
As I said above, it is difficult sometimes to put everything to work together in an Excel dashboard project. With the Demographic Dashboard, I wanted to show how to connect the dots and create a fully functional dashboard. So, I used a very interesting dataset from the US Census Bureau (population by sex, age and country, 1996-2050) to design it.
Actually, there are three dashboards… I took advantage of Excel’s built-in flexibility to create three versions of the same dashboard. They all look the same, but each one uses a different set of techniques:
The VBA version: This was the first one. It uses Visual Basic for Applications and pivot tables. It is faster than the other ones, but it relies on VBA to perform some tasks, and that may put off some users (I’m just using recorded macros, nothing too complex);
The VBA-Free version: After some discussion on the merits of using VBA (Excel Dashboards: Do You Need VBA?) I decided to add a VBA-free Excel dashboard; It mainly uses pivot tables and the powerful GETPIVOTDATA function. It is a well balanced version: it is slower than the previous version but since it doesn’t use VBA some users don’t have to leave their comfort zone;
The LookUp Table: It uses a spreadsheet database (basically a table that you paste into a sheet) and lookup functions (VLOOKUP, MATCH, OFFSET) to get the data for the dashboard report. Personally I don’t feel very comfortable with spreadsheet databases (more maintenance, more error prune, slower) but it is a good starting point to learn advanced Excel techniques (and sometimes is it your only option).
Excel is a great tool for any type of executive reporting. It allows for fast, flexible and cost-effective dashboard implementation (or prototyping). Intermediate users should be able to design and implement an executive dashboard in a matter of days (or even hours, for the first draft), provided that they know how to select the right techniques.
An Excel Dashboard is the Perfect Excel Learning Tool
You can read a million tips and still miss that wow! moment when you see all those small pieces working together. That’s why designing a dashboard is one of the best ways to learn Excel. If you want to get the details but also the whole picture, get my Demographic Dashboard Tutorial and start your Excel wow moment now!
