Create a Chart That Updates with New Data Automatically

This post will guide you how to create a chart that updates with new data automatically in Excel. How do I create self-updating chart in Excel. How to get charts to automatically update with new data point in your current worksheet. Or how to use defined names to update a chart with new data point automatically in Excel.

Create a Chart That Updates with New Data Automatically


Assuming that you have a list of data and you have created a column chart based on these data. And you want to add one row or data point in the original data source, and you also want the existing column chart can be updated automatically. How to achieve it. This post will introduce two methods to update chart automatically.

Method1: create a table based on the original source data

You can create a new table based on the source data of the chart, then you can add one row data point under the created table. And you will find that the newly added data point will be updated to the current column chart. Just do the following steps:

#1 select the source data in your worksheet.

create chart update with new data1

#2 go to INSERT tab, click Table command under Tables group. The Create Table dialog will open. If you data has headers and you need to select My table has headers checkbox.  Click OK button.

create chart update with new data2

create chart update with new data3

#3 the table has been created based on the selected range of cells.

#4 add one data point under the table. And the newly added data is added into the current column chart automatically.

create chart update with new data4

Method2: Use Defined Name to Update the Chart Automatically

You can also use the defined name for each column to update the chart with new data point automatically. Just do the following steps:

#1 Go to FORMULAS tab, click Define Name command under Defined Names group to create one defined name for each column.

create chart update with new data8

#2 Enter Product in the Name text box, and type the following formula into the Refers to text box.

=OFFSET($A$2,0,0,COUNTA(Sheet1!$A:$A)-1)

#3 repeat the step 2 to create defined name for Sales column. Type the following formula into the Refers to text box.

=OFFSET($B$2,0,0,COUNTA(Sheet1!$B:$B)-1)

create chart update with new data9

#4 click on the chart ,and the click any data series in your chart. Changing the current formula in the formula bar to the following formula. Then press Enter key.

=SERIES(,Sheet1!Product,Sheet1!Sales,1)

create chart update with new data6

#5 add one new data under the source data. The chart will update automatically for the newly added data point。

create chart update with new data7

Related Functions


  • Excel COUNTA function
    The Excel COUNTA function counts the number of cells that are not empty in a range. The syntax of the COUNTA function is as below:= COUNTA(value1, [value2],…)…
Related Posts

If Cell Begins with One of Three Supplied Characters
If Cell Begins with One of Three Supplied Characters

If you want to test values to see if they begin with some given specific characters like “x”, ”y”, or “z”, you can create a formula with COUNTIF and SUM functions to return results. EXAMPLE You can see “TRUE” or ...

Fix #N/A Error For VLOOKUP From Another Sheet
vlookup from anther sheet not working1

This post will show you how to fix the #N/A error why it occurs when you extract values from another sheet using VLOOKUP function in Excel 2016,2013,2010 or other Excel versions. How can you correct a #N/A error in VLOOKUP ...

How to Average Only Positive or Negative Numbers of a Range

Suppose both positive numbers and negative numbers exist in a table. If we want to know the average of only positive numbers in this table, we can create a formula to get average of all positive numbers with all negative ...

Sort Positive Numbers and Negative Numbers by Absolute Values

If both positive numbers and negative numbers exist in the same column, when sorting them by absolute values, we can sort them with the help of ABS function and helper column. In this article, we will show you the way ...

Get Employee Information by VLOOKUP

VLOOKUP is one of the key functions among all lookup & reference functions in Excel. Today, in this article, we will show you the way to apply VLOOKUP to retrieve employee information. I hope this article will help you in ...

VLOOKUP with Two Lookup Tables

VLOOKUP is one of the key functions among all lookup & reference functions in Excel. Today we will show you the application of VLOOKUP function when there are two lookup tables. EXAMPLE Table1 and table2 record the rates of Y2020 ...

VLOOKUP with Multiple Lookup Values

VLOOKUP is one of the key functions among all lookup & reference functions in Excel. It can scan and retrieve data from a static or dynamic table based on your lookup value. It can perform approximate match or exact match ...

VLOOKUP Data by Date

VLOOKUP is one of the key functions among all lookup & reference functions in Excel. It can scan and retrieve data from a static or dynamic table based on your lookup value. It can perform approximate match or exact match ...

VLOOKUP – Retrieve Data from Another Workbook
VLOOKUP - Retrieve Data from Another Workbook 1

VLOOKUP is one of the key functions among all lookup & reference functions in Excel. It can scan and retrieve data from a static or dynamic table based on your lookup value. It can perform approximate match or exact match ...

VLOOKUP – Retrieve Data from Another Worksheet
VLOOKUP - Retrieve Data from Another Worksheet 3

VLOOKUP is one of the key functions among all lookup & reference functions in Excel. It can scan and retrieve data from a static or dynamic table based on your lookup value. It can perform approximate match or exact match ...

Sidebar