Excel charts not updating

As user keeps on adding new rows we will have to update our chart’s source data. Now repeat the same for years column as well and call it “years_data” Create a column chart.

Click here to download the dynamic chart ranges workbook and use it to learn this trick.We all know that to make a chart we must specify a range of values as input.But what if our range is dynamic and keeps on growing or shrinking.You cant edit the chart input data ranges every time you add a row.Wouldn’t it be cool if the ranges were dynamic and charts get updated automatically when you add (or remove) rows? If you are using Excel 2003 or above you can create a data table (or list) from the chart’s source data.Well, you can do it very easily using excel formulas and named ranges. This way, when you add or remove rows from the data table, the chart gets automatically updated.

For some reason if you cannot use data tables, the next method is to use OFFSET formula along with named ranges.

We all know that OFFSET formula is used to get a range of cells by passing on starting point and number of cells to offset.

Steps for creating dynamic chart ranges using OFFSET formula: In our case the data should be filled in the following table. =OFFSET($G$6,0,0, COUNTA($G$6:$G$14),1) Set the named range’s name as “sales_data” or something like that.

I have given Excel 2007 file since the file includes tables.

If you have no time for writing lengthy formulas or setting up data tables, you can still save time when editing chart series data ranges. Now excel shows highlighted border around the cells from which the chart series is created.

Just click on the bottom-right corner and drag it up and down to edit the chart series data ranges.