Section 17.1. Formatting Chart Elements | Excel 2010: The Missing Manual

17.1. Formatting Chart Elements

Every chart is actually built out of small components , like titles, gridlines, axes, a legend, and the bars, points, or exotic shapes that actually represent the data. And Excel lets you manipulate each of these details separately. That means you can independently change the format of a label, the outline of bar, the number of gridlines, and the font and color of just about everything.

The first step in manipulating your charts components is learning how to select them. The easiest way is to click directly on the element you want to change. Black boxes (known as handles ) appear on the edges of whatever you've selected.

Tip: If you click a blank space anywhere in a chart you'll end up selecting the whole chart.

Some elements, like titles and legends, are easy to select this way. Others, like gridlines and axes, are a little trickier because you need to click in exactly the right place. If you end up selecting the wrong element, it's sometimes easiest to just use the arrow keys to select another one. Each time you press an arrow key, Excel selects the next chart element. So if you keep pressing an arrow key, eventually you'll cycle through all the elements that you can select in the current chart. (You can also use the Chart toolbar, which is covered later in this chapter.)

Tip: To check which element you've selected, just right-click it. The first item in the shortcut menu makes everything clear. For example, if the context menu starts with Format Axis, you'll know that you've selected a chart axis. If it says Format Gridlines, you've got yourself a gridline.

The chart elements you can select are shown in Figure 17-1. They include:

Title . The title labels the whole chart. In addition, you can add titles to the other axes. If you do, you can select these titles separately.
Legend . The legend identifies each data series on the chart with a different color. A legend is useful only when the chart contains more than one series.
Axis . An axis runs along each edge of the chart and determines the scale. In a typical two-dimensional chart, you have two axes: the category axis (typically on the bottom of the chart, running horizontally), and the value axis (typically on the left, running vertically).
Plot Area . The plot area is the background of the chart. In a standard chart, this is a plain gray background, which you can customize.

Figure 17-1. Before you can begin tweaking your chart's formatting, you need to know the names of the different elements of a chart, which are all labeled here.

Gridlines . The gridlines are the lines that run across the plot area. Visually, you line the data up with the gridlines to get an idea of the value of each data point. Every chart starts out with horizontal gridlines, but you can remove them or add vertical gridlines. You can tell Excel how many gridlines to draw, and even how to format them.
Data Series . The data series is a single set of data plotted on the chart across the category axis. In a line chart, for example, the data series is a single line. If a chart has multiple series, it's often useful to format them separately to make them easier to differentiate or to emphasize which one is the most important.
Data Point . A data point is a single value in a data series. For example, in a line chart a data point is a single dot, and in a column chart a data point is a single column. If there's an exceptionally important value that you want to call attention to, you can format a data point so it looks different than the rest of the series.

Excel Oddity AutoFudge

In one of its most bizarre features, Excel actually lets you change the data in your worksheet by dragging points and lines on your chart. You can try this out with a column chart, if you're nimble enough with the mouse. First, select a single column. You may need to click it twice to ensure that the individual column is selected, and not the entire series (as shown in Figure 17-6). Next, using the resizing handle at the top of the column, stretch it upward. Once you make your change, Excel modifies the corresponding data in your worksheet so it matches the adjusted column.

For example, if you adjust a sales column upward, Excel calculates the new dollar amount and inserts it into the source table. The same trick works with the slices in a pie chart and even points in a line chart or XY scatter chart.

Technically, this feature is known as graphical goal seeking . But it seems more likely that Microsoft is dedicated to giving business people all the tools they needeven if these tools include a convenient way to tweak the numbers so a chart looks better!

17.1.1. Manipulating Chart Elements

So what do you do with a chart element once you've selected it? There are three basic tasks you can perform, although not every chart element supports every task. The tasks include:

Deleting an element . To remove a selected element from your chart, just press Delete. You can delete any chart element, including titles, legends, data series, gridlines, the background, and even an axis. It's possible to restore these elements after you've deleted them, but only by using the Edit Undo command immediately.
Moving an element . You can move a chart element by dragging it, just like the way you move the whole embedded chart. Some elements are easily moved, like labels, legends, and the whole chart. Other chart elementsincluding axes, gridlines, and data pointscan't go anywhere. A few chart types support additional manipulation. For example, three-dimensional charts provide an extra sizing handle that lets you rotate a chart in three dimensions, tilting it forward or backward to offer a better view.
Resizing an element . You can resize an element by clicking on one of the resizing handles (the black boxes that appear on the edges of the element when you select it). Resizing is mainly useful with legends and the overall chartthere aren't many other chart elements you can resize.

