July 28, 2011, 5:53 p.m.
posted by tactics
Excel gives you a great deal of control over the format, position, and scale of your charts' axes. You can specify the line style, color, and weight of the axes, as well as the presence or absence of tick marks and tick labels. You can also override the default scaling and establish the positions at which vertical and horizontal axes intersect.
The default axis is a thin, solid, black line. But you can replace that with broken or compound lines in various colors and weights. For details, see "Formatting Lines and Borders" on page 635. Note that Excel draws your tick marks in the same style as your axis. Therefore, if you choose a heavy red line for the axis, you'll have heavy red tick marks as well-unless, of course, you opt for no tick marks.
Tick marks are short lines that either cross or abut an axis at regular intervals. Like the lines that mark inches and fractions of inches along a ruler, tick marks help define the scale of a value axis; they separate categories on a category axis. (A value axis is one that's numerically scaled; a category axis is one that's delineated by text labels. For more information, see "Manipulating Axes" on page 625.) Tick marks come in two degrees, major and minor. Minor tick marks delineate subdivisions between major tick marks. Axis labels (which earlier versions of Excel called tick-mark labels) are the labels that identify positions along the axis-for example, the month names and numbers in Figure 20-6.
By default, Excel displays major tick marks on the outside of axes and does not display minor tick marks. Excel displays an axis label for each major tick mark, adjacent to the axis. To reposition tick marks or axis labels, right-click the axis, click Format Axis, and select the Axis Options category in the formatting dialog box. The controls you're interested in are in the lower half of the dialog box, labeled Major Tick Mark Type, Minor Tick Mark Type, and Axis Labels.
You will probably want to reposition your axis labels in charts where the horizontal and vertical axes intersect somewhere other than the lower-left corner of the chart. This commonly occurs when the range of the value axis spans negative as well as positive numbers. In such cases, Excel plants the category labels within the plot area unless you visit the Format dialog box, open the Axis Labels drop-down list, and choose Low. Doing so transforms a chart like this:
into one like this:
When you first create a chart, Excel links the numeric format of your value-axis labels to that of the source data. Create a chart from data with the Currency format, for example, and Excel applies the Currency format to the labels on your value axis. Change the format of the source data, and the labels stay in step.
You can override this linkage by applying a specific numeric format to your axis labels. In the formatting dialog box, select the Number category, and then select the format you want from the list on the right. After you explicitly format your axis labels, Excel no longer copies formatting changes from the source data to the chart. To relink the format of your axis labels with that of the source data, revisit the dialog box, and select the Linked To Source check box.
To override the default scaling of a value axis, right-click it, click Format Axis, and select the Axis Options category in the Format Axis dialog box (see Figure). Then select the Fixed option to the right of Minimum or Maximum, and in the text box at the far right, type the desired value.
Figure: By selecting the Fixed option and typing a value in the text box at the right, you can set up your own value-axis scaling.
The Major Unit and Minor Unit values determine the spacing between tick marks-and hence between gridlines. If your axis labels or your gridlines are too close together, select Fixed beside Major Unit, and then in the box at the right, type a larger value. The Minor Unit value is relevant only if you've chosen to display minor tick marks.
By default, axes intersect at 0 on the value axis. You can move the intersection to a different point by selecting Axis Value below Floor Crosses At and typing a number in the text box at the right. You can also move the intersection to the highest point on the value axis by selecting Maximum Axis Value. On a typical chart with a vertical value axis and a horizontal category axis, that would put the category axis at the top of the chart (unless you also select Values In Reverse Order).
You can turn the value-axis scale upside down so the higher values appear near the bottom of the chart. You might find this option convenient if all your chart values are negative and you're interested primarily in their absolute value. To invert the axis scale, select the Values In Reverse Order check box.
To use logarithmic scaling, select the Logarithmic Scale check box, and if you want to use a base other than 10, type that base in the box to the right. (The ability to use log scaling on bases other than 10 is new with Office Excel 2007.)
In a logarithmic scale, the lowest value is 1. You cannot plot negative and 0 values. If you apply logarithmic scaling to a chart that includes negative or 0 values, Excel displays an error message and removes the offending values from the chart. You can restore them by returning to linear scaling.
As you saw in Chapter 19, the Axes item on the Ribbon (in the Axes group on the Layout tab) includes three options for assigning a scaling factor to a value axis-thousands, millions, and billions. These choices let you simplify the display of large-numbered labels. Instead of showing 1000000, 2000000, and 3000000, for example, your axis can be marked 1, 2, and 3 and have a label indicating that all values have been reduced by a factor of a million. The Display Units drop-down list in the Format Axis dialog box (see Figure) offers a few additional scaling options, including hundreds and trillions. Unfortunately, Excel doesn't offer any options for scaling by negative powers of 10.
Excel recognizes two kinds of category axes-those that have ordinary text labels and those whose labels are dates. Figure 19-1 on page 622 is an example of a chart with a text category axis. Figure 20-6 is one with a date category axis. (In earlier versions of Excel, date category axes were called time-scaled.) Excel 2007 handles the two types of axes somewhat differently. (For more about date category axes, see "Changing the Scale of a Date Category Axis" on page 648.)
Figure shows your options for formatting a text category axis.
Figure: The Format Axis dialog box for a text category axis lets you adjust the spacing between gridlines and the position of axis labels, among other things.
Major gridlines are ordinarily drawn from major tick marks, and on a text category axis, a tick mark normally occurs between each axis label (the labels are centered between the tick marks). Because gridlines are most commonly used to help the viewer discern the values of data points, rather than to demarcate categories, Excel leaves category-axis gridlines off by default. If you turn them on and find that you don't want quite as many as Excel gives you, you can have them appear between, say, every other pair of category labels. To modify the frequency at which they appear, increase the Interval Between Tick Marks value. (If you don't display category gridlines, changing this value will still affect the frequency at which axis tick marks appear.)
Note that you don't have any choice about the spacing of minor tick marks. They (and minor gridlines, should you choose to display either) are always evenly spaced between major tick marks and major gridlines.
Excel displays one category label for each data cluster (or for each data point in a single-series chart). If the chart has many data points, the program sometimes draws the labels vertically or at an angle so they don't overlap. If you coerce the labels into a horizontal posture and you have many labels, you might find them lying atop one another or, at the very least, crowding each other to the point of unreadability. One way to solve the problem is not to display every label. (Other ways include using shorter labels, reducing their font size, increasing the size of the chart, and resigning yourself to angled or vertical labels.)
To display alternate labels, select Specify Interval Unit, and in the text box to the right, type 2. To display every third label, make that 3-and so on.
If you display category labels below the axis (the usual placement for a horizontal category axis) and you have a border around the perimeter of the chart, you might find in some cases that Excel draws the labels too close to the border and too far from the axis. You can move them closer to the axis by reducing the number in the Label Distance From Axis text box.
By default, the value axis crosses the category axis to the left of the first category's data markers. However, you can position its crossing point elsewhere. For example, in a chart comparing two data clusters, such as the one shown in Figure, you might prefer to position the value axis in the middle instead of at the left.
Figure: When a chart compares two sets of values, it can sometimes be effective to put the value axis between the sets.
To reposition the value axis, select At Category Number (under Vertical Axis Crosses), and in the text box to the right, type a number.
Excel ordinarily decides whether a category axis should be treated as text or as dates. You can overrule this decision by selecting Date Axis or Text Axis under Axis Type. It wouldn't make sense to have Excel format text labels as dates, but sometimes you might want to treat dates as text. If you have forced a date-oriented axis to be handled as text and then change your mind, you can return it to date status by selecting Date Axis.
When a category axis is based on cells containing dates, Excel uses a date scale by default. Your scaling options then are quite different from those available on a text axis. Figure shows an example of the formatting options available on a date category axis.
Figure: You can change the appearance of a time-scaled chart dramatically using the parameters in this dialog box.
By default, Excel makes your earliest time value the minimum point on the scale and the latest time value the maximum. By specifying different values for these parameters, you can zoom in on a subset of your data. For example, if your chart plots monthly information from January through December, you can focus on the third quarter by changing the Minimum value to 7/1 of the year in question and the Maximum value to 9/30. Furthermore, if you want to stress that the future is unknown, you can extend the maximum to a date beyond the date of your last data point. Excel then compresses the plot into the left side of the chart, leaving white space on the right.
The Major Unit setting determines the spacing of major tick marks, axis labels, and major gridlines. To move labels and gridlines farther apart, increase the Major Unit value. The Minor Unit setting determines the spacing of minor tick marks and minor gridlines.
On a time-scaled chart, Excel plots data in chronological order even if it's not sorted by date on the worksheet. We don't recommend randomizing your data, but it's interesting to know that the Excel charting engine doesn't object.
On charts with date axes, Excel can plot data points only at intervals of the base unit. The available base units are Days, Months, and Years. Excel decides what base unit to use according to the smallest difference in value between points in your source data. In other words, the program usually knows what to do and usually gets it right. You might occasionally find it convenient to overrule, however.
For example, Figure plots daily price data, using the automatically determined base unit of Days. Switching the base unit to Months or Years (Figures 20-12 and Figure) turns the simple daily line chart into an open-high-low-close chart that shows the variation of prices over larger time intervals.
Figure: With the base unit set to its automatically determined value, Days, Excel generates a simple line chart of daily prices.
Unfortunately, Excel offers only three base unit choices-Days, Months, and Years. Thus, if your data provides, say, hourly price points, you will get a daily open-high-low-close chart regardless. If you'd prefer an ordinary line chart showing hourly price progressions, your recourse is to turn off date scaling in favor of a text category axis. To do that, right-click the category axis, and choose Format Axis. In the Format Axis dialog box (see Figure), select Text Axis under Axis Type.
Fiddling with the base unit value is not the only way to get open-high-low-close charts! Excel offers four types of stock charts, including open-high-low-close candlesticks. To see the choices, click the Insert tab on the Ribbon, and then click Other Charts. The stock charts, listed in the gallery under the heading Stock, require that you set up your data in columns-for example, with separate columns for Open, High, Low, and Close. The methods described in this chapter are useful to know about when your data is not arrayed as required for the built-in stock chart types.
A depth axis (what Excel used to call a series axis) appears only in certain charts that use three-dimensional perspective, such as the one shown in Figure. When each series appears in a separate plane, Excel displays two axes along the floor of the chart and a third axis straight up from the floor. One of the floor axes becomes the usual category axis, and the other becomes the depth axis.
Figure: This three-dimensional perspective chart has a depth axis as well as the usual value and category axes.
Options for customizing a depth axis are few. You can increase the space between depth gridlines (if you're displaying them) by typing a number greater than one in the Interval Between Tick Marks text box. You can reduce the number of axis labels (labeling every other point, for example) by selecting Specify Interval Unit and typing a number greater than 1 in the text box to the right. And you can reverse the order in which Excel plots the series by selecting the Series In Reverse Order check box. This last option might be useful if the points in your first series obscure those in subsequent series. (You can also deal with that sort of problem by rotating the chart; for information about that approach, see "Changing the Rotation of Chart Text" on page 627.)