Regular readers know that English is not my mother language , but are kind enough to forgive me for my many mistakes.
I am always willing to learn. Today, while researching for an upcoming post, I came across an expression I never heard before: “spiffy charts”. I felt in love with “spiffy charts” the moment I read it. And I read it straight from the horse’s mouth (I mean Microsoft).
If you don’t know how to make a chart and are keen to preserve that blissful ignorance, I highly recommend Microsoft Office Online Training, specially the module Create a professional looking chart (regular readers also know how I love “professional-looking charts“). You’ll learn how to “customize your charts to make them more attractive, memorable, and effective“. This means useless charts.
So, let’s see how to turn a humdrum (this is a new word, too…) chart into a spiffy one. First, to declutter your chart remove grid lines:
As you know, grid lines are useless, specially if you don’t care about the data. I would remove the gray background and the border around the legend. And I’d give the chart a more descriptive title to tell the users what they are seeing, but that’s my personal taste.
Then you should remove the y-axis and add labels to each column, further “decluttering” the chart. At this point the readers start sighing for a nicely designed chart table.
Want to give your chart a little more “flair” and make them more “professional-looking”? Just add a gradient fill and a subtle shadow:
Now comes the spiffy part. Imagine that you have a 3D column chart with two series, and one obscures the other. What do you do? No, you can’t remove the stupid 3D effect (remember: you want to make t spiffy chart, not a humdrum one). Well, all you have to do is to change the order of the series:
Much better now, don’t you think? They accept that 3D charts “can be more attractive, but sometimes more difficult to read accurately” (surprise, surprise!). Apparently that’s a detail in the grand scheme of things. You are excused from making accurate charts if you are making professional-looking ones.
So, what else can you do to improve your chart? Ah, yes: the y-axis in a humdrum column chart always starts at zero. We don’t want that, do we?
Now you know how to make inaccurate, professional-looking, spiffy-with-a-flair marmalade charts. Please go straight to the kitchen, make some real marmalade and forget all you’ve learned about data visualization in the Microsoft Office Online Training.
(This is not a real Microsoft Office Training site, is it? It must be some kind of spoof site, and I fell for that trick. Right? Right?)
If you want to add animation to your charts that’s a clear sign that you have too much free time. Go out and play with the kids instead.
Yes, animation is a powerful attention-grabber, even more powerful than a glossy 3D pie chart in Crystal Xcelsius. And yes, it can actually be helpful (from time to time). But.
A good example of animation in data visualization is the famous Hans Rosling’s TED presentation, where a long-term pattern is clearly seen (at min 4:00):
This works well because the trend is easily identifiable and you don’t care much about the details. It would be much more difficult to make sense of this data if there were multiple trends and short-term variability. (More on the use of animated charts.)
After watching this presentation people often ask me: “Wow! Can we do that in Excel?” Wrong question. The right question would be “Wow! Can our data do that?”.
You can always create a simple animation in Excel, but it’s hard for a non-programmer to get a smooth transition effect. Here is an example from my dynamic Excel dashboard:
Although you can see a pattern emerging, you would need to add a complex interpolation routine to make it look better (read Jon’s post to see how a simple interpolation can be used).
Animation is better used if there is a pattern to be discovered, but you need something more: the ability to interact with the data. You must be able to stop, go back, get the details. While you can create a simple animation effect with a for/next loop in VBA, interaction with the chart is much easier using a Google spreadsheet with a motion chart:
(This is just an image. Click here to play with the chart.)
Dynamic Charts in Excel
In Excel, instead of creating a VBA routine, consider using a scroll bar linked to the value you want to change (year, for example):
Using a scroll bar adds some level of interaction because you can scroll back and forth, pause and examine the details for a specific year. Obviously, you need to change the chart data source dynamically.
Let’s recap how to create a dynamic chart in Excel. You can do it by changing values or by changing the data source itself (using a different range).
Option #1: Copying the Data
The range A1:E11 is our data set and we are comparing regions. Moving the scroll bar at the bottom changes the year. Column F is our data source. You can enter this formula there:
ROW() gets the current row and MATCH() returns the position of value 2002 (A13) in the range B1:E1. So, the formula in cell F2 reads something like this: start at A1, go down one row, go right three columns and get the data in that cell (range of width 1 and height 1). When the user selects a different year the data is copied to column F.
Option #2: Using a Dynamic Named Range
The second option is to create a dynamic named range. Create a named range and, instead of entering a fixed range, enter this formula:
As you can see, it is very similar to the one above, but now the number of rows down is fixed (1) and it returns a range of 10×1 cells (because we have 10 regions, but if the number of regions varies you may use COUNTA(A:A)-1 to count the number of regions, excluding header – and don’t forget to move the year to a different cell…).
When you verify your range this is what you get:
You just need to use this range in your chart (when entering the range you must add the workbook name: “=Book1!SourceData”). You’ll also need a range for the category axis labels:
If you want to animate your charts make sure you do it because it adds value, not because you want to show off your skills. You can do it in Excel but it looks much better in Rosling’s Trendalyzer or in the Google spreadsheet gadget.
To create animations your data source must change dynamically, and that requires some work (and skills). I advise you to shift your focus from animation to interaction and use all that work to design a better user experience. Do you prefer a “wow!” (animation) or a “wow, thank you!” (interaction that actually helps the user)?
I believe you can squeeze some competitive advantage out from Excel if you avoid some basic mistakes everyone else makes. Here is a very short list (some generic, some chart-specific).
#1. Assuming that Excel Can Do It.
Because a spreadsheet is such a loose environment, people often assume that all things numeric can be done using Excel and that no specific skills are needed. Using Excel as a database tool is an obvious example. If you can’t stop singing Ode to Joy every time you see one million rows in Excel 2007 that’s a clear sign that you are on the wrong track.
#2. Assuming that Excel Can’t Do It.
Most people aren’t aware of how powerful Excel is and use it almost as a pocket calculator. If you routinely have to manage quantitative data, learning a little more always pays off. As an example, you can use it to create complex executive dashboards or, at least, as a dashboard prototyping tool.
#3. Not Having a Go-To Person.
Not everyone needs to be an expert, but having someone that understands the business and proactively tries to find better ways to perform common (or not so common) tasks should be a requirement.
#4. The Excel Islands.
If you have a data source you can connect your sheet to, do it. Don’t make the mistake of copy / pasting the data into your sheet. That’s a bad practice and it can undermine your organization’s entire information system.
If you connect your spreadsheet to the data source (via ODBC, for example) you are ensuring the integrity of your data and minimizing maintenance costs. If you are creating a new table, use a simple database tool like Access. And if you really, really, need to use Excel, make sure you create a table that can easily be exported and used by a different tool.
#5. The Uncharted Archipelago of Excel Workbooks.
Search for “book*.xls” in your hard drive. How many do you find? Get rid of all those useless files now.
#6. Bypassing IT.
We all have our little fights with IT, but we must understand their role and the reasons behind their actions sometimes make sense… Excel users often lack knowledge to understand data structures, data access, documentation, security (IT 101, basically). If your IT don’t want to conquer the world and actually helps users, you should work hand in hand with them.
#7. Not Using VBA.
I’m sorry to break this to you, but if you spend your time analyzing data and Excel is your primary tool, then you must use VBA. I’m not telling you should be a programmer, far from it. But you should be able to record a macro and make some simple tweaks. There are many repetitive tasks that can easily done using three lines of code.
#8. Excel is the only charting tool.
There are no neutral tools. They force upon you a certain way of doing things that you may not be comfortable with (or you shouldn’t). The Chart Wizard is one of the stupidest wizards I’ve ever met. If you need to spend time removing defaults there is something wrong with the defaults. Excel charts emphasize:
Stupid Defaults versus Cleaner Formatting Options
Static versus Dynamic
Marketing versus Insights
Disposable versus Reusable
One versus Multiple
Large versus Small
Island versus Continent
Presentation versus Discovery
I’ll detail this in a future post but, like PowerPoint, Excel charts do have their own cognitive style. If you want to do things differently you can, but you are choosing a path of endless pain. So, give some users the option to use a different charting tool or, as a bare minimum, use a better wizard.
#9. Forgetting the 3R’s.
Reduce, reuse, recycle. Yes, I’m talking about Excel charts. How many disposable charts are cluttering your hard disk? Go green when creating your charts:
Charts are typically much larger than needed, so you can safely reduce their size;
When you create a chart make sure that it can be reused when the data updates;
A chart for Market A is probably very similar to a chart for Market B. By recycling the chart from market to market you don’t need do create new charts and pollute your hard drive.
#10. Not Making Things To Work Together
Lists like this one are useless, unless you know how the basic system works. Try to see the big picture and it will be much easier to understand where each piece belongs to:
If a function needs a range address, you will know that another function can provide the address (first step to create dynamic charts);
If you are repeating the same stupid task, you’ll find a way to automate it;
etc.
Bottom line: make sure you know what you should use Excel for, find inefficiencies and give power users the option to use specialized tools.
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!
