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

How to Change Chart Data Range Automatically in Excel (2 Easy Ways)

In this article, we will learn to change the chart data range automatically in Excel. Generally, the range becomes static when we create a chart in Excel. The chart doesn’t update automatically if you add more values in the range. But, today we will show 2 methods. Using these methods, you can easily change the chart data range and update the chart automatically. So, without any delay, let’s start the discussion.


Download Practice Book

You can download the practice book from here.


2 Easy Ways to Change Chart Data Range Automatically in Excel

To explain the methods, we will use a dataset that contains information about the Sales Amount for some Dates of a company. We will plot these values in a Column Chart. After plotting them, we will change the chart data range and update the chart automatically. We will use the same dataset throughout the article.

Use Excel Table to Change Chart Data Range Automatically


1. Use Excel Table to Change Chart Data Range Automatically

In the first method, we will use the table method. You can convert the dataset into a table and change the chart data range automatically in Excel. This is a simple method and the steps are also easy. It is simple because you don’t need to apply any formula or perform any difficult task to implement the steps. So, without further ado, let’s pay attention to the steps below.

STEPS:

  • In the first place, select a cell in your dataset.
  • Secondly, go to the Insert tab and select the Table icon. Or, you can press Ctrl + T on the keyboard.

Use Excel Table to Change Chart Data Range Automatically

  • A message box will pop up.
  • Click OK to proceed.

Use Excel Table to Change Chart Data Range Automatically

  • As a result, the dataset will turn into an excel table.

Use Excel Table to Change Chart Data Range Automatically

  • Thirdly, navigate to the Insert tab and select Insert Column or Bar Chart icon. A drop-down menu will appear.
  • Select the Stacked Column icon from there.

Use Excel Table to Change Chart Data Range Automatically

  • Instantly, you will see the Sales Amount chart on the excel sheet.

Use Excel Table to Change Chart Data Range Automatically

  • Finally, insert the new Date and Sales Amount in Cell B13 and C13 respectively.

Use Excel Table to Change Chart Data Range Automatically

  • As a result, the excel chart data range will change automatically and display the added information on the chart.

Use Excel Table to Change Chart Data Range Automatically

Read More: How to Change Data Source in Excel Chart (3 Useful Examples)


Similar Readings


2. Change Chart Data Range Automatically with Excel Formulas

We can also use some formulas to change chart data range automatically in Excel. In this method, we will perform the tasks in two parts. In the first part, we will add formulas in the Name Manager, and in the second part, we will update the formulas in the chart. This process is also simple but lengthy. So, you need to keep patience and be careful while going through the steps. Because some small details can produce errors if you miss anything.

Let’s follow the steps below to learn the whole method.

STEPS:

  • Firstly, open the sheet that contains the dataset.
  • Go to the Formulas tab and select Name Manager.

Change Chart Data Range Automatically with Excel Formulas

  • Secondly, select New in the Name Manager window.

Change Chart Data Range Automatically with Excel Formulas

  • In the New Name message box, type MyDate in the Name box. You can type any name you want.
  • Then, copy the formula below and paste it into the ‘Refers to’ box:
=OFFSET(Formulas!$B$5,,,COUNTIF(Formulas!$B$5:$B$100,"<>"))
  • Click OK to move forward.

Change Chart Data Range Automatically with Excel Formulas

In this formula, we have used the combination of the OFFSET and COUNTIF functions together. This formula represents the Dates. We have specified Cell B5 as the reference cell. The OFFSET function extends starts from Cell B5 and extends to Cell B100 in this case. You must follow the writing convention here. You need to mention the name of the sheet inside the formula. In our case, the sheet name is ‘Formulas’. So, we have written Formulas!$B$5 and Formulas!$B$5:$B$100 here.

  • Again, click on New in the Name Manager window.

Change Chart Data Range Automatically with Excel Formulas

  • Like previously, type SalesAmount in the Name icon. You can type any name you want.
  • Then, copy the formula below and paste it into the ‘Refers to’ box:
=OFFSET(Formulas!$C$5,,,COUNTIF(Formulas!$C$5:$C$100,"<>"))
  • Click OK to proceed.

Change Chart Data Range Automatically with Excel Formulas

This formula works as the previous one but it represents the Sales Amount.

  • After that, click on Close in the Name Manager window.

Change Chart Data Range Automatically with Excel Formulas

  • In the following step, go to the Insert tab and select Insert Column or Bar Chart icon. A drop-down menu will appear.
  • Select the Stacked Column icon from there.

Change Chart Data Range Automatically with Excel Formulas

  • As a result, you will see the Sales Amount chart on the excel sheet.

Change Chart Data Range Automatically with Excel Formulas

  • At this moment, rightclick on the chart and click on Select Data from the Context Menu. It will open the Select Data Source window.

  • In the Select Data Source window, click on Edit in the Legend Entries box.

  • In the Edit Series dialog box, type =Formulas!$C$4 in the Series name box. Or you can simply type ‘=Sales Amount’.
  • Also, type =Formulas!SalesAmount in the Series values box.
  • Click OK to proceed.

Note: In the Series values box, you need to type =Sheet Name!Formula Name that is given in Name Manage. Because excel will show a warning message if you just enter the formula name. The sheet also must be entered like the above-mentioned pattern.

  • Similarly, click on Edit in the Horizontal Axis Labels box.

  • In the Axis Labels dialog box, type =Formulas!MyDate and click OK to proceed.

  • After that, click OK in the Select Data Source window.

  • At this moment, type the new Date and Sales Amount in Cell B13 and C13 respectively.

  • Finally, the excel chart data range will change automatically and display the added information on the chart.

Read More: How to Edit Chart Data in Excel (5 Suitable Examples)


Conclusion

In this article, we have discussed 2 easy methods to Change Chart Data Range Automatically in Excel. I hope this article will help you to perform your tasks easily. Moreover, using Method-1 you can change the chart data range very easily. Furthermore, we have also added the practice book at the beginning of the article. To test your skills, you can download it to exercise. You can also visit the ExcelDemy website for more articles like this. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.


Related Articles

Mursalin

Mursalin

Hi there! This is Mursalin. I am an Excel and VBA content developer at ExcelDemy. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I am currently working and doing research on Microsoft Excel. Here I will be posting articles related to Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo