Juice Analytics

It’s always better to suggest a solution than criticise. Recently, we’ve slung stones at Excel’s default charts. The default colors are bad, and some of the built-in charts are “what were the smoking?” ugly.

Edward Tufte and others provide principles for making good infographics, but beating Excel’s rusty butterknife into an explanatory sword is hard. People who want to make nice looking charts waste time fixing them up. People who don’t care about making nice looking charts inflict those charts on others.

We have a solution. The “Clean Charts” tool turns hard-to-read Excel default charts into Tufte-compliant wonderwerks in a single click. Here’s what it does:

• Removes “chart-junk” (the contrast-reducing light grey background on most Excel charts, extraneous lines
• Formats the axes with easy to read numeric formats (22000 becomes “22k”)
• Changes series colors to an optimally chosen set that are designed for maximum contrast and readability
• Removes 3D from the chart. 3D charts introduce distortions that make it hard for people to understand your numbers.
• Fixes axis scaling problems.
• Fixes font and marker sizes to make them readable if you have resized your chart

To try Clean Charts and install it, download both these files into the same directory. Then open the Clean Charts Installer.xls file with macros turned on. Follow the instructions inside the installer.

CleanCharts.zip

To turn macros on, go to Tools, Macro, Security. Select Medium security level. Close the workbook and re-open it. On re-opening, when Excel gives the security warning that asks if you want to enable macros, choose “Enable Macros”.

The add-in puts a menu item in the Format menu. If you have a chart already selected it will say “Clean this chart…” otherwise, it says, “Clean all charts…”. Select the option and you’ll get a number of ways to clean and simplify the chart.

This project is offered under the MIT License.

Sometimes the best ideas are the simplest ones. Sparklines are little, word-like graphics. A sparkline can shows a single time-series or the occurance of events. The idea is that as you can pick up the gist of the data in the flick of an eye. This lets you say things like:

The New Jersey Nets have been streaky all year while the Boston Celtics have been the picture of consistency–consistent mediocrity.

A note on interpretation: green upward whiskers are wins, red down whiskers are losses. So how can ordinary business folks make these things? Until now, sparklines have been the domain of programmers and graphic artists.

Thankfully, Bissantz, a German company, had an elegant idea. The created a set of special sparkline fonts and an easy to use tool that you can use to build sparklines in Excel using their fonts. The tool looks like this.

Sample sparklines look like this:

It works in Excel and it really is fun and easy.

If you want to learn more about sparklines and see some beautiful examples; the canonical page for sparkline theory and discussion is here. Edward Tufte provides a chapter on sparklines from his newest book followed by a back an forth discussion with practitioners in the field. Lots of great examples!

Just found this (thanks instantcognition), which offers examples of poor charting design along with a discussion of how to make the charts better. Both the good and bad examples are repeatable in Excel.

This is a video created entirely using Excel bubble charts. It illustrates flows over the course of the year between two starting states and four ending states.

Click picture to view video

I want to stimulate discussion on creative charting methods using common tools, Excel or otherwise. If you have an example of a creative use of charting, let me know and we can all get a little better at illustrating information.

Zach mentioned our penchant for telling a story about customers using little data-dense pictures. Happily, this style of data visualization won “Best in Show” in DM Review’s 2005 data visualization competition. Follow this jump for an excellent example of a data dense visualization created using Tableau.

We spent the last couple of days working with a client on displaying data for real-time dashboards. It got me to thinking: Are there an implicit assumptions and mental habits that people bring to data interpretation? And if so—are there some basic practices to consider for visualizing data?

Which isn’t to say this is a right and perfect way to display any particular data; there is room both for creativity and structure. (Check out Information Aesthetics for examples of creative data visualization.) But in the world of management communication, it can’t hurt to be aware of your audiences’ ingrained assumptions. You want the smoothest path to your important points. The risk is in missing your tiny window to focus a frazzed executive’s mind on your point–and finding your carefully constructed analysis get sidetracked.

Here’s a starter list of these embedded assumptions:

1. Axes are often the last thing people look at in a chart. They expect time to progress from right to left and linear scales that start at zero. If two charts are adjacent, they will probably assume the axes and scales are the same. When it comes to the famous two-by-two consulting matrix, good things happen in the upper-right; bad things are in the lower-left. That said, I’m mystified that the famous BCG growth/share matrix’s insists on rejecting my new rule.

2. Fluff. Dressing up your display implies you aren’t comfortable with the data’s ability to stand on its own or you don’t have much to say. This can include clip art, data incorporated into pictures, and animation. USA Today is particularly good at this. Check out a couple of examples from their Snapshots section. They have less than three numbers to communicate, but fill it up with eye-catching graphics.

3. Point of focus. Most data displays have a clear point of focus for the viewer, whether the presenter intends it or not. It could be the peak in a line chart, values crossing over zero, or a sudden change in values. In a chart like this (below), your intention may be to highlight the general growth trend — but you can’t avoid the inevitable questions about the drop after 2000. You can short-circuit these off-the-topic questions with an explanatory footnote or annotation. Ask yourself: what is the main point I want the reader to get, and what else will my data presentation imply?

4. Proximity and size. Placing information close together suggests a connection. Sometimes accidental proximity can cause confusion. You might present two unrelated phenomena next to each other and the audience will automatically try to draw a connection (e.g. dogs have big teeth; teeth are good for crunchiing carrots. Audience thinks: dogs must like to crunch carrots). I just ran across Live Plasma, a great site that lets you enter a musical artist (or band, movie, director, or actor) then shows you related artists. The designers of this data visualization do a great job of building on our data display expectations by using size and proximity to show related artists.

Here’s a common problem we run into: An organization wants to understand the dynamics of their customers as they interact with marketing channels, change products, and move between active and inactive. Presenting this type of information is tricky and cumbersome, despite the light it can shed on how a business works.

In systems dynamics-speak, this is the world of “stocks and flows.” As entities move through a system, they are either in a particular stock (aka bucket, status, state of being) or flowing to another stock. By measuring the speed of flows and levels of the stocks, you can begin to understand how to manage and optimize a system.

For us, the challenge is in finding an elegant way to visualize this dynamic data. I haven’t seen an easy or established way to handle this problem. Excel isn’t very good at it (though we wrote about how it can do the job if pressed). Here are a couple more examples of ways we to tackled the problem:

• You can show stocks and flows in a simple and intuitive way if you are willing to constrain yourself to a couple snapshots in time. The graphic below was a way we displayed the inflow, outflow, and flow between products for a client. The visual language is straightforward: size of balls represents the number of customers, size of arrows shows the magnitude of the flows.

• On another project, we tried something completely different: we created a
  movie (Windows Media only) of the movement of customers into, within, and out of the business. To make the movie, we represented each customer as a point, then took daily snapshots of each customers’ “location” (with a little extra marching between locations to make the flows come alive). It was a fun way to show dynamic data, if nothing else.

These were each custom solutions. I’ve been looking around for analytical tools that address this problem. No luck. Here’s a few interesting things I found along the way:

• Visitorville is a web analytics tool that shows data in the context of a virtual city with people (site visitors) moving around between buildings (web pages).

• Information Aesthetics is a great blog to see innovative examples of data visualization. In this post, a reference to Chaomei Chen, information visualization guru and his Top Ten Unsolved Information Visualization Problems. “Number 8: paradigm shift from structures to dynamics: towards time-varying datasets, data streams & immediate trend-detection”

• Processing is “an open source programming language and environment for people who want to program images, animation, and sound.” We’ve played around with the idea of using this as a way to visualize dynamic data.

• Systems dynamic software like iThink provides mechanisms for modeling stocks and flows. In my experience, these packages are more about creating simulations rather than reporting of historical information.

Why do our sports pages look like this?

Instead of this?

Eastern Conference
Atlantic
Nets
76ers
Celtics
Raptors
Knicks
Central
Pistons
Cavaliers
Bucks
Pacers
Bulls
Southeast
Heat
Wizards
Magic
Hawks
Bobcats
Western Conference
Pacific
Suns
Clippers
Lakers
Warriors
Kings
Southwest
Spurs
Mavericks
Grizzlies
Hornets
Rockets
Northwest
Nuggets
Timberwolves
Jazz
SuperSonics
Trail Blazers

Those green and red lines are “sparklines”–a term invented, I believe, by Edward Tufte. They are little, word-size graphics that show a trend more quickly and clearly than one could describe it. In this case, each sparkline shows an NBA’s team record throughout the season; a green up bar is a win, and a red down bar is a loss.

In less space than a standard standings listing, we see the sustained excellence of the Pistons, the steadiness of the Spurs and Mavericks, the Raptors recovering from their awful start, the wheels falling off the Pacers, the mystery that is the Nets. These large multiples of small graphics recover some of the romance and drama that is a season.

For a really beautiful example of sparklines applied to sports, look to Tufte’s professional example here. If you know Python, Grig Gheorghiu has written a simple tool for generating sparklines.

There is a vast yet ignored population out there. No, not the Blue States. I’m talking about people who want to know more, make better decisions, and understand their customers better — if only they could make sense of their data. These are the analysts in mid-sized companies without big data warehousing platforms to lean on, the small business owners who want to be savvier about their customers’ needs, and the executives who are frustrated by how few of their questions can be answered.

Resources for this audience are a little thin. While there are lots of sites and blogs for the “enterprise business intelligence” community and “web analytics”, our audience is underserved. I spent a piece of my Sunday (new boy Owen in my lap) making a list of blogs that may be of use. I split them into three categories: 1) general business analytics blogs/sites; 2) Data presentation and visualization; 3) Excel resources.

