Archive for 'Formatting'
High-Low Line Alternatives in Excel Charts
by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2010.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
High-low lines are a feature of Excel line charts that connect the high and low points at a given category. For example, in a stock chart the high-low lines connect the high and low value for each date in the chart.
A high-low line connects the highest of the values for a given category to the [...]
Posted: Wednesday, March 3rd, 2010 under Formatting.
Comments: 5
Add Individual Target Lines To Each Cluster in a Column Chart
by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2010.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
In I Keep Saying, Use Bar Charts, Not Pies, I described the process of making better column charts to replace a horrendous pie chart. At one point I said “Let’s add a weighted average of all responses, using a set of horizontal lines.” I neglected to share the technique for adding individual target lines, though [...]
Posted: Wednesday, December 2nd, 2009 under Formatting.
Comments: 3
Logarithmic Axes in Excel Charts
by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2010.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
In Custom Axis, Y = 1, 2, 4, 8, 16 I showed axes with base 2 logarithmic scales in both Excel 2003 and 2007. In Excel 2003 it is necessary to transform the data to get the intended result. In Excel 2007, the axis can be achieved with the untransformed data.
In the previous post, the data [...]
Posted: Tuesday, August 25th, 2009 under Formatting, General.
Comments: 3
Horizontal Bands in the Background of an Excel 2007 Chart
by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2010.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
In Horizontally Banded Chart Background I showed how to create colored horizontal bands in the background of a chart.
I first wrote up that tutorial back when I was using Excel 97, and the protocol remained the same for Excel versions through Excel 2003. The protocol is pretty much the same in Excel 2007, too, but the [...]
Posted: Wednesday, August 19th, 2009 under Formatting.
Comments: 3
Excel Chart Color Update
by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2010.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
In Using Colors in Excel Charts I discussed a lot of details about Excel colors, predominantly in Excel 2003, and specifically with charting in mind. I mentioned the Color Brewer, a neat little utility for selecting colors and designing color palettes. In ColorBrewer2.org Mark Harrower of Axis Maps announced Color Brewer 2, which updates the [...]
Posted: Tuesday, June 30th, 2009 under Formatting.
Comments: 14
Hide Series Data Label if Value is Zero
by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2010.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
With a little knowledge of number formats and a healthy dose of creativity, you can work around apparent shortcomings in Excel’s charting mechanism, neaten up your charts, and produce effects that are otherwise difficult.
In the Microsoft newsgroup, someone asked how to hide the data labels in his stacked column chart if the values are zero.
The [...]
Posted: Tuesday, May 12th, 2009 under Formatting.
Comments: 15
Mike Alexander’s Favorite Chart Labeling Tricks (Guest Post)
by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2010.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
My colleague and fellow Excel MVP Mike Alexander of DataPig Technologies has sent me another article, so instead of the blog being silent while I take today off, it will be graced with more of Mike’s wisdom. Last November, Mike shared with us his Ten Chart Design Principles.
Mike and I have taught advanced Excel classes [...]
Posted: Tuesday, April 28th, 2009 under Formatting.
Comments: 3
Double Legend in a Single Chart
by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2010.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
In The Problem with Marimekkos, I used a panel chart to separate the data from a single complex and hard-to-decipher chart into four much simpler charts, following the lead of Stephen Few of Perceptual Edge. I used a trick to place two separate legends into the bottom two panels of the chart.
I introduced a method [...]
Posted: Wednesday, February 25th, 2009 under Formatting.
Comments: 10
Order of Legend Entries in Excel Charts
by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2010.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
One of the mysteries of modern life is the order in which series appear in the legend of an Excel chart. People have been driven mad while attempting to reorganize legends in their charts. I’ve spent years working with Excel charts, and I’ve made some observations and generalizations about Excel chart legends. These are only [...]
Posted: Friday, February 13th, 2009 under Formatting.
Comments: 9
Legend Entry Tricks in Excel Charts
by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2010.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
This is a continuation of Legends in Excel Charts – Formats, Size, Shape, and Position, which started a discussion about the legends in Excel charts. That article talked about legend formatting, and how the best legend is no legend at all: it’s more effective to put the text labels right on the data. But sometimes [...]
Posted: Wednesday, February 11th, 2009 under Formatting.
Comments: 20

















