Following the 10 x 10 post series on tips for better charts, these are the 10 tips for line charts:
Feel free (and I would appreciate) to comment or add your own tips…
{ 6 comments }
How do you create a map like the one above for your next presentation if you don’t have a mapping tool? Simple, create it in Excel. Easier said than done, right? Well, not really…
Following the “geo-scatterplot”, in this screencast tutorial I’ll show you how to create a thematic map and color-code it, based on your own data. You don’t need add-ins or additional software, just a little time to set it up. Please note that this is not intended to replace even the simplest mapping tool.
To start the tutorial just click the link below:
Screencast: How to create a thematic map in Excel.
Note that you need a map. You can draw it yourself or you can import it. Drawing a States map is simple:
The other option is to obtain a file. You can get an ESRI Shape file from the National Atlas but you’ll have to convert it to Windows Metafile (WMF) or similar format.
Hope you’ll find this technique useful and feel free to suggest any improvements.
If you liked this tutorial you may be interested in How to create population pyramids and the “Howto Edition” fo the Demographic Dashboard. And you may consider subscribing…
{ 61 comments }
If you google for “Excel dashboards”, 6 out of the first 10 results link to Charley Kyd’s ExcelUser website or some of his affiliates. MrExcel calls Charley Kyd “the king of Excel dashboards”.
There is a good reason for that. Three years ago, Charley Kyd published an e-book, Dashboard Reporting with Excel, probably one of the first books discussing charts in the context of dashboard reporting (he recently published an Excel 2007 version). The reader will find sound advice on the use of some Excel techniques, dashboard planning or external database connection. A useful step-by-step tutorial is presented at the end of the e-book.
Kyd helps you to create a static, printer-friendly dashboard…
According to Kyd, a dashboard should be printed. This is obvious as soon as you look at any of his examples, like the one on the left, and, knowing the level of computer illiteracy at the higher management level, he has a point. For your dashboard project, don’t assume that the users will want to see the dashboard on screen. At least ask them.
Some of Kyd’s principles and techniques are freely available on the website, although you should refer to the e-book for a more detailed discussion. For example, regarding dashboard design, Kyd disapproves the use of gauges and condemns “pablum dashboards” (low data-density dashboards).
When discussing the benefits of dashboard reporting for management, he argues that Excel is often overlooked as a dashboard reporting tool because a) vendors prefer to offer costly proprietary solutions; b) users are not aware of the potential of Excel as a dashboard tool; and c) users overlook Excel’s capabilities to produce high-quality, magazine-like reports.
The dashboard design can be much improved by the use of smaller charts and templates for future reuse, and a magazine-like formatting. To achieve this “magazine-like” quality Kyd uses extensively the camera tool. In fact, he says that:
Without the Camera tool, you would find it virtually impossible to create high-quality dashboard reports. [page 77 of the e-book]
The camera tool is interesting, but this is an overstatement (there are many good examples that don’t use it, as you can see in the final pages of Stephen Few’s Information Dashboard Design). And of course there is the well known and persistent Excel bug that resizes a chart if you used the camera tool to display it (Kyd alert for that here; possible workarounds can be found here).
I have a different approach to dashboard design (more on that later) but I would recommend the Dashboard Reporting with Excel e-book if you plan to design a printer-oriented dashboard. Perhaps you should explore the website first (check those links above) to see if you/your audience are comfortable with Kyd’s dashboard approach.
A side note: Kyd tells us that “the most common mistake that Excel users make when they create charts is to make their charts too large.” He’s right, but given the higher resolution you can get from a printer, you have the option to drop Excel standard charts altogether and use sparklines instead.
… but there is something that bothers me
I confess there is something that bothers me when I see Kyd’s arguments in favor of Excel dashboards. It is this magazine-quality thing (he emphasizes this throughout the e-book and also on the website). He says in the e-book:
Ideally, benchmark reports should look like they came from a magazine or newspaper. This makes the reports both interesting and easy to read.
As a rule, however, bean counters aren’t qualified to design magazine-quality reports, just as graphic artists aren’t qualified to design cost accounting systems. But bean counters can copy graphic designs they find in magazines and newspapers. [page 17 of the e-book]
I understand what he means, but I believe something is lost in translation, because that’s what the readers already do, and the results are far from stellar. (A creepy idea: if “bean counters can copy graphic designs” then graphic designers can design cost accounting systems. After all, they are designers…)
I don’t even know where to begin… let’s see:
A different approach
I understand and respect Kyd’s work and I know that there is a market for printer-oriented dashboards. My approach is somewhat different. I believe that, given the amount of data we have to deal with, a minimum level of interaction is needed. For example, if you are monitoring your competition, you should show the user a predefined set of competitor products but you should also let her choose a different set from a larger list. Interaction is a fundamental way to build knowledge. In my Demographic Dashboard I try to show how this is easily implemented in Excel.
One of the problems with Excel dashboards is how you get the data into Excel… Kyd discusses six methods: 1) build-from-scratch spreadsheet reporting; 2) spreadsheet databases; 3) pivot tables and 4-6) OLAP cubes. I will not even bother to look at the first two (if you want an highway to spreadsheet hell, be my guest, use them). The last one (specially what he calls “Excel-friendly OLAP”) is the best option, but probably too advanced for the average Excel user.
This leaves us with pivot tables. They are safer than other basic methods and don’t require much interaction with the IT… According to Kyd, their main disadvantage is:
Excel formulas only can reference data that PivotTables return to spreadsheet cells. This forces Excel users to interact with each PivotTable to update their report data, rather than interacting only with controlling cell values in Excel.
He’s right, but the dashboard designer just needs a macro (he can record it) to change values. Let me exemplify:
PivotTables(“x”).PivotFields(“Year”).CurrentPage = Range(“selYear”).Value
This single line changes the Year field in pivot table “x” to whatever value is found in cell “selYear”. You just need one line for each pivot table. Then the macro is attached to a button et voilá! you have a basic interaction. Not too hard…
It really doesn’t matter if you use printer, on-screen or smoke signal dashboards. What matters are the insights a user gets from the data and how they can support better decisions.
Judging from your experience, do you think there is a pattern that can be used as a rule of thumb (like “higher management prefer printed dashboards, middle management prefer on-screen dashboards”) or, as usual, it depends?
{ 7 comments }
Excel is a great (but underrated) BI tool. Several BI vendors gave up fighting it and offer Excel add-ins as front-ends for their BI solutions. So, if you want to create a dashboard you should consider Excel, since it really offers better functionalities than many other applications for a fraction of the cost and development time. I know that Excel is not a one-size-fits-all solution, but first you should be sure that your requirements are not met by Excel. Let me share with you some random tips from my experience with the Demographic Dashboard.
But, shouldn’t I just ask my IT to create the dashboard?
This is a fact: many IT departments hate Excel. The IT spends millions in BI solutions and users keep using Excel. Why? Because they know it, they like it, they feel in control and can do what ever they want with the data. Ask your BI manager to replicate the image above using an expensive BI solution and he’ll come back six month later with something you didn’t ask for, to answer a need you don’t have anymore (I know, I’m oversimplifying…). Do you know Master Foo Defines Enterprise Data?
1. Go to the point, solve a business need
So, you have your idea for a dashboard, you’ve discuss the project it with the users (right?) and you are ready. But where to start? Remember this: a graph, a table, the entire dashboard, are merely instrumental to solve a business need. It’s about insights, not about data, not about design.
2. Don’t use formulas
Yes, I know, this is Excel, and it is supposed to have formulas. What I am telling you is that you should aim at minimizing the number of independent formulas, and this should be a fundamental constraint to your global strategy. Too often I see Excel used as a database application. It is not, it is a spreadsheet (not everyone finds this obvious).
Over the years I had my share of “spreadsheet hell”: a lookup formula in the middle of nowhere would reference a wrong range for no apparent reason. An update cycle adds a new column and suddenly there are errors all over the place. You leave the project for a week and when you come back you don’t know what all those formulas mean. Even if everything goes smoothly the auditing dep wants to trace every single result.
But how do you minimize the use of formulas? If your data table resides in an Excel sheet you’ll have to rely heavily on lookup formulas, and that’s one of the highways to spreadsheet hell. Instead, get the data from an external source (access, OLAP cube…) and bring data into Excel. Calculations should be performed at the source. After removing all the formulas you can, the remaining should be as clear as possible.
3. Abuse Pivot Tables
Every object (graph, table) in the Demographic Dashboard is linked to a pivot table. Let me give you an example. One of the charts shows population growth over the years, using 1996 as reference. Pivot tables can calculate that directly, I don’t need to add a new layer of complexity by using formulas (to calculate the actual values and look up formulas to get them).
The population table has 200,000 records, so I coundn’d fit into the Excel limit of 65 thousand rows (yes, that’s changed in Excel 2007, but it is debatable if a table with a million rows in a spreadsheet application can be considered good practice). By using a pivot table I can overcome that limit.
4. Use named ranges
To be able to use self-document formulas (“=sales-costs” is much simpler to understand than “=$D$42-$F$55″) is one of several uses of named ranges. But they are also the building blocks of interaction with the user and they make your Excel dashboard more robust.
5. Use as many sheets as you need, or more
You don’t have to pay for each additional sheet you use in a workbook, so use as many as you need. Each cell in your dashboard report sheet should point to some other sheet where you actually perform the calculations. You should have at least three groups of sheets: a sheet with the dashboard report itself, sheets with the base data and other group with supporting data, definitions, parameters, etc. Add also a glossary sheet and a help sheet.
6. Use autoshapes as placeholders
Once you know what you need, start playing with the dashboard sheet. Use autoshapes to test alternative layouts or, better yet, use real objects (charts, tables…) linked to some dummy data.
7. Get rid of junk
There are two ways to wow your users: by designing a dashboard that actually answer needs, or by planting gauges and pie charts all over the place (this one can guarantee you a promotion in some dubious workplaces, but it will not help you in the long run). In the series on Xcelsius Dashboards you can see how difficult is to create something beyond the most basic and irrelevant charts.
So, get rid of Excel defaults (take a look at this before/after example) and just try to make your dashboard as clean and clear as possible. You’ll find many tips around here to improve your charts, so I’ll not repeat myself.
8. Do you really need that extra-large chart?
Charts are usually larger than they should. What it really matters in a chart is the pattern, not the individual values, and that can be seen even with a very small chart.
9. Implement some level of interaction
A dashboard is not an exploratory tool, is something that should give you a clear picture of what is going on. But I believe that at least a basic level of interactions should be provided. User like to play with the tools and can they learn a lot more than just looking at some static image.
10. Document your work
Please, please, structure and document your workbook. Excel is a very flexible environment, but with flexibility comes responsibility… I am not a very organized person myself, but from time to time I try the tourist point of view: I pretend I never saw that file in my life and I’ll try to understand it. If I can’t or takes me too long, either I must redesign it or write a document that explains the basic structure and flow.
Bonus tip: there is always something missing…
Once you have a prototype, user will come up with new ideas. Some of them can be implemented, others will ruin your project and if you accept them you’ll have to restart from scratch. So, make sure the specifications are understood and approved and the consequences of a radical change are clear.
This is far too incomplete, but I’ll try to improve it. Will you help? Do you have good tips specific to the design of Excel dashboards? Please share them in the comments.
{ 8 comments }
Do you prefer the full report:
For Tufte’s fans, Minard’s map plays a central role in Tufte’s iconography, and the way he praises it (“best statistical graphic ever”) is quoted endlessly (974 results in Google as of today, to be precise). Tufte discussed The Map in his first book (The Visual Display of Quantitative Information) and in Beautiful Evidence he uses it to illustrate six Fundamental Principles of Analytical Design:
But, of course, they violate Seth Godin’s Principle of Make a Point in Two Seconds for Lazy People (here, about minute 17:30):
…this is one of the worse graphs ever made. He [Tufte]’s very happy because it shows five different pieces of information on three axis and if you study it for fifteen minutes it really is worth a thousand words. I don’t think that’s what graphs are for. I think they try to make a point in two seconds for people who are too lazy to read the forty words underneath. And to make me spend fifteen minutes studying it doesn’t make sense.(…) The kind of person he wants to reach they want to read a complicate, difficult to understand graph and get the satisfaction of figuring it out, because then they get it…
In this post he uses pie charts to further illustrate his principle:
Pie charts are a great example of how people go wrong. [First pie chart, eight slices] It’s accurate. It shows more than a half a dozen places that traffic come from. It’s also useless. It’s ungrokable. It doesn’t have a point. [Second pie chart, three slices, one of them exploded] Here’s the same data, grouped to make a point. “We get our traffic from three sources, one dominates the other two, but only one of them is under our control in terms of our ability to scale it directly. So let’s talk about how we grow that slice.”
Godin is not alone. Minard’s map also violates Kosslyn’s Principle of Capacity Limitations and Principle of Compatibility:
The hazards of not respecting the limitations of human mental processes are nicely illustrated – ironically – by one of Tufte’s favorite graphics. (…) This display has never captivated me for the simple reason that given human processing limitations – I needed several minutes to figure it out. (…) I can’t agree that this is an effective way to communicate; the display doesn’t present the facts so that they’re clear or easily absorbed. If you are in the mood, you may enjoy taking the time to study the display for the fun of solving a puzzle, pondering intricate details, or appreciating the graphic devices employed. But if you want the facts and want them in a clear, easily understood way, this display is not the solution.
So, in order to design a better graph, Kosslyn proposes a new design that could take advantage of the Principle of Salience, Principle of Relevance, Principle of Perceptual Organization and Principle of Discriminability e makes a better use of the Principle of Compatibility. The end result doesn’t seem much better than some revisions of Minard in Michael Friendly’s Gallery.
(By the way, I’m also posting a series on design principles for better charts…)
I find all this slightly absurd.
The divide here is, of course, the level of detail you need or are prepared to accept. A good/bad chart is not defined by the time it takes to read it, it is defined by the insights you get from it and how efficiently it tells the story. Minard’s map is much better at this than the typical dashboard or presentation slide we are used to, a War and Peace in a single image against a boy-meets-girl-boy-loses-girl-boy-gets-girl plot in fifty slides.
Tufte says “to clarify, add detail”, but detail is a dangerous thing. It is the detail you choose that defines how you interpret reality, and it exposes your biases. Minard adds detail to explicitly link deaths to cold temperatures during retreat, but around 75% of men died on the way to Moscow and he doesn’t tell us why. You can seriously undermine your analysis by adding detail to the wrong places.
No details for Godin, please. He contradicts himself, but I suspect he couldn’t care less. He wants less data and a chart that makes a point in two seconds. But then he needs “to talk about how we grow that slice” and that means more data. Wouldn’t it be nice to have in the same chart something that helps him? Of course, that would require more than two seconds to read the entire chart…
At the end of the day, what really matters is how you manage your data. If your skills are poor, your charts will always reflect that, even if you are a Tufte fan and no matter how much detail you add…
So, do you believe that this is “the best statistical chart ever” or that’s something that we shouldn’t take too seriously?
{ 8 comments }
I know, I know, no one likes pie charts, but I can’t ignore them. A pie chart compares proportions but it is of limited use: either the data is too complex and a pie chart can’t handle it, or it is too simple and you should just use a table. So, the first tip should be:
I am sure you can come up with some ideas to make a better pie. Please share your receipt in the comments.
{ 3 comments }
Funny story… I was playing with domain names today, trying to register names for future projects like “exceldashboards.com”. Unfortunately, this is already taken. And who owns it? Business Objects, the makers of Crystal Xcelsius (remember my Xcelsius dashboard series?). Type exceldashboards.com (I refuse to add a link) and you’ll be redirected to the xcelsius home page where there’s a tag line that reads “steal the show”…
After mumbling things unpublishable, I typed “xcelsiusdashboards.com” and lo and behold, it was available! Seems that BO don’t believe in their own dashboard products…
Steal the show, they say…
{ 2 comments }
This is the second of 10 posts where I’m listing tips for better charts. Please take a look at the first post where the project is discussed. These are my chart formatting tips:
What are your best chart formatting tips? Please share them in the comments!
{ 5 comments }
This is the first in a series of 10 posts where I’ll suggest a (hopefully) coherent set of tips to improve our charts and, more important, to improve the way we make sense of the data. These are the planned posts:
These will be very short tips, and I’m sure some of them will require further explanation. In time, all of them will be properly linked to a more detailed post (that will keep me off the streets for a while…). This is a kind of road map for the next few months and, when finished, a (partial) table of contents for the blog.
So, general tips on charting:
It’s your turn, now. What are your best tips in this category? Please share them in the comments (if you have specific tips please save them for the next posts). I believe that Tufte’s principles (avoid chart junk, maximize data/ink ratio, high data density…) are already implicit in some of these tips.
{ 7 comments }
 |
|
 |
 |
