You know how useful graphs can be to to find trends and patterns in the data. But you can also use them to detect some mistakes, something that is much more difficult to do with a table.
Let me give you a real world example. Is there anything unusual in this time series, anything that stands out?
No? Are you sure? Take a closer look and you’ll see it. Too much guesswork? Well, why don’t you just use a chart?
Can you see it now? Of course you can. Try not to see it…
This is clearly an error. I just fund it in this table from the US Bureau of Census (I’ll ask them to fix it).
If you need to proofread your tables start by a quick check using a chart. You can select the entire table or a single series. Look for inconsistencies and outliers. Many outliers are simple mistakes, but sometimes there is a good reason behind them.
This kind of errors is almost impossible to avoid, but the obvious ones are easily spotted, so the next time you publish a table do yourself a favor and scan the data using a chart before hitting the publish button.
You can learn Excel and spend more time with the kids, but Ian comments that:
I have found that I have ended up doing many things that other should have done – even if it took them longer. Being great at Excel slows promotion through management ranks – delegate and show what a manager you are!
I agree with Ian and yes, I believe that too much Excel can harm your career. In fact, you shouldn’t master any tool (or be recognized as such) if you want to be a manager, and if good people management skills is what is expected from you.
Generic office applications like Excel or Word have no intrinsic value for the organization, they are just tools used by the lower ranks to help implement the corporate mission. You can be great at Excel, but if you want a promotion you must hide it, and remember the Peter Principle.
The Peter Principle
In the stock market, “past performance is not necessarily indicative of future results”. Likewise, if you excel in your current job you’ll get a promotion, but the skills that got you there may be irrelevant or even work against you in your new position. This is the well-known Peter Principle: “In a Hierarchy Every Employee Tends to Rise to His Level of Incompetence”. To avoid to reach your level on incompetence too early you should think strategically: be good enough to get promoted and start practicing in your current job the skills that you’ll need in the new position.
Become a Craftsman, Instead
Trent, over The Simple Dollar, writes that your most valuable asset is you, not your career. So, what happens when you really love what you do, want to be really good at it (a tool, a specific knowledge field), and a promotion means that you can’t do it anymore? Well, probably you must leave, join a company that actually needs your advanced skills or start your own consultancy business. Get a virtual assistant to take care of administrative matters and hire a freelancer for larger projects. Focus on your skills and buy time. Become a craftsman. There are less formal promotions, but networking is still a fundamental skill.
A Balanced View
Mastering any tool or knowledge field is a decision that you can’t take lightly. You must understand its role in the overall career/life goals.
You want to be an craftsman (in the sense above), you don’t want to leave, and you want that promotion. What do you do? Well, I’m probably the last person in the world you should ask for advice on this. In fact, I am not sure if there is a balanced view at all (yes, you can look for your dreams at a higher place). Worse of all, remain on the fence and you risk losing your dreams and your career.
Do you have an answer? Do you think you can hurt your career because you love your work?
Excel 2007 is useless. This was a shocking revelation when I tried to create the charts for last post (Chart Design: Abortion Ratios 1980-2003).
It was my first serious attempt at using Excel 2007. I failed miserably and gave up. I had to do everything in Excel 2003 and then open the file in Excel 2007 for some polishing.
I new there was some senseless options in conditional formatting (actually, it is worse than I expected, as you can see on the left chart). And Stephen Few said everything I needed to know about Excel 2007 new chart engine. And, by that time, Microsoft bought Dundas. Isn’t that enough to raise some red flags?
But I had to see it for myself. I will not talk about the ribbon. I keep it hidden, so it really doesn’t bother me. And having to learn a new interface is something that I don’t mind – let’s call it brain workout.
There are several small but frustrating things, like trying to select multiple charts as objects, but what really pissed me off was that empty macro I got, after recording some chart formatting. I couldn’t believe it and tried several times, before googling for this “error”. It isn’t one (you can compare both versions here and get more bad news, as I did).
I suspect that this is just the tip of the iceberg. And it is not the major changes in features or the new user interface. It is the small things, left behind just to annoy you.
Bottom line: stay with Excel 2003. Charts in Excel 2007 look much better due to the new rendering engine, but do you really want to buy Office 2007 just to polish some charts?
Please share your thoughts in the comments. I’d like to know if (and how) you can do productive work with Excel 2007.
Source: U.S. Census Bureau (original Excel file). The abortion ratio is defined by the number of abortions per 1,000 abortions and live births.
(Click to enlarge)
Notes:
1. We know that information visualization is all about pattern detection. But often our design choices hide relevant patterns behind the obvious one(s). Take this panel, for instance. Everyone can see the downward pattern, but what about the U-shaped pattern across age groups? You can see it, right? Well, follow the usual path and you’ll miss it.
2. A ratio (or a growth rate) is something that should always be put in the context of actual volumes or proportions. There is no “best answer” to link both dimensions but the panel displays a reasonable solution. As you can see, the abortion ratio among women less than 15 years old is very high but its proportion in the total number ob abortions is almost residual. On the other hand, the ratio in the 15-19 age group may be lower, but it is much higher than the average ratio and accounts for around 17 of the total abortions. Whenever possible, you should keep these two measures close together.
3. There are seven age groups in this data set. Put them all together in a single line chart and you’ll miss the pattern across groups, as discussed above, but you’ll also have a hard time disentangling the whole thing. Before creating the chart always ask: what is my specific question? This will help you to prioritize and create a focus-context display. If a series answers your question (need-to-have) add it to the chart and color-code it. If a series is interesting but doesn’t directly answer your question (nice-to-have), you may add it to the chart to provide context, but gray it out and delete if from the legend, if you have one.
Please let me know what you think and suggest ways to improve the panel.
[Update: Jon discusses the process of pattern discovery in RE: Abortion Ratios 1980-2003. Andreas adds several good suggestions and shows how to display the date in a more consistent small multiples chart.]
Nathan asks us Can You Improve this Mediocre Statistical Graphic?
Since there are only two series (two parties) with a obvious mirror effect, I would say it doesn’t make sense (from a chart economy point of view) to display both series. And since the 50% mark is relevant in election results, why shouldn’t we just look at the trend of one of those parties around that mark? It would help to tell a more interesting story.
So, this is my radical suggestion, with Bonavista’s sparklines:
“The percentage of counties in California that have a Democrat majority of registered voters in Presidential election years droped sharply in the last four elections and now stays well below the 50% mark ( 
). Loren ipsum….” (I have to work on a better integration of sparklines and the blog template, but you get the idea…)
It would be nicer to have more data points, but this small footprint chart conveys the essencial message. Of course you can follow the standard approaches (a line chart with both parties or a stacked bar chart). As always, it all depends on what you want to say and how you want to say it.
Seth’s blog is always interesting, and usually I agree with him and learn something. However, when he discusses charts and graphs he’s often a very light purple cow. This week he confirms that.
Godin’s The three laws of great graphs tells us that, when using graphs in presentations, we should follow these simple principles: “one story”, “no bar chars” and “motion”. Strange laws: a story with no substance, a caricature and motion without motion.
One Story per chart? No, one story per slide
“No, the reason you put a chart in a presentation is to tell a story. A single story, one story per chart. (…) There is no room for nuance here.”
Hmmm… story, no nuance. OK. But if a chart can only say “boy meets girl” isn’t there something missing?
A slide can be seen as an information unit (an “information chunk“) – that’s why Tufte writes about arranging things “adjacent in space rather than stacked in time”. It is the slide that tells the story, not the chart or any other single object. Actually, the presentation is your story – a slide is just a paragraph, a chart is just a sentence.
I can accept the idea of a nuance-free slide, but that doesn’t equal to an information-poor slide.
No bar charts – A caricature
Godin tells us that bar charts are overrated, and they should be replaced by a line/area chart (change over time) or a pie chart (comparing items at the same scale). You should use bar chart for before/after comparisons only.
I absolutely agree with Godin, you should never use bar charts – if your idea of a bar chart is similar to the one displayed in his post.
A nuance-free slide content is one thing, but different tools convey different messages, and you should be aware of those nuances. Time series 101: use bar charts to compare data points, use line charts to display trends. Categories 101: use pie charts to show proportions, use bar charts to rank data points.
Motion without motion
What Godin calls “motion” is just a silly dramatic effect for presentation purposes. “Look how fat I was”, and boom! “look how many pounds I’ve lost!” (fireworks and John Williams soundtrack). Motion can play a relevant role in presentations, but this is no motion, like a fake 3D chart is just a fake.
Bar graphs vs. pie charts
For whatever reasons, Godin decided to take a stance against bar charts. That’s ok, but his arguments are unfair (just look at the examples he uses in both blog posts). Let’s change the data in his second post and add meaningful trends to the time series:
“Trolls are where we should focus our energy.” Are you sure? In his bar chart example there is no trend, no visible pattern. “There’s data here, but no information.” Well, just remove the unnecessary data. By the way, if the slices in a pie chart are labeled you don’t need a legend…
Do you really want three laws of great graphs? Here they are:
- Tell a story;
- Remove irrelevant data;
- Choose the right messenger;
- Bonus law: let the user write the story.
[Update: several "data presentation purists" are discussing this:
I follow some of the top blogs-about-blogging and they often come up with advices that I can relate to when thinking of information visualization: simplicity, consistency, go to the point, remove clutter, tell a story…
Problem is, bloggers about blogging fail to follow their own advice when they attempt to graphically display the results of their online surveys or other quantitative data. This is something that I’ve been planning to write about because it is recurrent, but last week both Darren Rose and Chris Garret decided to offer me very good examples…
Anatomy of a bad chart
I’m afraid to say that in Darren’s chart almost everything is wrong:
- No scale: A chart is about trends and patterns, but you must give the reader at least some quantitative reference;
- Unnecessary multiple colors: If you are displaying a single series, it doesn’t make sense to vary colors by point;
- No sort: sorting the data establishes a pattern and helps the reader to immediately see the relative importance of each item;
- Unmanageable legend: since there are more data points than you can store in your working memory, the chart forces a pendular movement from the legend to the chart and back;
Pies, Arab countries and eight decimal places
Chris likes pies. 2D, 3D, it really doesn’t matter. But sometimes he gets tired of them and uses a bar chart. For very similar data and similar messages, you should use very similar charts, right? It’s called consistency (tip number 8 for a successful blog). But you don’t find consistency in these charts.
From the pie chart “Blogger Ages” and the column chart “Year Started Blogging” I infer that Chris lives in or wants to target some Arab country, since they read from right to left.
Chris infers from the survey results that “most bloggers live in the USA”. I would say that “most bloggers that [read English blogs and happen to read Chris'] live in the USA”.
And at the end you’ll find that table with eight decimal places.
Blogging, information visualization and information management
This is not about Darren or Chris or some irrelevant charts. They are great bloggers, better than I aspire to be. But why do they fail to meet their own standards when dealing with quantitative data?
Of course they would replay to me “you fail to meet your own standards when blogging”. Yes, I do. We all know the basic laws. We know they apply across several knowledge fields, but many of us only recognize them and make use of them inside our little silos.
But I would really like to see at least some decent charts and good information assessment in these top blogs. We desperately need higher numeric and graphic literacy to handle all this amount of data, and they could help to set better standards.
Wow is Beauty, Eureka is Knowledge.
Wow alone is a dumb blond, Eureka alone is a decrepit old wizard.
Great information visualization is 20% meaningful wow and 80% useful Eureka.
I love to spend time playing with my three-years old twins, and I wholeheartedly want to create for them the memories of a happy childhood. This is something that I can’t delegate, and if I don’t have the time, I’ll have to find it. They will not wait to grow up.
Stupid time wasting at work really bothers me. When you work for a company where a high percentage of people use Excel as their primary tool you inevitably find horror stories of people spending days with a task that should take minutes. Here are some examples:
- Hundreds of sums, calculated one by one, when a pivot table could give the same result in seconds;
- Two persons matching two lists, when they could use a lookup function;
- Hundreds of copies of the exactly same chart, but with different markets, sales territories or measures (a single chart with some basic interactivity would do);
(This also leads to errors and to the spreadsheet hell, but that’s another story.) I really hate to see people wasting their time this way, when they could leave earlier and go play with their kids. And it is so simple! You just have to tell yourself: “this is stupid; there must be a better way“. And usually there is. You just have to find it. Keyword: curiosity.
If you are a manager, let me share with you a management secret: you can’t imagine how inefficient a beginner Excel user can be, and you are likely to have several eternal beginners in your team. It is not a matter of training. People forget theory and examples that don’t apply to their own problems. You better hire an Excel power user. Make him/her the go-to person for Excel things. Tell this person to meet users, find inefficiencies and solve them.
I am sure you have much better inefficiency-related Excel horror stories. Do you want to share them in the comments?
