When you create a chart, you almost always start by selecting the worksheet data to be plotted. Normally, you select the numeric data as well as the category labels and series names, if they exist.

When creating a chart, a key consideration is the orientation of your data: by rows or by columns. In other words, is the data for each series in a single row or in a single column?

Excel attempts to guess the data orientation by applying a simple rule: If the data rows outnumber the data columns, each series is assumed to occupy a column. If the number of data columns is greater than or equal to the number of data rows, each series is assumed to occupy a row. In other words, Excel always defaults to a chart that has more categories than series.

After you create the chart, it's a simple matter to override Excel's orientation guess. Just activate the chart and choose Chart Tools

Your choice of orientation determines how many series the chart has, and it affects the appearance and (possibly) the legibility of your chart. Figure 3-1 shows two charts that use the same data. The chart on the left displays three series, arranged in columns. The chart on the right shows four series, arranged in rows.

Figure 3-1. Your choice of data orientation (by row or by column) determines the number of series in the chart.

Note

Excel allows you to create an empty chart — a chart with no data. For example, select an empty cell and choose Insert

Dealing with numeric category labels

It's not uncommon to have category labels that consist of numbers. For example, you may create a chart that shows sales by year, and the years are numeric values. If your category labels include a heading, Excel will (incorrectly) interpret the category labels as a data series. The following figure shows an example.

You can, of course, choose Chart Tools

In many situations, you may find it necessary to modify the ranges used by a chart. Specifically, you may want to do the following:

All these topics are covered in the following sections.

Excel provides four ways to add a new series to an existing chart:

These techniques are described in the sections that follow.

Adding a New Series by Copying a Range

One way to add a new series to a chart is to perform a standard copy/paste operation. Follow these steps:

1. Select the range that contains the data to be added.

2. Choose Home

   

3. Click the chart to activate it.

4. Choose Home

   

For more control when adding data to a chart, choose Home

Figure 3-2. Using the Paste Special dialog box to add a series to a chart.

Following are some pointers to keep in mind when you add a new series using the Paste Special dialog box:

• Make sure that the New Series option is selected.

• Excel will guess at the data orientation, but you should verify that the Rows or Columns option is guessed correctly.

• If the range you copied included a cell with the series name, ensure that the Series Names in First Row/Column option is selected.

• If the first column of your range selection included category labels, make sure that the Categories (X Labels) in First Column/Row check box is selected.

• If you want to replace the existing category labels, select the Replace Existing Categories check box.

Adding a New Series Using the Select Source Data Dialog Box

The Select Source Data dialog box provides another way to add a new series to a chart, as follows:

1. Click the chart to activate it.

2. Choose Chart Tools

   

3. Click the Add button to display the Edit Series dialog box.

4. Use the range selector controls to specify the cell for the Series Name (optional) and Series Values. (See Figure 3-3.)

5. Click the OK button to close the Edit Series dialog box and return to the Select Source Data dialog box.

6. Click the OK button to close the Select Source Data dialog box, or click the Add button to add another series to the chart.

Figure 3-3. Using the Edit Series dialog box to add a series to a chart.

Note

The configuration of the Edit Series dialog box varies, depending on the chart type. For example, if the chart is a scatter chart, the Edit Series dialog box displays range selectors for the Series Name, the Series X Values, and the Series Y Values. If the chart is a bubble chart, the dialog box displays an additional range selector for the Series Bubble Size.

Adding a New Series by Typing a New SERIES Formula

Excel provides yet another way to add a new series: Type a new SERIES formula. Follow these steps:

1. Click the chart to activate it.

2. Click the formula bar.

3. Type the new SERIES formula and press Enter.

Figure 3-4 shows a new SERIES formula in the formula bar. When the user presses Enter, this SERIES formula will add the data in column D to the chart.

Figure 3-4. Entering a new SERIES formula for a chart creates a new series.

This method is certainly not the most efficient way to add a new series to a chart. It requires that you understand how the SERIES formula works, and (as you might expect) it can be rather error-prone. Note, however, that you don't need to type the SERIES formula from scratch. You can copy an existing SERIES formula, paste it into the formula bar, and then edit the SERIES formula to create a new series.

Note

For more information about the SERIES formula, see the "SERIES formula syntax" sidebar, later in this chapter.