Often, you won't select a chart element to delete or move it, but instead to format it with a different border, font, or color. In this case, simply right-click the element and choose the format option from the pop-up menu. However, different chart elements are formatted in different ways; they're all explained separately in the following sections.

17.1.2. The Chart Toolbar

The Chart toolbar provides one-stop -shopping for tweaking and refining any chart. Although you can show the Chart toolbar at any time by selecting View Toolbars Chart, it automatically springs into view when you select a chart or an element inside a chart.

The most useful part of the Chart toolbar is the drop-down list on its left side. This list allows you to select any chart element (except for a single data point) in the chart. When you choose the option from the list, Excel automatically highlights the corresponding part of the chart. Figure 17-2 dissects the Chart toolbar.

Figure 17-2. The Chart toolbar helps you quickly select and format almost any chart element. It also offers a quick way to change the chart type, although most other chart options (like chart titles and scaling) aren't available. To access these, you need to right-click the chart or choose an option from Excel's Chart menu.

17.1.3. Coloring the Background

Now you're ready to start creating spiffy-looking, polished charts. A good starting point is the background color. Initially, this color is a dull gray, but it's easy enough to change. Just follow these steps:

Select the plot area.

The easiest way to do this is to click the gray space between gridlines, or just choose Plot Area from the list in the Chart toolbar.

Note: In a 3-D chart, the Chart toolbar uses the term Walls instead of Plot Area. However, it's really the same thing.

Right-click the plot area and choose Format Plot Area, or click the Format button on the Chart toolbar.

A dialog box appears containing a single Patterns tab (Figure 17-3). Here you can set the background color (on the right) and add a border around it (on the left). The border can sport a variety of colors, line thicknesses, and line styles (like dashed, dotted , double, and so on). The fill area can use any ordinary color. Or, if you feel adventurous, click Fill Effects to open another dialog box where you can set a background using a gradient that shades between two colors, a regular pattern (like spots or hatches), special textured tiles, or even a background picture. (Fill Effects are described in more detail in the next section.)

Figure 17-3. It's worth getting familiar with all the formatting options provided in the Format Plot Area dialog box, because you'll see the same Patterns tab again when you format other chart elements, like columns , gridlines, and just about everything else. In this example, a thick dashed outline is selected for the border (indicated by the Style and Weight settings), along with a solid fill (indicated by the Color setting).

When you've finished making your changes, click OK.

Excel applies the results of your artistry.

Tip: Remember, if you don't have a color printer, you'll need to think about how colors convert when you print them in black and white. In some cases, the contrast may end up being unacceptably poor, leading to charts that are difficult to read. And even if you do have a color printer, remember you can always spare your ink by choosing File Page Setup, selecting the Chart tab, and turning on the "Print in black and white" checkbox. As a general rule, the less powerful your printer, the less you should use graphically rich details like tiles, background images, and gradientsunless, of course, you're planning to view your worksheet only onscreen.

17.1.4. Fancy Fills

Coloring the background of a chart is nice, if a little quaint. In the 21st century, charting mavens are more likely to add richer details like textured backgrounds or gradient fills. Excel provides these options and more. And although patterned and textured fills don't always make sense, they can often add pizzazz when used in the background of a simple chart. You can apply fancy fills to the chart background (the plot area) or individual chart items, like the columns in a column chart.

To use a fancy fill, simply click the Fill Effects button in the Patterns tab (Figure 17-3). (The dialog boxes for formatting most chart elements have a Patterns tab.) The Fill Effects dialog box in turn has four separate tabs. You choose the tab that represents the type of fill you want to create:

