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 Create Excel Chart Using Data Range Based on Cell Value
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 Change Data Source in Excel Chart (3 Useful Examples)
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.
Read More: How to Expand Chart Data Range in Excel (5 Suitable Methods)
Similar Readings
- How to Plot Time over Multiple Days in Excel (With Easy Steps)
- Excel Chart Not Updating with New Data (2 Suitable Solutions)
- Excel Chart by Month and Year (2 Suitable Examples)
- How to Add Multiple Trendlines in Excel (With Quick Steps)
- Excel VBA: Get Source Data Range from a Chart (with Quick Steps)
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.
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.
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.
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.
- Finally, press on OK.
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.
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 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.
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. 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
- How to Group Data in Excel Chart (2 Suitable Methods)
- How to Import Data into PowerPivot & Create Pivot Table/Pivot Chart
- Use Scatter Chart in Excel to Find Relationships between Two Data Series
- How to Create Graph from List of Dates in Excel (with Easy Steps)
- How to Format Data Series in Excel (with Easy Steps)
- Data Table Not Working in Excel (7 Issues & Solutions)