The easiest way to delete a series from a chart is to use the keyboard: Select the series and press Delete.

You can also use the Select Source Data dialog box to delete a series. Choose Chart Tools

After you've created a chart, you may want to modify the data ranges used by the chart. For example, you may need to expand the range to include new data. Or, you might need to substitute an entirely different range. Excel offers a number of ways to perform these operations:

Each of these techniques is described in the sections that follow.

Using Range Highlighting to Change Series Data

When you select a series in a chart, Excel highlights the worksheet ranges used in that series. This range highlighting consists of a colored outline around each range used by the series. Figure 3-5 shows an example in which the chart series (Region 1) is selected. Excel highlights the following ranges:

• C2 (the series name)

• B3:B8 (the category labels)

• C3:C8 (the values)

Figure 3-5. Selecting a chart series highlights the data used by the series.

Note that these cell addresses for these ranges also appear in the SERIES formula for the selected series. Each of the highlighted ranges contains a small "handle" at each corner. You can perform two operations with the highlighted data:

• Expand or contract the data range: Click one of the handles and drag it to expand the outlined range (specify more data) or contract the data range (specify less data). When you move your cursor over a handle, the mouse pointer changes to a double arrow.

• Specify an entirely different data range: Click one of the borders of the highlight and then drag it to highlight a different range. When you move the cursor over a border, the mouse pointer changes to a four-way arrow.

Figure 3-6 shows the chart after the data range has been changed. In this case, the highlight around cell C2 was dragged to cell D2, and the highlight around C3:C8 was dragged to D3:D8 and then expanded to include D3:D14. Notice that the range for the category labels (B3:B8) has not been modified. To finish the job, that range needs to be expanded to B3:B14.

Figure 3-6. The chart's data range has been modified.

Modifying chart source data by using the range highlights is probably the simplest method. Note, however, that this technique works only with embedded charts (not with chart sheets). In addition, it does not work when the chart's data is in a worksheet other than the sheet that contains the embedded chart.

Note

A surface chart is a special case. You cannot select an individual series in a surface chart. But when you select the plot area of a surface chart, Excel highlights all the data used in the chart. You can then use the range highlighting to change the ranges used in the chart.

Using the Select Data Source Dialog Box to Change Series Data

Another method of modifying a series data range is to use the Select Data Source dialog box. Select your chart and then choose Chart Tools

Figure 3-7. The Select Data Source dialog box.

Notice that the Select Data Source dialog box has three parts:

• The top Part of the dialog box shows the entire data range used by the chart. You can change this range by selecting new data.

• The lower-left Part displays a list of each series. Select a series and click the Edit button to display the Edit Series dialog box to change the data used by a single series. (See Figure 3-8.)

• The lower-right Part displays the category axis labels. Click the Edit button to display the Axis Labels dialog box to change the range used as the axis labels.

Figure 3-8. The Edit Series dialog box is one way to change the data used by a series.

Note

The Edit Series dialog box can vary somewhat, depending on the chart type. The Edit Series dialog box for a bubble chart, for example, has four range selector controls: Series Name, Series X Values, Series Y Values, and Series Bubble Size.

Editing the SERIES Formula to Change Series Data

Every chart series has its own SERIES formula. When you select a data series in a particular chart, its SERIES formula appears in the formula bar. In Figure 3-9, for example, you can see one of two SERIES formulas in the formula bar for a chart that displays two data series.

Figure 3-9. The SERIES formula for the selected data series appears in the formula bar.

Although a SERIES formula is displayed in the formula bar, it is not a "real" formula. In other words, you can't put this formula into a cell, and you can't use worksheet functions within the SERIES formula. You can, however, edit the arguments in the SERIES formula to change the ranges used by the series. To edit the SERIES formula, just click in the formula bar and use standard editing techniques. Refer to the nearby sidebar, "SERIES formula syntax," to find out about the various arguments for a SERIES formula.

Note

When you modify a series data range using either of the techniques discussed previously in this section, the SERIES formula is also modified. In fact, those techniques are simply easy ways of editing the SERIES formula.

Following is an example of a SERIES formula:

=SERIES(Sheet2!$D$2, Sheet2!$B$3:$B$8, Sheet2!$D$3:$D$8, 2)