Gradient . A gradient is a blend between two colors. For example, you might use a black and white gradient that gradually fades from black in the top-left corner to white in the bottom-right corner. When you create your own gradient, you choose the colors and the shading pattern. You can even choose from a list of color and shading presets, with picturesque names like Late Sunset and Ocean.
Texture . A texture is a detailed pattern that's tiled over the whole chart element. The difference between a texture and an ordinary pattern is that patterns are typically simple combinations of lines and shading, while a texture uses an image that has greater, more photographic detail. When you choose a texture, you have choices like Woven Mat, Canvas, and Blue Tissue Paper. When you apply the texture, it actually looks like its name , which is great for presentations, although potentially problematic for all but the most capable printers.
Pattern . A pattern is a simple way of combining two colors using hatches, squares, and other simple line patterns. When you use a pattern, Excel works with the background and foreground colors you've already chosen . Patterns usually aren't so attractive, but they're often easier to print on ordinary printers than textures.
Picture . A picture is a graphical image that goes behind your chart and stretches itself to fit. Excel doesn't provide any ready-made pictures for you to use. Instead, you'll need to browse to a graphics file on your computer (a .bmp, .jpg, or .gif file). This option works well if you need a themed chartfor example, a beach scene behind a chart about holiday travel choices. If you just want to add a company logo somewhere on your chart, you're better off using the drawing tools described in the next chapter to place the logo exactly where you want it.

Tip: Once you add a picture for your background, the only way to remove it is to select another, different background type (either a solid fill or another fancy fill pattern). You can't use more than one fill effect at once.

Figure 17-4 demonstrates some of your fill choices.

Figure 17-4. Depending on the type of fill you use, you can subtly change the message your chart conveys. These figures show a gradient fill (top), texture fill (middle), and picture fill (bottom), each time with the same numbers and chart type. Fancy fills are particularly useful if you want to use your charts in a presentation program like PowerPoint. (Integrating your Excel worksheets with other programs is covered in Chapter 22.)

17.1.5. Adding Titles

It doesn't matter how spectacular your chart looks if it's hard to figure out what the data represents. To clearly explain what's going on, you need to make sure you have the right titles and labels.

Here's how you add basic titles:

Right-click anywhere on your chart and select Chart Options.

The Chart Options dialog box appears.
Choose Titles, which is the first tab. Enter the appropriate titles in the text boxes labeled "Chart title," "Category (X) axis," and so on.

As you switch from one text box to another, the preview on the right side of the tab updates to include the titles you've added.
Click OK.

The titles appear in the chart.
Right-click one of the titles that you want to format. Choose Format Chart Title or Format Axis title (depending on which element you selected) from the pop-up menu.

It's easy to format your new titles. The only drawback is that you need to format them one at a time.

The formatting dialog box provides three tabs: Patterns, Font, and Alignment. The Patterns tab provides options for adding a fill color and a border to the title background. The Font tab is where to change the typeface, color, and size , and apply bold and italic formatting. The Alignment tab lets you configure how the text is positioned in the title box (the standard option is centered). None of these options are newyou've seen the Patterns tab when setting the chart background and the Font and Alignment tabs when formatting cells , all of which were covered in Chapter 4.

Note: When you set a font size, you might not get exactly what you expect. For example, if you set a title to 36-point type, and then you resize your chart object smaller, Excel also reduces the point size of the title proportionately. If this isn't the behavior you want, look for the "Auto scale" setting at the bottom of the Font tab. Turn off this setting to get a font size that sticks, permanently.
Once you've made your changes, click OK.

You can now return to step 4 to format additional chart titles.

Tip: Once you've created a title, you can edit it quickly without needing to go back to the Chart Options dialog box. Just click the title once to select it, and then click it again. After a brief delay, the title switches into edit mode (try it a few times if necessary, as the timing is a little tricky). Once the title is in edit mode, you can freely insert some new text, or you can edit what's already there.

17.1.6. Adding a Legend

Titles help explain a chart's overall purpose. Usually, titles indicate what a chart is comparing or analyzing. For example, you might add a chart title like "Patio Furniture Sales" and the axis labels "Gross Revenue" and "Month of Sale" to a chart that shows how patio furniture sales pick up in the summertime. However, the category labels don't help you single out important data. They also don't let you point out multiple series (for example, the sales results in two different stores). The solution is to add additional labels or a legend . A legend is a separate box off to the side of the chart that contains one entry for each data series in a chart. The legend indicates the series name, and it adds a little sample of the line style or fill style that's used to draw that series on the chart.

