Juice Analytics

Ask E.T.: Cleaning up Excel’s poshlust graphics

Discussion of the Chart Cleaner along with other approaches to make Excel look good.

The Universe of Discourse : Excessive precision

Humorous take on one of the ways excessive precision can creep into reports.

Data Visualization: Intelligent Dashboard Design

The third in a series of columns that feature the winners of DM Review’s 2005 data visualization competition.

Dear PPT,

I have a feeling you knew this time would come. And I have to say that it’s not you, it’s all me.

You and I have been through hundreds of presentations together and our relationship has been pretty good. Up until now. You never demanded exclusivity and I didn’t imagine I’d need to go anywhere else for my presentation needs.

Sadly, it has come to this: I need to see other applications.

I’ve started to look around and I’m finding that there are many different ways that I can share information without your homeliness (mostly in the sagging visual and functional constraints areas). I know that sounds unfair—I have my own constraints. Face it, PPT, the world has changed and you’re just not the stunner you used to be.

Why, just the other day I had to share a lot of data with a client and wanted to flip through different scenarios. I admit that I have been spending a lot of time with your sister application Excel. But it’s not what you’re thinking. Once I took out the gridlines, labeled the tabs, jacked-up the typeface, and went to full-screen, I was practically in the world of slide-ware. And Excel offers built-in tables, calculations, nice visual alignment, more flexible charting, easy interactivity, and many of your drawing features. Excel’s been around the block but still managed to keep up with the times.

You might argue that we are talking about a non-traditional presentation format. But here’s the really bad news: I’ve found myself dazzled by Keynote. That’s probably the last thing you want to hear, I know. Unspoken, I knew I should have kept away, but once I had the Mac, that little temptress consumed my thoughts.

Oh, the shocking out-of-the-box design appeal is just too much for a presenter not to fiddle with. Check out a few of your default templates:

Now take a look at what Keynote will do for me:

Oh, PPT, why did you let yourself go like this? I know, it shouldn’t be all about sizzle. And to be fair, I’m not so sure that Keynote provides the range of functionality that I’ve been accustomed to with you, PPT. But it sure does push me toward better looking presentations. You know how visual we MBAs are.

I thought back to the times I used you to merely to compile and organize information. You know I dumped your other sister Word for this task (I hope Thanksgiving wasn’t too awkward for you that year). But then I ran into OmniOutliner. What a great tool for capturing information, organizing my thoughts, and structuring a story. Here’s a look at how it can integrate different file types into one information document:

PPT, you know I used to try and juice up my presentation while I was thinking through my message. But now I know that it is far better to separate data from presentation—two distinct steps. You may have noticed that when we spend less time together, I’m off writing in a plain text editor. Massaging my thoughts before I use your presentation capabilities.

We need to redefine our relationship. I still love you for your strengths but I need more than this. I have to go elsewhere for those things that you don’t do as well.

Don’t hold this against me. Particularly in the middle of an important presentation.

Regretfully yours,
Zach

P.S. See you Wednesday if it isn’t raining.

Sunday mornings at my house:

Wife: “What do you want for dinners this week?”

Me: “Dunno. Something easy.”

Wife: “Think of something.”

Me: “Bratwursts?”

Wife: “Something real.”

…

And so it goes, every weekend. Making the weekly grocery list is one of those tedious tasks that feels like an unavoidable mallet to the brain.

My friend Cathy decided to do something about it—she built an Excel tool that helps her pick dinners and automatically build a list of ingredients. This Recipe Manager is a fine feat of Excel engineering and she has been kind enough to let us share it with our readers.

Step 1: Use the handy recipe input sheet to add new items to your list. Tip: Only add ingredients that you don’t normally stock.

Step 2: Select from drop down lists of dinner options–one for each day of the week. The dinner suggestions area at the bottom randomly selects a set of recipes to provide some fresh ideas.

Step 3: Print out your automatically generated shopping list.

Download Cathy’s Recipe Manager here. It may just free up some quality time.

Editor’s note: this post has been edited to sort the comments by time and to point to the recorded webcast.

This is the post that we updated live during Tom Davenport’s Competing on Analytics webcast on October 31. Once we get this out of our system, I promise we’ll close the week with a few killer Excel tips.

In the meantime, here’s some background reading.

10 Ways Not to Build an Analytics Based Business: “We disagree with quite a few of these points and even where we agree, we want add real-world nuance.” Includes a rather sharp discussion in the comments about the relative value of centralized vs. decentralized analytics.

Neil Raden on Competing on Analytics : Neil Raden, an experienced consultant, gives his perspective on centralization of analytics

Tom Davenport’s original Competing on Analytics Paper

—-

Commentary begins:

12:59 (Chris) Just got an email confirmation from the AMA 1 minute before the presentation. That’s “just in time.” Bad news. Meebo seems to not be working.

1:04: Apparently this is to be a book. Preorders available.

1:08: Niel Raden points out that Tom seems over his head on this topic. Feels true in the intro–”Things just seem different” Tom says. Can’t describe why.

