Auto updating your Excel Charts

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

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%20abbreviated%20data - Auto updating your Excel Charts

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

Apollo%20abbreviated%20data%20plus%20one%20row - Auto updating your Excel Charts

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.

image 163 360x296 - Auto updating your Excel Charts

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.

subs profile e1563205311409 - Auto updating your Excel Charts
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address