Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)

 Download CD Content

When most people think of Excel, they think of analyzing rows and columns of numbers. As you probably know already, though, Excel is no slouch when it comes to presenting data visually in the form of a chart. In fact, it's a safe bet that Excel is the most commonly used software for creating charts.

After you've created a chart, you have almost complete control over nearly every aspect of each chart. This chapter, which assumes that you're familiar with Excel's charting feature, demonstrates some useful charting techniques-most of which involve formulas.

Understanding the SERIES Formula

You create charts from numbers that appear in a worksheet. You can enter these numbers directly, or you can derive them as the result of formulas.

Normally, the data used by a chart resides in a single worksheet, within one file, but that's not a strict requirement. A single chart can use data from any number of worksheets, or even from different workbooks.

A chart consists of one or more data series, and each data series appears as a line, column, bar, and so on. Each series in a chart has a SERIES formula. When you select a data series in a chart, Excel highlights the data with an outline, and its SERIES formula appears in the formula bar (see Figure 17-1).

Figure 17-1: The formula bar displays the SERIES formula for the selected data series in a chart.

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

• 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 series_name argument is used as the title. The series_name 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 range's 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 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 non-contiguous range reference is also valid. (The range's addresses are separated by a comma and enclosed in parentheses.) The argument may 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 range's addresses are separated by a comma and enclosed in parentheses.) The argument may 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)

A range reference can consist of a noncontiguous range. If so, each range is separated by a comma, and the argument is enclosed in parentheses. In the following SERIES formula, the values range consists of B2:B3 and B5:B7:

=SERIES(,,(Sheet1!$B$2:$B$3,Sheet1!$B$5:$B$7),1)

Although a SERIES formula can refer to data in other worksheets, all the data for a series must reside on a single sheet. The following SERIES formula, for example, is not valid because the data series references two different worksheets:

=SERIES(,,(Sheet1!$B$2,Sheet2!$B$2),1)

Using Names in a SERIES Formula

You can substitute range names for the range references in a SERIES formula. When you do so, Excel changes the reference in the SERIES formula to include the workbook name. For example, the SERIES formula shown here uses a range named MyData (located in a workbook named budget.xlsx). Excel added the workbook name and exclamation point.

=SERIES(Sheet1!$B$1,,budget.xlsx!MyData,1)

Here I present a number of chart-making tips that you might find helpful:

• Right-click any chart element and choose Format xxxx, where xxxx represents the element's name (or press Ctrl+1). Excel displays its Format dialog box, which remains open until you close it. The formatting controls in the Format dialog box enable you to perform actions that aren't available in the Ribbon.

• To create a chart with a single keystroke, select the data you want to chart and press Alt+F1. The result is an embedded chart of the default chart type. To create the chart on a chart sheet, press Alt+F11 instead of Alt+F1.

• If you have many charts of the same type to create, create and format the first chart and make a template from that chart by choosing Chart Tools Design Type Save as Template. When you create your additional charts, use Insert Charts Other Charts All Chart Types, and then choose your template from the list.

• To print an embedded chart on a separate sheet of paper, select the chart and choose Office Print. Excel prints the chart on a page by itself and does not print the worksheet.

• If you don't want a particular embedded chart to appear on your printout, select the chart and click the dialog box launcher in the Chart Tools Format Size group to display the Size And Properties dialog box. Click the Properties tab and remove the check mark from the Print Object check box.

• Sometimes, using a mouse to select a particular chart element is tricky. You may find it easier to use the keyboard to select a chart element. When a chart is activated, press the up arrow or down arrow to cycle through all parts in the chart. When a data series is selected, press the right arrow or left arrow to select individual points in the series. Or, select the chart element by using the Chart Tools Format Chart Elements drop-down control. This control is useful for selecting chart elements, and it also displays the name of the selected element. Better yet, put this control in your Quick Access toolbar so it's always visible.

• You can delete all data series from a chart. If you do so, the chart appears empty. It retains its settings, however. Therefore, you can add a data series to an empty chart, and it again looks like a chart.

• For more control over positioning your chart, press Ctrl while you click the chart. Then use the arrow keys to move the chart 1 pixel at a time.

Using names in a series formula provides a significant advantage: If you change the range reference for the name, the chart automatically displays the new data. In the preceding SERIES formula, for example, assume the range named MyData refers to A1:A20. The chart displays the 20 values in that range. You can then use the Name Manger to redefine MyData as a different range-say, A1:A30. The chart then displays the 30 data points defined by MyData. (No chart editing is necessary.)

As I noted previously, a SERIES formula cannot use worksheet functions. You can, however, create named formulas (which use functions) and use these named formulas in your SERIES formula. As you see later in this chapter, this technique enables you to perform some useful charting tricks.

Unlinking a Chart Series from Its Data Range

Normally, an Excel chart uses data stored in a range. If you change the data in the range, 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 you can compare it with other scenarios. There are two ways to create such a chart:

• Paste it as a picture. Activate the chart and then choose Home Clipboard Paste As Picture CopyAs Picture. (Accept the default settings in the Copy Picture dialog box.) Then, activate any cell and choose Home Clipboard Paste As Pictur Paste As Picture (or press Ctrl+V). The result is a picture of the copied chart. You can then delete the original chart if you like.

• Convert the range references to arrays. Click a chart series and then click the formula bar to activate the SERIES formula. Press F9 to convert the ranges to arrays (see Figure 17-2). Repeat this for each series in the chart. This technique (as opposed to creating a picture) enables you to continue to edit and format the chart. This technique will not work for large amounts of data because Excel imposes a limit on the length of a SERIES formula (about 1,024 characters).

  Figure 17-2: A SERIES formula that uses arrays rather than ranges.