How to Make Charts Auto Update in Excel

Making charts in Excel is a great way to visualize your data, but what if you need them to update automatically as your data changes? Knowing how to make charts auto update in Excel can save you time and ensure your visuals are always up-to-date. In this guide, we’ll show you how to link your charts to dynamic data ranges using features like Excel tables and named ranges, so your charts reflect the latest information without manual adjustments. By the end of this article, you’ll be able to create auto-updating charts in Excel that keep your reports and dashboards current with minimal effort.

Method 1: Using tables

If we convert the above dataset into a Table then no matter what we add to it, the chart will be updated automatically.

Step 1: Select the dataset and click on the Insert tab

Select the entire dataset and click on Insert on the top of the ribbon and then select Table. The following dialog box opens.

Remember to check: My table has a headers option is checked. Headers are the column names such as Student Name, DSA, OS, etc. Now our dataset is a Table, now whenever we add any new values to the table like rows or columns or add columns our plot will get updated.

Step 2: Select the entire table and click on the Insert tab

Now select the entire table and from Insert click on any type of chart the user likes. Here Plot is generated with the default dataset.  

Step 3: Now Add a New Row

Then add a new row or column or change any value and see the plot which automatically changes. After Adding a new Row with some values.

Method 2: Using Dynamic Formula

Using a Dynamic formula is another way of auto-updating a chart after entering new data in Excel. If the user doesn't want to change the table range then this dynamic formula comes in role. To use a dynamic formula you have to insert a dataset in the sheet and create a chart. Let's discuss the example: Below is a dataset of students and their subject marks, Follow the steps to create a dynamic formula.

Step 1: Click on the Defined Name

First, click on the defined name in the formula and create a dynamic formula for each column. 

Step 2: In the Dialogue Box, Fill the details, and Click OK

In the New Name dialog box, fill Name as StudentName, scope as a current worksheet, and write a dynamic formula in refers to column i.e, =OFFSET($A$2,0,0,COUNTA($A:$A)-1)  then click "OK"

Things to know: In the above formula the OFFSET() is the function that refers to the first data point and COUNTA refers to the column of the data.

Repeat the above process and create a dynamic formula for each series using the following range names and formulas. 

1. Dynamic formula for column B(DSA): =OFFSET($B$2,0,0,COUNTA($B:$B)-1)
2. Dynamic formula for column C(OS): =OFFSET($C$2,0,0,COUNTA($C:$C)-1)
3. Dynamic formula for column D(DBMS): =OFFSET($D$2,0,0,COUNTA($D:$D)-1)

Step 3: Choose 'Select Data' by right-clicking on the bars

Now right-click on any of the bars and choose "Select data". 

Step 4: "Select Data Source'

A "Select data source" dialog box will appear. Now In the Legend Entries (Series) section. 

• Select DSA and then click the edit button.

Step 5: Type Edit Series

Edit series will pop up, type "=Sheet2!DSA" in the series value section as shown below.

Repeat the same step for the other series also to update the other series as mentioned below:

1. OS - "=Sheet1!OS
2. DBMS -"Sheet1!DBMS  

Step 6: Click Edit under the horizontal category on the right side of the data source

Now, On the right side of the Select data source, click Edit under Horizontal (Category) Axis Labels. 

Step 7: A dialog box of Axis label will pop up, enter "Sheet1! Student_name" and click "OK"

Conclusion

With the steps outlined in this guide, you can now easily create auto-updating charts in Excel that automatically adjust as your data changes. Whether you're using Excel tables or named ranges, these techniques will help you maintain accurate and timely charts without needing to manually refresh or update them. By automating this process, you’ll save time, reduce errors, and keep your data visualizations consistent and reliable.

dwaipayan_bandyopadhyay

Follow

Improve

Article Tags :

• Excel
• Technical Scripter 2022
• ExcelGuide