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 🙂
I’m always searching for brandnew infos in the world wide web about this matter. Thanx.
December 11th, 2009 at 10:39 amThis is great except it’s not working for me. Whenever I enter the dynamic named range, it is replaced with the actual range when I hit ok.
January 10th, 2011 at 4:33 pmTnx for the tutorial. Works great, all except that named range gets replaced with range presently corresponding to the named range (likewise in downloaded example). So, have the dilemma
April 30th, 2012 at 1:29 pmtnx very much for the technique! Follow-on question…can the technique be extended to include multiple columns of Y-values? Referring to your example program, can the named range “values” be extended to include 10 rows(say for all 10 test scores)? Or do you need to define a unique named range for each column?
April 30th, 2012 at 1:35 pmI’ve got the named ranges working well in the chart I’m working on. However, if it want to copy the worksheet into another worksheet tab, it appears that all the named referencing goes out the window. Is there a way to address this issue?
Regards,
Aaron DeBuhr
May 8th, 2013 at 1:37 amThe only problem with this way is if my values are dynamic and let’s say A2,A3 & A4 (in the above example) doesn’t have any values then it won’t work.
July 29th, 2013 at 11:54 amFound a way to fix that issue if the first labels don’t have any values..
=OFFSET(Sheet1!$B$1,Sheet1!$C$1,0,COUNTA(Sheet1!$B:$B)-1,1).
And in Cell C1, use Ctrl+Shift+Enter (Array) for the following formula
July 30th, 2013 at 1:38 am=MATCH(TRUE,$B2:$B100″”,0)
Looks great cannot wait to be in your free mailing list.
Neville.
May 2nd, 2018 at 7:19 am