Excel automatically adds a legend to most charts. If you don't already have a legend, just right-click your chart, select Chart Options, and choose the Legend tab. Turn on the "Show legend" checkbox to insert a floating legend box, which you can resize, format, or drag around your chart like most other chart elements.

Unfortunately, legends aren't always an asset when you need to build slick, streamlined charts. They introduce two main problems:

Legends are distracting. In order to identify a series, the person looking at the chart needs to glance away from the chart to the legend, and turn back to the chart again.
Legends are confusing. Even if you have only a few data series, the average reader may find it hard to figure out which series corresponds with each entry in the legend. This problem becomes more serious if you print your chart out on a printer that doesn't have the same range of colors as your computer monitor, in which case different colored lines or blocks may begin to resemble each other.

The best way to overcome these problems is to add data labels, as described in the next section.

17.1.7. Adding Data Labels to a Series

Data labels are labels that you attach to every data point in a series. This text floats just above the point, column, or pie slice that it describes, clearly identifying each piece of information. Data labels have unrivalled explaining powerthey can identify everything . The only possible drawback is that adding data labels to a chart that's already dense with data may lead to an overcrowded jumble of information.

To apply data labels, right-click your chart and select Chart Options. Then, choose the Data Labels tab, which provides several options for labeling your data. You can apply one piece of information or a combination that includes multiple items. If you use multiple items, you can also choose a character from the Separator list box to specify how to separate each piece of text in the full label (for example, with a comma, space, semicolon, or new line). If you look carefully , you can see the effect of your choices in the Preview window on the left side of the dialog box. After you've made your selection, click OK to add the labels to the chart.

The text you can use in your labels depends on the type of chart you've created. Here are your choices:

Series name . The series name identifies the series each data point comes from. Because most series have multiple data points, using this option means the same text repeats again and again. For example, in a line chart that compares sales between two stores, using this option would put the label "Store 1" above each data point for the first store, which is probably overkill.
Category name . The category name adds the information from the category axis. For example, if you're using a line chart to compare how sales fluctuated month by month, this option adds the month above every data point. Assuming you have more than one line in your line chart, this creates duplicate labels, which crowds out the important information in your form. For that reason, category labels don't work very well with most charts, although you can use them to replace the legend in a pie or donut chart.
Value . Value labels insert the data that corresponds with a data point. This is the actual information in the corresponding cell in your worksheet. For example, if you're plotting changing sales, this is the dollar amount of sales for a given month. Value labels are probably the most frequently used type of label.
Percentage . Percentage labels apply only to pie charts and donut charts. They're similar to value labels, except they divide the value against the total of all values to find a percentage.
Bubble size . Bubble size labels apply only to bubble charts. They add the value from the cell that was used to calculate the bubble size next to each bubble. Bubble labels are quite useful in bubble charts because bubble sizes don't correspond to any axis, so you can't understand exactly what numeric value a bubble represents just by looking at the chart. Instead, you can judge relative values only by comparing the size of one bubble to another.

Figure 17-5 shows a few examples of useful data labels at work.

Figure 17-5. Top : Here you can see how a value label adds information to a column chart. Even without the labels, you could still get this information by eyeing where the bar measures up to on the value axis on the left, but the labels make it dramatically easier to get the information you're interested in with a single glance. The labels have been customized slightly via the Format Data Labels dialog box to shrink their font size and add a simple box border with a shadow effect. Middle: Here's how you can combine percentage and category information to make a pie chart more readable and eliminate the legend altogether.
Bottom : Here's a bubble chart with the helpful bubble-specific labels.

Note: In some charts (including the XY scatter chart and bubble chart types), the checkboxes "Category name" and "Value" are renamed to "X Value" and "Y Value", although they have the same effect.

Once you add your data labels, they become distinct chart elements. That means you can select them and change their font, background, and border in exactly the same way as you modify any other chart element, like titles. To do so, just right-click one and choose Format Data Labels. This trick is extremely handy because it's often important to shrink a label's font so it fits into a chart properly, or apply bold formatting so it really stands out. You can even edit the text in each labeljust click once to select the label, and click again to put it in edit mode.