1:09: Describing lots of “new” tools: conjoint analysis, CHAID, …. This feels more like one man’s personal journey into learning new analytic techniques rather than a change in industries.

1:12: (Zach) My take so far: none of this is news. we could probably find a presentation from 20 years ago making these points

1:13: Meebo’s back. (Chris) this historical review is killing me. I want to get to the more interesting issues of centralizing vs. decentralized analytics.

1:15: (Zach) If he was putting the spotlight on something that wasn’t well known and fully within the conventional wisdom, then I would appreciate his efforts

1:16: (Chris) “BestBuy shifts from ready-fire-aim to ready-aim-fire” on more initiatives. Of course, BestBuy didn’t call their previous strategy ready-fire-aim. I’d like to see ALL business writers be more careful with their word usements.

1:20: (Chris) Reviewing his list of top analytics competitors. The absolutely brutal counterargument to this list of companies is http://www.hiredbrains.com/Davenport_Rebuttal.htm. This shows “competing on analytics” companies have generally underperformed the market recently.

1:23: (Zach) This presentation is perfectly suited to a room through of wanna-be MBAs. It would give them a bunch of superficial anecdotes and catch-phrases. [ed. note: Zach has a MBA]

1:25: (Zach) I found a sentence I like: “Find your distinctive capability, and use analytics to support it”

1:27: (Zach/Chris): There’s a real problem with how the sample set of companies for Tom’s study is built. He’s starting with a very limited understanding of analytics, it’s a non-random sample (two of the teams are local sports teams he’s interested in), the sample is too small (only two companies are in “Stage 1″), self-reported data. Tom is far too willing to draw conclusions from this weak base.

1:33: (Chris): Talking about the need for hardware.

1:35: (Chris): From the presentation: Is your senior management committed? If yes, “go full steam ahead”, if no, “prove the value”. How do you measure commitment? Most everyone pays at least lip service to the need of using data for analytics

1:37: (Chris): “data dog”, mmm

1:38: (Jules): “If your organization prefers to make decisions by the gut…find a new CEO” ?!? It’s hard to get rid of problem employees at the lowest levels. How are you going to slot in a new CEO? [ed. note: Jules is our HR guy]

1:39: (Zach): well, Jules, if he/she doesn’t like analytics, I think that is all the ammunition you’ll need

1:40: (Chris): A ladder to heaven. Campaign management is considered “higher up” than event-based triggers. Bzzt. I think most experienced folks people would reverse those. Anyone disagree?

1:45: (Jules): This is a wonderful company where you can just tell IT to go and do the stuff you want. Do they knock it out over the weekend or are they that talented that they only work 35 hours a week? Marketing and IT have always traditionally been had a hard time seeing eye to eye.

1:46: (All): Some things he’s missing: IT challenges, getting culture-wide buy in, using the right tools, getting tools into the hands of decision-makers,

1:46: (Chris): An absolutely brutal slide appears. A X-Y graph with NO LABELS whatsoever. This is madeupware. Timestamp in the presentation: 49:18.

1:46: (Zach): last two slides are a disaster, a black hole of information

1:47: (Jules): Don’t ghostwrite charts, Tom.

1:50: (Chris): Skeleton mouse?

1:53: (Chris): Q&A time. Where are these questions coming from? Wait-a-sec: I thought this was realtime?

1:56: (Zach): “no offense to any PhDs out there, but if you were cultivating your analytical skills you probably weren’t developing your social skills” (paraphrase of Tom Davenport) Ohhhhhhh, snap!

1:58: (Jules): Do you think Anna is a bot? [ed. note: Double snap!]

1:59: (Chris): And we’re clear. Thanks guys.

1:59: (Jules): Interesting. I downloaded the wrf file and it’s timestamped Oct 25 at 5:06pm.

2:10: (Zach): Why do we have to be live when he isn’t?

prairieFyre Software, a provider of contact center solutions, has created a reporting tool that takes a table of data and encrypts it in chart form. The original numbers and trends are virtually unrecoverable. Congratulations, prairieFyre, for this exciting new approach. This may be patentable, but I’m afraid there is prior art.

Beat that, Junk Charts.

Welcome, Jules! Here are a few more sources for tech haiku. Error messages: pain rendered as poetry.

A file that big?

It might be very useful.

But now it is gone.

Dratz offers the occasional Thursday haiku.

Roads covered with ice,

Winds blowing danger my way.

Telecommute day.

Lastly, here’s another Juice haiku.

Wet October sky,

Alt E D R cleaning sees

Scrubbing fingers dance.

Human kind is doomed.

The earth looks like a grapefruit.

Segmented downfall?

(The full article is here.)

The folks at Trixie Tracker very nearly read my mind. They created an online service that helps you keep track of the daily patterns of your infant. Users enter information about nap times, feedings, and diaper activity—then have access to a variety of informative charts and graphs. “Learn more about your baby’s needs and behavior… get more sleep,” they promise. Here’s an example of their “sleep telemetry” graphic:

