# Excel Chart Not Updating with New Data (2 Suitable Solutions)

If you are in a situation where your Excel chart is not updating with new data, and searching for solutions, you are in the right place. In this article, I have discussed 2 possible solutions to this problem.

Watch Video â€“ Solved! Excel Chart Not Updating with New Data

## Excel Chart Is Not Updating with New Data: 2 Possible Solutions

First, look at the following data and the corresponding chart.

Itâ€™s a 100% stacked chart of 3 storesâ€™ sales data. Now, the problem is- if you add new data to the existing data, the chart will not update automatically.

Here I am going to show you 2 solutions to this issue.

Reminder:

Before exploring the solutions, remember that if your Calculation Options (in the Formulas tab, Calculation group) is not set to Automatic, each time you have to press the F9 key to apply the changes. So do that first!

### Solution 1: Convert Data into an Excel Table

If you turn your data into a table, Excel will automatically update the chart whenever you add new data. To make your data a table, follow the steps below.

📌 Steps:

• First, select your data or a cell inside your data, and then go to the Insert tab.
• Then click on the Table button and the Create Table window will open up.
• Here, mark the checkbox named My table has headers.
• Finally, press OK.

Now, add a new column or row and input values in them; Excel will automatically update the chart.

Note:

Enter new data just next to the last entry, i.e., there should be no blank rows or columns between the new and the old last entry.

### Solution 2: Set a Dynamic Formula to Each Data Column

If you are a user of Excel 2003 or earlier versions, the 1st solution will not work for you. In such a case, you may need to use a dynamic formula instead to enable Excel chart updates with new data. Here I will show you how to do that.

📌 Step 1: Create Defined Names and Set Dynamic Formulas for Each Data Column

First, you have to define names for each data column and set a dynamic formula for each of them. To do that-

• Go to the Formulas tab >> Click on the Defined Names button and click on Define Name from the list.

The New Name window will appear.

• Type the first data column header name in the Name: box. Here, we have typed Month. The other names coming next are; Store_1, Store_2, and Store_3.

Note:

While defining names, put an underscore (_) instead of space in the names. Excel Name Manager doesn’t support space in the defined names.

• Select the current worksheet name from the Scope: drop-down. In our case, itâ€™s the Dynamic Formula worksheet.
• In the Refers to: box, insert the following formula for the first data column. We will have to make changes for other data columns according to their data ranges.
`=OFFSET(\$B\$5,0,0,COUNTA(\$B:\$B)-1)`

The OFFSET function refers to the first cell of data. So, if your data starts from cell A2, type A2 instead of B5. The COUNTA function refers to the whole data column. Make changes in the formula according to your data range.

• Finally, press OK.

Repeat all these steps for the next 3 data columns. Name them as Store_1, Store_2 & Store_3, and set the following dynamic formulas for each of them respectively.

For Store 1:

`=OFFSET(\$C\$5,0,0,COUNTA(\$C:\$C)-1)`

For Store 2:

`=OFFSET(\$D\$5,0,0,COUNTA(\$D:\$D)-1)`

For Store 3:

`=OFFSET(\$E\$5,0,0,COUNTA(\$E:\$E)-1)`

So, creating defined names and setting dynamic formulas for the data columns is completed now. You can recheck them from the Name Manager option.

📌 Step 2: Change Legend Entries and Horizontal Axis Labels with Defined Names

• Now, click anywhere on the chart area >> right-click your mouse >> click on the Select Data option from the context menu.

The Select Data Source window will appear.

• Look at the Legend Entries (Series) section. Select the first entry and click on the Edit button.

• From the Edit Series window, write â€˜Dynamic Formulaâ€™!Store_1 in the Series values: box. I mean, replace the range with the corresponding defined name.

• Press OK.

• Similarly, repeat these for Store 2 and Store 3.
• Then Go to the Horizontal (Category) Axis Labels section and click on the Edit button.

The following window will appear.

• Replace the cell ranges with the defined name for them, e.g., we have typed Month instead.
• Then press OK twice to close all the windows.

Now, if you add new data, the Excel chart will update. Look at the following image for proof.

## Conclusion

If your Excel chart is still not updating with new data, try rebooting or let us know in the comment box. We will respond as early as possible and will try to fix your case.

## Related Articles

<< Go Back to Excel Chart Not Working |Â Excel Charts | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Masum Mahdy

Abu Talha Masum Mahdy (Assistant Project Manager, ExcelDemy) has been working with the ExcelDemy family for over 2 years. He wrote 30+ articles and reviewed more than 1300 others for ExcelDemy. Currently, he is responsible for reviewing articles from 2 writing teams of ExcelDemy. He ensures correctness and fluency in his team write-ups. Mahdy holds a BSc in Naval Architecture and Marine Engineering from BUET. His interests are Project Management, Content Writing, SEO, Digital Marketing, Data Analytics and... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF