How to Change Chart Data Range in Excel (5 Quick Methods)

Sometimes you may need to change a chart data range in Excel. In this article, I will explain how to change a chart data range in Excel.

Here, I have described 5 methods to change a chart data range in Excel. For your better understanding, I will use a sample dataset. Which has 3 columns. They are Customer Name, Sales, and Profit. The dataset is given below.

1. Using Design Tab to Change Chart Data Range in Excel

There is a built-in process in Excel for making charts under the Charts group Feature. In addition, I need a chart to see you how to change that chart data range. Here, I will use Bar Charts Feature to make a Bar Chart. The steps are given below.

Steps:

• Firstly, you have to select the data. Here, I have selected the range B4:D9.
• Secondly, you have to go Insert tab.

• Now, from the Charts group section, you must select Insert Column or Bar Chart.
• Then, I have chosen under 2-D Column >> Clustered Column. Here, the selection will be according to your preference.

Now, you will see the result by clicking the 2-D Clustered Column feature.

• Now, you have to select the chart.
• Then, from Chart Design >> you need to choose Select Data.

At this time, you will see the following dialog box named Select Data Source.

• Now, from the dialog box of Select Data Source, you have to choose the Sales Option.
• Then, you must click on the Edit feature.

After that, a new dialog box named Edit Series will appear.

• Then, you just need to write down the data range in Series values up to what you want to keep in the chart. Here, I have included a new cell C10. So, I have written as $C$5:$C$10.
• Now, you need to click OK.

Subsequently, the previous dialog box of Select Data Source will appear.

• Now, from this, you may select the Profit option to change the data range of Profit.
• Then, you need to choose the Edit feature.

• Again, the dialog box named Edit Series will appear.
• Similarly, you need to edit the Profit.
• Here, I have included a new cell D10. So, I have written as $D$5:$D$10 in the Series values box.
• Finally, you must click on OK.

Subsequently, the previous dialog box of Select Data Source will appear.

• Now, from this, you may click on the Edit option to change the Axis Labels.

At this time, a dialog box named Axis Labels will appear.

• Then, you have to select the Axis label range. Here, I have selected the range from B5:B10.
• Now, press OK to make the changes.

• After this, press OK on the Select Data Source box.

Finally, you will see the following Chart with the changed data range.

Read More:  How to Change Data Source in Excel Chart

2. Changing Chart Data Range Through Context Menu Bar

You can apply Context Menu Bar to change the chart data range in Excel. Firstly, you need a chart in which you will change the chart data range.

The steps are given below.

Steps:

• Firstly, you have to select the data. Here, I have selected the range B4:D9.
• Secondly, you have to go Insert tab.

• Now, from the Charts group section, you must select Insert Column or Bar Chart.
• Then, I have chosen under 2-D Column >> Clustered Column. Here, the selection will be according to your preference.

At this time, you can see the following Column Chart.

Now, you want to change the chart data range.

• Firstly, you must Right-Click on the chart.
• Secondly, from the Context Menu Bar >> you need to choose Select Data.

After that, you will see the following dialog box of Select Data Source.

• Now, from the dialog box of Select Data Source, you have to choose the Edit feature under the Sales option.

After that, a new dialog box named Edit Series will appear.

• Then, you just need to write down the data range in Series values up to what you want to keep in the chart. Here, I have included a new cell C10. So, I have written as $C$5:$C$10.
• Now, you need to click OK.

Subsequently, the previous dialog box of Select Data Source will appear.

• Now, from this, you may click on the Edit option to change the Axis Labels.

• Then, you have to select the Axis label range. Here, I have selected the range from B5:B10.
• Now, press OK to make the changes.

• After this, press OK on the Select Data Source box.

Finally, you will see the following changed chart. Here, I have changed the Chart title.

Read More:  How to Edit Chart Data in Excel

3. Using Mouse Pointer to Change Chart Data Range in Excel

Maybe the easiest method to change the chart data range in Excel is using Mouse Pointer. Suppose, you have the following bar chart. Now, you want to include more data in it.

Steps:

• Firstly, you need to click on the chart.
• Secondly, you must go to the data range and drag the Mouse Pointer below to include the data.

Here, you can see that all wanted data are selected.

At this time, you will see the result.

Moreover, if you want to remove some data.

• At first, you must click on the chart.
• Then, you have to drag the Mouse Pointer up in the data range.

