Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.


Download Practice Workbook

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


2 Suitable Ways to Refresh Chart in Excel

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.

refresh chart in excel


1. Create 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

Create a Table to Refresh Chart in Excel

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

Create a Table to Refresh Chart in Excel

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.

Create a Table to Refresh Chart in Excel

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.

Create a Table to Refresh Chart in Excel

Read More: How to Refresh Pivot Table in Excel (4 Effective Ways)


Similar Readings


2. Use 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 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

Use Dynamic Formula to Refresh Chart in Excel

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

Use Dynamic Formula to Refresh Chart in Excel

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

Use Dynamic Formula to Refresh Chart in Excel

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

Use Dynamic Formula to Refresh Chart in Excel

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

Use Dynamic Formula to Refresh Chart in Excel

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.

Use Dynamic Formula to Refresh Chart in Excel

Read More: [Solved]: Excel Formulas Not Updating Until Save (6 Possible Solutions)


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.


Related Articles

Md. Abdur Rahim Rasel

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.

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

    What can I do?

  2. Reply
    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.
    refresh chart
    Thank you for your comment.
    If you get error, please mail me personally at below mail address.
    [email protected]
    Regards
    Md. Abdur Rahim Rasel (Exceldemy Team)

Leave a reply

ExcelDemy
Logo