General business analytics

• Open Source Analytics: Nice personal style and we share some of the same axes to grind.
• Intelligence Economy: This blog offers some nice, in-depth descriptions of companies that are using analytics to their advantage. Also provides summaries of BI reports by companies like Forrester.
• Steve Krause Blog: Written by the VP of Analytic Product, CNET Channel. An interesting read covering a wide range of current topics, including commentary on the use and misuse of data.
• Information on Demand Blogs: A new addition that covers a range of information topics such from technology trends to data services
• Hired Brains knowledge repository: Here is a great group of articles and white papers about business analytics by Neil Raden.
• Jim Novo’s Drilling Down: Despite a recent disagreement with Jim, I still think he generally gets what it takes to help businesses make sense of data. His expansive tagline says it all: “Turning Customer Data into Profits with a Spreadsheet, A Guide to Maximizing Customer Marketing ROI.” What is particularly valuable: He isn’t afraid to share his content freely. This isn’t a blog, but he does have a free newsletter.
• Sort’s Feed: Here’s one I just found — I like the cut of their jib. “Gain analytical perspective and clear insight from their marketing data.”
• BI Toolbox: “Articles about Business Intelligence Resources and How-To’s for the professionals”
• Net Intelligence A blog for professionals looking for the next level of competitive advantage. Wide-ranging posts from myths about information to intelligent decision making.
• Business Intelligence Network, David Loshin The BI network has a whole stable of bloggers–I found David’s blog the most relevant to the everyday analyst.

