Creating a chart that uses a named range is excel can be accomplished by following these basic steps. Before we begin, let us first look at the building blocks.

What is a named range?

A named range is a group of cells that have explicitly been given a name. Every time you refer to that name, the workbook automatically knows that the user is referring a collection of cells that name represents. You can assign a name to a range of cells by simply selecting the cells and then entering a name for them in the name box. The cells don’t have to be contiguous and can consist of several independent ranges.

What is a dynamic named range?

A dynamic named range is a special class of named range that can expand or contract based on a value specific in a criteria. Often a user or a formula will determine that value and the dynamic named range adjusts itself accordingly. The Excel OFFSET function is quite useful here. If used along with its height and width parameters, it allows you to offset a particular cell by a certain number of rows and columns and returns the range enclosed between those cells. The number of rows and columns to offset can be decided by other cells present on the worksheet or another function itself such as COUNT. You can read more about dynamic range in excel in this article.

How do you create a dynamic named range in excel?

To create a dynamic named range, you can simply go to the menu bar and click ‘Insert’ -> ‘Name’ -> ’Define’. In the box that appears, you can simply type a name for the range and the formula that defines that range.

How can you use named range in a chart?

A chart can make use of a named range in the same manner in which it would use a normal range. So rather than using a normal range, you simply enter the name of the named range as the chart’s source data. Infact if you use multiple series in a chart and they are located in different sheets and locations, providing a name to the source data range can result in an immense improvement in the readability of the chart.

In our example, let’s say that you were maintaining a list of students along with their marks in physics. You wanted to create a chart in excel that shows the scores of the students one after another. Now if you were to create a chart by specifying a range in excel, your customer list can always exceed the range specified. Also for some type of charts, for example line charts, the empty cells in the yet unfilled cells in the range can still be treated as 0 and hence may show a wrong picture. In this case, the chart will simply go from the last point straight to 0 and that can startle people (especially your head of department).

Named Range in Excel 2003 and Earlier

We can create a a named range in Excel 2003 and earlier versions using the menu -> ‘Insert’ -> ‘Name’ -> ’Define’ option.

Named Range in Excel 2007 and Later

We can create a a named range in Excel 2007 and later by clicking on the “Formulas” button in the ribbon and then the “Name Manager”.

For a simple chart with date and values You can create two ranges, one that contains the cells with the names and the other with the scores. In the example above, we define a new named range ‘scores’ as =OFFSET(Sheet1!$B$1,1,0,COUNTA(Sheet1!$B:$B)-1,1). You can work out the formula for the range that contains the names.

We can now simply create an empty chart. Then right click on the chart and click ‘Source Data’. In the ‘Series’ section, press ‘Add’. In the ‘Values’ box, type Sheet1!scores (the basic format is =SheetName!Name_Of_Range). Press ‘ok’. Similarly for the category labels, you can specify the ‘names’ range to be picked up. And there you are, ready with your excel chart with named ranges.

Now you can keep on typing as many name and score combinations as you want and the chart will keep expanding to accommodate all of them automatically.

You can download a sample worksheet with an example of chart using a named range in excel here or click on the button below:

Before we leave just a small tip: Incase the scores themselves were dynamic and were dependent upon other cells, you could face issues in terms of the chart not updating properly using the named range. In such a case, you can pick up the cell that was used as the offsetting point and add the volatile RAND() function to it. A volatile function gets recalculated every time a value changes in a worksheet and causes the chart to read the updated values. You can use =RAND() * 0 to minimize any side-effects