General

I have three new books, which I’ll be reading and reviewing here. Here are the books with very brief first impressions.

Guide to Microsoft Excel 2007 for Scientists and Engineers

By Bernard Liengme, Retired Professor of Chemistry and Lecturer in Information Systems St Francis Xavier University, Nova Scotia, Canada, and Microsoft Excel MVP.

This is a follow-up written explicitly for Excel 2007, but much of the book is really version-agnostic. It assumes little foreknowledge of Excel, and starts out with the preliminaries, such as entering  simple formulas and printing a worksheet.

The book proceeds to advanced topics, including VBA programming, advanced data analysis and display, and finally into modeling of physical systems.

Professional Excel Development – 2nd Edition

By Rob Bovey, Dennis Wallentin, Stephen Bullen. and John Green.

The first edition of this book was considered the seminal book on Excel development. It covered many advanced topics, in such a way that a user understood the topics and could implement them (unlike certain renowned help files).

This edition is as advanced as the first edition, and several sections have been added or enhanced. I already have sticky notes in a few chapters I need to read in more detail. There is a section on working with the Excel 2007 ribbon, and the coverage of VSTO and .Net has been changed from practically just a sidebar to several chapters, thanks to the fine efforts of Wallentin, a new author in this volume, but no stranger in the Excel blogosphere.

Now You See It: Simple Visualization Techniques for Quantitative Analysis

By Stephen Few, leading authority on practical information visualization.

Stephen Few stands out among infoviz experts in that his writings are approachable by “mere” typical users, his style is easy to follow, and his examples can be implemented by almost anyone, with almost any software package. Few backs up his assertions about visualization techniques with insights from the field of human cognitive science, not simply graphic design rules handed down from the mount. Few isn’t the biggest fan of Excel, but he acknowledges that it is the most prevalent data visualization package, and his approach can be followed in Excel.

Few starts with a brief history of visualization: “[Scottish social scientist William] Playfair invented the bar graph, was the first to use line graphs to represent change through time, and, on one of his off days, invented the pie chart.” This fluid style permeates the book, and makes it an enjoyable and informative read.

Stay Tuned

As time allows, I’ll finish these books and add more detailed reviews. I’ll also provide links to reviews by others.

I’ve been obsessed with maps since I was a kid. One of my favorite magazines is National Geographic, and my favorite issues are the ones with a large folded map. I can waste spend hours on a well-designed map. The National Geographic maps generally have a modern physical map on one side, showing geological and political details, and the reverse is often an historical perspective, showing how different cultures have interacted in the region. (I’m also interested in archaeology, both anthropological and paleontological.)

Google got into maps long after we’d been using MapQuest, taking the search analogy beyond web pages to physical locations. Google started showing aerial and satellite photographs of the mapped regions some time after Microsoft introduced us to them with its TerraServer. I don’t know whether Google is using the same data or if they’ve launched their own satellites. Google’s trip calculators became very powerful when they introduced the ability to drag points on the route to other roads, recalculating based on the new selections. And now Google Earth allows us to view information in amazing 3D virtual globe representations.

In this article I will review some work done on these visualizations, and discuss criticisms of this work.

Google Earth Visualizations

Google offers at least a couple different visualizations based on embellishments to globes. Notable are the 3D Prism Map and the 3D Proportional Symbols.

Google Earth 3D Prism Map, from 1.

Google Earth 3D Proportional Symbols, from 1.

Very pretty, aren’t they? But are they effective ways to display information?

I will concentrate my discussion on the prism maps. I’ve already blogged about the use of proportional symbols in Bad Bar Chart Practices, or Send in the Clowns. That discussion applies here, with the additional problem that the 3D versions have with varying orientation of the symbols.

All About 3D Prism Maps

This isn’t really all about these visualizations, it’s mainly about one person’s obsession with them. Bjørn Sandvik loves 3D prism maps. He writes code and blog articles relating to them.

Bjørn shows us a chloropleth, that is, a color-coded map or globe, in this case a globe showing infant mortality statistics for Africa and surrounding regions. It shows Africa pretty well, without too much distortion as the viewpoint moves from countries directly below the observer to those which are further away along the earth’s curvature.

Chloropleth: Infant Mortality in African Countries, from 2b.

If you really want to compare the numbers, you can’t go wrong with a simple bar chart.

Seeing the map helps to identify particular regions with high or low mortality rates. The best solution would be side by side map and bar chart.

Well, the map isn’t very sexy, and the bar chart is a definite turn-off, although Dr. Mark Harrower of Axis Maps [1] says that “nothing is fundamentally wrong with our proven and highly efficient planimetric thematic maps.”

To add a little excitement, Bjørn extrudes hot magma outward from the earth’s core, through a hole in the earth’s crust shaped like each country, to an altitude representing the number of infant deaths per 1000 live births in that country.

Prism: Infant Mortality in African Countries, from 2a.

Now that’s cool, I think, but it’s no longer easy to recognize the geographic boundaries, let alone determine what the relative heights of the odd-shaped prisms are.

Proportional Symbols, Warts and All

I wasn’t going to talk about proportional symbols, but Here is one that I had to share.

Warts, Pimples, and Goosebumps, from 2d.

Bjørn assures us that the dome shape allows people to judge their volumes. The problems are that (1) people are not particularly good at estimating volumes, and (2) the apparent diameters for a given volume change as the domes move further away along the curve of the globe’s surface.

UUhat in the UUorld?

A group called UUorld Inc. implores us to “Explain the world with maps”. Their maps are extruded prisms also, as shown in these maps of unemployment rate by state. I think simple bar or line charts would be as effective.

Unemployment Rate by State in 2007, from 3a.

Unemployment Rate by State in 2008, from 3a.

In another pair of charts, UUorld shows eligibility by state for certain federal tax cuts. The 3D chart does not show the data any better than the 2D, and the 2D chart doesn’t forget that Montana is a state too.

Eligibility for Tax Cuts, from 3b.

Eligibility for Tax Cuts, from 3b.

Many of the problems described below apply to the UUorld graphics, especially the occlusion of shorter blocks by taller ones. UUorld likes to animate their models while rotating them, which replaces the problem of occlusion with one of vertigo.

The Problem With Prisms

This representation has a number of problems, some of which Bjørn himself admits. According to Dr. Mark Harrower of Axis Maps [1], the approach has these limitations:

• Visual occlusion (not all of the map can be seen at once since some places hide others)
• People suck at estimating volumes, especially of complex shapes (e.g., try estimating the size of moving van you’ll need for your home)
• Mental rotation of complex shapes is extremely hard, so hard that it is often used as a measure of intelligence in IQ tests.
• Lack of a zero-line referent makes it hard to judge absolute magnitudes.
• The “fish eye lens” effect mean each prism is viewed from a different angle than its neighbors, making comparison just a little bit harder as we have to mental account for these differences in our estimates.
• It is hard to judge the height of something when you are staring directly down at it.

Dr. Harrower says that the prism charts are a prime example of chart junk:

Chart junk takes what should have been a simple-to-read graphic and makes extracting information (1) slower, (2) more difficult, and (3) more prone to reading errors, because of excessive ornamentation and unnecessary design additions—like adding a 3D effect that communicates nothing in and of itself but simply “looks cool.”

Dr. Harrower calls these charts a “technology in search of a problem”. The 3D graphical technology is very advanced and look very cool. But being cool is a poor justification for using graphical techniques which do not improve the ability to understand a chart’s data. And “nothing is fundamentally wrong with” the existing techniques, so there is no problem to be solved.

Bjørn reminds us that the 3D prisms make country comparison easier when spinning the globe.

For me, the need to spin the globe to see all of the data is a severe drawback. I want it all, and I want it now. I don’t want to have to turn over my map to see more information, and lose sight of information on the first page. It should all be visible at one time.

Bjørn gives us a flat representation instead of the globe, but he is fiercely reluctant to give up his extruded prisms.

Flattened 3D Prisms, from 2a.

This view remedies one deficiency with the 3D prisms, while retaining all others. For example, I don’t think I can see Egypt on this map.

Bjørn shares a few more prisms, which further illustrate the problems with prisms. This one shows life expectancy in countries around southern Asia.

Prism: Life Expectancy, from 2c.

Well, there’s a problem: the prism for Afghanistan is so short that it is obscured by Pakistan. No problem, just change the view, right? Let’s center the view on Afghanistan. This has the primary effect of adding a very wide border around Afghanistan.

Prism: Life Expectancy at High Viewing Elevation, from 2c.

Bjørn adds an additional redundant scale, using color to help us when we can’t judge the height of the prisms.

Prism: Life Expectancy, Color Coded, from 2c.

This merely raises the question, Why not dispense with the prism height metaphor and stick to color-coded countries? In fact, why not use a bar chart to show the values?

To these criticisms of 3D prism maps Bjørn replies, “In conclusion, country comparisons are problematic on a 3D globe, but it shouldn’t stop us from doing it!” [2a] And he insists that Virtual Globes are a good idea for thematic mapping.

So he’ll keep doing his cool thing, and Dr. Harrower will go on making readable maps.

Sources

1. Dr. Mark Harrower, Axis Maps, Virtual Globes are a seriously bad idea for thematic mapping.

2. Bjørn Sandvik

2.a. Why 3D works #1: Looking on the other side

2.b. Why 3D works #2: It’s all about prisms

2.c. Why 3D works #3: Mother Earth gives us a clue

2.d. Why 3D is not working #4: Am I sacrificing accuracy for eye candy?

3. UUorld, Inc.

3.a. US Unemployment in 2009 – More Job Losses in Production

3.b. The Obama Stimulus Package, State by State

In Callout Labels with Error Bars, I showed how to construct simple callout labels with vertical lines and data labels, such as those used in Replacement for and Oil Price Radial Chart.

In this post I’ll show an alternative that uses XY chart series to free you from the vertical or horizontal lines provided by error bars. The technique is pretty easy. Here is the line chart, unadorned by any labels.

The data you need to label two points is shown below; there’s one set of data and one added series per label. The date and price in the first column correspond to the point where the segment meets the line chart, and those in the second column are where the segment terminates and the label is located. The arrangement allows each added series to use the label as the series name.

Copy the first set of data, select the chart, and use Paste Special to add the data as a new series; repeat for the second set of data.

For each added series, select the point that is not connected to the line series: single click on it once to elect the whole series, then a second time to select the individual point. Format the point to use the series name as a data label. In Excel 2003 and earlier, right click on the point, choose Format, and on the Data Labels tab check the Series Name box. In Excel 2007, on the Chart Tools > Layout tab, click on Data labels, select More Data Label Options at the bottom of the list, and in the Label Options pane of the dialog, uncheck Value, check Series name, and choose the label position you want.

Finally, format the extra series so they look like callout lines. Hide the markers and if desired change the line color to black.

In Replacement for and Oil Price Radial Chart I used error bars as callouts, to connect labels to points on a line chart.

The technique is actually pretty simple. Here is the line chart, unadorned by any labels.

The data you need to label two points is shown below. The dates are the same as the corresponding line chart data points. The Y values (under “Labels”) are spaced above the line chart data points by the lengths of the error bars (under “Error Bar”).

Copy the first two columns of the data range shown above, select the chart, and use Paste Special to add the data as a new series.

Add custom negative Y error bars using the values in the table (see Error Bars in Excel Charts or Error Bars in Excel 2007 for assistance). Also, apply the labels in the last column of the range to this new series using Rob Bovey’s Chart Labeler.

Hide the added series (no markers and no lines), and format the error bars to remove the end caps.

With a lot of labels, I positioned the labels to the side of the hidden data points, as above. If it isn’t so crowded, you can center the labels above the points, below.

In an upcoming post, I’ll show how to add callouts using XY series to add line segments oriented at any angle, not just the horizontal or vertical line segments afforded by error bars.

A reader wanted a pie chart to help track weight loss. She acknowledged that pie charts aren’t the best way to show data, and in fact a timeline shows progress over time well.

But there is a certain charm in, say, a pizza pie chart, which shows the current progress by eating away at the pizza. To compare two values, I pie chart is not totally terrible, so I’ve put together a simple protocol here.

The data is simple. Cell B1 contains the weight loss goal, cell B2 holds the current weight lost, and cell B3 has a formula that shows the difference, =B1-B2.

Select A2:B3 and create a pie chart. It looks like this. For illustration purposes I’ve shaded the plot area of this chart light yellow, and the chart area light gray. We will use a special fill for the plot area in a moment.

First, remove the colored fills from the chart. The two wedges should have no fill at all. I’ve left black borders on the chart, or it would vanish completely.

Now find a nice picture of a pizza pie (or a chocolate cake or lemon meringue pie). This picture came via Google Images from Taste Test: Pizza Slimdown in Chicago Magazine, who judged this beauty from Spacca Napoli to be the best Margherita pizza in Chicago.

Right click on the plot area (not the chart area or either of the pie sections), choose Format, and where appropriate for your version of Excel, use the picture as the fill pattern for the plot area. In Excel 2003 and earlier, go to the Patterns tab of the Format Plot Area dialog, click the Fill Effects button at the bottom of the Area section, select the Picture tab, click on the Select Picture button, and browse to your image.

The pie fills up the whole image. Add labels to the center of the pie wedges, using a white background instead of transparent, so it shows up among the pizza toppings, and change the fill of the Already Loat segment to a solid color (white in this case).

You may even want to remove the black borders.

As you lose more weight and change the value in cell B2, the “eaten” portion of the pie increases in size.

You could probably use an image editing program to get rid of the very light gray packground in the original picture. For this demonstration I left it unchanged.