In Pareto lines on bar charts – an Excel fudge, Alex Kerin of Data Driven Consulting took data from a very badly distorted pie chart, and generated a Pareto chart. I busted the pie in Extra Distortion in a Pie Chart, but thought I’d chime in on the subject of Pareto Charts.

A Pareto Chart is a horizontal or vertical bar chart with its data sorted in descending order. The largest items (categories) in the chart are listed first for emphasis. Often a line is overlaid on the bar chart, showing cumulative sums or percentages of the total.

The Pareto Principle, named for Italian economist Vilfredo Pareto, is based on the observation that most of the effects of an action come from a small amount of the causes. This is often called the 80-20 rule, implying that 80% of the failures come from 20% of the types of defect, or that 80% of one’s sales come from 20% of one’s customers, or pretty much any 80-20 metaphor you can come up with.

The simplest Pareto chart is a plain column chart.

The percentages of each value are used as data labels. In this case, obviously the first three bars are substantially taller than any of the rest.The last four bars aren’t even as tall as the axis line is thick, so the bottom five categories can be combined into a category called “Other”.

To show the cumulative proportion of items (in this case, apps you can buy for your iPhone), a line can be added to show the percentage on a secondary axis. In Excel this is a simple column-line combination chart on two axes.

This shows that we didn’t reach 80% of the effect until about 50% of the categories were accounted for. It’s still disproportionate, but not as severely as the “80-20″ nickname would indicate.

An alternative approach plots the accumulated value line on the same scale as the individual bars, with data labels showing the cumulative percentages. In Excel it’s even simpler: a column-line combination chart on one axis.

The heights of all bars is reduced substantially, but lowering the resolution also stresses that none of the bars is a huge amount of the total.

Vertical bars seem to be more common in Pareto charts, but there are problems with this orientation. The category labels have to be listed vertically, making them hard to read. Data labels may overlap; the labels were placed in a distracting staggered layout to make them legible.

To remedy these legibility problems, the chart can be rotated. Categories and percentage labels in the following chart are perfectly legible.

There is no difference in our ability to interpret the data in this chart, compared to the first vertically oriented chart above.

As before, a line can be added to show the percentage on a secondary axis. In Excel this requires a bit of a trick, using a bar-XY combination chart on two axes. For the XY series, X is the percentage and Y indicates the order of the categories.

Again, the readability is the same for horizontally and vertically aligned charts.

Anything done vertically can also be done horizontally.

I prefer the horizontal bar charts for improved readability of the labels. In most cases I also prefer the one-axis version, where the line shows the accumulated totals on the same scale as the individual values.

148Apps recently treated us to a fancy pie chart in Apple iTunes App Store Metrics, Statistics and Numbers for iPhone Apps which demonstrated how easy it is, if you’re not satisfies with how much a 3D pie chart can distort data, to add extra distortion. If ever there was a job for Chart Busters, this is it. I originally learned about this exciting chart from Alex Kerin, in Pareto lines on bar charts – an Excel fudge.

In all of its glory, here is the original 3D not-quite-exploded pie chart.

What’s Wrong?

What’s wrong with this chart? Well, it’s a pie, and we’ve covered this ineffective visualization method before. It’s rendered in 3D, which through the magic of simulated parallax distorts the apparent sizes of the data points (i.e., the wedges). Also the legend is truncated: the last three items are left off the bottom. They’re each only 0% of the total, but you can see paper-thin wedges struggling to remain upright in the pie.

In a few moments I’ll describe the larger visualization sin. But first I’ll try to duplicate this same chart.

Read more »

Back in November I came across a new blog, called Data Driven Consulting. On this blog I found articles covering some of my favorite topics, including graphics and data visualization. I snooped around and discovered that the author of the blog, Alex Kerin, lives only 20 miles away from me here in the snow belt of Massachusetts. We got together over lunch to discuss our mutual interests, and the first outcome of our meeting is this post which Alex has prepared for the Peltier Tech blog.

Alex is founder of Data Driven Consulting, an independent consulting company that focuses on obtaining high quality data to facilitate effective business decisions. Data Driven Consulting specializes in a number of areas, including market research, dashboard and data presentation, and authoring of white papers and e-content.

