How to Refresh Chart in Excel (2 Effective Ways)

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:

• Where the OFFSET function indicates the first data and the COUNTA function indicates the entire column data.
• At last, press OK.

Step 2:

• Now, repeat Step 1 for columns C and D. The formula for the Physics column is,

• Again, the formula for the Chemistry column is,

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

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

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

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.

<< Go Back To Excel Charts | Learn Excel