How to Refresh Chart in Excel (2 Effective Ways)

Get FREE Advanced Excel Exercises with Solutions!

While working with Microsoft Excel, sometimes we need to refresh our data chart. Refreshing data charts in Excel is an easy task. This is a time-saving task also. Today, in this article, weâ€™ll learn two quick and suitable ways to refresh charts in Excel effectively with appropriate illustrations.

Letâ€™s say, we have a dataset that contains information about several Students of XYZ school. The Name of the students and their securing marks in Physics and Chemistry are given in columns B, C, and D respectively. We will create a table, and use a dynamic function, to refresh charts in Excel. Hereâ€™s an overview of the dataset for our todayâ€™s task.

1. Creating a Table to Refresh Chart in Excel

In this section, we will create a table to refresh the chart. This is an easy and time-saving task also. To refresh a chart, we will create a table first. Letâ€™s follow the instructions below to refresh a chart!

Step 1:

• First of all, select the data range. From our dataset, we will select B4 to D10 for the convenience of our work. Hence, from your Insert tab, go to,

Insert â†’ Tables â†’ Table

• As a result, a Create Table dialog box will appear in front of you. From the Create Table dialog box, press OK.

• After pressing OK, you will be able to create a table which has been given in the below screenshot.

Step 2:

• Further, we will make a chart to refresh. To do that, firstly select the table range B4 to D10. Secondly, from your Insert tab, go to,

Insert â†’ Charts â†’ 2-D Column

• After that, you will be able to create a 2-D Column.

Step 3:

• Now, we will add a row to our table to refresh the chart. Letâ€™s say, we will add Keatâ€™s securing marks in Physics and Chemistry are 80 and 70 We notice that our chart will refresh automatically which has been given in the below screenshot.

Read More: How to Create Embedded Chart in Excel

2. Using Dynamic Formula to Refresh Chart in Excel

In this method, we will use the dynamic formula to refresh charts. We will use the Chart that has been created in Method 1.Â  Letâ€™s follow the instructions below to refresh a chart using a dynamic formula!

Step 1:

• First, we will make the defined name and the dynamic formula for every column. Now, from your Formulas tab, go to,

Formulas â†’ Defined Names â†’ Defined Name

• Hence, a New Name dialog box will appear in front of you. From the New Name dialog box, firstly, type Name in the Name typing box. Secondly, select the current worksheet named Dynamic Formula from the Scope drop-down box. Thirdly, type the below formulas in the Refers to typing box. The formulas are:
`=OFFSET(\$B\$5,0,0,COUNTA(\$B:\$B)-1)`

Step 2:

• Now, repeat Step 1 for columns C and D. The formula for the Physics column is,
`=OFFSET(\$C\$5,0,0,COUNTA(\$C:\$C)-1)`
• Again, the formula for the Chemistry column is,
`=OFFSET(\$D\$5,0,0,COUNTA(\$D:\$D)-1)`
• After that, press right-click on any column of your chart. Instantly, a window pops up. From that window, select the Select Data option.

• As a result, a Select Data Source dialog box will appear in front of you. From the Select Data Source dialog box, firstly, select Physics. Secondly, select the Edit option under the Legend Entries (Series).

• Hence, again, a window named Edit Series pops up. From the Edit Series dialog box, type =â€™Dynamic Formulaâ€™!Physics in the Series values typing box. At last, press OK.

• Similarly, from the Edit Series dialog box, type =â€™Dynamic Formulaâ€™!Chemistry in the Series values typing box. At last, press OK.

Step 3:

• After that, select the Edit button under the Horizontal (Category) Axis Labels option.

• As a result, an Axis Labels dialog box pops up. From the Axis Labels dialog box, type the below formula in the Axis label range typing box. The formula is,
`='Dynamic Formula'!Name`
• At last, press OK.

• Hence, again, press OK.

Step 4:

• Now, we will add a row to our table to refresh the chart. Letâ€™s say, we will add Johnâ€™s securing marks in Physics and Chemistry are 75 and 78 We notice that our chart will refresh automatically which has been given in the below screenshot.

Read More: How to Print Graph in Excel

Things to Remember

âžœ While a value can not found in the referenced cell, the #N/A error happens in Excel.

âžœ To create a table, you can press Ctrl + T simultaneously on your keyboard.

Conclusion

I hope all of the suitable methods mentioned above to refresh charts will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

1. If for some moments I have no data at all, I get error.

What can I do?

Md. Abdur Rahim Rasel Oct 15, 2022 at 3:13 PM

Hello BABA,
I have checked all of the methods of the above article again that can figure out how to refresh chart in excel. I think the article is appropriate to refresh chart in Excel. While removing data from the dataset, I would not get any error. Look at the below screenshot.