The Excel Chart is Not Updating – 2 Solutions

Observe the following dataset and the corresponding chart.

It’s a 100% stacked chart of 3 stores’ sales data.

Reminder:

If your Calculation Options (in the Formulas tab, Calculation group) is not set to Automatic, you need to press F9 to update.

Solution 1 – Converting Data into an Excel Table

Steps:

• Select your data and go to the Insert tab.
• Click Table to open the Create Table window.
• Check My table has headers.
• Click OK.

• Add a new column or row and enter the values; Excel will automatically update the chart.

Note:

There should be no blank rows or columns between the new and the last entry.

Solution 2- Setting a Dynamic Formula to Each Data Column

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

• Go to Formulas >> Click Defined Names and choose Define Name.

The New Name window will open.

• Enter the first data column header name in the Name: box. Here, Month.

Note:

While defining names, enter an underscore (_) instead of a space.

• In Scope:, select the current worksheet (Dynamic Formula).
• In Refers to:, enter the following formula for the first data column.
`=OFFSET(\$B\$5,0,0,COUNTA(\$B:\$B)-1)`

The OFFSET function refers to the first cell containing data. If your data starts in A2, enter A2 instead of B5. The COUNTA function refers to the whole data column. Change the formula according to your data range.

• Press OK.

• Repeat the steps for the next 3 data columns. Name them as Store_1, Store_2 & Store_3, and set the following dynamic formulas:

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)`

Defined names were created and dynamic formulas set for the data columns.

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

• Click the chart area >> right-click  >> choose Select Data.

In the Select Data Source window:

• In Legend Entries (Series), select the first entry and click Edit.

• In the Edit Series window, enter ‘Dynamic Formula’!Store_1 in Series values:.

• Click OK.

• Repeat the steps for Store 2 and Store 3.
• Go to the Horizontal (Category) Axis Labels and click Edit.

In the new window:

• Replace the cell ranges with the defined name.
• Click OK twice to close the windows.

If you add new data, the Excel chart will update.

<< Go Back to Excel Chart Not WorkingExcel 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