Support Analytics Blog

There is a very interesting discussion going on that Jorge Camoes started on his blog, Charts.  The discussion is in regards to Edward Tufte principals and business charts or data visualizations.  From that post, Jon Peltier chimed in and provided his insights.  These two discussions are centered on implementing Tufte’s design principals in the corporate [business] sector. 

I am introducing a third piece to the discussion that I think may help.  In reading two of Stephen Few’s books, I’ve seen references to Tufte’s work, which dates back to the 80s.  Personally, I think Tufte’s book, TVDoQI is one of the most influential books I have on data visualization.  I think Stephen’s upcoming book may write a new chapter on data visualization for analysis that will help bridge the gap between theory and practice in the business world.  I would be willing to bet that this book will be the next staple in the library of anyone involved in data visualization and analysis.

What I really enjoy about Few’s books is that they are very applicable to the business world and present data in a simple and intuitive way.  I first got wind of this book back in November of 2007, when Stephen and I had a brief conversation.  Ever since that Friday in November, I have been anxious to see it released.  The posts by Jon and Jorge, along with a tweet via Twitter reminded me that the release date should be near.

Right now you can pre-order his new hardcover book on Amazon for $29. Its release date is scheduled for 4/1/2009.  I personally think it will be worth every penny.

Now You See It: Simple Visualization Techniques for Quantitative Analysis

Book Description per Amazon:

"This companion to Show Me the Numbers teaches the fundamental principles and practices of quantitative data analysis. Employing a methodology that is primarily learning by example and “thinking with our eyes,” this manual features graphs and practical analytical techniques that can be applied to a broad range of data analysis tools—including the most commonly used Microsoft Excel. This approach is particularly valuable to those who need to make sense of quantitative business data by discerning meaningful patterns, trends, relationships, and exceptions that reveal business performance, potential problems and opportunities, and hints about the future. It provides practical skills that are useful to managers at all levels and to those interested in keeping a keen eye on their business." [Amazon]

What do you think?  Is this book going to be worth the hype or another book that’ll soon be forgotten?

Other books worth a look by Stephen Few:

1. Show Me The Numbers: Designing Tables and Graphs to Enlighten, Stephen Few
2. Information Dashboard Design: The Effective Visual Communication of Data, Stephen Few

There are referral links within this post to Amazon. However, there is no endorsement from Stephen Few for this post or any reference to his books.

[source]

The chart above has two different sections built into one column chart.  The first set of columns represent the S&P500.  The second set represents the summation of the rest of the columns, which are highlighted within the gray box.  I think this is better than a drill-in feature because all of the data can be seen at once.

You can easily add this type of highlighting to your Excel 2003 charts by using the drawing toolbar.  In the drawing toolbar, select the autoshape under basic shapes called rounded rectangle.  Place the box on the section of data you want to highlight.  Then right-click and select ‘format autoshape’ (or just double-click on the box).  On the color and lines tab, set the transparency to around 85% with the color gray.   It’s that simple.  You can use whatever color and transparency you want to customize the chart to your liking.

This feature is really just a finishing touch to a chart.  If you resize the chart, add or remove data, the box will not resize with the chart.  I’m sure there is an automated way to keep it locked to the chart, but it only takes a few seconds to move or resize the box.

This is some good humor if you are an Excel user!

If excel were a car…

• It would crash two or three times per day for no apparent reason. The driver is often hurt, but the car itself receives no permanent damage. You’d just accept this fact, restart the car, and begin your trip again.
• Occasionally, your car would fail to restart after a crash, and you’d have to reinstall the engine.For some strange reason, you’d just accept this too.
• You would be forced to buy a new model every 18 months, and your old model would have no resale value. Each new model would be bigger that the previous one, require more gas, and would operate differently. Furthermore, parts from the old car would not be interchangeable with the new car.
• You could call a special phone number when you had a problem. The phone would be staffed by people who know less about your car than you do.
• There would be a special Macintosh model, powered by the sun. However, it would only run on 5 percent of the roads and require different driving skills.
• You would have to spend additional money to buy the operating manuals.
• The oil, engine, gas and alternator warning lights would be replaced by a single warning light: "This car has performed an illegal operation."
• Before engaging, the airbag system would display a message, "Are you sure?"
• Every time you looked under the hood, an obnoxious cartoon character would appear and ask if you need help. No matter how many time you refused help, it would keep appearing.
• A special feature would let you automatically record the route for a particular trip, so you could repeat the trip automatically later on. However, after repeating the trip you always end up at a different location.  Source

