This article will show you the trick to update your chart series when you add new data to your source.

Old technique with the OFFSET function

For years, the only solution provided on the blog or the forum was to use the function OFFSET.

But there are 2 reasons to not use it now

• The OFFSET function uses too much memory. It's a volatile function
• The formula must be written in a range of name

So how to proceed now?

Example with a collection of Temperature

Let's take the temperatures of the city of Montreal.

Add your data in a Table

The trick is to insert your data in a Table with the menu Insert > Table.

And also, don't forget to give a significative name to your table with the menu Table Design > Table Name

Select all the data with one click

When you insert your data in a Table, it's very easy to select all the data with only one-click

1. Put your cursor on the very first cell of the Table
2. The cursor will change to a diagonal black arrow
3. Click to select the data only
4. A second click select also the header

Create a line chart

When your data are selected, go to the menu Insert > Chart Line > Line

And you can select one of the Chart Style proposed in the ribbon

And you have this

Check the source of data

Now, if we look at the formula of the series, we can see that we have absolute cells' references and not Table references.

Add rows to your Table

But, and this is why it's MAGIC, just the action to add a new row in the table, Excel knows it must extend the series.