Dynamic Name Range Charts in Excel

Excel Tip #014

Dynamic Name Range Charts in Excel

It would be nice if a chart that gets updated right before your eyes at each data entry. This can be done creating Dynamic Charts. The key is to define the chart's source data as a dynamic range. By doing so, the chart will automatically reflect changes and additions to the source data.

There are two ways to build dynamic charts in Excel - Table method and Formula method. Let us see how to work with both the methods.

Consider the below example having the sales for 5 sales rep.

Example - Dynamic Chart

Table Method:

The first step is to create the table. To do so, simply select the data range and do the following:

1. Click the Insert tab.
2. Click Table.
3. Excel will display the selected range, which you can change. If the table does not have headers, be sure to un-check the "My table has headers" option.
4. Click OK and Excel will format the data range as a table.

   

   Creating the data as Table

Now we have created a Table which acts as a dynamic range. Any chart you build on the table will be dynamic. To illustrate, create a quick column chart as follows:

1. Select the table.
2. Click the Insert tab.
3. In the Charts group, click Column and choose the first 2-D column chart.

   

   Dynamic Chart - Table Method

Now in the consecutive rows, I have added 2 sales rep (Leslie and Luther) and their sales and the chart adds those data and changes dynamically. Please refer the below image.

Data added in Table - Chart changes dynamically

Formula Method:

You won't always want to turn your data range into a table. Furthermore, this feature isn't available in pre-ribbon versions of Office. When either is the case, there's a more complex formula method. It relies on dynamic ranges that update automatically, similar to the way the table does.

To do this, we must learn to use 2 formulas - OFFSET() and COUNTA().

OFFSET():

The OFFSET function in Excel returns a cell or range of cells that is a specified number of rows and columns from a cell or range of cells. Its syntax is,

     =OFFSET(reference, rows, cols, [height], [width])

Arguments

• reference - The starting point, supplied as a cell reference or range.
• rows - The number of rows to offset below the starting reference.
• cols - The number of columns to offset to the right of the starting reference.
• height - [optional] The height in rows of the returned reference.
• width - [optional] The width in columns of the returned reference.

Consider the same example, sales of 5 sales rep. Using offset(),

     =OFFSET(A2, 3, 1, 1, 1)

OFFSET() Example

In the above example, it returned the value 655 (sales value of Deborah) from 3rd row and 2nd column with reference to cell A2.

COUNTA():

TThe COUNTA function counts the number of cells that are not empty in a range. Its syntax is,

     =COUNTA(value1, [value2], ...)

Arguments

• value1 - An item, cell reference, or range.
• value2 - [optional] An item, cell reference, or range.

Consider the below example, using COUNTA() in it,

COUNTA() Example

To create dynamic chart, we have to first create a dynamic named range. Follow the below steps,

1. Click the Formulas tab.
2. Click the Define Names option in the Defined Names group.
3. Enter a name for the dynamic range, SalesRepData.
4. Enter the following formula =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1)
5. Click OK.

   

   Defining Name Range

I am referencing A2, with rows and columns set to 0. Then using COUNTA function to count the number of non-blank cells.

You will definitely have a question, why I used -1 at the end. This is because, I am referencing from A2 and COUNTA(A:A) will give me 6 (including the headers). I want to consider only the sales rep information and not the header. Hence I have given -1.

Similarly create another dynamic range SalesData for sales information

     =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1)

1. Now create a simple 2-D column chart using the SalesRep and Sales data.
2. Click on any of the bar inside the chart and click Select Data.
3. A small pop-up window opens displaying the data range of the chart.
4. Click on Sales under Legend Entries and then select Edit.
5. In the Series values from =Sheet1!$B$2:$B$6 change it to =Sheet1!SalesData
6. Likewise, click on Horizontal axis label and select Edit. Change the Axis Label Range from =Sheet1!$A$2:$A$6 change it to =Sheet1!SalesRepData
7. Now dynamic named ranges are added in the chart, making it a dynamic chart

   

   Dynamic Chart - Formula Method

If you notice, there is a difference when adding data while using Table method and Formula method. In table method, the chart will get updated at each data entry point. But in Formula method, the chart gets updated only when the entire row of data is completed.