There are two methods of creating dynamic charts in Excel – using Tables or using OFFSET and range names. Which 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.
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 more about creating range names.
Click here to learn how OFFSET is used to create dynamic range names.