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

Get FREE Advanced Excel Exercises with Solutions!

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.

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.

## 1. Using 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.

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

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

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

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

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

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

Read More: How to Change Data Source in Excel Chart

## 2. Changing 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.

• Secondly, select New in the Name ManagerÂ window.

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

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.

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

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

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

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

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

• At this moment, rightâ€“click 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.

You can download the practice book from here.

## Related Articles

<< Go Back to Edit Chart Data | Excel Chart Data |Â Excel Charts | Learn Excel

## What is ExcelDemy?

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

Mursalin Ibne Salehin holds a BSc in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. Over the past 2 years, he has actively contributed to the ExcelDemy project, where he authored over 150 articles. He has also led a team with content development works. Currently, he is working as a Reviewer in the ExcelDemy Project. He likes using and learning about Microsoft Office, especially Excel. He is interested in data analysis with Excel, machine learning,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF