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.
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 more about creating range names.
Click here to learn how OFFSET is used to create dynamic range names.