I received an email out of the blue yesterday asking if its possible to put pictures in Excel comments:

I am working on a media report based on creative templates and wanted to give the end user easy access to a view of the creative and my thought was it would be neat to have the ability to build in a comment like function but with graphics.

As far as I know you can't put images in comments, but maybe we can do better. Web developers have developed a technique called Lightbox that was pioneered by Loresh Dhakar. When you click on a thumbnail the image opens full size in the center of your browser screen and the background is grayed out. Check out an example here.

Can we do the same in Excel? Absolutely. Download these two files to see how.

Excel Lightbox.xls

media.jpg (save to the root of your C:\ drive)

Here's a simple report in Excel Lightbox.

Clicking the Show button brings up the lightbox view. The image is dismissed by clicking on it.

This could be improved and extended in lots of ways. The image could be fetched from a URL. Thumbnails could be automatically generated. I'd love to see your ideas on how to extend this.

A few days ago Zach wrote about Seth Godin's take on Edward Tufte. You know you're really onto something when your first three comments include: "Seth Godin is out of his gourd and totally wrong." and "Hallelujah, Seth!!!!!" (note the five exclamation points).

Let's start with some facts:

• Godin is a provocateur. "I think this is one of the worst graphs ever made," he says about the Napoleon graph. That's hardly a well-reasoned statement—but it makes a point. Personally, I think Godin's in way over his head when talking about what graphs are for.
• Tufte is a provocateur. "This is, for example, the conclusion of sparkline analysis in Beautiful Evidence, where the idea is to make our data graphics at least operate at the resolution of good typography (say 2400 dpi).", he writes. This provocation is more subtle than his well documented aversion to PowerPoint. He's saying that a computer screen is not an effective tool for data graphics.
• We are provocateurs, too. Pitting these luminaries against each other with only a brief amount of context is a recipe for delightful blog swirl and discussion.

In a battle between provocateurs it's best to at least keep your sense of humor about you. You should also be careful and clear when defining your terms.

There are at least two categories for infographics: exploratory and explanatory. A great example of exploratory infographics is what Hans Rosling is doing with Gapminder. This shows us that we can use infographics to go on a personal journey of discovery to understand data. I choose what questions to explore and how to represent the data. Exploratory graphics can be quite complex because I maintain a thread of context in my mind as I explore the data. Animation is very useful here to help maintain context while changing dimensions.

Explanatory graphics are at best the distilled product of exploration and at worst, as Tufte often points out, a tool of deception. Explanatory graphics are often used to establish facts to guide a discussion. "We're selling more widgets than wodgets! The widget sales trend is up!". I'm sure this is what Godin is talking about: "I think you're trying to make a point in two seconds for people who are too lazy to read the 40 words underneath."

Tufte has done a great job at increasing awareness of good information display. On the other hand, he promotes graphs that are strongly tied to a specific context—a facet of the data that they are illustrating. For instance, the Minard graph is a story about the survivors of Napoleon's march. It does not directly illuminate the battles fought, or how men died, or the story of the armies that faced Napoleon, or the demographics of his army, or the strategic choices Napoleon faced. While this famous graph illustrates many dimensions, it obscures many others, and we need to be aware about this editorial judgment.

Tufte frustrates on a number of levels. He is enormously influential in business. Businesses send people to his seminars and they come back energized with the essential truthfulness of his message. Yet weeks later those principles are abandoned by the lack of practicality of his message. No one in business is going to design a graph in Adobe Illustrator as he can. They use Excel. Seldom can we spend days or weeks refining and testing a graph. The work must be done and then we move on.

Notes:

The Google Video links here jump directly to a point in the presentation. You can create direct links like this:

http://video.google.com/videoplay?docid=-4101280286098310645#17m26s

The #17m26s appended to the end of the URL jump you to 17 minutes and 26 seconds into the video.

Thanks for all the comments on the previous post, but I wanted to single out Jorge Camoes for a particularly level-headed comment. Thanks.

Some more discussion can be found at Emergent Chaos were Thomas Ptacek has posted an insightful comment.

Seth Godin's presentation at the Gel 2006 conference is a light-hearted romp through things that are "broken—illogical signs, confusing user interfaces, and flawed marketing tactics. Seth and Mark Hurst have an accompanying web site (This is broken) and a list of seven reasons why things end up broken. My favorite: "I'm not a fish" (people who create things but have no ability to put themselves in the position of the user) is at the core of our complaints about poor reporting interfaces.

My favorite part is when Seth takes on Tufte's favorite infographic, Napoleon's March to Moscow:

Tufte is really proud of this graph. He says this is the best graph ever made.

I think he is completely out of his gourd and totally wrong. I think this is one of the worst graphs ever made.

