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
- Put your cursor on the very first cell of the Table
- The cursor will change to a diagonal black arrow
- Click to select the data only
- 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.