Tip: No matter how you choose to label or distinguish a series, you're best off if you don't add too many of these elements to the same chart. Adding too many labels makes for a confusing overall effect, and it blunts the effect of any comparison.

17.1.8. Adding Individual Data Labels

In simple charts, data series labels work well. But in more complex charts, data series labels can be more trouble than they're worth because they lead to chart overcrowding, particularly with line charts or any chart that has multiple series. The solution is to add labels to only a few data points in a seriesthose that are most important.

To add an individual data label, follow these steps:

Click the precise data point that you want to identify. For example, this could be a slice in a pie chart, a column in a column chart, or a point in a line chart.

Selecting a data point is a little tricky. You need to click twicethe first click selects the whole series, and the second click selects just the data point you want. You'll see the handles appear around the specific column or point to indicate you've selected it, as shown in Figure 17-6.
When you have the right data point selected, right-click it and choose Format Data Point.

The Format Data Point dialog box appears.

If you can't find the Format Data Point options, and the menu only displays the Format Data Series option, you probably have inadvertently selected the whole series. Return to step 1 to fix the problem.
Click the Data Labels tab.

The Format Data Point dialog box includes other options for formatting a data point. You can use these options too, as detailed in the next section.

Select the label information you want to use.

The Data Labels tab provides the same options for a data point as it does for an entire data series, including value, category, series, and percentage information.

Figure 17-6. Top : In order to select a single data point, you'll need to click it twice. The first click selects the whole Sales-05 series.
Bottom : The second click gets just the data point you want, which is the Sales-05 data in the Western sales office. In some cases, you may not be able to tell which data point is the one you want, especially if you're creating a dense scatter chart. When that happens, just hover over the data point briefly . A tooltip appears with the category, series, and value information.

Click OK to add the label to your data point.

As with data series labels, you can format your data label by right-clicking it and choosing Format Data Labels. It's almost always a good idea to format your labels so they stand out and don't crowd other information on the chart. Figure 17-7 shows two charts with data points labeled.

Tip: You can also add arrows and text boxes just about anywhere you please to call out important information. However, you'll need to use Excel's drawing features to do so, as explained in the next chapter.

17.1.9. Formatting Data Series and Data Points

Adding labels is one way to distinguish important points on your chart. Another technique is to use color, borders, and patterns. These techniques can't provide any additional information (like the value of the data point), but they're a great way to emphasize important information without cluttering up your chart. (Figure 17-8 shows a few examples.)

Note: Remember, your use of color and patterns is limited only by your imagination . For example, you might use different colors in a bar chart to help highlight the meaning of the results on a company's annual sales chart. Red-colored bars could represent losses, while black bars could show profits.

There are several reasons for formatting a data series or data point:

You want to draw attention to specific data.
You want to make sure that you distinguish between different series.
You want to make sure your printout is legible, and that you can identify all the important information, even if your printout is black and white.

Figure 17-7. Top : Data point labels work particularly well with line charts and scatter charts because both these chart types are dense with information. The two examples here underscore that fact. Here, a single data point label indicates the point where the sales changed dramatically for the Region 1 office.
Bottom : Here's the mess that results if you try to add data labels to the whole Region 1 series. No amount of formatting can clear up this confusion.

If you've added data labels, you already know the basic steps to take to format a data series because they're almost identical. It works like this:

Select the data series or data point that you want to format.
If you select a data series, your changes affect all the data points in that series. Usually, you'll use this approach to help distinguish between different series.

If you select a single data point, you'll affect only that one data point. Usually, you'll use this approach to highlight important values.

Right-click the data series or data point, and choose either Format Data Series or Format Data Point.

The formatting dialog box appears.

Figure 17-8. Top : This figure shows two examples of formatting at work. Here's a line chart where the two lines are carefully distinguished from one another with different shaped markers and line styles. To get this effect, format each series separately.
Bottom : Here's a column chart where a few columns are emphasized with a gradient fill color. For this result, format data points individually.

Tip: It rarely makes sense to format an individual data point in a line chart or scatter chart. Instead, you'll usually format the whole series so that different lines stand out from one another. If you have more than two or three lines, you may need to get creative with different line styles and marker shapes to make sure that you really can tell them apart at a glance.

