DSA Insights

Just last week, I was reminded of the scene from The Christmas Story where the father (Darren McGavin) finally gets his "contest prize" delivered to the house.  There was so much excitement and mystery about what he had won.  Well, I shared that same type of excitement when the big brown truck delivered my copy of Excel Basics to Blackbelt: An Accelerated Guide to Decision Support Designs directly from the publisher.  This book, which reminds of a college text book, just hit the book stores on July 7th.  There are a few Excel books that I wouldn’t do without, but this book is different from the ones in my library.  This book explores the opportunities in Excel for decision support instead of Excel functions.  Throughout my career, I have created many decision support spreadsheets that helped management make more informed decisions.  I think this book will help build upon that résumé and explore new ideas or design methods.

As I get into the book, I will be sure to share my thoughts here on DSA Insights.  Some sections that caught my attention upon a quick flip through the pages were:

4.1 Value of Data Visualization

4.3 Visualizing Constraints

6.0 The Analytics of Optimization

7.0 Complex Optimization

10.0 Visualizing Complex Analytical Dynamics

Note: there is no payment or quid pro quo arrangement for these comments or review.

I just can’t seem to help myself when it comes to sharing beauties like the pie chart below with my readers.  The pie chart was found here.  Yes, it’s colorful.  Yes, it’s an example of pimping your chart.  And finally, yes, it’s extremely ineffective.  Just recreating this chart accurately was nearly impossible because of the small slices that are not even visible.

It takes entirely too long to go from legend to chart, legend to chart, legend to chart, (you get the point) to cross reference the two.  A simple bar/column chart would have been a much better choice.

Here is my version that took me longer to figure out which values referenced which slice percentages than creating the actual chart.

Suggested Reading:

The Visual Display of Quantitative Information, 2nd edition

Visualizing Data

Show Me the Numbers: Designing Tables and Graphs to Enlighten

Creating More Effective Graphs

Jon Peliter recently wrote about the differences and advantages between saving charts as .jpg or .png files.  To test Jon’s add-in and see the difference, I included two charts from my last post saved as .png and .jpg respectively.  The Excel add-in is amazing easy to use and is going to save me so much time from having to use an intermediate graphics program.

Click here to get Jon’s export chart add-in (scroll to the bottom of his post).

.PNG

.JPG

Can you see a difference?

Related:

Excel 2007 Bible

Excel 2003 Bible

Here are two charts that débuted in the June 9th issue of BusinessWeek and reminded me of a recent newsletter from Stephen Few found here.  I have noticed that they [BusinessWeek} tend to use colors that jump out at you for that WOW factor.  I may not have picked this color, but when you see all of the graphics with the same color scheme on one page, it definitely works.

Here is the Excel color palette, which contains the lime green color.   In the chart on top, the graphic designer used the top color (black) and the middle color (lime green) found below to differentiate between 2004 and 2008.  In the map image, just lime green was used to highlight some states regarding the average cost per day for a private room in a nursing home.

As I learned from Stephen Few's newsletter, an excellent expert resource regarding color [besides Stephen's article} is Cindy Brewer's website found here.

Here are a few examples regarding the use of color in a column chart.  In my opinion, the first example below is one of the biggest abuses of color in chart design.

Novice:

Often, I see colors used in charts with the sole purpose to be colorful.  A perfect example would be the rainbow column chart (below), where a different color is used for each column.

Better:

A better option for the column chart would be to use more effective colors that are all the same.

Expert:

An expert may use the same color except for the column that they are pointing out.  By using this technique, the reader's attention is immediately drawn to the value for June (black).

Best:

A line graph would be a better option for showing the Widget sales over time.  Whenever time is on the x-axis, a line graph is probably a better option unless you're trying to illustrate a certain point.  If you want to highlight a certain time period, the use of text boxes [with high transparency] can be handy.

According to the participation and statistics, the DSA Insights Post Project was a success even though the numbers of posts was less than I hoped for.  The official 1st and 2nd place winners* are:

1st Place:

Jon Peltier of Peltier Technical Services

Jon wrote the post: Changes to Charting in Excel 2007.  Congratulations to Jon!  He has selected an autographed copy of James Taylor and Neil Raden’s, Smart [enough] Systems! 

