Some years ago, as part of my (then) new job, I had to maintain a monthly updated Excel dashboard. It was a maintenance hell, I hated it, but I couldn’t change it because of my poor Excel skills.
“This is stupid, there must be a better way”, I kept saying to myself.
So, I searched, and searched, and searched, and within a few months I became the most skillful Excel user in my company and I could solve my initial problem. An all day long update turned into a ten minute task. I revamped the entire dashboard, but I kept the same user interface.
An Excel Dashboard is a Jigsaw Puzzle. Learn How to Solve It.
Back then, I was able to use some of the more common formulas, like most Excel users do. But if you want to create Excel dashboards you must understand how everything fits and works together. If you don’t, expect nothing less than a spreadsheet hell. You should never underestimate that.
I hate repetitive and time-consuming tasks, and I avoid them like the plague. If I suspect that a co-worker, after asking for a report, will come back and ask for different scenarios, I usually offer that functionality from the start. It’s a win-win situation: it doesn’t take longer than a static report, I avoid extra work and the user loves to play with his/her new toy.
Make Sure You Market Your Skills
As I said, I kept the same user interface in that dashboard. It proved to be a huge mistake, from a personal marketing/career perspective. After all these years, I still believe I made a remarkable job but, because I kept all the changes behind the scenes, they went unnoticed by the users — including the boss. Well, marketing and promotion is a big step out of my comfort zone, and office politics is not exactly what I do best…
Lessons Learned
That old dashboard was created by an above-average Excel user, but he failed to understand this basic concept: an Excel dashboard is a jigsaw puzzle, and fewer pieces makes it simpler to solve (for example, a simple pivot table can often replace dozens of formulas).
Go beyond the individual formulas. Create a project that forces you to learn how they work together (that’s what my dashboard tutorial is all about).
Excel dashboards and executive reports are powerful, fairly easy to design and a great way 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 those tasks you must impose some structure to your data.
Usually the data should not be entered directly into the spreadsheet. You may copy /paste the data, but the best option is to 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. [Update: go to the members area, create a free account and watch the sample video on how to connect Excel to an Access table.]
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. 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 to 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 making 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 and more reliable work.
Dynamic Charts
I am a truly believer in dynamic charts, and dashboards that the user can’t interact with don’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. Some managers prefer a static dashboard (perhaps just a sheet of paper, or a PDF file) but even then you should implement interaction, because it will be easier to create those static reports (you can automate them).
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 kind 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 a matter of opinion only: 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 are useless for serious 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 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 business 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!
