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

Charts

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:

Leave a Comment: