Yesterday morning when I checked my blog traffic, I noticed a large spike in page views on the previous day, Wednesday. My blog traffic has been steadily rising since the end of summer, but this was an unusually large increase for a single day. It was around 1000 page views higher than Tuesday, and 1400 higher than Monday. This morning I saw that the count for Thursday was around 700 page views higher than Wednesday’s.

Read more »

When you have two series in a chart, and you want to show them using two different chart types, you have a broad array of choices. If you have target and actual data for a set of product attributes, for example, you can make a column-line combination chart readily.

Suppose your data looks like this (ignore the “height” column for now):

Make a column chart with all of the data:

Read more »

On my web site I wrote a tutorial that showed how to Label the Last Point in an Excel Chart’s Series, and I posted a small add-in with the labeling procedure. I later blogged about this technique in Label Each Series in a Chart. When Excel 2007 came around, I discovered that the code no longer worked as expected because Excel 2007 treats unplottable points (blank cells or #N/A errors) differently than Excel 2003 had. I figured out a workaround and blogged about it in Label Last Point for Excel 2007. I never posted the corresponding add-in for Excel 2007.

The problem with the code in these pages is that they only labeled the active chart. The problem with this is that I often have a dozen or more charts to label, and it’s tedious to select and run the code, select and run the code, select and run the code, etc. So I recently made a minor adjustment to the code that would apply last point labels to the active chart, or to all selected charts. (You can select multiple charts by holding Shift or Ctrl while clicking on them.)

Read more »

In this edition of Chart Busters, I’ve taken on a charting tutorial on the Microsoft web site. I know they are trying to help their customers make better use of their software, and some of the Microsoft tutorials are really pretty good. They have some helpful pages on using pivot tables, for example. But this charting tutorial is an absolute disaster.

The tutorial in question is entitled Charts III: Create a professional-looking chart, and it is the third in a series of four tutorials. The others are Charts I: How to create a chart, Charts II: Choose the right chart type, and Charts IV: Charts for the scientist. I will probably review these other tutorials at some point, but this one is really a job for Chart Busters.

Before I begin, I’d like to thank Jorge Camoes, whose Spiffy Charts: Want to know how to create bad charts? Visit Microsoft Office Online Training blog post pointed me to this horrid tutorial.

I’d also like to acknowledge all of the bloggers and commenters who have bemoaned the fact that “making a professional chart” is really a nice sounding phrase that really means “use as much gratuitous formatting and effects to make your chart pretty, without regard for its effectiveness.”

Read more »

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 »