It’s really easy to create a chart in Excel that expands automatically when you add new data to it. Usually a chart is set up to only display what is currently on the data sheet, but by a few tweaks and Excel magic we can set up a chart in such a way to allow a chart to automatically accommodate new data as it is added.
In Excel 2007 we can use a new table feature, if you need the workbook to be backward compatible with previous Excel 2003 versions you need to do a few more clicks to get the same result. Let’s look at Excel 2007 and Excel 2003 first.
To create your self expanding chart in Excel 2007 and Excel 2003 do the following
- Create your chart as usual ( you can use the sample data below)
- Select any cell in the range that contains data that is used by the chart
- Choose Insert- Tables Tables to convert convert the range to a table
NB if you are using Excel 2003 in Step 3 above choose Data- List-Create List.
Believe it or not thats all there is to it!!. Excel recognise the data as a table and that any data that is added to it, is automatically updated in the chart. As you can see in the screen shot above Northern has been added in automatically after I typed it then tabbed along to the Widget Sales ready for figure to be entered.
Give this charting technique a go, it a good one to have in you Excel magic box of tricks.