SERIES formula syntax

A SERIES formula has the following syntax:

=SERIES(series_name, category_labels, values, order, sizes)

The arguments you can use in the SERIES formula include the following:

• 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 scatter charts, this argument specifies the x values. A noncontiguous range reference is also valid. (The ranges' addresses are separated by a comma and enclosed in parentheses.) The argument may also consist of an array of comma-separated values (or text in quotation marks) enclosed in braces.

• values: (Required) A reference to the range that contains the values for the series. For scatter 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 may also consist of an array of commaseparated values enclosed in braces.

• 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 a comma and enclosed in parentheses.) The argument may also consist of an array of values enclosed in braces.

This SERIES formula does the following:

• Specifies that cell D2 (on Sheet2) contains the series name

• Specifies that the category labels are in B3:B8 on Sheet2

• Specifies that the data values are in D3:D8, also on Sheet2

• Specifies that the series will be plotted second on the chart (the final argument is 2)

Notice that range references in a SERIES formula always include the worksheet name, and the range references are always absolute references. An absolute reference, as you may know, uses a dollar sign before the row and column Part of the reference. If you edit a SERIES formula and remove the sheet name or make the cell references relative, Excel will override these changes.

Every chart series has a name, which is displayed in the chart's legend. If you don't explicitly provide a name for a series, it will have a default name, such as Series1, Series2, and so on.

The easiest way to name a series is to do so when you create the chart. Typically, a series name is contained in a cell adjacent to the series data. For example, if your data is arranged in columns, the column headers usually contain the series names. If you select the series names along with the chart data, those names will be applied automatically.

Figure 3-10 shows a chart with three series. The series names, which are stored in B3:D3, are Main, N. County, and Westside. The SERIES formula for the first data series is as follows:

=SERIES(Sheet1!$B$3, Sheet1!$A$4:$A$8, Sheet1!$B$4:$B$8, 1)

Figure 3-10. The series names are picked up from the worksheet.

Note that the first argument for this SERIES formula is a reference to the cell that contains the series name.

Changing a Series Name

The series name is the text that appears in a chart's legend. In some cases, you may prefer the chart to display a name other than the text that's in the worksheet. It's a simple matter to change the name of a series. Follow these steps:

1. Activate the chart.

2. Choose Chart Tools

   

3. In the Select Data Source dialog box, select the series that you want to modify, and click the Edit button to display the Edit Series dialog box.

4. Type the new name in the Series Name box.

Normally, the Series Name box contains a cell reference. But you can override this and enter any text.

Note

If you go back to a series that you've already renamed, you'll find that Excel has converted your text into a formula — an equal sign, followed by the text you entered (the new series name), in quotation marks.

Figure 3-11 shows the previous chart, after changing the series names. The first argument in each of the SERIES formulas no longer displays a cell reference. It now contains the literal text. For example, the SERIES formula for the first series is as follows:

=SERIES("Branch 1",Sheet1!$A$4:$A$8, Sheet1!$B$4:$B$8, 1)

Figure 3-11. The series names have been changed; the new names are shown in the legend.

If you need to change the name of a series, you may find it easier to edit the SERIES formula directly.

=SERIES(,Sheet2!$A$2:$A$6, Sheet2!$B$2:$B$6, 1)

Every chart series has a plot order parameter. A chart's legend usually displays the series names in the order in which they are plotted. I say usually, because you do find exceptions. For example, consider a combination chart that displays a column series and a line series. Changing the series order does not change the order in which the series are listed in the legend.

To change the plot order of a chart's data series, use the Select Data Source dialog box. In the lower-left list, the series are listed in the order in which they are plotted. Select a series, and then use the up- or down-arrow buttons to adjust its position in the list — which also changes the plot order of the series.

Alternatively, you can edit the SERIES formulas — specifically, the fourth parameter in the SERIES formulas. See the "SERIES formula syntax" sidebar, earlier in this chapter, for more information about SERIES formulas.

For some charts, the plot order is not important. For others, however, you may want to change the order in which the series are plotted. Figure 3-12 shows a stacked column chart generated from the data in A1:E1. Notice that the columns are stacked, beginning with the first data series (Region 1) on the bottom. You might prefer to stack the columns in the order in which the data appears. To do so, you need to change the plot order.