Select the Patterns tab.

The options you see in the Patterns tab depend on the type of chart you're working with. For a column, bar, or pie chart, you'll see the familiar Border and Area sections, which were first introduced in Section 17.1.3 when you learned how to format a chart's background. You can use these settings to add outlines, fill colors, or even fancy fill effects like the textures and gradients explained earlier.

If you're using a line or scatter chart, you'll see a Line and Marker section. The Line section allows you to set the color, line style (dashed, dotted, solid, and so on), and weight (or thickness ) of the line. The Marker section lets you set the style (square, triangle, cross, and so on) and size of each data point that's placed on the line.
Once you've made your changes, click OK to apply them to the data series or data point.

If you change a whole data series, Excel also updates any legend to make sure it shows the new style.

Note: If you format a data point and then format the series that contains that data point, the new formatting for the series takes over. Therefore, you need to reapply your data point formatting if you want a specific value to stand out from the crowd. There's one nifty way around this limitationyou can use the helpful Redo feature to apply changes one by one. First, format a data point the way you want it. Then, select a second data point, and press Ctrl+Y to reapply your formatting to the new data point. This technique can save you loads of time.

17.1.10. Controlling a Chart's Scale

Many people don't think twice about the scale they use when they create a chartinstead, they let Excel set it automatically based on the values their chart has been built from. There's nothing wrong with this laissez-faire approach, but if you know how to take control of your chart's scale, you can make important data stand out and make it easier for people looking at your chart to spot relative differences in data and understand overall trends.

Note: It's worth noting that quite a few unsavory individuals try to skew charts with crafty scale tricks. One common example is showing two similar charts next to each other (for example, sales in 2003 and sales in 2004), and using a smaller scale in the second one to make it look like nothing's changed. Once you finish this section, you'll have a good idea how to spot these frauds. Some companies even have policies that enforce strict scale usage!

Usually, you'll be most interested in changing the scale of the value axis that runs on the left side of most charts. You can modify the scale of the value axis on most charts, including column charts, line charts, scatter charts, and area charts. (In a bar chart, the value axis actually runs horizontally along the bottom of the chart, although you can modify the scale in the same way as you do with these other chart types.) Pie and donut charts don't show a value scale at all.

To change the scale, select the value axis, either by clicking it or selecting Value Axis from the Chart toolbar. Right-click the axis and choose Format Axis. Choose the Scale tab (shown in Figure 17-9), make your changes, and click OK to apply them.

Several settings determine the scale of your chart. These include:

Minimum and Maximum . These values set the range of your scale. The axis starts at the minimum value (at the bottom of your chart), and ends at the maximum (at the top). Usually, Excel sets these values so that the minimum is 0 and the maximum is just a little bit above your largest data point. However, if your data points are very large and only have minor differences, you'll be able to help your chart's viewers make comparisons more easily if you compress the scale, by having it start at a higher minimum value.

Figure 17-9. The Format Axis dialog box allows you to change the scale used on your chart. In this example, the scale is currently set so that it stretches from 0 to 80,000, with an axis label shown every 20,000 units. (The Minor unit field, as well as all the other settings on this dialog box, are explained below.) There's an "Auto" checkbox next to each value. If any of these boxes are turned on, it indicates that Excel sets these values automatically. The current values, as set by Excel, appear in the text boxes, but Excel may modify the scale if you add more data, change the data values, or resize the chart (in which case there is more room to show intermediate values on the axis). When you edit the value in a text box, Excel automatically clears the checkmark, indicating that it's using the value you've entered.

