The preceding chapter introduced charting in Excel and described how to create basic charts. This chapter takes the topic to the next level. You learn how to customize your charts to the maximum so that they look exactly as you want. You also pick up some slick charting tricks that will make your charts even more impressive.
Excel 2007 makes creating a basic chart easier than ever. Select your data, choose a chart type, and you’re finished. You may take a few extra seconds and select one of the prebuilt Chart Layouts, and maybe even select one of the Chart Styles. But if your goal is to create the most effective chart possible, you probably want to take advantage of the additional customization techniques available in Excel.
Customizing a chart involves changing its appearance, as well as possibly adding new elements to it. These changes can be purely cosmetic (such as changing colors modifying line widths, or adding a shadow) or quite substantial (such as changing the axis scales or adding a second Value Axis). Chart elements that you might add include such features as a data table, a trend line, or error bars.
Modifying a chart is similar to everything else you do in Excel: First you make a selection (in this case, select a chart element), and then you issue a command to do something with the selection.
You can select only one chart element (or one group of chart elements) at a time. For example, if you want to change the font for two axis labels, you must work on each set of axis labels separately.
Excel provides three ways, described in the following sections, to select a particular chart element:
To select a chart element with your mouse, just click it. The chart element appears with small circles at the corners.
To ensure that you’ve selected the chart element that you intended to select, view the Chart Element control, located in the Chart Tools Format Current Selection group of the Ribbon (see Figure 20.1).
When you move the mouse over a chart, a small chart tip displays the name of the chart element under the mouse pointer. When the mouse pointer is over a data point, the chart tip also displays the value of the data point.
If you find these chart tips annoying, you can turn them off. Choose Office Excel Options and click the Advanced tab in the Excel Options dialog box. Locate the Display section and remove the check mark from either or both items labeled Show Chart Element Names On Hover or Show Data Point Values On Hover.
Some chart elements (such as a series, a legend, and data labels) consist of multiple items. For example, a chart series element is made up of individual data points. To select a particular data point, click twice: First click the series to select it and then click the specific element within the series (for example, a column or a line chart marker). Selecting the element enables you to apply formatting only to a particular data point in a series.
You may find that some chart elements are difficult to select with the mouse. If you rely on the mouse for selecting a chart element, you may have to click it several times before the desired element is actually selected. Fortunately, Excel provides other ways to select a chart element, and it’s worth your while to be familiar with them.
When a chart is active, you can use the up-arrow and down-arrow keys on your keyboard to cycle among the chart’s elements. Again, keep your eye on the Chart Elements control to ensure that the chart element that’s selected is what you think it is.
When a chart series is selected, use the left-arrow and right-arrow keys to select an individual item within the series. Similarly, when a set of data labels is selected, you can select a specific data label by using the left-arrow or right-arrow key. And when a legend is selected, you can select individual elements within the legend by using the left-arrow or right-arrow keys.
The Chart Element control is located in the Chart Tools Format Current Selection group. This control displays the name of the currently selected chart element. But it’s a drop-down control, and you can also use it to select a particular element in the active chart (see Figure 20.2).
This control lists only the top-level elements in the chart. To select an individual data point within a series, for example, you need to select the series and then use the left and right arrow keys (or your mouse) to select the desired data point.
You have two main ways of working with chart elements. You can use the Format dialog box or use the Ribbon and Mini toolbar.
When a chart element is selected, you can access the element’s Format dialog box to format or set options for the element. Each chart element has a unique Format dialog box that contains controls specific to the element (although many Format dialog boxes have controls in common). To access the Format dialog box, use either of these methods:
Right-click the chart element and choose Format xxxx from the shortcut menu (where xxxx is the name of the element).
Select a chart element and then choose Chart Tools Format Current Selection Format Selection.
Select a chart element and press Ctrl+1.
Any of these methods displays a tabbed Format dialog box that enables you to make many changes to the selected chart element. For example, Figure 20.3 shows the dialog box that appears when a chart’s legend is selected.
When a chart element is selected, you can also use the commands on the Ribbon to change some aspects of its formatting. For example, to change the color of the bars in a column chart, use the commands in the Chart Tools Format Shape Styles group. For some types of chart element formatting, you need to leave the Chart Tools tab. For example, to adjust font-related properties, use the commands in the Home Font Group.
If you need to adjust a text element in a chart, it may be more efficient to right-click the element and use the Mini Toolbar that pop up.
The Ribbon controls do not comprise a comprehensive set of tools for chart elements. The Format dialog box presents many additional options. But then again, the Format dialog box is missing some formatting options. For example, if you want to apply Glow or Soft Edges formatting, you need to use the Ribbon commands.
The Chart Area is an object that contains all other elements in the chart. You can think of it as a chart’s master background or container.
The only modifications that you can make to the Chart Area are cosmetic. You can change its fill color, outline, or effects.
Note that if you set the Chart Area to use No Fill, the underlying cells are visible. Figure 20.4 shows a chart that uses No Fill and No Outline in its Chart Area. The Plot Area, Legend, and Chart Title do use a fill color. Adding a shadow to these other elements make them appear to be floating on the worksheet.
The Chart Area element also controls all the fonts used in the chart. For example, if you want to change every font in the chart, you don’t need to format each text element separately. Just select the Chart Area and then make the change using the control on the Home Font group or by using the Mini toolbar.
The Plot Area is the part of the chart that contains the actual chart.
If you set the Shape Fill property to No Fill, the Plot Area will be transparent. Therefore, the fill color applied to the Chart Area will show through.
You can move and resize the Plot Area if you like. Select the Plot Area and then drag a border to move it. To change the size of the Plot Area, drag on one of the corner handles.
Different chart types vary in how they respond to changes in the Plot Area dimensions. For example, you can’t change the relative dimensions of the Plot Area of a pie chart or a radar chart. The Plot Area of these charts is always square. But with other chart types, you can change the aspect ratio of the Plot Area by changing either the height or the width.
Figure 20.5 shows a chart in which the Plot Area was resized in order to make room for a Shape that contains text.
In some cases, the size of the Plot Area changes automatically when you adjust other elements of your chart. For example, if you add a legend to a chart, the size of the Plot Area may be reduced to accommodate the legend.
A chart can have several different types of titles:
Chart title
Category (X) axis title
Value (Y) axis title
Second category (X) axis title
Second value (Y) axis title
Depth axis title (for true 3-D charts)
The number of titles that you can use depends on the chart type. For example, a pie chart supports only a chart title because it has no axes.
To add a chart title, activate the chart and choose Chart Tools Layout Labels Chart Title. To add a title to one or more of the axes, choose Chart Tools Layout Labels Axis Titles. These controls are drop-down lists, and each has several options.
After you add a title, you can replace the default text and drag the titles to a different position. However, you can’t change the size of a title by dragging. The only way to change the size of a title is to change the font size.
The chart title or any of the axis titles can also use a cell reference. For example, you can create a link so the chart always displays the text contained in cell A1 as its title. To create a link, select the title, type an equal sign (=), and then point to the cell and press Enter. After you create the link, the Formula bar displays the cell reference when you select the title.
A chart’s legend consists of text and keys that make it easier to identify the data series. A key is a small graphic that corresponds to the chart’s series (one key for each series).
To add a legend to your chart, choose Chart Tools Layout Labels Legend. This drop-down control contains several options for the legend placement. After you’ve added a legend, you can drag it to move it anywhere you like.
If you move a legend from its default position, you may want to change the size of the Plot Area to fill in the gap left by the legend. Just select the Plot Area and drag a border to make it the desired size.
The quickest way to remove a legend is to select the legend and then press Delete.
You can select individual items within a legend and format them separately. For example, you may want to make the text bold to draw attention to a particular data series. To select an element in the legend, first select the legend and then click the desired element.
If you didn’t include legend text when you originally selected the cells to create the chart, Excel displays Series 1, Series 2, and so on in the legend. To add series names, choose Chart Tools Design Select Data to display the Select Data Source dialog box (see Figure 20.6). Select the series name and click the Edit button. In the Edit Series dialog box, type the series name or enter a cell reference that contains the series name. Repeat for each series that needs naming.
In some cases, you may prefer to omit the legend and use callouts to identify the data series. Figure 20.7 shows a chart with no legend. Instead, it uses Shapes to identify each series. These Shapes are from the Callouts section of the Chart Tools Layout Insert Shapes gallery.
For more information about using chart templates, see “Creating Chart Templates,” later in this chapter.
Gridlines can help the viewer determine what the chart series represents numerically. Gridlines simply extend the tick marks on an axis. Some charts look better with gridlines; others appear more cluttered. Sometimes, horizontal gridlines alone are enough, although XY charts often benefit from both horizontal and vertical gridlines.
To add or remove gridlines, choose Chart Tools Layout Axes Gridlines. This drop-down control contains options for all possible gridlines in the active chart.
Each axis has two sets of gridlines: major and minor. Major units display a label. Minor units are located between the labels.
To modify the color or thickness of a set of gridlines, click one of the gridlines and use the commands in the Chart Tools Format Shape Styles group.
If gridlines seem too overpowering, consider changing them to a lighter color or one of the dashed options.
Charts vary in the number of axes that they use. Pie and doughnut charts have no axes. All 2-D charts have two axes (three, if you use a secondary-value axis; four, if you use a secondary-category axis in an XY chart). True 3-D charts have three axes.
Excel gives you a great deal of control over these axes, via the Format Axis dialog box. The content of this dialog box varies depending on the type of axis selected.
Figure 20.8 shows the Axis Options tab of the Format Axis dialog box when a Value Axis is selected. The other tabs in the dialog box deal with cosmetic formatting.
In the Axis Options tab, the four sets of option buttons at the top determine the scale of the axis (it’s minimum, maximum, and intervals). By default, Excel determines these values based on the numerical range of the data, and the settings are set to Auto. You can override Excel’s choice and set any or all of them to Fixed and then enter your own values.
Adjusting the scale of a Value Axis can dramatically affect the chart’s appearance. Manipulating the scale, in some cases, can present a false picture of the data. Figure 20.9 shows two line charts,, which depict the same data. The top chart uses Excel’s default axis scale values, which extend from 8,000 to 9,200. In the bottom chart, the Minimum scale value was set to 0, and the Maximum scale value was set to 10,000. The top chart makes the differences in the data seem more prominent. The lower chart gives the impression that there is not much change over time.
The actual scale that you use depends on the situation. There are no hard-and-fast rules regarding setting scale values, except that you shouldn’t misrepresent data by manipulating the chart to prove a point that doesn’t exist.
If you’re preparing several charts that use similarly scaled data, keeping the scales the same is a good idea so that the charts can be compared more easily.
Another option in the Format Axis dialog box is Values In Reverse Order. The top chart in Figure 20.10 uses default axis settings. The bottom chart uses the Values In Reverse Order option, which reverses the scale’s direction. Notice that the Category Axis is at the top. If you would prefer that it remain at the bottom of the chart, select the Maximum Axis Value option for the Horizontal Axis Crosses setting.
If the values to be plotted cover a very large range, you may want to use a logarithmic scale for the Value Axis. A log scale is most often used for scientific applications. Figure 20.11 shows two charts. The top chart uses a standard scale, and the bottom chart uses a logarithmic scale.
The Base setting is 10, so each scale value in the chart is 10 times greater than the one below it. Increasing the major unit to 100 results in a scale in which each tick mark value is 100 times greater than the one below. You can specify a Base value between 2 and 1,000.
If your chart uses very large numbers, you may want to change the Display Units settings. Figure 20.12 shows a chart that uses very large numbers. The chart below uses the Display Units as Millions settings, with the option to Show Display Units Labels On Chart.
The Major and Minor Tick Mark options control how the tick marks are displayed. Major tick marks are the axis tick marks that normally have labels next to them. Minor tick marks are between the major tick marks.
Excel lets you position the axis labels at three different locations: Next To Axis, High, and Low. Each axis extends from -10 to +10. When you combine these settings with the Axis Crosses At option, you have a great deal of flexibility, as shown in Figure 20.13.
Figure 20.14 shows the Axis Options tab of the Format Axis dialog box when a Category Axis is selected. Some options are the same as those for a Value Axis.
Excel chooses how to display category labels, but you can override its choice. Figure 20.15 shows a column chart with month labels. Excel displays the labels at an angle. If you make the chart wider, the labels will then appear horizontally.
You can adjust the Interval Between Labels settings to skip some labels and cause the text to display horizontally. Figure 20.16 shows the chart with an Interval Between Labels setting of 3.
When you create a chart, Excel recognizes whether your category axis contains date or time values. If so, it uses a time-based category axis. Figure 20.17 shows a simple example. Column A contains dates, and column B contains the values plotted in the column chart. The data consists of values for only 10 dates, yet Excel created the chart with 30 intervals on the category axis. It recognized that the category axis values were dates and created an equal-interval scale.
You can override Excel’s decision to use a time-based category axis by choosing the Text Axis option for Axis Type. Using a time-based category axis presents a truer picture of the data.
Every chart consists of one or more data series. This data translates into chart columns, bars, lines, pie slices, and so on. This section discusses some common operations that involve a chart’s data series.
When you select a data series in a chart, Excel:
Displays the series name in the Chart Elements control (located in the Chart Tools Layout Current Selection group).
Displays the Series
formula in the Formula bar.
Highlights the cells used for the selected series by outlining them in color.
You can make changes to a data series by using the Ribbon or by using the Format Data Series dialog box. This dialog box varies, depending on the type of data series you’re working on (column, line, pie, and so on).
To delete a data series in a chart, select the data series and press the Delete key. The data series disappears from the chart. The data in the worksheet, of course, remains intact.
In some situations, you may need to add another data series to an existing chart. You can re-create the chart and include the new data series, but adding the data to the existing chart is usually easier, and your chart retains any customization that you’ve made.
Figure 20.19 shows a column chart that has two data series (Jan and Feb). The March figures just became available, and now the chart needs to be updated to include the new data.
Excel provides two ways to add a new data series to a chart:
Activate the chart and choose Chart Tools Design Data Select Data. In the Select Data Source dialog box, click the Add button, and Excel displays the Edit Series dialog box. Specify the Series Name (as a cell reference or text) and the range that contains the Series Values.
Select the range to add and copy it to the Clipboard. Then activate the chart and press Ctrl+V to paste the data into the chart.
You may find that you need to modify the range that defines a data series. For example, you may need to add new data points or remove old ones from the data set. The following sections describe several ways to change the range used by a data series.
An easy way to handle data ranges that change over time is to use a table as the data source. When you add rows to a table, the chart updates automatically.
The easiest way to change the data range for a data series is to drag the range outline (this technique works only for embedded charts). When you select a series in a chart, Excel outlines the data range used by that series (see Figure 20.19 You can drag the small dot in the lower-right corner of the range outline to extend or contract the data series. Most of the time, you’ll also need to adjust the range that contains the category labels as well.
Another way to update the chart to reflect a different data range is to use the Edit Series dialog box. A quick way to display this dialog box is to right-click the series in the chart and choose Select Data from the shortcut menu. Excel displays the Select Source Data dialog box. Select the data series in the list, and click Edit to display the Edit Series dialog box, shown in Figure 20.20
You can change the entire data range used by the chart by adjusting the range references in the Chart Data Range field. Or, select a Series from the list and click Edit to modify the selected series.
Every data series in a chart has an associated Series
formula, which appears in the Formula bar when you select a data series in a chart. If you understand how a Series
formula is constructed, you can edit the range references in the Series
formula directly to change the data used by the chart.
The Series
formula is not a real formula: In other words, you can’t use it in a cell, and you can’t use worksheet functions within the Series formula. You can, however, edit the arguments in the Series
formula.
A Series
formula has the following syntax:
=SERIES(series_name, category_labels, values, order, sizes)
The arguments that you can use in the Series
formula include
series_name
: (Optional) A reference to the cell that contains the series name used in the legend. If the chart has only one series, the name argument is used as the title. This argument can also consist of text in quotation marks. If omitted, Excel creates a default series name (for example, Series 1).
category_labels
: (Optional) A reference to the range that contains the labels for the category axis. If omitted, Excel uses consecutive integers beginning with 1. For XY charts, this argument specifies the X values. A noncontiguous range reference is also valid. The ranges’ addresses are separated by commas and enclosed in parentheses. The argument could also consist of an array of comma-separated values (or text in quotation marks) enclosed in curly brackets.
values
: (Required) A reference to the range that contains the values for the series. For XY charts, this argument specifies the Y values. A noncontiguous range reference is also valid. The ranges addresses are separated by a comma and enclosed in parentheses. The argument could also consist of an array of comma-separated values enclosed in curly brackets.
order
: (Required) An integer that specifies the plotting order of the series. This argument is relevant only if the chart has more than one series. Using a reference to a cell is not allowed.
sizes
: (Only for bubble charts) A reference to the range that contains the values for the size of the bubbles in a bubble chart. A noncontiguous range reference is also valid. The ranges addresses are separated by commas and enclosed in parentheses. The argument can also consist of an array of values enclosed in curly brackets.
Range references in a Series
formula are always absolute, and they always include the sheet name. For example:
=SERIES(Sheet1!$B$1,,Sheet1!$B$2:$B$7,1)
You can substitute range names for the range references. If you do so, Excel changes the reference in the Series
formula to include the workbook. For example:
=SERIES(Sheet1!$B$1,,budget.xlsx!MyData,1)
Sometimes, you may want your chart to display the actual numerical values for each data point. You specify data labels by choosing Chart Tools Layout Labels Data Labels. This drop-down control contains several data label options.
Figure 20.21 shows a chart with data labels.
Use the Format Data Labels dialog box to customize the data labels. For example, you can include the series name and the category name along with the value.
The data labels are linked to the worksheet, so if your data changes, the labels also change. If you want to override the data label with other text, select the label and enter the new text.
Often, the data labels aren’t positioned properly—for example, a label may be obscured by another data point. If you select an individual data label, you can drag the label to a better location.
As you work with data labels, you discover that the Excel data labels feature leaves a bit to be desired. For example, it would be nice to be able to specify an arbitrary range of text to be used for the data labels. This capability would be particularly useful in XY charts in which you want to identify each data point with a particular text item. Despite what must amount to thousands of requests, Microsoft still hasn’t added this feature to Excel. You need to add data labels and then manually edit each label.
Sometimes, data that you’re charting may be missing one or more data points. As shown in Figure 20.22, Excel offers three ways to handle the missing data:
Gaps: Missing data is simply ignored, and the data series will have a gap. This is the default.
Zero: Missing data is treated as zero.
Connect With Line: Missing data is interpolated—calculated by using data on either side of the missing point(s). This option is available only for line charts, area charts, and XY charts.
To specify how to deal with missing data for a chart, choose Chart Tools Design Data Select Data. In the Select Data Source, click the Hidden and Empty Cells button. Excel displays its Hidden and Empty Cell Settings dialog box. Make your choice in the dialog box. The option that you choose applies to the entire chart, and you can’t set a different option for different series in the same chart.
Some chart types support error bars. Error bars often are used to indicate “plus or minus” information that reflects uncertainty in the data. Error bars are appropriate only for area, bar, column, line, and XY charts.
To add error bars, select a data series and then choose Chart Tools Layout Analysis >>Error Bars. This drop-down control has several options. You can fine-tune the error bar settings using the Format Error Bars dialog box. The types of error bars are
Fixed value: The error bars are fixed by an amount that you specify.
Percentage: The error bars are a percentage of each value.
Standard Deviation(s): The error bars are in the number of standard-deviation units that you specify. (Excel calculates the standard deviation of the data series.)
Standard Error: The error bars are one standard error unit. (Excel calculates the standard error of the data series.)
Custom: You set the error bar units for the upper or lower error bars. You can enter either a value or a range reference that holds the error values that you want to plot as error bars.
The chart shown in Figure 20.23 displays error bars based on percentage.
When you’re plotting data over time, you may want to plot a trendline that describes the data. A trendline points out general trends in your data. In some cases, you can forecast future data with trendlines. A single series can have more than one trendline.
To add a trendline, select the data series and choose Chart Tools Layout Analysis >Trendline. This drop-down control contains options for the type of trendline. The type of trendline that you choose depends on your data. Linear trends are most common, but some data can be described more effectively with another type.
Figure 20.24 shows an XY chart with a linear trendline and the (optional) equation for the trendline. The trendline describes the “best fit” of the height and weight data.
For more control over trendlines, use the Format Trendline dialog box. One option, Moving Average, is useful for smoothing out data that has a lot of variation (that is, “noisy” data).
The Moving Average option enables you to specify the number of data points to include in each average. For example, if you select 5, Excel averages every five data points. Figure 20.25 shows a chart that uses a moving average trendline.
3-D charts have a few additional elements that you can customize. For example, most 3-D charts have a floor and walls, and the true 3-D charts also have an additional axis. You can select these chart elements and format them to your liking using the Format dialog box.
One area in which 3-D charts differ from Excel’s 2-D charts is in the perspective—or viewpoint—from which you see the chart. In some cases, the data may be viewed better if you change the order of the series.
Figure 20.26 shows two versions of 3-D column chart with two data series. The top chart is the original, and the bottom chart shows the effect of changing the series order. To change the series order, choose Chart Tools Design Data Select Data. In the Select Data Source dialog box, select a series and use the arrow buttons to change its order.
Fortunately, Excel allows you to change the viewing angle of 3-D charts. Doing so may reveal portions of the chart that are otherwise hidden. To rotate a 3-D chart, choose Chart Tools Layout Background 3-D Rotation, which displays the 3-D Rotation tab of the Format Chart Area dialog box. You can make your rotations and perspective changes by clicking the appropriate controls.
Figure 20.27 shows four different views of the same chart. As you can see, you can accidentally distort the chart to make it virtually worthless in terms of visualizing information.
A combination chart is a single chart that consists of series that use different chart types. A combination chart may also include a second value axis. For example, you may have a chart that shows both columns and lines, with two value axes. The value axis for the columns is on the left, and the value axis for the line is on the right. A combination chart requires at least two data series.
Creating a combination chart simply involves changing one or more of the data series to a different chart type. Select the data series to change and then choose Chart Tools Design Type Change Chart Type. In the Change Chart Type dialog box, select the chart type that you want to apply to the selected series. Using a second Value Axis is optional.
If anything other than a series is selected when you issue the Chart Tools Design Type Change Chart Type, all the series in the chart change.
Figure 20.28 shows a column chart with two data series. The values for the Precipitation series are very low—so low that they’re barely visible on the Value Axis scale. This is a good candidate for a combination chart.
The following steps describe how to convert this chart into a combination chart (column and line) that uses a second Value Axis.
Select the Precipitation data series
Right-click and choose Format Data Series for the shortcut menu.
In the Format Data Series dialog box, click the Series Options tab and select the Secondary Axis option.
With the Precipitation data series still selected, choose Chart Tools Design Type Change Chart Type.
In the Change Chart Type dialog box, select the Line type and click OK.
Figure 20.29 shows the modified chart. The Precipitation data appears as a line, and it uses the Value Axis on the right.
This workbook is available on the companion CD-ROM. The filename is weather combination chart.xlsx.
In some cases, you can’t combine chart types. For example, you can’t create a combination chart that involves a bubble chart or a 3-D chart. If you choose an incompatible chart type for the series, Excel lets you know.
Figure 20.30 demonstrates just how far you can go with a combination chart. This chart combines five different chart types: Pie, Area, Column, Line, and XY. I can’t think of any situation that would warrant such a chart, but it’s an interesting demo.
In some cases, you may want to display a data table, which displays the chart’s data in tabular form, directly in the chart.
To add a data table to a chart, choose Chart Tools Layout Labels Data Table. This control is a drop-down list with a few options to choose from. For more options, use the Format Data Table dialog box. Figure 20.31 shows a chart with a data table.
Using a data table is probably best suited for charts on chart sheets. If you need to show the data used in an embedded chart, you can do so using data in cells, which provide you with a lot more flexibility in terms of formatting.
This section describes how to create your own custom chart templates. A template includes customized chart formatting and settings. When you create a new chart, you can choose to use your template rather than a built-in chart type.
If you find that you are continually customizing your charts in the same way, you can probably save some time by creating a template. Or, if you create lots of combination charts, you can create a combination chart template and avoid making the manual adjustments required for a combination chart.
To create a chart template:
Create a chart to serve as the basis for your template. The data you use for this chart is not critical; but for best results, it should be typical of the data that you’ll eventually be plotting with your custom chart type.
Apply any formatting and customizations that you like. This step determines how the appearance of the charts created from the template.
Activate the chart and choose Chart Tools Design Type Save As Template. Excel displays its Save Chart Template dialog box.
Provide a name for the template and click Save.
To create a chart based on a template:
Select the data to be used in the chart.
Choose Insert Charts Other Charts All Chart Types. Excel displays its Insert Chart dialog box.
In the left side of the Insert Chart dialog box, select Templates. Excel displays an icon for each custom template that has been created.
Click the icon that represents the template you want to use and click OK. Excel creates the chart based on the template you selected.
This section describes some interesting (and perhaps useful) chart-making tricks. Some of these tricks use little-known features, and several tricks enable you to make charts that you may have considered impossible to create.
Excel makes it easy to incorporate a pattern, texture, or graphic file for elements in your chart. Figure 20.32 shows a chart that uses a photo as the background for a chart’s Chart Area element.
To display an image in a chart element, use the Fill tab in the element’s Format dialog box. Select the Picture Or Texture Fill option and then click the button that corresponds to the image source (File, Clipboard, or ClipArt). If you use the Clipboard button, make sure that you’ve copied your image first. The other two options prompt you for the image.
Figure 20.33 shows two more examples: a pie chart that uses Office clipart as its fill, and a column chart that uses a Shape, which was inserted on a worksheet and then copied to the Clipboard.
The examples in this section are available on the companion CD-ROM. The filename is picture charts.xlsx.
Using images in a chart offers unlimited potential for creativity. The key, of course, is to resist the temptation to go overboard. A chart’s primary goal is to convey information, not to impress the viewer with your artistic skills.
You’re probably familiar with a “thermometer” type display that shows the percentage of a task that’s completed. Creating such a display in Excel is very easy. The trick involves creating a chart that uses a single cell (which holds a percentage value) as a data series.
Figure 20.34 shows a worksheet set up to track daily progress toward a goal: 1,000 new customers in a 15-day period. Cell B18 contains the goal value, and cell B19 contains a simple formula that calculates the sum. Cell B21 contains a formula that calculates the percent of goal:
=B19/B18
As you enter new data in column B, the formulas display the current results.
A workbook with this example is available on the companion CD-ROM. The filename is thermometer chart.xlsx.
To make the thermometer chart, select cell B21 and create a column chart from that single cell. Notice the blank cell above cell B21. Without this blank cell, Excel uses the entire data block for the chart, not just the single cell. Because B21 is isolated from the other data, only the single cell is used.
Other changes required:
Select the horizontal category axis and press Delete to remove the category axis from the chart.
Remove the legend.
Add a text box, linked to cell B21 to display the percent accomplished.
In the Format Data Series dialog box (Series Options tab), set the Gap width to 0, which makes the column occupy the entire width of the plot area.
Select the Value Axis and display the Format Value Axis dialog box. In the Axis Options tab, set the Minimum to 0 and the Maximum to 1.
Make any other cosmetic adjustments to get the look you desire.
Figure 20.35 shows another chart based on a single cell. It’s a pie chart set up to resemble a gauge. Although this chart displays only one value (entered in cell B1), it actually uses three data points (in A4:A6).
A workbook with this example is available on the companion CD-ROM. The filename is gauge chart.xlsx.
One slice of the pie—the slice at the bottom—always consists of 50 percent, and that slice is hidden. (The slice uses No Fill and No Outline.) The other two slices are apportioned based on the value in cell B1. The formula in cell 44 is
=MIN(B1,100%)/2
This formula uses the MIN function to display the smaller of two values: either the value in cell B1 or 100 percent. It then divides this value by 2 because only the top half of the pie is relevant. Using the MIN function prevents the chart from displaying more than 100 percent.
The formula in cell A5 simply calculates the remaining part of the pie—the part to the right of the gauge’s needle:
=50%-A4
The chart’s title was moved below the half-pie. The chart also contains a text box, linked cell B1, that displays the percent completed.
You may have noticed that the Fill tab of the Format Data Series dialog box has an option labeled Vary Colors By Point. This option simply uses more colors for the data series. Unfortunately, the colors aren’t related to the values of the data series.
This section describes how to create a column chart in which the color of each column depends on the value that it’s displaying. Figure 20.36 shows such a chart (it’s more impressive when you see it in color). The data used to create the chart is in range A1:F14.
A workbook with this example is available on the companion CD-ROM. The filename is conditional colors.xlsx.
This chart displays four data series, but some data is missing for each series. The data for the chart is entered in column B. Formulas in columns C:F determine which series the number belongs to by referencing the bins in Row 1. For example, the formula in cell C3 is
=IF(B3<=$C$1,B3,"")
If the value in column B is less than the value in cell C1, then the value goes in this column. The formulas are set up such that a value in column B goes into only one column in the row.
The formula in cell D3 is a bit more complex because it must determine whether cell C3 is greater than the value in cell C1 and less than or equal to the value in cell D1:
=IF(AND($B3>C$1,$B3<=D$1),$B3,"")
The four data series are overlaid on top of each other in the chart. The trick involves setting the Series Overlap value to a large number. This setting determines the spacing between the series. Use the Series Options tab of the Format Data Series dialog box to adjust this setting.
With a bit of creativity, you can create charts that you may have considered impossible. For example, Figure 20.37 shows a chart sometimes referred to as a comparative histogram chart. Such charts often display population data.
A workbook with this example is available on the companion CD-ROM. The filename is comparative histogram.xlsx.
Here’s how to create the chart:
Enter the data in A1:C8, as shown in Figure 20.38. Notice that the values for females are entered as negative values, which is very important.
Select A1:C8 and create a bar chart. Use the subtype labeled Clustered Bar.
Select the horizontal axis and display the Format Axis dialog box.
Click the Number tab and specify the following custom number format:
0%;0%;0%
This custom format eliminates the negative signs in the percentages.
Select the vertical axis and display the Format Axis dialog box.
In the Axis Options tab, set all tick marks to None and set the Axis Labels option to Low. This setting keeps the vertical axis in the center of the chart but displays the axis labels at the left side.
Select either of the data series and display the Format Data Series dialog box.
In the Series Options tab, set the Series Overlap to 100% and the Gap Width to 0%.
Delete the legend and add two text boxes to the chart (Females and Males) to substitute for the legend.
Apply other formatting and labels as desired.
A Gantt chart is a horizontal bar chart often used in project management applications. Although Excel doesn’t support Gantt charts per se, creating a simple Gantt chart is fairly easy. The key is getting your data set up properly.
Figure 20.38 shows a Gantt chart that depicts the schedule for a project, which is in the range A2:C13. The horizontal axis represents the total time span of the project, and each bar represents a project task. The viewer can quickly see the duration for each task and identify overlapping tasks.
A workbook with this example is available on the companion CD-ROM. The filename is gantt chart.xlsx.
Column A contains the task name, column B contains the corresponding start date, and column C contains the duration of the task, in days.
Follow these steps to create this chart:
Select the range A2:C13, and create a Stacked Bar Chart.
Delete the legend.
Select the category (vertical) axis and display the Format Axis dialog box.
In the Format Axis dialog box, specify Categories In Reverse Order to display the tasks in order, starting at the top. Choose Horizontal Axis Crosses At Maximum Category to display the dates at the bottom.
Select the Start Date data series and display the Format Data Series dialog box.
In the Format Data Series dialog box, click the Series Options tab and set the Series Overlap to 100%. Click the Fill tab, and specify No Fill. Click the Border Color tab and specify No Line. These steps effectively hide the data series.
Select the value (horizontal) axis and display the Format Axis dialog box.
In the Format Axis dialog box, adjust the Minimum and Maximum settings to accommodate the dates that you want to display on the axis. Unfortunately, you must enter these values as date serial numbers, not actual dates. In this example, the Minimum is 39181 (April 9, 2007) and the Maximum is 39261 (June 28, 2007). Specify 7 for the Major Unit, to display one-week intervals. Use the number tab to specify a date format for the axis labels.
Apply other formatting as desired.
An XY chart is useful for plotting various mathematical and trigonometric functions. For example, Figure 20.39 shows a plot of the SIN function. The charts plots y for values of x (expressed in radians) from –5 to +5 in increments of 0.5. Each pair of x and y values appears as a data point in the chart, and the points connect with a line.
The function is expressed as:
y = SIN(x)
The corresponding formula in cell B2 (which is copied to the cells below) is
=SIN(A2)
The preceding section describes how to plot functions that use a single variable (x). You also can plot functions that use two variables. For example, the following function calculates a value of z for various values of two variables (x and y):
z = SIN(x)*COS(y)
Figure 20.40 shows a surface chart that plots the value of z for 21 x values ranging from 1 to 4, and for 21 y values ranging from 1 to 4. Both x and y use an increment of 0.15.
The formula in cell B2, copied across and down, is
=SIN($A2*COS(B$1))