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, best of all, you may want to implement it yourself or consider it a prototype and ask IT to implement it.
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). Here is a step-by-step tutorial on how to establish a connection between Excel and Access (this is one of the modules in my dashboard tutorials):
[fusion_builder_container hundred_percent=”yes” overflow=”visible”][fusion_builder_row][fusion_builder_column type=”1_1″ background_position=”left top” background_color=”” border_size=”” border_color=”” border_style=”solid” spacing=”yes” background_image=”” background_repeat=”no-repeat” padding=”” margin_top=”0px” margin_bottom=”0px” class=”” id=”” animation_type=”” animation_speed=”0.3″ animation_direction=”left” hide_on_mobile=”no” center_content=”no” min_height=”none”][S3VIDEO file=’http://excel.charts.s3.amazonaws.com/sample1/DDlkp2007_01_defining_regions_sound.mp4′ authurl=’false’]
(click the button on the bottom right to maximize the video)
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. Calculations should be performed at the source, if possible.
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).
The example below shows the growth of Walmart (this is a bonus file available in the members area). The user can select a year and a radius. Then the total population (at county level) is calculated for each catchment area. Population characteristics like sex and age structure is also available.
Obviously the dataset is very large. It’s not “big data”, but 11 million records are not easily managed in Excel. I had to use pivot tables, tables and crosstabs in Access. New to Excel 2010, you can synchronize pivot tables that share the same data source with slicers, And I used them in this file. Excel tables are also great, because they greatly reduce calculation time if you have lots of formulas.
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. Actually, you shouldn’t use formulas at all. 🙂 That’s impossible, of course, but you get the idea. Fewer formulas mean a safer, more reliable, easier to maintain dashboard. You can decrease this by using pivot tables. That’s one of the reasons I choose them by default.
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 manually. With dynamic named ranges, the extra column is immediately available. This means less and more reliable work.
I am a truly believer in dynamic charts. 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. In the video above you can see an example. The next video is also a a sample of my dashboard tutorial, where we discuss how to make a dynamic chart (also one of the modules in my dashboard tutorials):
[S3VIDEO file=’http://excel.charts.s3.amazonaws.com/sample2/ddvba2007_06_growth_sound.mp4′ authurl=’false’]
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).
Often all you need to do is to record a macro, add a FOR NEXT or a FOR EACH loop, make simple changes to make sure the macro points to the right cell. If you’ve never recorded a macro, here is a very basic example:
Sub Macro1() ActiveCell.FormulaR1C1 = "=ROW()*5" End Sub
So, in the active cell, enter the formula “=ROW()*5”. Instead of the active cell, let’s tell Excel where we want the formula, using Range instead of ActiveCell:
Sub Macro1() Range("A5").FormulaR1C1 = "=ROW()*5" End Sub
Now we need to enter the formula 500 times, starting in cell 5:
Sub Macro1() For i = 5 To 505 Range("A" & i).FormulaR1C1 = "=ROW()*5" Next i End Sub
This is all you have to do, most of the time:
- Define what cells should be selected, using the RANGE function;
- Use a variable (i) instead of row number;
- Add a loop.
It’s not always this simple, but you don’t have to be a programmer to take advantage of Excel macros, and probably there is the right macro waiting for you. Search the web and you’ll find it. Suppose you want your dashboard to display a specific market when a user opens the file. You have a table assigning markets to users, but how do you identify the user? Well, you could try these macros.
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.
You may still be using Excel 2003. Excel 2003 chart defaults are ugly and you should avoid them like the plague (and make sure you select the acceptable formats). Excel 2007 and 2010 are much better, but they still need some work. 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 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).
The reference in dashboard design if of course Stephen 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.
More than One Way to Skin a Cat
While you should have a clearly defined framework (allow interaction, connect to external data sources, focused design, effective chart formats) the way you implement is pretty much up to you. That’s what I wanted to prove with my video tutorials. They use the same data set, and a similar output, but each one uses a different set of techniques:
- The VBA version uses pivot tables to get the data and a few recorded macros to synchronize them and process user input;
- The Lookup version 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).
After some discussion on the merits of using VBA (Excel Dashboards: Do You Need VBA?) I decided to make 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 context will help you to decide the best way to skin your cat, but feel free to test multiple paths. In general, a large number of formulas will degrade performance, while VBA improves it (but may be off-limits for you).
An Excel Dashboard is the Perfect Excel Learning Tool
Think about it: when you make a dashboard you need to know more about your organization, you must interact with users and managers, analyse data, make charts, connect the dashboard to the formal information structure (if possible), use advanced formulas and functions… It’s a complex and usually very enjoyable task for an information worker. You have to use a wide range of skills, and that can only improve your position in the workplace.
This is why an Excel dashboard is a great business tool and also a great learning project that goes beyond Excel and helps you to improve your lifelong skills. Start making one today!
Shameless plug: try my step-by-step video tutorials and free data visualization book!
My Excel dashboard tutorials will guide you from a blank sheet to a fully functional dashboard and you’ll be able to apply the techniques to your own projects in no time. Here is an overview:
- 2 tutorials;
- 24 modules;
- 2 free dashboards;
- more than 4 hours of step-by-step video tutorials covering dynamic ranges, advanced pivot table techniques, interactive charts, advanced Excel functions and more;
- 24-month access.
Bonus File: Walmart Growth
You may have seen the Walmart stores dataset around the web, but things become really interesting (from a business point of view) when when you connect this dataset to population data: how much American population is covered by a store in 10-mile radius? What about a 5-mile radius? You can use this file to explore your own data (ask me how)!
A match made in Heaven? Get the dashboard tutorials and get a free copy of my data visualization book
Now, while you learn how to make Excel charts like a pro, you can apply these best practices your dashboards. Get access to two dashboard tutorials, more than three hours of step-by-step videos that will help you unleash your creativity and create great dashboards using Excel. And you also get access a growing number of bonus files that you can explore and use to improve your Excel skills. I’m and updating my dashboard tutorials to better reflect what I wrote in the to my data visualization book and to take advantage of Excel 2013 and 2016. To mark this I’m giving the Kindle version of my book away to new users. Hurry! Grab one of the remaining digital copies! Kidding, but this campaign will not last for long.]
(Two dashboard courses only $97.00 and a free electronic copy of my book. 24-month access, 30-day, no questions asked, full money-back guarantee. Pay securely with your Paypal account. You’ll get a message from Amazon with a link to your gift.)