This article will show you the trick to update your chart series when you add new data to your source.
In Excel, if you want to update chart series automatically based on the changing data, you can use OFFSET or Excel Tables
- The OFFSET function
For years, it was the only way to update your chart Series automatically. But now, It's better to avoid this technique
- Insert your data into an Excel Table
When you insert your data into a Table, Excel does the job automatically.
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.
- The trick is to insert your data in a Table with the menu Insert > Table.
- Change the name to your table with the menu Table Design > Table Name
- Select all your data with the trick of the arrow
- 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. But it's not a problem for Excel.
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.
28/12/2024 @ 21:54
I really appreciate this tool. I'm just a beginner to learn how to create a chart for y own Blood sugar level that I take test daily.
This helped me a lot.
Thank you very much.
Hoang Huu