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:
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
Figure 3-19 shows a simple chart with a SERIES formula that does not use
named ranges. The SERIES formula for this chart is
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.