Excel is the best place to start designing a new executive report or dashboard. 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 dashboard be used for and what kind of metrics users expect, there are three major areas that you must address when creating a dashboard in Excel:
- how to bring data into your Excel 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 a dashboard you can’t just paste some data and add a few charts, can you? The dashboard must be maintained and updated and if you want to minimize 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 that you can use 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 perform 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, while 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 a large number of formulas and calculations can be dangerous for the integrity of your dashboard. You can minimize 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 more understandable, but they also play a fundamental 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, that extra column is immediately available. This means less, but more reliable work.
Dynamic Charts
I am a truly believer in dynamic charts, and a dashboard that the user can’t interact with doesn’t make much sense to me, so all my dashboards include 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
You may not be 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 dashboard can only be done using macros, like greeting the user and selecting the right profile when he/she opens 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.
One must recognize that 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 many charting tips, 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 reveal 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).
Books
The reference in dashboard design if of course Stepen Few’s book, Information Dashboard Design. Other recommended reading are John Walkenbach’ Excel Bible (2003 or 2007) and Excel Charts (2003 or 2007); Tomothy Zapawa’ Excel Advanced Report Development (2003 or 2007); Michael Alexander’s Excel 2007 Dashboards and Reports for Dummies and Jon Peltier’s blog.
An Excel Dashboard Example: The Demographic Dashboard
This is an example of dashboard reporting using many Excel’s advanced functions:
As I said above, sometimes 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 identical, 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 can be a good starting point to learn advanced Excel techniques (and sometimes is it your only option).
For a deeper discussion on the pros and cons of each version you may want to read the post Performance vs flexibility in Excel: Demographic Dashboard Lookup Edition.
Wrap-Up
Excel is a great tool for any type of executive reporting. It allows for fast, flexible and cost-effective dashboard implementation (or prototyping). Intermediate Excel 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 Excel 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!
You may also be interested in:
{ 3 trackbacks }
{ 31 comments… read them below or add one }
This is great. Thanks for sharing your knowledge!
“by the end of the trial period I must have a working Crystal Xcelsius version of…”
If you *need* an Xcelsius version of your dashboard, then stop reading this blog. Jorge is showing how to design an information-dense but practical and usable one-screen data display. Xcelsius is good for dashboards and cockpits and other glitzy texture-rich but information-neutral decorations.
Jorge -
This is a good start-up example of what a dashboard can really be.
Thanks Jon. I hope you don’t find my charts too advanced for you…
No matter what we say, people will keep using Xcelsius and similar tools for eye-catching hum… things, create 2D pie charts in Excel and put flying clip art in their presentations.
The only strategy that could really work regarding visualization is to show the options, explain the differences, make people understand the trade-offs and let them decide.
Since everyone uses Excel, it makes sense to use something like my dashboard as a benchmark and try to replicate it with other tools. By explaining and discussing each step you end up either proving yourself wrong (if you don’t like Xcelsius) or undermining it from inside because you expose its weaknesses in several standard tasks.
If you like this stuff then hopefully you might also like the stuff we do at http://www.instantatlas.com – based on many of the same visualisation principles championed by people like Stephen Few but, like XCelsius, does use Flash for interactivity.
You can see examples for world population on http://www.geohive.com, a site we like and we have supported.
You are a solution provider
Is there anyway to export the dashboard data into an excel spreadsheet? we receive a dashboard created in excel but we want to export it into a spreadsheet. Can it be done? Is it difficult? thahk you
Dee: I am not sure if I understand your question. If you mean exporting the data from the Demographic Dashboard to a new spreadsheet, you can just copy one of the pivot tables to the new workbook and use it the way you want.
Another option is to copy the data from the Access query and past it into the new sheet. Please note that there are more than 200,000 records in that query, so they will not fit into the 65 thousand rows you have available in Excel (try the crosstab query with years in columns).
Hope this helped.
The main problem with Excel dashboarding is keeping them updated as new data arrived.
I think I;ve alreadt spent 40% of my professional life redefining ranges for charts and dashboards in Excel.
Dale
http://www.sisense.com
“Dashboards, Reports and Guided Analytics”
Dale
Yes, it can be a problem, but if the structure doesn’t change much you can manage updates with dynamic ranges. Another option is to make sure that range width or height doesn’t change (for example, by showing only the last 24 months).
Thank you dale
I tried the Prism product in your comment
and i have to say im impressed, created all my excel dashboards (100)
in a few hours!
Do you know how much it cost?
Eian
Jorge – Great work on this. I purchased the training and it has been very helpful to me in setting some things up. I’ve got a question for you – in your training you use offset named ranges to retrieve pivot table data. The ranges use absolute references. This works on your tables, since changing a country doesn’t change which columns show in the pivot tables. I’m working on something where I’m pulling from data submitted by companies. I use a macro to change the company name in the page field in the pivot table, and then pull the data from the pivot. The problem is that if we don’t have data on a company for a particular metric, than that column in the pivot table disappears, and the named range is now pulling a different metric. Is there anyway to reference a column of data so that no matter what column it ends up in, excel will still grab the right data? The only workaround I’ve thought of for this is to make one sheet for each column of data. Would love any suggestions you have.
Thanks,
Sam
Thanks Sam. I usually solve that problem by selecting the field and turn on the “Show items with no data” option. This way the table structure is kept and you can easily handle the missing data.
DashboardGeek (nice name),
The product is still in beta so they don’t charge for it yet, but I contacted their support and they said the generally, a single authoring license goes for about 100 US Dollars a year.
Excellant piece of information. Thanks for sharing the same.
Hey,
I picked up this link in from the article about 14 misconceptions of charts. But since reading it – I must recommend people looking at this.
http://sparklines-excel.blogspot.com/
I downloaded and started to use it just last week and published a highly informative dashboard in hours, complete with spark lines and bullet charts.
I’d really like to get a copy of the three versions you have listed here.
I’m interested in creating more effect reports for my job.
Thank you,
Glenn
Glenn, you just have to leave your email on the right side bar and they will be sent to you.[Update: This is no longer available.]As always great post. thanks
This is great, just what I’ve been looking for. I’m a very basic developer, with goo HTML experience but little experience in actual coding technologies. I get leads that come from our distributor site, who are interested in buyng one of our distributorships. I want a bashboard so that I can retrieve data from that persons geographical area. This will give me how many business are in there are and the population as well as Business growth.
I also want to create a basic customer service data base as well, to retrieve thier data I have entered into excell. Thanks alot.
Joe
Hi Jorge,
I accidentally discovered your site and I am super impressed. I am a data analyst at e-commerce company and am looking to streamline and and automate reports for executives.
I am trying to expand my understanding of excel and building up my repetoire so to speak and I realize that pivot tables and VBA are key. The demographic dashboard is an excellent learning tool for people like me who are looking to learn the “integration” bit by example. And, your example is extremely clean and non-convoluted. So thank you, I am looking forward to learning a lot from your site and tutorials.
I have two questions for you:
a) In your everyday work how do you deal with scope creep from business users after implementing a very tedious dashboard or solution.
b) How long did it take you to finish the demographics dashboard, from concept to completion.
@ Rachana: thanks for your great feedback. I wanted to show how to synchronize several pivot tables using simple VBA, but the other versions show that you can get similar results without code. I would try to do find VBA-free approaches and use code when there is no other choice. For example, I usually run a macro to detect the user id and filter the data for that specific profile. That’s something you can’t do without code.
I don’t really have a good answer regarding the best way to deal with scope creep. I try to create very specific information products that answer very specific business needs, leaving everything else outside. I usually don’t need more than some user feedback regarding the use of the tool. I would say that a project like the demographic dashboard does not require more than a month to implement (and a few more days/weeks for testing).
The challenge is not so much in the Excel techniques (well, only a little
), but it is more in the creativity and empathy of the dashboard designer.
Thanks Jorge, for your brilliant solutions!
Jorge – An excellent presentation.
I frequently use live data from databases via odbc and tuned via MS query to deliver targeted and summarised business information for live reporting. I also use pivot tables, and have found the GETPIVOTDATA function provides dynamic data, provided you are prepared to link the parameters to cells outside the table, and of course, show items with no data. I have never needed to use named ranges. Because most business information is highly structured, I can usually provide a screen full of charts from just one pivot table, then using a selection box or boxes on the dashboard to return the selected data via the GETPIVOTDATA function.
Hi Jorge
I am trying to build dashborads for revenue analysis (product wise), employee performance, budget analysis etc..Can I use the same demographic dashborad for same or would it have to built in a different manner.
Pl advice..
Pooja
@Pooja: I believe that an Excel dashboard is defined by the way it handles the data, and not by the data itself. The three demographic dashboards exemplify how to get the data (connecting to an external data source or creating a spreadsheet table), how to go from there to the chart data sources and how to create the interactive user interface. You can use the demographic dashboard to see how these techniques can be used to work together. Your dashboards will surely be very different, but you can use similar techniques. For example, I’ve been creating employee performance dashboards for several years and they typically use very different sources but don’t need frequent updates (monthly or quarterly basis), while sales would need a single data source but may need daily updates. In the first case, I usually use spreadsheet tables, and in the second case I connect a pivot table to the Oracle database.
Ta..I use Navision as my database. Can you help in buliding good dashboards for revenue/gross margin analysis. Where from your website can I learn how to create dashboard other than specified above.
Pooja
Pooja: I am not familiar with Navision, so I can’t help you with the specifics, but if you search for something like “Navision ODBC Excel” you’ll find a tutorial on how to link Excel to your Navision tables. The Demographic Dashboard uses the Access ODBC, but the process should be similar. After you get your data into Excel you can use the usual Excel formulas to create your dashboard.
Hi Jorge,
Your dashboard ROCKS!!
Just finished the dashboard tutorial and cant wait to get into creating my own for our data. I have searched for a long time for a good quality tutorial that covers all the essential functions for creating reports and reporting tools. This is the most amazing tool for learning advanced Excel I have come across and is also a shining example of Excel best practices. I wish I had this years ago. You have a great understanding of how to communicate and teach complex information easily and clearly.
Thank you.
T
on
http://www.hichert.com/de/software/exceldiagramme/55
you can find a lot of examples (downloadable) of excel-charts which could be used in dashboard
on
http://www.hichert.com/de/consulting/dashboards you find selctions of well-done dashboards and not-so-well-done….
“you just have to leave your email on the right side bar and they will be sent to you.”
Where is this location? I, too, would love to get these templates to learn from.
Thanks!
Thanks Sam. I usually solve that problem by selecting the field and turn on the “Show items with no data” option. This way the table structure is kept and you can easily handle the missing data.