You can see the result below.

4. Using Table Feature for Changing Chart Data Range

You can convert your data into a table by using the Table command. Then, you can easily change the chart data range. Now, let’s convert the data into a table. The steps are given below.

Steps:

• Firstly, you must select the data. Here, I have selected the range B4:D9.
• Secondly, from the Insert tab >> select the Table feature.

Now, a dialog box of Create Table will appear.

• Next, select the data for your table. Which will be auto-selection, here the auto-selected range is  B4:D9.
• Make sure that “My table has headers” is marked.
• Then, press OK.

At this time, you will see the following table.

• Now, you have to select the table.
• Then, you need to go to the Insert tab.

• After this, from the Charts group >> choose 2-D Line >> select Line with Markers feature.

So, the Chart is ready now.

Now, if you Copy-Paste any data to the cell which is adjacent below to the table, then you will see the curve will be auto-modified. Here, I will copy some data to the cell B10.

Following this, you will see the result. Moreover, I have changed the Chart title in the modified chart.

5. Using Formulas to Change Chart Data Range in Excel

You can employ Formula to auto-modify your chart. Which is called a dynamic chart range. In this method, I will use OFFSET, and COUNTIF functions. The steps are given below.

Steps:

• Now, from Formulas ribbon >> you need to go to Name Manager.

At this time, a new dialog box named Name Manager will appear.

• Now, you need to click on New to that box.

Subsequently, you will see another dialog box named New Name.

• Firstly, you must write down the name as ChartName in the Name box to call the Name column.
• Secondly, select your worksheet in the Scope box. My worksheet is Formulas.
• Thirdly, you have to write down the following formula in the Refers to box.

• Finally, press on OK.

Again, the Name Manager dialog box will appear.

• Now, you need to choose New.

Basically, you need to do the same thing according to your data in the chart.

• Firstly, you must write down the name as ChartSales in the Name box to call the Sales column.
• Secondly, select your worksheet in the Scope box. My worksheet is Formulas.
• Thirdly, you have to write down the following formula in the Refers to box.

• Finally, press on OK.

• Now, close your Name Manager dialog box.

• Firstly, you must choose any cell. Here, I have chosen Cell B11.
• Secondly, you have to go to the Insert tab.
• Thirdly, from 2-D Line >> choose Line with Markers.

At this time, you can see the following blank box.

• Now, you need to select the Blank Chart.
• Then from the Chart Design tab >> go to Select Data ribbon.

Subsequently, a dialog box of Select Data Source will appear.

• Now, you must select Add from the following box.

As well as that, another dialog box will appear.

• Now, you must mention the worksheet in the Series values. Here, I have mentioned the worksheet of Formulas.

• Then, you should call your data by writing the following.

This formula will call you data. Which column header is Sales.

• Finally, you must click on OK to get the changes.

Subsequently, the previous dialog box of Select Data Source will appear.

• Now, from this, you may click on the Edit option to change the Axis Labels.

• Similarly, you need to write the following formula in the Axis label range.

This formula will call you data. Which column header is Name.

• Finally, you must click on OK.

• After this, press OK on the Select Data Source box.

Finally, you will see the following line chart.

Now, if you add any value to your data range by copying or writing it down, you will see the changes in the chart automatically.

Here, I have included two more pieces of information B10:C11.

The following is the final result in which I have introduced the Chart title.

Read More: How to Change Chart Data Range Automatically in Excel

Things to Remember

• If you change any data which is used in the chart then the chart will be auto-updated.
• Furthermore, using the Mouse Pointer method is the easiest method.
• Moreover, using a table for the chart is the best option.

Practice Section

Now, you can practice the explained method by yourself.

Download Practice Workbook

You can download the practice workbook from here:

Conclusion

I hope you found this article helpful. Here, I have explained How to Change Chart Data Range in Excel. Please, drop comments, suggestions, or queries if you have any in the comment section below.

Related Articles

• How to Edit Data Table in Excel Chart
• How to Change X-Axis Values in Excel
• How to Sort Data in Excel Chart
• How to Group Data in Excel Chart
• How to Limit Data Range in Excel Chart
• How to Skip Data Points in an Excel Graph
• How to Remove One Data Point from Excel Chart
• How to Hide Chart Data in Excel
• How to Ignore Blank Cells with Formulas in Excel Chart

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