One of the more anticipated functions of Excel 2010 is Microsoft’s implementation of sparklines (as defined by Edward Tufte – data intense, design-simple, word-sized graphics). Although the recent patent application made by Microsoft would seem to imply that they are the inventors of in-cell sparklines, both Edward Tufte and the software vendors who have offered Excel sparkline solutions would beg to differ.

One of these solutions, Sparklines for Excel (SFE), is favored by many, is free and open-source, and works well. There are versions for Excel 2003 (and before), and Excel 2007, with the latter version adding a new section to the ribbon. Below are screenshots of the implementations – click on these reduced images to open a new window showing the full size sparkline tabs.

Excel 14 Native Sparkline ribbon tab

Sparkline For Excel Add-In Ribbon Tab

Read more »

Kaiser of JunkCharts has posted twice in a couple days about some charts that compare the effectiveness of different profile photo topics, in terms of attracting a potential date. The article that led to the controversy is The 4 Big Myths of Profile Pictures on a site called OK Cupid. Kaiser posted first in Light entertainment, where his attitude was “who cares, really?”, but after receiving serious feedback, he revisited the charts in From light to heavy. I should note that Kaiser himself is quite a good Chart Buster.

There are a number of strange and wonderful charts on the dating site, but the one that led to this specific discussion is shown here.

Read more »

Last fall in LOESS Utility – Awesome Update I introduced the latest version of my LOESS Utility for Excel. Compared to the earlier version, this one provides the user with much more flexibility in data range selection. As with all of my utilities, I made these adjustments after using the utility for my own real analysis. I don’t know of too many companies where the programmers are also users of the same software.

Lately I’ve received a few comments and emails asking how to use the utility’s dialog. I thought it was self-explanatory, but then, as the designer I already knew how it worked. This article is intended to answer these questions.

The LOESS Utility Dialog

The dialog has seven icon-buttons, and depending on which of these has been selected, one to four range selection boxes appear in the large empty space to the right of the buttons. The icons represent the data arrangement: Blue = X Input, Red = Y Input, Green = X Output, Orange = Y Output. The labels above the icons and the labels adjacent to the range selection boxes are intended to guide the user.

Read more »

In Yikes! Another Pie Horror Show, I was showing problems with yet another pie chart. I know the criticism of pie charts getting tiresome, but I wanted to respond to a comment by Jeff Weir:

Q: What’s worse than comparing categories within a pie chart?
A: Comparing categories between 2 pie charts.

I will use an article Jeff cited to show a better way to make comparisons.

The Analysis

Jeff pointed to Why are men more affected by labour market downturns?, a study by the NZ Department of Labour. The article stated that of the 34,000 jobs lost by the NZ economy in the eight quarters ending September 2009, 80% were lost by men.

This disparity in job losses can be seen through an analysis of the jobs held by men and women. Many more men hold jobs in manufacturing and construction, sectors which are hard hit during recessions. Women on the other hand are more likely to be employed in fields such as education and health, which are largely government funded and tend to be less sensitive to economic conditions.

The Visualization

This is a well thought out analysis, but as Jeff pointed out, comparing wedge sizes in two different pie charts is not an effective way to support the argument. Their two original pie charts were huge, and not even posted on the same page, though they did show barely legible thumbnails adjacent to the text of the article. The following two charts show the breakdown of jobs by men and by women. I’ve enlarged the pit thumbnails somewhat, to improve legibility and to compare with an alternate display I’ve created below using Dot Plots.

Seasonally adjusted male employment by industry September 2009 quarter

Seasonally adjusted female employment by industry September 2009 quarter

The Alternative Visualization

I’ve made two dot plots to show the same data. Within a couple pixels, the Dot Plots (including title) are the same size as these pie charts (without title), but each Dot Plot shows twice as much information, the Dot Plots have much more legible text, and the comparisons are much easier to make within a Dot Plot than between multiple pie charts.

It would be so easy to make a few simple annotations on one of these dot plots to drive home the point of the analysis.

