How to Update your chart Series automatically?

How to Update your chart Series automatically?
Last Updated on 23/10/2023
Reading time: 2 minutes

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

  1. 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

  2. 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.

  1. The trick is to insert your data in a Table with the menu Insert > Table.
Menu Insert Table
  1. Change the name to your table with the menu Table Design > Table Name
Add the temperature in a table
  1. Select all your data with the trick of the arrow
  2. When your data are selected, go to the menu Insert > Chart Line > Line
Menu Insert Chart Line

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

Select one of the chart style

And you have this

Line Chart for the temperature

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.

Cell reference and not table reference

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.

Add row to a table

Leave a Reply

Your email address will not be published. Required fields are marked *

How to Update your chart Series automatically?

Reading time: 2 minutes
Last Updated on 23/10/2023

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

  1. 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

  2. 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.

  1. The trick is to insert your data in a Table with the menu Insert > Table.
Menu Insert Table
  1. Change the name to your table with the menu Table Design > Table Name
Add the temperature in a table
  1. Select all your data with the trick of the arrow
  2. When your data are selected, go to the menu Insert > Chart Line > Line
Menu Insert Chart Line

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

Select one of the chart style

And you have this

Line Chart for the temperature

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.

Cell reference and not table reference

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.

Add row to a table

Leave a Reply

Your email address will not be published. Required fields are marked *