Masum Mahdy
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.
Download Practice Workbook
Download the following practice workbook from the link below.
2 Solutions If Excel Chart Is Not Updating with New Data
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, remind 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 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: How to Edit Chart Data in Excel (5 Suitable Examples)
Conclusion
If still, your Excel chart is 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. Stay with ExcelDemy and keep learning!
Related Articles
- Use Scatter Chart in Excel to Find Relationships between Two Data Series
- How to Import Data into PowerPivot & Create Pivot Table/Pivot Chart
- How to Group Data in Excel Chart (2 Suitable Methods)
- Selecting Data in Different Columns for an Excel Chart
- How to Create Graph from List of Dates in Excel (with Easy Steps)
- How to Format Data Series in Excel (with Easy Steps)
