Two Ways to Create Dynamic Charts in Excel that Resize – Which is Best for You?


There are two methods of creating dynamic charts in Excel – using Tables or using OFFSET and range names. Which is best for you?

The Table feature in recent versions of Excel is the easiest method of creating dynamic charts, but it is not backward compatible

There are two ways to create dynamic charts in Excel. Which way is best for you?

The easiest method uses the TABLE feature of Excel 2007, Excel 2010, and Excel 2013. A second method works for all versions of Excel, even those from way back, and uses the OFFSET function and range names.

Should You Create a Dynamic Chart in Excel using an Excel Table or using OFFSET and Named Ranges?

Each of these two methods overcomes the problem of modifying a chart to include new data. Which method you use depends upon your situation. Use this table to decide.

If you need… Use OFFSET and named range… Use Table method…
Compatibility with any version of Excel Yes No
Compatibility only with Excel 2007, Excel 2010, Excel 2013 No Yes
Control over the start and end points of the X-axis range (e.g. an adjustable X-axis) Yes No
A blank cell at top left of data range Yes No
Expansion at end of existing data series Yes Yes
Expansion with additional data series Requires creating OFFSET and range names for X- and Y-axis Yes
Expansion to a known limit Yes Yes
Expansion without limit No Yes
Easy to create Requires formulas and range names Yes
Automatic data and list formatting No Yes
Automatically adds table sorting and data filters in data headings No Yes

Click here to learn how to create a dynamic chart in Excel using a Table.

Click here to learn how to create a dynamic chart in Excel using the OFFSET function and dynamic range names.

Click here to learn more about creating range names.

Click here to learn how OFFSET is used to create dynamic range names.