Customizing Chart Axes

Problem

While Excel automatically sets chart axis scales, they aren't always what you desire; therefore, you'd like to customize the axes to suit your needs.

Solution

To format an axis, right-click on it and select Format Axis from the pop-up menu. This launches the Format Axis dialog box , allowing you to format many aspects of the selected axis.

Discussion

Right-clicking on an axis to select it can be difficult at times, especially when the chart is a little cluttered. Take a look at the chart in Figure 4-9, whcih shows angular vibration samples taken during a laboratory experiment.

Figure 4-9. Dense chart

This is a standard Line chart with sample numbers (Excel calls them categories) shown on the horizontal axis. By default, Excel places tick marks along this axis, one at every other sample. With a thousand samples as shown here, the axis is quite cluttered. Indeed, the density of samples plotted makes selecting the axis with the mouse difficult.

An alternative way to select chart elements for formatting is to first select the chart itself by clicking anywhere on it, and then use the arrow keys to cycle through, selecting each chart element. The name of the currently selected element is displayed in the Name box to the left of the formula bar. In this case, we're looking for Category Axis. After selecting the chart axis in this manner, you can launch the Format Axis dialog by selecting Format images/U2192.jpg border=0> Selected Axis...(Ctrl-1) from the main menu bar.

Figure 4-10 shows the Format Axis dialog box with the Scale tab selected.

Figure 4-10. Format Axis dialog box

To make the horizontal axis a bit more readable you could set the number of categories between tick marks and tick-mark labels to, say, 100. You could also go to the Font tab and change the font size of the axis labels to make them larger and more readable. The Font tab also allows you to change the font type, style, and color, among other settings.

If you'd prefer to have your category labels oriented vertically, you can go to the Alignment tab and alter their orientation. The Number tab allows you to format the labels just as you might format numbers or text in a cell. For example, you could have numbers displayed in scientific notation, as percentages, or as fractions.

In addition to formatting axis labels, you can change the appearance of the axis line itself by using the format controls on the Patterns tab. For example, you could change the thickness, line style, and color of the axis line, as well the appearance of tick marks.

The Scale tab contains different controls depending on the type of data plotted along the axis. The horizontal axis in this case shows categories or numbers that are evenly spaced. An XY scatter chart's x-axis shows real numbers with arbitrary spacing. The vertical axes in both chart types also show arbitrary real numbers. Figure 4-11 shows the scale controls available for axes that display arbitrary real numbers.

The controls on this tab allow you to specify the minimum and maximum values (i.e., the range of values shown along the axis). You can also specify the major and minor units, which control how often labels and gridlines are displayed. And you can specify where the x-axis intersects the y-axis, in case you want to shift the x-axis out of the way for clarity.

Figure 4-11. Format Axis dialog box with different scale controls

By default, all of these controls are set to Auto, which means Excel will attempt to determine suitable values for you. For the most part, Excel does an OK job at this. However, there are several reasons why you'd want to set these controls manually. One reason is that you may not want to view the entire range of data on the chart, but instead would rather focus in on a specific subrange. You can set the minimum and maximum values to a suitable range for this purpose. Another reason is that you may want specific scale divisions shown rather than those selected by Excel. For example, Excel may automatically set the major unit to 2 and the minor unit to 1; however, for your particular data you may prefer a major unit of 10 and a minor unit of 2.

As you can see from Figure 4-11, Excel also allows you to specify a logarithmic scale (see Recipe 4.5 for an example). You can even reverse the order of values along the axis or specify the x-axis crossing point at the maximum value, whatever that may be.