Almost a year ago now, I wrote a post on the topic of Harvey Balls and how they can be used as a qualitative analysis tool.  I have seen them used primarily in Consumer Reports and also in many marketing departments.  Shown below is an example of the five scale Harvey Ball font that can be used in analysis.

The problem with this font is that it is not included in the default Excel package.  In my original post, I included a link to a site that provided this font, which can be added to the default Excel fonts.  Adding a new font isn’t that hard, but it does take an effort.  The downside of this font is that it is a derivative of pie charts, which have shown to be ineffective in data visualization.

Chandoo at Pointy Haired Dilbert just wrote a post describing how to use Wingdings2 to accomplish the same type of visual.  Also, he included a formula to automate the process when you have a table of data that you want to visualize.

So a table like this can easily become…

this Spot Matrix chart:

Using the Wingdings2 font:

Check out Chandoo’s post to see the formula to automate this process.

I think there are some limitations to this type of data visualization because it can be hard to identify/quantify the variance between two coordinates in the table.  For example, can you tell the difference between Option 2 – Availability and Scalability or option 2 and option 4 Availability using the spot matrix chart above?  It’s very difficult.  I think the use of a table with conditional formatting can be a more effective tool for analysis.  A table wouldn’t pass the pretty chart test, but would be more effective.

Suggested Reading:

The Visual Display of Quantitative Information, 2nd edition

Related – Excel Blackbelt Review – Part 1

This isn’t much of an analysis after the part one review.  To be quite honest, the second half of this book is not really up my alley and would not be the best use of my limited time.

I think the first 6 chapters were beneficial and helped me in a few different areas.  I got through chapter 6, which I thought was really good and got me thinking about trying to use Excel’s Solver more.  Here are the chapters that I didn’t get to so you can judge for yourself if this text is for you.

Chapter 7 – Complex Optimization

Chapter 8 – Controlled Simulation Analysis

Chapter 9 – Scenario Generation and Optimization

Chapter 10 – Visualizing Complex Analytical Dynamics

Chapter 11 – VB Editing and Code Development

Chapter 12 – Automating Application Calls

Chapter 13 – Guided and User-Friendly Interfaces

When skimming the last chapters, I found them to cover very scientific topics, functionality and practices.  The chapters were not geared towards the everyday business analyst in the corporate sector.  Even chapter 10 on analytical visualizations was too scientific for me to really get much use from.  If you are in engineering, heavy statistics or science, it may be helpful.

As I stated in the previous review, I didn’t like the fact that this book is about Excel, but many of the sections discuss other software products like, MapPoint, XLStat and RISKOptimizer, which are not part of Excel.  A more accurate title would not have included the word "Excel" and just left it as "A Guide to Decision Support Designs".

Click here to see a few other reviews of this book at Amazon.com.

This is by no means an all-inclusive list of Excel add-ons.  If it were, the list would probably be in the thousands.  Here is Support Analytics’ short list of those we like and find very useful to data analysis and visualization in no particular order. 

1. PTS
   1. Waterfall Chart Utility
   2. Chart Image Exporter
2. DataPig 
   1. Excel Explosion (amazing tool)  
   2. Custom Button Builder
3. JMT – Excel Utilities
4. BonaVista Systems
   1. MicroCharts
   2. MicroCharts Chart Tamer (Coming soon)
   3. Parameter link
5. Juice Analytics - Clean Charts (hard to find)
6. J-Walk - Chart Tools
7. ExcelUser - Bullet Charts (not an add-on, just tutorial)
8. Bissantz - SparkMaker
9. Microsoft - Treemapper
10. AppsPro - XY Chart Labeler

Best of all, I believe there is a free version for each of the ones above.

I just scratched the surface with my list above.  If there are any utilities you use or find helpful, please share them here by submitting a comment with a link or emailing me.  I hope you find these utilities as valuable as I do.  A special thanks goes to each of the developers who made these tools available.

Francisco, on the XLCubed blog, announced last week that BonaVista Systems is teaming up with Stephen Few of Perceptual Edge to develop a new Excel add-in product called Excel Chart Tamer. 

Chart Tamer will do the following:

Limit the library of chart types to those that really work, thereby reducing the complexity of choosing appropriate charts

• Revise the formatting defaults of the charts to present data clearly
• Restrict chart formatting options to those that really work
• Revise the color palette to encourage the effective use of color in charts
• Provide a new interface for selecting the appropriate chart type, which will guide the user to an effective choice based on the nature of the data and purpose of the chart

Chart Tamer will work with all versions of Excel from Excel 2000 on. (source)

