Software

Use custom formats in an Excel chart's axis and data labels

Adding a custom format to a chart's axis and data labels can quickly turn ordinary data into information.

Charts allow us to quickly assimilate data into information. With a quick glance, you can discern many important facts about your data, without viewing rows and rows of detail values. Once the data's in a chart, you can further enhance the experience with formatting. For instance, the following two graphs show the difference between no additional formatting and a simple custom number format applied to both the axis and the data labels. It's subtle, but it still draws your attention to a specific point, without actually saying, "Hey… look at this!" or drawing a big red arrow.

Now, let's suppose that everything above $325 shows a profit, everything below $325 is a break-even venture. Everyone might know this already, but changing the color of the axis values and the data labels makes profit items stand out.

Creating the effect is simple. First, let's change the axis number format, as follows:

  1. Right-click the Axis area and choose Format Axis from the context menu. If you don't see Format Axis, right-click another spot.
  2. Choose Number in the left pane. (In Excel 2003, click the Number tab.)
  3. Click Custom in the Category list.
  4. Enter the following format: [Blue][<325]$0;[Green][>=325]$0. (The period isn't part of the format.)
  5. Click Add (not in Excel 2003).
  6. Click Close (OK in Excel 2003).

The blue numbers indicate break-even sales; the green numbers show those sales that produce a profit. However, it still isn't a complete picture because the $326 point is just a hair above the break-even point, but you really don't get that message. That's why you might want to add the same custom number format to the data labels, as follows:

  1. Right-click a series and choose Format Data Labels from the context menu. If you don't see that option, right-click again—you've selected the wrong element.
  2. Repeat steps 2 through 6 above.

You'll probably want to format all of the series, but in this example, only the one series changes. The break-even point isn't explicit, but formatting the data labels adds a second layer of information for those values that fall between the actual break-even point and the first formatted axis value.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

3 comments
pamela_devol
pamela_devol

we like to print in black and white. Is there a way to conditionally format the axis with bold or italic, or a different font size?

stephenmj
stephenmj

How do I use this if I have more than two ranges I want to work with? I tried the following but it does not seem to work. [Blue][=200][Green][=325]0 Also what are the color fields that can be used? Obviously not all color names seem to work.

GKap
GKap

Yes - this is a very good point - how do we show more than two ranges??

Editor's Picks