Auto updating your Excel Charts

How to make an Excel chart that changes as you add more cells or rows is really simple these days. Forget all the old stuff about named ranges or changing the Chart data. Setup your Excel list correctly and any linked charts will update automaticall.

In a recent article we looked at making a chart with a timeline for regular and irregular dates.

That’s OK for fixed data but often you add or remove data and want the chart to automatically update. That’s much easier these days since Excel has smarts that were missing in earlier releases.

Let’s look again at just a part of our sample data.

Apollo abbreviated data image from Dynamic Date Charts at Office-Watch.com

Now we just add the next mission by typing into the next row.

Apollo abbrevited data plus one image from Dynamic Date Charts at Office-Watch.com

Excel has automatically added the row to the chart! Long time Excel users may remember when Excel was a lot more rigid. If you added data, you’d then have to manually edit the chart ranges.

Tables

The trick is to use Tables for your chart data.

Tables will automatically expand or contact as you change the rows or columns. That automatic ranging flows through to Charts.

There are many, many benefits to using Tables, automatic charts is just one of them.

Named Ranges

The cells and chart axis could be defined by named ranges like ‘LandedonMoon’ instead of B2:B5 . Change the cell references for the range name and the chart would update.

But these days (the above example is from way back in Excel 2010) Excel should update charts more directly in response to adding rows to a table.

If Excel doesn’t figure it out and the chart remains the same, right-click on the chart and choose ‘Select Data’ then change the data range.

To include another row to the chart change the end $C$5 to $C$6 .

If you need to do a manual change, have a look at how the chart is made. Some tinkering with the chart data should make it work automatically with a table.