2nd Place:

Andréa Coutu of Consultant Journal

Andréa wrote the post: 5 Ways to Promote Your Business Online.  Congratulations to Andréa for winning a $20 Amazon gift card.

This project was a great learning experience and I already have some ideas on ones for the near future.  Thank you to everyone involved!  A special thanks goes to James Taylor and Stephen Few for their signed book sponsorship!

* RANDBETWEEN function in Excel was used to determine both winners

A few months ago, I wrote about an alternative way to present fractions (parts of a whole) instead of using a typical and flawed pie chart.  The first graphic below comes from BusinessWeek and the second one is DSA’s Excel created version.

Jon Peltier (Microsoft MVP) of Peltier Technical Services and PTS Blog, recently wrote me to tell me about a workbook in Excel, which contains VBA code to create something similar to our dollar visualization.  The results, which only took me a few minutes using Jon’s workbook, are below in the third graphic.  It’s a fairly useful tool to do something different for your audience.

Finally, the last graphic was created from a similar workbook in Excel by Andy Pope, Microsoft MVP.  The workbook (link below) contains instructions regarding how Andy was able to accomplish splitting the image.

In the related section at the end of this post are links to the workbooks and web sites of the creators.  Feel free to check them out!  If you are interested in how I created the DSA Insights version, feel free to contact me and I can walk you through it.

Thank you Jon and Andy!

BusinessWeek Version:

DSA Insights Excel Version:

Jon Peltier Excel Version with Easy Macro:

Andy Pope Excel Version (different Picture) with instructions:

Original Post:

Spending InfoVis, DSA Insights

Resources:

Jon’s Workbook, Jon Peltier

PTS Blog, Jon Peltier

Version of splitting a graphic, Andy Pope

Andy’s Workbook, Andy Pope

The Back of the Napkin: Solving Problems and Selling Ideas with Pictures at Amazon.com

Here’s the list of participants to date for the DSA Insights Post Project.  As more enter the project, this list will be updated.  Thank you to everyone who participated!

• Changes to Charting in Excel 2007 by Jon Peltier at Peltier Technical Services
• Weekend Minis… by Nathan Yau at FlowingData
• SPAM by Tony Rose at Support Analytics (not eligible for prize)
• 5 Ways to Promote Your Business Online by Andrea Coutu at Consultant Journal
• Analytics: No one can agree on what it means by James Taylor at Smart [enough] Systems

Get your submissions in before the deadline on Friday at midnight. 

If you are already blogging, feel free to submit a post to me for consideration in this project and to win a prize!  Click here to submit a post.  Based on the feedback I have received and the gracious offerings from some excellent Authors, there will be more DSA Projects in the future.  Plus, who doesn’t like to get free things?

During the months of April and May 2008, BonaVista Systems is running an Excel Dashboard competition. I have said before that their MicroCharts product is exceptional for dashboard design and improving presentations or Excel analytics. The new version (3) has a lot of great new features, which can be found here.

MicroCharts is the first software, to my knowledge, that has a solution for creating Sparklines in Excel without having to finagle using the old camera feature. MicroCharts is easy to use and truly a great product. For under $200, you can get this add-on for Excel. You can download MicroCharts and try it for free for 30 days.

For the last few days, I have seen many of the blogs that I read promote this competition. I have been hesitant to follow suit because of redundancy, but also believe that it would be a shame for my readers to miss out on this event.

Winners of the competition will receive:

• 1st Place - iPhone
• 2nd Place - A Stephen Few data visualization workshop
• 3rd Place - Stephen Few’s book - Information Dashboard Design: The Effective Visual Communication of Data

Some other exceptional blog/web sites that you should check out are:

• Stephen Few’s - Perceptual Edge
• Nathan Yau’s - FlowingData
• Jorge Camoes’ - Charts
• Jon Peltier’s - PTS Blog
• Andrea Coutu’s - Consultant Journal
• Andrew Vande Moere’s - Infosthetics

Microsoft Office

