There are some ways of using Excel names in charts that can help make your charts easier to set up and maintain. You can use names to define the data series; in conjunction with dynamic ranges, you can then build dynamic charts without the use of macros.
Using names in charts
You can use a name in most of the places that you can use a range, when setting up a chart. However, you must always prefix the name with the worksheet or workbook name and an exclamation mark.
In particular, you can use names when specifying data series.
If you are using a name with workbook visibility (ie not local to a worksheet), you can prefix the name with either the workbook name or the sheet name when you type it in. Excel will automatically change the prefix to the workbook name. If you are using a name that is local to a worksheet, you must prefix the name with the worksheet name.
The useful thing about using a name to specify a data series is that if you change the range the name refers to, the chart will automatically show the new data. You don’t have to edit the chart in any way. As editing charts can be fiddly, this can be very helpful.
You cannot use names when specifying a data range. If you type a name in, Excel will automatically change it to the absolute address.