Support Analytics Blog

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!

In the December 10, 2007 copy of Businessweek, I came across the horizontal bar chart below. Fundamentally, it depicts employee versus employer annual percent change in health insurance costs from 2004 to 2007. As you can quickly see, the horizontal bar chart is lacking in effectiveness unless you tilt your head ninety degrees to your right. So, I added a few of my own that really only took a few minutes to create.

Businessweek version:

Here is my Excel version using approximate values.

Here is my Xcelsius version with approximate values.

What I found interesting is that when you adjust the size of the graph, you get a more dramatic slope in both lines between 2006 and 2007. I wouldn’t recommend changing the size to maximize your theory or objective. Also, I do not like that the y-axis starts at 2% instead of 0% in the Xcelsius version. (Recommended reading: How to Lie With Statistics)

Below is my version using Open Office , which is an open-source (free) project that contains most of the products commonly found in the Microsoft Office Suite.

Finally, below is my version using Google Docs, which had minimal formatting options that I could find. I’m not even going to post the Many-Eyes version due to its lack of formatting. If you want to see it click here .

My preference is either the Excel or Xcelsius version, which illustrate the 4-year trend much better than the horizontal bar chart. Both took about the same amount of time to fine-tune. If I was limited on budget and didn’t want to spend any money, I would go with Open Office over Google Docs. There are many more formatting options in Open Office that help to create an effective data visualization. The only drawback is the amount of manipulation it takes to get from the default graph to the ones shown above. Both Excel and Xcelsius default to a horrid looking graph that I wouldn’t recommend using (both shown below).

Default Excel Chart:

Default Xcelsius Chart:

Which one(s) do you like the best? Would you suggest another option not found here?

Just the other day, I received the latest copy of the Fidelity Investor’s Quarterly magazine in the mail.  You can see the same chart below if you click on the link and scroll to page – P2.  After dinner, I was reading through the articles when I happened upon this data visualization (shown below).  At first, I was drawn to the amount of pie charts that fit inside one line graph.  It’s no secret that I avoid pie charts like the plague.  However, I found myself thinking that this was a pretty crafty use of pies to show the parts of the whole that made up each asset allocation mix. 

In retrospect, I’m almost ashamed that my first thought was that this may be a good use for pie charts.  That is until I started trying to figure out which shade of green matched to bonds and which to cash.  I found that as my eyes moved across the line graph towards aggressive, I quickly forgot the color for bonds and domestic stocks.  The cross reference between the legend and pie charts while trying to follow the line graph was not working too well.  Also, don’t forget that the more you move away from zero on the x-axis and y-axis, the more risk and return are expected.

Feeling frustrated, yet challenged, my brain started to churn through designs that would work better for this given data set.  My first thought was, keep the line graph and add a bar chart for the mix of investments making up the allocation matrix.  A stacked bar would work just fine.  Then I recalled a recent post I wrote detailing how ineffective stacked bar charts are for making a comparison. 

My next option was to just add a simple 4-row table below the line graph that plainly listed the percentages for the four different investment options.  That would have worked, but I knew there must be something more effective.  Then the light bulb went off.  I remember seeing a similar example on Stephen Few’s website under the examples section found here.

Finally, I found a better alternative and off to Excel I went.  Below, you will see my version of Stephen’s design with a few minor changes using this data.  This design, using bar charts instead of pie charts, makes it much easier and faster to compare the six different asset allocation strategies.  Some of the formatting is a little bit off due to converting the Excel workbook to a .jpeg file.  Since some of the numbers are a little hard to read, I have included a link here to the Excel version.  Just when I think there may be room for pie, I quickly dismiss the notion.