He's very happy because it shows five different pieces of information on three axes and if you study it for 15 minutes it really is worth 1000 words.

I don't think that is what graphs are for. I think you are trying to make a point in two seconds for people who are two lazy to read the forty words underneath

To make me take 15 minutes to study it doesn't make sense. And I thought about it and I was going to jump all over him, then I moved it to this section, 'cause he picked it because it is broken on purpose. For the kind of person that you want to reach, they want to read a complicated, difficult to understand graph and get the satisfaction of figuring it out.

Sometimes the best thing to do it so break it for the people you don't care about and just make it work for the people you do.

I think Seth has it just right. Personally, I can hardly resist a well-constructed infographic, but I have an unnatural interest in data. For the many business users, better to construct information displays that are simple and to the point.

P.S. In the comments, you'll find a thoughtful and heated debate about this graphic and the danger of simplifying information displays. Simon Raybould comments that we might benefit by deconstructing the Minard graphic to make it more accessible to certain audiences. With that same thought in mind, I had set out to find the underlying data for Napoleon's March. I thought it might be an interesting challenge to consider alternative ways to present this information—under the theory that different audiences require different approaches to absorb information. Here is the spreadsheet with the data—thanks to Robert Allison. It comes from this fascinating page that shows a gallery of re-constructions of this famous graphic.

[This is part two of a two part series. See the first post for resources about charting, visualization, and presentations.]

If you work with data for a living, the following sites are worth a visit (or a subscription) to learn from some of the best, most passionate practitioners. While we're at it, we are handing out our first annual Juicy Awards celebrating contributions to the analytics community. The categories are:

1. Charts and graphs
2. Information visualization
3. Presentations
4. Excel
5. Data analysis
6. Business intelligence

(Items 1, 2, and 3 were covered in part 1)

Here is an OPML file containing the finalists if you want to subscribe to these feeds (right click and save-as)

Juice Resources for Analysts.opml

***

4. Excel

The Juicy goes to Jon Peltier's Excel Page. Jon shares a wide array of detailed descriptions and tutorials on Excel features. He demonstrates a particular passion for Excel charting.

Best of the rest:

• Process Trends: Kelly O'Day, a FOJ (Friend of Juice) has put together a bunch of tips and tools to help with presenting and working in Excel.
• Daily Dose of Excel: A multi-author blog focused on advanced Excel capabilities (i.e. lots of Visual Basic) and the new Excel 2007.
• Andrew's Excel Tips: Andrew demonstrates his exceptional Excel skills in this blog.
• Tips and Tricks: A huge archive of (mostly) Excel tips.

5. Data analysis (mining, statistical, database marketing)

Winner of the Juicy is KD Nuggets. This is a data mining supercenter--not particularly pretty to look at but offering the data miner a vast directory of resources. You can subscribe to the newsletter here.

Best of the rest:

• MineThatData: An active and well-researched blog about multichannel markeing and database marketing.
• Jim Novo's Drilling Down: Not exactly a blog--but Jim does offer lots of fresh content about customer analytics.
• Data Mining Research: A new find for me, Sandro Saitta reviews books and discusses recent news and applications of data mining.

6. Business intelligence in the enterprise

Winner of the Juicy: Enterprise Data Management, a blog by the VP of Product Marketing at Fair Isaac covering the business intelligence industry and practice. James Taylor has a rare corporate blog that is informative and passionate without feeling like a pitch.

Best of the rest:

• Andy on Enterprise Software: Thoughts on data warehousing, business intelligence, and performance management from an industry insider.
• Hired Brains knowledge repository: Here is a great group of articles and white papers about business analytics by Neil Raden.
• 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.
• Data Doghouse: "Unleashing observations on the business and technology of performance management, business intelligence, and data warehousing."
• IT Toolbox nurtures a blog farm with dozens of IT-related blogs. Here is the section on business intelligence.

Just got an email from someone we work with:

I'm researching options for being able to find zip codes within a specific distance from a location. Thus far I've found a couple of desktop applications, ZIP Code Download’s Lookup GXE and Xionetic’s ZIPFind Deluxe, both for about $200.

I was curious if you had any experience or thoughts on either of those, or if you had any other product suggestions for this functionality. As an example, we’re looking to be able to define an audience consisting of contacts with ZIP codes between 20 and 30 miles from Chicago.

This is a pretty common problem. The applications referenced don't seem to bring a lot to the table. Why not stick with the tools you already know. Geocoded zip code lists aren't hard to find (the Yahoo Geocoder, for instance, lets you build your own).

So, here's an Excel-based tool that lets you enter a US address and returns zip codes within a certain number of miles of that address.

Find Zip Codes within Distance.zip

Address lookup is courtesy of the free Yahoo geocoder.

Note: This tool was updated to use the new Yahoo geocoding service.