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