How to Create a Professional Chart using Excel 2007

re: How to Create a Professional Chart using Excel 2007

Jon Peltier — Mon, 23 Mar 2009 22:58:51 GMT

Sean -

If you watch it while it redraws, you see that the 0 value slice is really a 360° slice. The slices before it are under this full circle, and those after it are above it.

The best way to prevent this problem is not to use pie charts. Everyone "knows" they're good for showing proportion of a whole, but in fact, they are really pretty bad at it, unless you have only two, or at most three, slices, and the values are easily distinguishable from each other.

Pie charts are often prone to error, as I showed in peltiertech.com/.../pie-chart-rounding-in-excel, and as C.H. Art showed in hiw comment.

re: How to Create a Professional Chart using Excel 2007

SeanC — Mon, 23 Mar 2009 11:13:18 GMT

Regarding the pie charts, it appears that Excel does give the correct slices, it's the fill colour that goes wrong. Where you have a 0 sized slice the colour from that slice, which shouldn't show at all, spans across any prior slices back to the origin (12 o'clock position). If your 0 slice is last in the series then you lose all visibilty of slices! This obviously shouldn't be happening, but until it is fixed:

Ways to avoid this:

Stick with a style that works;

Order the series so that 0 values are 1st;

Add a tiny value to them (0.00001) but keep the displayed values to fewer places of accuracy;

0 values in pie charts are not always so easy for a viewer to notice (or notice the lack of) so in order to show that something is 0, where possible choose a different chart type.

If you really want to see this going wrong in glorious style, use the exploded pie charts!

It's a bit concerning. I think for the time being I will be on a strict diet and avoid pies altogether.

re: How to Create a Professional Chart using Excel 2007

Xl User — Sat, 21 Mar 2009 21:22:03 GMT

Is there any way to display tool tips for all the series of data in a chart all at once and make it constantly visible (and changing) as one moves the mouse over the chart e.g. say I am plotting a line chart with five series of different data hence five line appears with time as the horizontal axis (same for all the series), now instead for the viewer to eyeball the value for any particular data point and try to compare it with other series won't it be nice if there are floating tool tips where the Y and X axis value are displayed for all the series in their respective colors when one slides the mouse left to right.

re: How to Create a Professional Chart using Excel 2007

Jon Peltier — Sat, 21 Mar 2009 05:01:19 GMT

C H Art:

Ha, as if a pie chart isn't bad enough, Excel can't even get the slices right! A few more of the styles are messed up too.

re: How to Create a Professional Chart using Excel 2007

C. H. Art — Sat, 21 Mar 2009 03:56:32 GMT

Avoiding unessential formatting is a good suggestion if one wants to create professional charts, as using some of that formatting can actually make the chart INCORRECT. Here's an example:

1. Enter the following in Sheet1:

Item Value

A 2

B 1

C 0

D 1

E 2

2. Select the range (A1:B6) and create a pie chart.

3. Select on the resulting pie chart and then click Style 10, Style 18, or Style 26 from the Chart Styles box on the Chart Tools/Design tab. Notice how dramatically the slices on the pie change. It's a brand new, totally WRONG chart!

re: How to Create a Professional Chart using Excel 2007

Jon Peltier — Fri, 20 Mar 2009 12:46:59 GMT

Asbjorn -

This axis is a category (text) axis, not a date/time axis. The first chart, with the blue bars, has a two-column category axis range, with the 2-digit years in the first column and the one-character months in the second. I show this technique in <a href="peltiertech.com/.../" title="Chart with a Dual Category Axis">Chart with a Dual Category Axis</a>.

This centers the year under all of the associated months. To align the year with the first month of the year, you could (1) insert a space character into the previously blank cells of the first column of labels, or (2) revert to a one-column label range, where you put the year and month into special cells and just the month in others.

re: How to Create a Professional Chart using Excel 2007

Asbjorn — Thu, 19 Mar 2009 14:41:19 GMT

On the x-axis, how did you format the dates so that the year (i.e., '08 and '09) is only shown for January and the first month and not the rest of the year?

re: How to Create a Professional Chart using Excel 2007

Jon Peltier — Thu, 19 Mar 2009 03:11:06 GMT

Mike -

Not for the first time. John Walkenbach showed us how to do this a number of years ago, at least as early as Excel 97. Here's a recent revision of the article:

spreadsheetpage.com/.../creating_a_transparent_chart_series

And it prints fine on my HP deskjet printer.

re: How to Create a Professional Chart using Excel 2007

MikeM — Wed, 18 Mar 2009 22:35:06 GMT

Hello All:

This is a great resource. Thanks for all the postings here. For anyone who is interested, it is possible to add the shaded area below the category axis. See my site above for a step by step explanation to add the shaded area to the chart in Excel 2007. For the first time, it is now possible to apply a transparency to a data series in an Excel chart. The chart is relatively easy to create, however, we have discovered numerous printing issues with color print drivers and transparencies in Office 2007. One workaround is to SAVE AS to a .PDF or Microsoft .XPS document before printing. The .PDF/.XPS files print out fine.

re: How to Create a Professional Chart using Excel 2007

David — Fri, 13 Mar 2009 20:01:22 GMT

Thanks for the update, I look forward to testing it out once SP2 rolls out.