Categorized | excel chart, excel tips

Create Chart using Named Range in Excel




TumblrStumbleUponRedditLinkedInAIMDiggEvernoteWhatsAppMySpaceKindle It

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.

an-excel-chart-based-on-a-named-range

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.

creating-a-named-range-for-a-chart-in-excel

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.

adding-the-named-range-as-data-series

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:

create-chart-using-named-range-in-excel

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 :-)

TumblrStumbleUponRedditLinkedInAIMDiggEvernoteWhatsAppMySpaceKindle It

Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?


XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>


Comments and Trackbacks

  1. atrosmepoveta wrote:

    I’m always searching for brandnew infos in the world wide web about this matter. Thanx.

  2. Wayen wrote:

    This 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.

  3. David wrote:

    Tnx 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

  4. David wrote:

    tnx 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?

  5. Aaron wrote:

    I’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

  6. Shanka wrote:

    The 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.

  7. Shanka wrote:

    Found 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
    =MATCH(TRUE,$B2:$B100″”,0)

  1. da TaB is On » Data Validation in Excel wrote:

    [...] familiar with what a named list is, I suggest that you learn about them in the article dealing with creating chart using Named Range in Excel. Once we know how to define static and dynamic named lists, we can use them to restrict entry of [...]

  2. da TaB is On » How to Create a Dashboard in Excel wrote:

    [...] way to show and organize data in an excel dashboard. You can read about data validation and named range [...]

  3. Control Chart | da TaB is On wrote:

    [...] control chart, we are going to make frequent use of the named range functionality. (Read more about creating chart with named range [...]

  4. Timeline Chart in Excel with Interactive Event Descriptions | Excel & VBA - da Tab Is On wrote:

    [...] you noticed, we have used a named range to set the X-values of the first chart series. The named range refers to a dynamic range consisting [...]

  5. Control Chart in Excel Using VBA (Code, Software) | Excel & VBA - da Tab Is On wrote:

    [...] both the data and the label ranges, its time to create the chart. Note that we have made use of the named ranges to add the upper and the lower control limits. [...]

  6. Data Validation List in Excel - Create Drop Down List Using Data Validation in Excel | Excel & VBA - da Tab Is On wrote:

    [...] named range is a group of cells that have been assigned a specific name. This name can then be used in formulas [...]

Subscribe

Keep up with the latest stories - Delivered right to your inbox
feedburner

Translate

English flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagDutch flagHindi flagPolish flagSwedish flagFilipino flagHebrew flagIndonesian flagUkrainian flagThai flagTurkish flag
treeemap software for excel