The idea is good…but it doesn’t cover our most pressing need as parents. Like a business, we need real-time information that will help us make game-time decisions. Is he ready for a nap? What’s going on in that diaper? Does he need to eat now? These are the answers we need to ensure a contented baby.

With these concerns in mind, I took baby analytics a step further: I developed a real-time baby dashboard with heads-up display. Using the Trixie Tracker log data as a starting point, I added a durable in-diaper sensor to capture the, err, “raw data” necessary for timely action. The final step was to attach a wearable DLP, high-lumen (indoor, outdoor usage) projection lens to baby’s outfit. Now I’m always one step ahead of an unhappy kid.

(Click for larger image)

Dear Microsoft Excel team,

In the last all hands meeting, you probably heard that I’ve been temporarily assigned as program manager of Microsoft Excel 2007. Granted this is an extraordinary action, but these are extraordinary times, my friends.

In the spirit of openness, I will tell you we will be making some specific changes to the product. We must work quickly as my time here is limited. I’m not even an Microsoft employee and I’m sure HR will have some say in the matter once they figure out what’s going on.

In my limited time we will improve one specific thing, Excel’s charting and data visualization functionality. People live and, unfortunately, die on infographics and frankly, the situation in Excel 2007 right now is deeply…busted.

We will improve the situation by working on five projects:

Project 1: Improve the defaults: Making a basic chart in Excel with default settings is easy but the results are poor from a data visualization standpoint. For instance, Excel’s default colors do not provide good contrast and nearly all Excel charts are laden with chart-junk. Let’s get out the brooms! I know many of you have read Tufte. Good infographics are within our reach. Let’s not multiply choice, but gently guide users to a limited set of good choices.

Incidentally, 3D charts will be turned off globally, although users can easily turn it back on by going to Preferences, selecting the Charting tab, opening the Advanced Charting subdialog box, finding the “Pimp my ride” sub-sub tab (it’s down there in the back, just look for it!), and choosing both the “3D” and “extra glossy” checkboxes. There may also be some permissions issues here, too.

Project 2: Provide guidance on chart types: Users choose from a large variety of chart types in Excel without any background in effective data visualization principles. As a result, users can select from charts that are known to be ineffective in displaying data. These chart types include: pie charts, stacked column charts, clustered bar charts, cone charts, pseudo 3-D charts.

Well, we’re going to solve some of that problem in Phase 1 by removing ineffective chart types. However, we can do better. We’re the intellisense guys, remember! We will intelligently introspect on selected data to present no more than three good charting choices to a user. If lots of series are selected, then we offer multi-line and parallel trellis graphs as options. If it’s a single series with just a few values, then we show bar. If it’s a lot of data with a category dimension, we can offer box charts. We can do this.

Project 3: Provide missing chart types: As the right hand taketh away (Project 1), the left hand giveth. Excel lacks a number of effective chart types including: box charts, bullet charts, dot plots, trellis displays. Tableau’s got all this advanced infovis stuff, why don’t we? This is our chance to exercise innovation and move the industry forward.

Project 4. Create a common packaging format for Excel add-ins: Mozilla Firefox provides an excellent example of how to build a lightweight and easy to use extension process. There, I said it. Let me say it again: Mozilla Firefox provides an excellent example of how to build a lightweight and easy to use extension process. Let’s all say it together. Mozilla Firefox provides an excellent example of how to build a lightweight and easy to use extension process. Whew! Feel liberated?

Let’s face it, using Excel add-ins is difficult, confusing and, at times, frightening, to users. Furthermore, add-ins from different providers are packaged differently. There’s no consistent process to add functionality to Excel.

We must make it easier for ordinary users to innovate and add features they need to Excel. A side benefit of this project will be that this will damp down our need to pack the product with every conceivable feature under the sun. What’s more, we can harvest the best of these add-ins as built-in features for future versions of Excel. That’s what Firefox has done.

Project 5: Develop a community around Excel: There are a number of practitioners providing good technical advice for Excel charting. However, even this excellent technical advice is not reaching users. A recent user group meeting in Atlantic City, New Jersey featuring several Excel luminaries got only 50 attendees!When SAS puts together a user conference, they have thousands of attendees and hundreds of papers are presented. They have it in a convention center for goodness sake.

Users don’t know what they don’t know. They don’t know that they need to keep learning and sharing what they know. We need to celebrate the luminaries among our users that help fill the inevitable gaps in our products. And we need to encourage innovation and sharing from our lead users.

That’s it. Just five things. Five things that will make Excel a better product and the world a better place. Let’s get to work. One way or another these changes must come to Excel even if I have to do them myself.

Megalomanically yours,

Chris Gemignani

Microsoft Excel 2007 Program Manager

Note: Concept cribbed from the temporarily ennobled Jonathan ’Wolf’ Rentzsch. Thanks to Kelly O’Day for indirect inspiration.