It’s been just over a year since the release of Microsoft Office™ 2007 (code named Office 12). The next version, code named Office 14, is rumored to be released in the first half of 2009. With only two years between the releases, how much can really change? Office 2007 had quite a few changes, especially in the look and feel of the user interface. The beta release is expected soon (first half of 2008).

According to Paul Thurrott’s site, Microsoft is estimating a 20% increase in dollars spent on research and development compared to Office 2007. I doubt they will increase the price from $400 to $480, which would be a 20% increase. In fact, I think you will find a less than stellar revenue stream from the next release. We all know the corporate world drives the sales for Office. In my opinion, without the vast corporate entrenchment, other free products would prevail.

What I found interesting is that they are going to try to bring online access to some of the Office products, like Excel in the Office 14 version. This enhancement would seem logical when compared to a competitor like Google Spreadsheets, who already provides this feature. They need this enhancement just to stay competitive. It will be interesting to see if they enhance any of the charting tools that Excel has consistently lacked.

Some free alternatives to Microsoft are: Google Docs & Spreadsheets, Zoho and Open Office.

My personal opinion is for Microsoft to spend a little more time on research and development and deliver a superior product instead of pushing out a new version every few years. Bugs like, in-cell charting should never have made it to market. Here at Support Analytics, we use the Office 2003 suite every day (mostly Excel, Word, Project, Access, PowerPoint and Outlook).

Windows™

Windows™ 7, the next version of Microsoft’s operating system (codename - Vienna ) has been rumored to be out sometime between 2009 and 2011. At first, it was leaked that Windows™ 7 could be out as early as next year. The next rumor was, don’t hold your breath until sometime in the 2011 vicinity. It’s no secret that Windows™ Vista was the second coming of the disastrous Windows™ ME, which I owned a while back. We all know that Windows™ XP will be around until the next operating system is released. I am no OS expert, but I don’t see companies throwing XP by the wayside and loading up Vista.

What’s pretty entertaining is that there is a downgrade (a.k.a. revert to an older version) to go from Vista to XP.

I have never owned a MAC computer in the many years I have been a user dating back to the ’80s when my parents bought a Commodore 64. Ah, 64kbs of memory and the old dot-matrix printer, those were the days.

I think we are starting to see Apple really capitalize on Vista’s low adoption, which brings me to my dilemma. Is now the time to go with a MAC OS? If it wasn’t for all of the software I own being PC only, I just might. Maybe a new MAC Air laptop would suppress my appetite…

Commodore 64

In a recent issue of BusinessWeek , I found the picture shown below. A simple pie chart would show this data, but this adds a certain flare that caught my eye. Maybe it only caught my eye because I live and breathe data analysis and visualization… Regardless, it’s similar to a horizontal stacked bar chart, but with more pizzazz.

I have stated before that I don’t like stacked bar charts, and I don’t. Whenever you compare more than one component over time, it becomes very ineffective. Once you get beyond the first series of data, the baseline is not the same, making a comparison difficult. However, when you only compare a few pieces of data without time on an axis, a horizontal stacked bar chart can be effective. You can visit an earlier post to get more information on stacked bar charts.

In the graphic below, there are some things I might have done differently. For example, the last 3% is a little hard to distinguish because of how small it is compared to the rest. The first and last colors are very hard to differentiate in the print version. In this picture, the colors are more defined, making the comparison easier.

I share this with you to promote more abstract thinking when it comes to presenting data without losing effectiveness. I can tell you this: if I was an Executive and someone brought me this visualization instead of a pie chart, I would be impressed!

Visualize this: you’re watching a presentation and the slides are gliding by with every imaginable abuse of PowerPoint: fifteen bullets per slide, clip-art images, data-packed charts that aren’t even visible, goofy transitions, then along comes this slide. The only thing on the slide is this picture below. All of a sudden ears perk up and slouching turns to posture only a second grade teacher could be proud of. Dare to be different, yet effective!

Without regard to any minute details or scale, I replicated this visualization using Excel, which is shown below. I literally spent about seven minutes creating this in Excel. Granted it looks a little better when the image isn’t modified to fit this blog, but you get the idea. In a future post, I may show how this is done in a screencast, which I can guarantee won’t take longer than a minute.

Stay tuned!