Is Excel a commodity, with no competitive advantage? Only if you want it to. Why? Because a vast majority of Excel users:

• Have the data analysis skills of a toddler (or less);
• Can’t go beyond Excel 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 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 Excel 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 Excel 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 Excel 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). Excel’s 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.

______________________

Post from: Jorge Camoes' Charts.

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-wire) 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.

______________________

Post from: Jorge Camoes' Charts.

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)

______________________

Post from: Jorge Camoes' Charts.

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!

______________________

Post from: Jorge Camoes' Charts.

Most managers don’t care about a better visualization of business data. As a reader puts it:

Short of locking management in a room with Tufte and Few, how do I sell management on the value of seeing things differently?

Instead of trying to sell, let’s see why the aren’t buying. Here are some reasons.

Good Charts Are For Middle-Management

Making sense of a large amount of data is a task for the middle management, while senior management only needs a couple of carefully chosen KPI.

I suspect that some middle managers secretly use good dashboards, dynamic charts, the works. If they are doing a good job, their reports for the senior management are filtering out all the less relevant data and now they can focus on what is important: making a good impression. That’s why middle managers use charts for illustration purposes only, and PowerPoint (low resolution, animation effects, 3D and textures) is the perfect tool.

This is also why top managers don’t really care about charts. They like to see some color in a report, but little knowledge (or none at all) derives from the charts. Each new 3D chart reinforces their perception that charts are pretty but fundamentally useless in the decision-making process.

This is a gross simplification, naturally. I just want to emphasize that:

• impression management should be taken into account when discussing real-world business visualization;
• upper managers need less (but more focused/filtered) data than middle managers;
• upper management can hardly evaluate the role of charts because they don’t use them in their decision making processes.

I strongly believe that interaction is a critical feature when creating charts and dashboards, but top management needs answers, not tools to explore the data. When designing an executive dashboard you must know who will use it, and how. Middle managers will be please to know that they can select different competitors from a list, but top managers want you to tell them who the competition is. Corolary: know your users.

Show me the Numbers

A piece of advise: display a label like “12,893,239.873″ on the top of a column in a column chart and your managers will sleep much better. To you, it may seem a useless precision. To them, it brings a priceless sense of security and “being in control”. Tip: try to find the optimal rounding digit that makes your manager look more relaxed (extra points is he/she starts to levitate).

Seriously, a chart is not a table, and it shouldn’t be treated as one (this is one of many misconceptions about charts). But you can display the exact value of some relevant data points, provided it doesn’t interfere with the patterns. If that’s not possible, add a table below the chart or, better yet, link the chart to the underlying table. With a little VBA you can use a “mouse over” event in PowerPoint to show/hide the table.

Iliterate Inertia

Let’s face it: most people are unaware of our little knowledge field of information visualization. They don’t learn about it in school, they have a bad addiction to the wrong role models (the media), they are exposed on a daily basis to ugly and stupid defaults (in Excel and PowerPoint) and corporate culture isn’t helpful. Neither inertia.

So, do you have a better answer? Please share it in the comments.

Photo credit: Serena TH

______________________

Post from: Jorge Camoes' Charts.