From the monthly archives:

June 2009

Are Excel Charts Hurting Your Business? 10 Mistakes You Should Avoid.

by Jorge

Are Excel charts, and Excel in general, a commodity, with no competitive advantage? Only if you want it to. Why? Because a vast majority of users:

  • Have the data analysis skills of a toddler (or less);
  • Can’t go beyond chart defaults;
  • Functions? They know how to click the SUM() button;
  • Don’t know what a dynamic chart is;
  • Think pivot tables are too complex;
  • VBA? No way!

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.

{ 11 comments }

Charts: Monthly Unemployment Rates by State 1976-2009

by Jorge

Here are two ways to display a relatively large dataset, montly unemployment rates by state since 1976. The first one is perfect to see the overall patterns, the range from the lowest to the highest, the outliers and the slopes. An interactive version would allow the user to highlight specific series.

A small-multiple version allows the user to focus on specific states, compare them to the normal band, etc. States are ranked by labor force size and, as you can see, in the first row seven out of ten are above the US average in April. In the last row, only one is above the US average. You can also see that Michigan was not well (unemployment-wise) long before the current crisis, or a spike in Luisiana (Katrina). It pays to study this chart carefully.

Bottom line: try to see the same data from different angles. There will always be semething interesting to find.

What do you think? How would you improve these charts? Would you use a different display? Share it in the comments! (here is the data file)

Update: I usually stay away from Excel’s surface charts, but I’d like to add this one:

Also check Michael’s Horizon chart.

{ 17 comments }

Just Another Stupid Bar Chart

by Jorge

stupid-bar-chart

Human creativity is virtually limitless. But:

  • You don’t vary color by data point.
  • You don’t force the eyes to a pendular movement if you can avoid it.
  • You don’t use a legend when you can use axis labels.
  • You can’t have a residual category that large.

Bloggers don’t seem to learn, even with a good teacher.

(Bar chart published in the ReadWriteWeb)

{ 0 comments }

Will you Help Me Write a Data Visualization E-Book (and Win a Free Copy)?

by Jorge

I’m writing an e-book, kind of “101 Data Visualization Questions You Were Afraid to Ask”, and I’d really appreciate your help. You just have to submit your data visualization question(s) using the contact form on the right (feel free to add additional suggestions or comments). The best 101 messages win a free copy of the e-book. And there may be some surprise prizes for the best 10.

You can submit your messages until June 30. There are no specific guidelines because I’m trying to avoid my own bias. You can ask basic questions or be as creative and as challenging as you like.

This goes beyond the e-book. It’s a great opportunity for me to get feedback from you and shape the blog to your needs. So, go ahead and ask a question!

{ 4 comments }