Microsoft Office Tutorials and References

In Depth Information

**Using Range Names in a SERIES Formula**

The Plot Visible Cells Only setting applies only to the active chart. It is not a

global setting that would be applied to all charts.

Using Range Names

in a SERIES Formula

As you may know, Excel allows you to provide a name to a cell or range. After

defining the name, you can use it in your formulas in place of the range reference.

For example, if range A1:A12 is named
Sales
, you can use the following formula to

calculate the sum of those cells:

=SUM(Sales)

When you create a formula that uses a named range, Excel will automatically

substitute the name. But when you create a chart that uses a named range, Excel

does
not
automatically substitute the name in the SERIES formulas. You can,

however, edit the range references in a SERIES formula and replace the range references

with the appropriate range name.

Using named ranges in a SERIES formula offers two advantages:

The SERIES formulas are easier to read.

When you change the range name definition, the chart will update

automatically.

Figure 3-19 shows a simple chart with a SERIES formula that does not use

named ranges. The SERIES formula for this chart is

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

When you edit the SERIES formula and replace the range references with names,

make sure that you replace only the range address and keep the sheet reference and

the exclamation point that precedes the range reference. When you press Enter

after making the modification, you’ll find that Excel changes the reference in the

SERIES formula to include the workbook name. If you don’t want to edit the

SERIES formula, you can use the Series tab of the Source Data dialog box to

substitute the name for the range address.