Dot Plots

Dot Plots have been developed as a more effective tool for categorical comparisons than bar charts, pie charts, or (gasp!) donut charts. You can read about them in several places:

• Good Graphs for Better Business by William S. Cleveland and N.I. Fisher
• Dot Plots: A Useful Alternative to Bar Charts by Naomi Robbins
• Compare Metrics by Category Using Excel Dot Plot Charts by Charley Kyd
• Excel Dot Plots by Kelly O’Day
• Dot Plots on this web site

The last three links show various techniques for creating Dot Plots in Excel. Excel doesn’t make it easy to create Dot Plots: you need to use one helper series technique or another to generate the text labels along the vertical axis. The easiest approach is probably to use a horizontal bar chart with hidden bars to supply the labels, and an XY chart to supply the data points.

To simplify the creation of Dot Plots in Microsoft Excel, I’ve developed a utility that generates a dot plot in a simple button click. The utility is a standard Excel add-in, and it adds a Dot Plot button to the Excel menu (versions 2000 through 2003) or to the Excel ribbon (versions 2007 and later). The button pops up a simple dialog for the user to select formatting options.

Excel detects a preselected data range, and remembers the Chart Lines options from the previous time you used the utility. It produces a very simple Dot Plot adjacent to the data range.

Two minutes of formatting produces charts like those I used above to support the findings of the NZ Department of Labour.

The PTS Dot Plot Utility is available from the Peltier Tech web site for a nominal fee. It can be downloaded as soon as the purchase is approved, it installs itself using the downloaded setup file, and the button appears the next time Excel is started.

Click on the icon to the left or visit PTS Dot Plot Utility to check it out.

To learn about Peltier Tech’s other utilities, visit PTS Excel Charting Utilities.

You have a chart that you make every week or every month, and you’re tired of making it from scratch every time. So you record a macro to automate the process.

When you run the macro to make your chart the next time, you get no chart. Instead you get an ugly error message.

Read more »

My friend Debra Dalgleish notified me the other day that it was National Pie Day.

I missed National Pie Day, but while reading The Real Reason Outsourcing Continues To Fail, I encountered a pie chart that gave me indigestion. It purports to show problems with inter-cultural relationships that lead to outsourcing failures.

Read more »

This is the fifth in a series of posts that shows the steps involved in building an Excel add-in. In Build an Excel Add-In 1 – Basic Routine I showed a VBA procedure that creates a regular chart from an arbitrary rectangular range, including from a pivot table. In Build an Excel Add-In 2 – Enhanced Functionality I turned modularized this routine, so it could be called from any code that passed the appropriate settings into the function as arguments. In Build an Excel Add-In 3 – Auxiliary Modular Functions I included an additional modular function which cleans up the chart created in the previous article’s code. Because this function is widely useful, it will be made more general in a future article, after this series is finished. In Build an Excel Add-In 4 – Create the Dialog I showed a simple dialog that asked the user to identify the data to plot and select a few simple options.

In this installment of the Build an Excel Add-In series, I will show a VBA procedure that ties together all of the pieces that we’ve built so far. This master procedure is really a short list of the other procedures that do the individual tasks.

Read more »

Microsoft MVPs Jon Peltier (Peltier Technical Services) and Mike Alexander (DataPig Technologies) are joining together to host our third Excel Dashboard and Visualization Boot Camp! The last two events were hugely successful, and we can’t wait to do it again.

The conference will be held on Tuesday through Thursday, April 27-29, 2010, in Frisco, Texas (just north of Dallas).

This 3-day boot camp is designed for Excel users who need to more effectively synthesize data into meaningful dashboards, charts, and visualizations. The topics presented during this boot camp will introduce you to advanced techniques that will help you build and manage better reporting mechanisms. Going beyond simple tables and charts, you will learn to:

• Synthesize data in meaningful views with advanced charting techniques
• Create dashboards that communicate and get noticed
• Create interactive dashboarding mechanisms
• Implement macro-charged reporting
• Automate the creation of PowerPoint slides directly from Excel
• Integrate external data into your reports

Read more »