Major unit . The major unit setting determines how many units the scale is divided into. For example, if you have a scale from 0 to 1,000 and a major unit of 100, gridlines and axis labels appear every 100 units. Altogether, that makes for 11 labels.
Minor unit . The minor unit setting determines how many tick marks are on the scale. (Tick marks are tiny lines added to the axis to help you judge the scale. Unlike gridlines, they don't cross the whole chart.) Usually the minor unit is less than the major unit. For example, you might use a major unit of 100 and a minor unit of 10 in a chart that stretches from 0 to 1,000 (which generates 11 gridlines and 10 tick marks between each gridline). Five to 10 major units on each axis, and 5 to 10 minor units for every major unit, usually make for a pleasant looking chart.

Note: When you first create a standard chart, tick marks are turned off, so the minor unit setting doesn't have any effect. To turn them on, select the Patterns tab in the Format Axis dialog box, and make sure that you select an option other than None in the "Minor tick mark type" box.

Category (X) axis crosses at . This number controls where the category axis line crosses the value axis. Usually, this line is placed right at the bottom of the chart, at the point. However, you can accomplish an interesting effect by placing it somewhere in the middle of your chart. For example, you might want to plot a chart of test scores, and want to draw the axis at a point that would indicate the minimum passing mark. Note that in a column chart, when a column has a value that's less than the axis, it points "downward," as you can see in Figure 17-10 (bottom).

Using these five basic ingredients , you have a good deal of flexibility. Figure 17-10 compares a few different options that demonstrate how different scale choices can transform a chart, with the help of a little formatting.

Figure 17-10. These charts show the same data prepared three different ways. Top : Here's a column chart the way Excel creates it, with the addition of minor tick marks in the value axis. The problem with this chart is that the difference between the column values don't come across as very substantialbut, in fact, it is.Middle: The other two examples solve this problem by doing a better job of emphasizing the differences between the columns' values. Here, the minimum value is set to $100,000, which shrinks the scale (compared to the top figure). In addition, the gridline color is changed to blend into the background and data labels are added to the series to show each column's value.
Bottom : Here, the axis is set at $150,000 so that you can compare which regions made the sales targets versus those that didn't. The labels have been removed from the X axis so they don't cross the bars, and a category label has been added to the whole data series so the region's name shows up just above or below the bar. This example is further enhanced with gradient fills, dotted gridlines, and a scale set to use thousands.

The Format Axis dialog box also provides a few specialized options, which aren't as commonly used but are still quite interesting. They include:

Display units . You can use this option to shorten the text labels on your axis. This is particularly useful if you have large dollar amounts. For example, imagine your value axis stretches from $100,000 to $300,000. If you choose Thousands as your display unit, Excel adds the label Thousands along the value axis and changes the scale values to three digit numbers like $100 and $300. The bottom example in Figure 17-10 shows this space-saving trick at work.
Logarithmic scale . A logarithmic scale is one that doesn't increase gradually. Instead, every major unit represents an increase by a power of 10. For example, the values 0, 10, 20, 30, 40, 50 make up a typical scale. The values 0, 10, 100, 1000, 10000 make a logarithmic scale. Logarithmic scales lend themselves to scientific and mathematical applications to help you see certain types of relationships and patterns in your data.
Values in reverse order . This option turns your chart upside down. It places the category axis at the top of the chart (instead of the bottom), and changes the value scale so that it increases as it stretches down the side of the chart.
Category (X) axis crosses at maximum value . This option places the category axis at the top of the chart, somewhat like the "Values in reverse" order option. The difference is that the scale remains the same, so that the minimum value is at the bottom of the chart and the maximum value is at the top.

Tip: If you're using a numeric or date-based category axis, you can format the scale of the category axis in the same way as you format the scale of the value axis. This is most commonly the case when you're creating an XY scatter chart or a line chart. If your category axis just displays labels, you can still format it, but you have fewer options. You can't change the scale, but you can reverse the order of categories, add tick marks, hide labels, and format how the axis line looks.

GEM IN THE ROUGH
Even More Formatting

Once you get the hang of formatting, you'll really appreciate all the ways that Excel lets you tweak even the smallest details on your charts. For example, you can format axes and gridlines the same way that you formatted titles, labels, and the chart background.

Here are some details you might want to change:

The font, alignment, and number style used for the text in the axis labels. You'll find these settings in the Font, Alignment, and Number tabs of the Format Axis dialog box.
The color, size, thickness, and style (dashed, dotted, solid, and so on) of the gridlines. You'll find these settings in the Patterns tab of the Format Axis or Format Gridlines dialog box.
Whether labels, major tick marks, and minor tick marks appear on each axisand what they look like. You'll find these settings in the Patterns tab of the Format Axis dialog box.