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.


Download Practice Workbook

You can download the practice workbook from here:


5 Methods to Change 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.

How to Change Chart Data Range in Excel


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.

How to Change Chart Data Range in Excel

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.

How to Change Chart Data Range in Excel

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

How to Change Chart Data Range in Excel

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

How to Change Chart Data Range in Excel

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.

How to Change Chart Data Range in Excel

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.

How to Change Chart Data Range in Excel

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

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

How to Change Chart Data Range in Excel

Read More: How to Create Excel Chart Using Data Range Based on Cell Value


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

How to Change Chart Data Range in Excel

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.

How to Change Chart Data Range in Excel

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.

How to Change Chart Data Range in Excel

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.

How to Change Chart Data Range in Excel

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

How to Change Chart Data Range in Excel

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


3. Employing 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.

How to Change Chart Data Range in Excel

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

At this time, you will see the result.

How to Change Chart Data Range in Excel

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.

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


Similar Readings


4. Use of Table Feature in Excel

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.

How to Change Chart Data Range in Excel

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.

How to Change Chart Data Range in Excel

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

So, the Chart is ready now.

How to Change Chart Data Range in Excel

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.

How to Change Chart Data Range in Excel

Read More: How to Add Data Table in an Excel Chart (4 Quick Methods)


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.

How to Change Chart Data Range in Excel

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.
=OFFSET(Formulas!$B$5,,,COUNTIF(Formulas!$B$5:$B$100,”<>”))
  • Finally, press on OK.

How to Change Chart Data Range in Excel

Formula Breakdown

  • COUNTIF will count the number of cells for a range that meet a given criterion.
    • COUNTIF(Formulas!$B$5:$B$100,”<>”)—> will count all the valued cells from B5 to B100 and will skip the duplicate values. This will count here 7.
  • Formulas!$B$5—> is the reference here.
  • OFFSET will return some values from a reference range.
    • OFFSET(Formulas!$B$5,,,7)—> This will call your 7 data from the cell

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.
=OFFSET(Formulas!$C$5,,,COUNTIF(Formulas!$C$5:$C$100,”<>”))
  • Finally, press on OK.

How to Change Chart Data Range in Excel

Formula Breakdown

  • COUNTIF will count the number of cells for a range that meet a given criterion.
    • COUNTIF(Formulas!$C$5:$C$100,”<>”)—> will count all the valued cells from C5 to C100. This will count here 7.
  • Formulas!$C$5—> is the reference here.
  • OFFSET will return some values from a reference range.
    • OFFSET(Formulas!$C$5,,,7)—> This will call your 7 data from the cell.
  • 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.

How to Change Chart Data Range in Excel

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.

How to Change Chart Data Range in Excel

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.
=Formulas!ChartSales

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.

How to Change Chart Data Range in Excel

  • Similarly, you need to write the following formula in the Axis label range.
=Formulas!ChartName

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.

How to Change Chart Data Range in Excel

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.

How to Change Chart Data Range in Excel

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

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


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.

How to Change Chart Data Range in Excel


Conclusion

I hope you found this article helpful. Here, I have explained How to Change Chart Data Range in Excel. You can visit our website Exceldemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Musiha

Musiha

I am Musiha, graduated from Naval Architecture and Marine Engineering Dept., BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo