Dustin Smith’s World Opinions on Climate Change article in Chart Porn blog pointed me to an attractive but ultimately ineffective graphic. The road to Copenhagen Summit on Whatype blog presents results of a 2007 World Public Opinion survey of worldwide public sentiment over global warming-related issues. The graphic is reproduced in small size below; click on the image for a full size view.

Read more »

The family of PTS Excel Charting Utilities has welcomed a new member: The PTS Cascade Chart Utility. Many customers of other utilities have asked about this type of chart, and I have answered with a new program. A cascade chart shows one value vertically (e.g., revenue) against a cumulative value horizontally (e.g., market segment size).

Read more »

A common question is “How can I label the total stack values in a stacked column chart?” When you add data labels to the chart, you can’t get any labels showing the totals.

Let’s use some simple data to illustrate.

Here is a standard stacked column chart.

Read more »

I field a fair number of questions, either in my blog’s comments, in the various online forums, or via email. Sometimes the question in a comment is a bit off topic, or the answer is more than I want to bury in my own comment. The forum’s text-only format may not be sufficient to show the answer. Or the email question is of more general interest and I want to share it.

To address this situation, I’ve decided I need a new category of blog posts: You Asked For It.

In this first installment, I’ll answer a question from the comments about Clustered-Stacked Column Charts. Janez asked how to make a chart that has a column chart with composite values (e.g., 1, 2) growing like stalagmites from the bottom, and clustered constituent values (1a, 1b, 2a, 2b) hanging like stalagtites from the top. I’m not sure this is the best way to represent this information, but I’ll show this chart and I’ll show my preferred approach.

Read more »

Elsewhere in this blog I’ve showed how to Plot Two Time Series With Different Dates:

In Category Axis Tricks for Line and Area Charts – 1 I extended this technique to show how to format parts of a line chart in distinct colors:

A reader asked how to show two years of data on two separate lines and show a trendline for each.

Read more »

A reader encountered problems applying conditional formatting to a pivot table. I tried it myself, using the same kind of formulas I would have applied in a regular worksheet range, and had no problem. The reader responded that he was having problems in Excel 2007, and I was using 2003. Apparently in 2003 the conditional formatting is preserved when the table is refreshed, but in 2007, the CF in the data range is wiped out.

Well, this is just the type of thing Bill gates invented Visual Basic for. I’ll apply an approach related to that in my VBA Conditional Chart Formatting series: VBA Conditional Formatting of Charts by Series Name, VBA Conditional Formatting of Charts by Category Label, and VBA Conditional Formatting of Charts by Value, with a little help from Referencing Pivot Table Ranges in VBA. I’ll use arbitrary data and an arbitrary condition for the example.

Read more »

A Bar-of-Pie Chart. And a Pie-of-Pie Chart may be even worse. Let the Chart Busters explain.

Last week, Excel guru John Walkenbach conducted a poll on his wildly popular J-Walk Blog asking people to Post Your Pet. The poll results were tabulated in Tabulating The Pets.

I know John’s an expert and all, and he literally wrote the book on Excel Charts. Make that, the books. And these are good books, starting with the basics and progressing into some intermediate topics.

I know the books are good, because I helped out with technical editing. I made sure the protocols and the tricks all worked, and I checked out all the sample files. I even encouraged discussions of proper chart type selection.

Read more »

Blown Saves vs. Fielding Independent Pitching shows the following chart comparing Blown Saves to FIP (Fielding Independent Pitching) for ten relief pitchers on the MLB teams that made the 2009 playoffs. Fielding Independent Pitching is a quantity designed to remove the effect of fielding on a pitcher’s statistics.

This is the wrong chart type for a handful of reasons.

Read more »

I talked about LOESS smoothing in LOESS Smoothing in Excel, where I showed my improved VBA function for calculating smoothed data. I introduced an improved LOESS Smoothing utility in LOESS Utility for Excel. Since I use the utility frequently, I encountered many things about it that I wanted to change. Since I am the developer of the utility, I have actually been able to make these changes. And after a few months of use and a few hour-long sessions aimed at making it do what I want it to do, I’ve developed this utility into something that is, in the local vernacular, “wicked awesome”. If you’ve never seen “Good Will Hunting”, that means “way cool”.

What makes this utility so great, you ask? The calculations were already perfectly adequate, and I didn’t change them at all. But I’ll show the new dialog, so you can see what has changed.

Read more »

Today’s post is written by David Montgomery, who has a new blog called David @ Work. David read two of my recent posts, Fill Below an XY Chart Series (XY-Area Combo Chart) and Fill Between XY Chart Series (XY-Area Combo Chart), and noted the lack of an explanation for different colors between XY series that cross.

David attacked the problem in his blog post Two Color XY -Area Combo Chart, and he has graciously accepted my offer to repost his article below.

Two Color XY-Area Combo Chart

Last Wednesday, Jon explained the technique for creating an XY-area combo chart. The original article shows how to color the area between two lines in a chart where the lines never cross. I’m going to assume you read the article and that we’re all starting at this point:

But what if you want to cross the lines? The good news is that this isn’t Ghostbusters and crossing the lines doesn’t break anything, the area between the lines is still filled in perfectly. If we flip the values in B5:C5, B7:C7, and B9:C9, we get the following:

Read more »