Creating Dynamic Charts in Excel that Resize Using the OFFSET Function and Named Ranges

Excel

Dynamic charts using Excel’s OFFSET and range names resize when data is added or deleted. Unlike using Tables for dynamic charts, this method is backwards compatible with early versions of Excel.

Use Excel's OFFSET and COUNTA functions to create dynamic range names automatically adjust as data is added or deleted.

Use Excel’s OFFSET and COUNTA functions to create dynamic range names automatically adjust as data is added or deleted.

Click to download the tutorial example file on dynamically resizing charts using OFFSET and range names. The example includes both horizontal and vertical data.

The OFFSET and range name method takes more work than the two clicks used for a Table, but it gives you control over everything about the chart data as my book “Balanced Scorecards and Operational Dashboards with Microsoft Excel” shows. This site, CriticalToSuccess.com, has examples of other ways to use OFFSET, for example, creating a scrolling chart, controlling charts with drop-down menus, and more. Search CriticalToSuccess.com using the term “OFFSET” or “dynamic range” to see examples.

Creating a Static Chart

Begin your dynamic chart by first creating a static chart as follows,

1. Click inside the range B7:D12 so Excel knows where the chart data is located.

2. On the Insert tab, in the Charts group, select a simple chart like a column, bar, or line chart.

Move this chart so you have room to add data below the data range.

Creating Dynamic Range Names with OFFSET

Different versions of Excel have different methods of creating and editing range name formulas. The following instructions are for Excel 2007, 2010, and 2013.

1. In the Formulas tab, in the Defined Name group, click Define Name. The Define Name box appears.

Create range names in the Define Name box.

Create range names in the Define Name box.

2. In the Name text box, enter rngVDate. The naming conventions is,

rng         Range Name

V             Vertical orientation of data in B7:B16

Date      The type of data in the range

3. Write a comment in the comment text box that will help you or others know what this range name is for.

4. In the Refers to box, type the OFFSET formula that calculates the range of Date data.

=OFFSET(wrksht!$B$7,0,0,COUNTA(wrksht!$B$7:$B$16),1)

5. Click Ok.

With this OFFSET formula you can enter additional dates anywhere in the range B7:B16 so long as the new dates are at the bottom of the old list of dates with no blank cells between dates.

Now, repeat the same process to create range names for Sales and Units data.

The names and formulas you will add are,

Data       Range Name      Refers to

Sales      rngVSales            =OFFSET(wrksht!$C$7,0,0,COUNTA(wrksht!$C$7:$C$16),1)

Units     rngVUnits            =OFFSET(wrksht!$D$7,0,0,COUNTA(wrksht!$D$7:$D$16),1)

Click here to learn how OFFSET calculates the size of the dynamic range name. This articles also gives you tips on checking dynamic range names.

Click here to learn the mistake you never want to make with OFFSET. Do you make this mistake?

Click to download the tutorial example file on dynamically resizing charts using OFFSET and range names. The example includes both horizontal and vertical data.

 Inserting a Range Name into the Chart’s Date Formula

Now you can make your chart’s dynamic by inserting the range names you have created into the formulas charts use to reference data. Don’t worry. This is very straightforward. In fact, learning how to do this gives you mystical abilities and the power to bend any chart to your will. (It also enables you to fix charts that get messed up.)

Above, you created three dynamic range names that reference data to be used by the chart,

rngVDate

rngVSales

rngVUnits

The following replace the cell references used by chart with the dynamic range names you have created,

1. Select the chart by clicking on its edge.

2. On the Chart Tools Design tab, in the Data group, click the Select Data tool to display the Select Data Source dialog box.

The Select Data Source dialog box gives you magical control over the data a chart uses.

The Select Data Source dialog box gives you magical control over the data a chart uses.

3. Under the Horizontal (Category) Axis Labels, click Edit to display the Axis Labels box. It is hard to see the formula in this dialog box, so click the selector icon to the right of the cell reference. This displays a wider Axis Labels box in which you can reference the formula. BE CAREFUL before you edit!

The Axis Labels box shows the reference for the Horizontal Axis.

The Axis Labels box shows the reference for the Horizontal Axis.

4. BE CAREFUL! Before you try to edit the formula, click inside the formula and press the Edit key, F2, so you can edit the formula. Press the End key to go to the end of the formula. Replace the cell cell references after the ! sign with the range name as shown in this picture.

Your formula should now look like,

=’Dynamically Resize Charts to Data.xlsx’!rngVDate

The Axis Labels box looks like this after pressing F2 and editing the formula to include the Date range name.

The Axis Labels box looks like this after pressing F2 and editing the formula to include the Date range name.

TIP: If you make a mistake when editing the formula, press Ctrl+Z (Undo), to go back.

5. Click Ok to return to the Select Data Source dialog box so you can add the Sales and Units names.

Inserting a Range Name into the Chart’s Sales and Units Formula

Now, replace the Sales and Units axis formulas with range names using almost the same process.

1. In the Select Data Source dialog box, click Sales and the Edit button to display the Edit Series dialog box.

Replace the Salesdata cell reference with its range name.

Replace the Salesdata cell reference with its range name.

2. Click the selector icon to the right of the Series Value formula that references the Sales series.

3. Click inside the formula, press F2 to edit, and change the cell references for Sales data to the range name, rngVSales.

The chart now uses the dynamic range name to reference Sales data.

The chart now uses the dynamic range name to reference Sales data.

The chart now uses the dynamic range name to reference Sales data.

Now repeat the same process by clicking on Units, then Edit, and change the Edit Series to reference the rngVUnits range name.

Click here to learn how to create dynamic legend names for your data series. Hint! Look at the Series Name formula in the Edit Series dialog box above.

Share the power...

Leave a Comment:

(5) comments

Ian

Very useful . You mention scrolling charts….I am trying to get a simple line chart which plots the movement of a stock price during the day . I have the real time price in cell a1 ….have you done any lessons on that ? It can’t be rocket science as this is common on most charting websites and must be possible in excel …I hope ! Any guidance would be most welcome . I just cannot find anything in the excel books or online unless I am not looking in the correct area!

Ian

Reply
    RonTest2

    Hi Ian,

    I apologize for not answering earlier. I only respond on weekends and no longer do consulting.

    I do not know of a way to do this by formula. You will need to use a macro. It should be fairly simple. If you are not familiar with macros you may be able to hire someone to do it through one of these websites
    * Fiverr.com
    * eLance-oDesk.com
    * upwork.com

    It should be pretty straightforward.

    Here is how to approach it,
    1. Use an “on cell change” macro to watch for changes in the cell containing your stock price.
    2. When the price changes then the macro enters the date, time, and price in three cells across three columns. e.g. A10, B10, C10.
    3. The next time “on cell change” senses a change, then the macro runs again, but uses COUNTA to see how many filled cells are in your data column. It then goes down to the next lower set of cells and enters data in A11, B11, C11.
    4. Repeat
    For info on “On Cell Change” use this search query in Google,
    excel macro on cell change

    Thanks for subscribing.

    Best,
    Ron

    Reply
Add Your Reply

Leave a Comment: