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.

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.

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.

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.

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

• 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:

For Store 2:

For Store 3:

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.

Read More: [Solved]: Excel Graph Is Not Showing All Dates

Download Practice Workbook

Download the following practice workbook from the link below.

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

• Plotting Row Number Instead of Value in Excel
• Excel Chart Disappears When Data Is Hidden

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