Figure 3-12. The plot order of this chart does not correspond to the order of the data.

After changing the plot order of the series, the chart now appears as in Figure 3-13.

Figure 3-13. After changing the plot order, the stacked columns correspond to the order of the data.

Figure 3-14 shows another example. This chart displays three data series in a 3-D column chart. The columns for the Laptops and PDAs series are obscured by the columns for the Desktops series. One solution is to edit the plot order parameter of the SERIES formulas, as described previously. But in this case, you can use a more direct solution. Follow these steps:

Figure 3-14. Some of the data points are obscured.

The result, shown in Figure 3-15, is a much more legible chart. Note that the option to plot the series in reverse order does not actually change the plot order for the SERIES formulas. The SERIES formulas remain the same, but Excel displays them in reverse order on the series axis. Consequently, if the chart has a legend, the order of the entries in the legend remains the same.

Figure 3-15. After reversing the series axis, the chart is more legible.

Most of the time, a chart series consists of a contiguous range of cells. But Excel does allow you to plot data that is not in a contiguous range. Figure 3-16 shows an example of a noncontiguous series. This chart displays monthly data for the first and fourth quarter. The data in this single series is contained in rows 2:4 and 11:13. Notice that the category labels display Jan, Feb, Mar, Oct, Nov, and Dec.

Figure 3-16. This chart uses data in a noncontiguous range.

The SERIES formula for this series is as follows:

=SERIES(,(Sheet1!$A$2:$A$4, Sheet1!$A$11:$A$13),(Sheet1!$B$2:$B$4, Sheet1!$B$11:$B$13),1)

The first argument is omitted, so Excel uses the default series name. The second argument specifies six cells in column A as the category labels. The third argument specifies six corresponding cells in column B as the data values. Note that the range arguments for the noncontiguous ranges are displayed in parentheses, and each subrange is separated by a comma.

Typically, data to be used on a chart resides on a single sheet. Excel, however, does allow a chart to use data from any number of worksheets, and the worksheets need not even be in the same workbook.

Normally, you select all the data for a chart before you create the chart. But if your chart uses data from different worksheets, you need to create the chart using data from a single sheet and then either edit the existing series or add new series after the chart is made (see the section "Adding a New Series to a Chart," earlier in this chapter).

Figure 3-17 shows a chart that uses data from two other worksheets.

The SERIES formulas for this chart are as follows:

=SERIES(Region1!$A$2, Region1!$B$1:$G$1, Region1!$B$2:$G$2, 1)
=SERIES(Region2!$A$2, Region2!$B$2:$G$2, 2)

Figure 3-17. This chart uses data from two worksheets.

Sometimes, data that you use in a chart may lack one or more data points. Excel offers the following ways to handle the missing data:

For some reason, Excel makes these options rather difficult to locate. The Ribbon doesn't contain these options, and you don't specify these options in the Format Data Series dialog box. Rather, you must follow these steps:

Figure 3-18. Use the Hidden and Empty Cell Settings dialog box to specify how to handle missing data.

The setting that you choose applies only to the active chart and applies to all series in the chart. In other words, you can't specify a different missing data option for different series in the same chart.

Figure 3-19 shows three charts that depict the three missing data options. The chart shows temperature readings at one-hour intervals, and four data points are missing. The "correct" missing data option depends on the message that you want to convey. In the top chart, the missing data is obvious because of the gaps in the line. In the middle chart, the missing data is shown as zero — which is clearly misleading. In the bottom chart, the missing data is interpolated. Because of the time-based and relatively "smooth" nature of the data, interpolating the missing data may be an appropriate choice.

Figure 3-19. These three charts depict the three ways to present missing data in a chart.

Usually, Excel doesn't plot data that is in a hidden row or column. You can sometimes use this to your advantage, because it's an easy way to control what data appears in the chart.

Figure 3-20 shows a scatter chart that uses data stored in a table (created by choosing Insert

Figure 3-20. A scatter chart that uses data in a table.

Figure 3-21. After filtering the table, the scatter chart shows only the visible rows.

In some cases, when you're working with outlines or filtered tables (both of which use hidden rows), you may not like the idea that hidden data is removed from your chart. To override this, activate the chart and choose Chart Tools

Normally, an Excel chart uses data stored in a range. Change the data in the range, and the chart updates automatically. In some cases, you may want to "unlink" the chart from its data ranges and produce a static chart — a chart that never changes. For example, if you plot data generated by various what-if scenarios, you may want to save a chart that represents some baseline so that you can compare it with other scenarios. You can create such a chart in the following ways:

Converting a Chart to a Picture

To convert a chart to a static picture, follow these steps:

1. Create the chart as usual and make any necessary modifications.

2. Click the chart to activate it.

3. Choose Home

   

4. Click any cell to deselect the chart.

5. Choose Home

   

The result is a picture of the original chart. This picture can be edited as a picture, but not as a chart. In other words, you can no longer modify properties such as chart type, data labels, and so on.

Although a chart converted to a picture cannot be edited as a chart, it can be edited as a picture. When you select such a picture, you see Excel's Picture Tools

Converting Range Reference to Arrays

The other way to unlink a chart from its data is to convert the SERIES formula range references to arrays. Figure 3-23 shows an example of a pie chart that does not use data stored in a worksheet. Rather, the chart's data is stored directly in the SERIES formula, which is as follows:

=SERIES(,{"Work","Sleep","Drive","Eat","Other"},{9, 7,2, 1,5},1)

Figure 3-22. After converting a chart to a picture, you can apply various types of formatting to the picture.

Figure 3-23. This chart is not linked to a data range.

The first argument, the series name, is omitted. The second argument consists of an array of five text strings. Notice that each array element appears in quotation marks and is separated by a comma. The array is enclosed in braces. The chart's data is stored as another array (the third argument).

This chart was originally created by using data stored in a range. Then, the SERIES formula was "delinked" from the range and the original data was deleted. The result is a chart that does not rely on data stored in a range.

Follow these steps to convert the range references in a SERIES formula to arrays:

1. Create the chart as usual.

2. Click the chart series.

   The SERIES formula appears in the formula bar.

3. Click the formula bar.

4. Press F9.

5. Press Enter, and the range references are converted to arrays.

Repeat this procedure for each series in the chart. This method of unlinking a chart series (as opposed to creating a picture) enables you to continue to edit the chart. Note that you can also convert just a single argument to an array. Highlight the argument in the SERIES formula and press F9.

Note

Excel imposes a 1, 024-character limit to the length of a SERIES formula, so this technique does not work if a chart series contains a large number of values or category labels.

A chart can use zero, two, three, or four axes, and any or all of them can be hidden if desired.

Pie charts and doughnut charts have no axes. Common chart types, such as a standard column or line chart, use a single category axis and a single value axis. If your chart has at least two series — and it's not a 3-D chart — you can create a secondary value axis. Each series is associated with either the primary or the secondary value axis. Why use two value axes? Two value axes are most often used when the data being plotted in a series varies drastically in scale from the data in another series.

Creating a Secondary Value Axis

Figure 3-24 shows a line chart with two data series: Income and Profit Margin. Compared to the Income values, the Profit Margin numbers (represented by squares) are so small that they barely show up on the chart. This is a good candidate for a secondary value axis.

Figure 3-24. The values in the Profit Margin series are so small that they aren't visible in the chart.

To add a secondary value axis, follow these steps:

A new value axis is added to the right side of the chart, and the Profit Margin series uses that value axis. Figure 3-25 shows the dual-axis chart. I added axis titles (by choosing Chart Tools

Figure 3-25. Using a secondary value axis for the Profit Margin series.

Creating a Chart with Four Axes

Very few situations warrant a chart with four axes. The problem, of course, is that using four axes almost always causes the chart to be difficult to understand. An exception is scatter charts. Figure 3-26 shows a scatter chart that has two series, and the series vary quite a bit in magnitude on both dimensions. If the objective is to compare the shape of the lines, this chart does not do a very good job because most of the chart consists of white space. Using four axes might solve the problem.

Figure 3-26. The two series vary in magnitude.

Follow these steps to add two new value axes for this scatter chart:

Figure 3-27 shows the result. The 2006 series uses the left and bottom axes, and the 2007 series uses the right and top axes (I added axis titles to clarify this). The scales for each axis can be adjusted separately.

Figure 3-27. This chart uses four value axes.