This is excellent news and really made my week.  Bringing the expertise of Stephen together with a company that understands data visualization and analytics using add-in products for Excel is definitely the way to go.  It’s too bad Business Objects hasn’t caught on to this outlandish and intricate concept.  Sorry, I hate to digress…  If Excel Chart Tamer is anything like MicroCharts by BonaVista Systems, I think it will be a huge success.  According to Francisco, they are expecting to have a beta version available in late October.  Stay tuned!

Recently, I received a copy of Excel Blackbelt and blogged about my excitement surrounding the book in a previous post.  As I write this, I am roughly half way through the book and am looking forward to finishing it and writing part 2 of the review.  Below are my comments on the first five chapters of the book.

Chapter 1 - Chapter one is basically a foundation chapter that sets the stage for the book.  The chapter does contain five excellent principals from Edward Tufte’s Beautiful Evidence book.  I found it interesting that that author makes a point at the end of the chapter to state that the book is not:

• An advanced programming guide
• Statistics text
• Single source dictionary of all things Excel

He goes on to state that the book “is intended to be a guide for the sorcerer’s apprentice – for those professionals who want to demonstrate their own genius, and need only the right coaching”…etc.  Someone could read this as, it covers a little bit of everything and not too much of anything.  I guess it can be extremely difficult to write a book for an audience with a wide range of skills.

The last sentence is very fitting and something I believe exists.  “It’s time to shatter the wall between the untouchable programmer and the professional in need”.  So very true! 

Chapter 2 - Contains very basic principals and a few more advanced concepts towards the end.  Basic concepts like, formatting in Excel, how cells work, ranges, worksheets, comments, links and functions.  More advanced concepts are: conditional formatting, VLOOKUP, OFFSET and MATCH.  At this point, the author introduces practice problems at the end of the chapters.  To me, this addition to the book seems to make it into a supplemental text for academia.  I can relate to this chapter because I use the functions previously stated on a daily basis.

Chapter 3 – This chapter starts with an overview of importing a text file to Excel, which I think is extremely important because I also use this often.  The next part that covers integrating Excel with MS MapPoint somewhat annoyed me.  I have used MapPoint and am familiar with its functionality and value.  What annoyed me was that Excel doesn’t come with MapPoint as it is a stand alone product, which isn’t in the MS Office suite.  Also, there are better technologies out there today to integrate data and maps.  I have found MapPoint to have visualization flaws, which is a topic for another day. 

The section of the chapter that discusses online data sources and integrating or importing web data into Excel is valuable.  What caught me off guard was that the text goes from 0-80 instantly when discussing Uniformly Distributed Randoms.  I have many years of Statistics and could follow the heavy statistics principals, but think a lot of people may be lost.  Don’t get me wrong, I think the section is extremely valuable, but could have been transitioned better.

Chapter 4 - Chapter 4 starts with the basics of Excel charting and especially bar charts.  I like that the author points out some tips, like the fact that Excel can sometimes pick the wrong way to transform data into a chart.  An illustration that I think is not effective is Figure 4.10.  This Figure shows two firms’ inventory/sales by quarter for four years.  I would never show this data in a dual series column chart.  I would have chosen a line graph.  Another idea that I would disagree with is using a stacked bar chart due to the baseline being difference for the second series.  I have commented in previous posts about the ineffectiveness of stacked bars, which can be found using the search feature. 

The next section shows a stacked bar chart with a background image of money on page 73.  Unless very (very) subtle and for a good reason, I would never use a background image.  Something that I think is worthwhile to point out is that it would greatly enhance the text if the pages or at least charts/images were in color. 

Pages 76 and 77 discuss 3-D plots, which can be very tricky (ineffective) for data visualization.  Also, the author gives the reader tips on how to upload images to use in a scatter plot instead of circles, which I would question.  The section on pivot tables is pretty good and I definitely can relate to the author regarding his comments on pivot charts being limited. 

Chapter 5 - Chapter 5 introduces the term Heuristics, which refers to “codified approaches to developing ideas/decisions/solutions”.  In simple terms, this chapter discusses ways to improve decision-making through coding.  This was an important chapter for me because this concept is something I practice regularly.  Some of the concepts in this chapter become very advanced for the average user.  Concepts and tools like, PCA (Principle Components Analysis), XLStat (an Excel statistical analysis add-on that runs about $500), P-Level, Z-Score, and Multidimensional Bins and Splits may not be common with many readers. 

Other concepts that are discussed are MIN/MAX/STDEV/HLOOKUP (also VLOOKUP), which are extremely useful and I use all the time.  I see this chapter as only useful for those that need to do heavy statistical analysis and not really for everyday Excel or spreadsheet users.  Good concepts in theory or in a text book, but not that practical in the corporate world.  I also understand the difficulty in being able to please everyone.

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

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