Data presentation and visualizatoin

• Presentation Zen: How you present your data can be as important as the analysis. We’ve written about the skill and art of presentation building. In this blog, Garr Reynolds offers tips, tricks, and examples for making great presentations.
• Information Aesthetics weblog: A daily dose of information visualization to spark your creativity.
• Stephen Few: “Thoughts about how visual representations of data and visual interaction techniques can be used in practical ways to analyze and communicate business information”
• Dash Tracker: “Keeping a watchful eye on the growing field of desktop graphical dashboard applications”. Dashboards are an overemphasized piece of a business’ analytics picture–particularly if you’re in a fast changing environment. Still, this offers a good industry overview, with particular focus on software solutions for building dashboards.
• The Dashboard Spy: “A collection of enterprise dashboard screenshots. This reference work is proudly offered as a source book to everyone involved in business dashboard design and implementation.”

Excel resources

• Process Trends: Kelly O’Day has put together a bunch of tips and tools to help with presenting and working in Excel.
• Andrew’s Excel Tips: Andrew demonstrates his exceptional Excel skills in this blog. He’s available for Excel consulting (at a reasonable rate) if you need help putting together a particularly challenging Excel tool.
• Jon Peltier, Excel MVP: Not a blog, but a nice resource for Excel tips and tricks, with a focus on charting
• Tips and Tricks A huge archive of (mostly) Excel tips.
• The JLD Excel Blog “This blog is aimed to help Excel users who have not the time or the patience to learn Excel in depth, and to share my experience with others users. You can add your comments in Spanish and in Hebrew too. See the limks in the sidebar.”
• Microsoft Excel 12: Specific discussion of what’s new in Excel 12. Did you know that the new Excel will allow 1 million rows?

Other suggestions?

Just got a comment from one of our readers: “I like what you have to say, but I had a hard time reading your post on the Excel charts, because that picture made me want to turn away.”

Well, it doesn’t have to be that bad. We put together an alternative set of default charts that are a little more pleasing to look at.

What did we do? First we picked some colors that would offer contrast, without being scarring to the eyes. Then we went on a “chartjunk” killing spree. Chartjunk is an Edward Tufte term that describes all the stuff in charts that adds no value, distracts from the data, and promotes confusion. For us, chartjunk includes the borders on the chart and around bars or columns, background colors, and even some axes. Gridlines are borderline: they offer modest value in helping a viewer see the height of bars — we typically change them to an unobtrusive light grey. It takes a lot of clicks to fix up a chart to the Juice-approved format.

Excel has a nice feature that lets you save new chart formats. It’s simple (if a little hidden): After you make a good-looking chart, click on Chart/Chart Type/Custom Types, then the “User Defined” radio button. Choose “Add” and name your new chart. Or, if you like the formats you see here, download our user defined graphs. Simply (I say that so you don’t get scared) drop this file into the folder at C:\Documents and Settings\UserName\Application Data\Microsoft\Excel. Maybe you can make a few more and send them back.

One more thing: We made just seven chart types (column, bar, line, column with line on two axes, pie, scatter, bubble). I’m convinced that this set can cover 98% of your charting needs. While Microsoft may offer up donut charts instead of pies and cones instead of columns, please don’t bite on those.