How to Expand Chart Data Range in Excel (5 Suitable Methods)

We use Excel Charts for the visual representation of data. It is easier to get an idea of some from a chart rather than a dataset. We form this chart using the data. But sometimes we need to add new data to the data set. It means the dataset may expand from time to time. In this article, we will explain how to expand chart data range in Excel.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


5 Methods to Expand Chart Data Range in Excel

We will explain 5 methods to expand the chart data range in Excel. Follow the below methods.

1. Expand Chart Data from Chart Design Options

We have a dataset of the name and salaries of some employees of an organization and a corresponding chart for the data.

The chart is as follows.

  • Now, we add a new row to the dataset.

Now apply the following steps to add the row data to the chart.

šŸ“Œ Steps:

  • Click on the Chart. The Chart Design shows now.
  • Click on the Select Data option from the DataĀ group.

Expand Chart Data from Chart Design Options

  • Now, the Select Data Source window appears.
  • Select the Salary option and press the EditĀ button.

Expand Chart Data from Chart Design Options

  • Then, change the range of the Series values box and press OK.

Expand Chart Data from Chart Design Options

  • Now, go to the Horizontal Axis LabelsĀ section.
  • Click on the EditĀ option.

Expand Chart Data from Chart Design Options

  • In the Axis label range, the box changes the range from the dataset.
  • Again, press OK.

Expand Chart Data from Chart Design Options

  • Finally, look at the dataset.

The chart has been extended with the new data.

Read More: How to Select Data for a Chart in Excel (2 Ways)


2. Expand Chart Data Range from Right-Click Context Menu

In this section, we will select an option from the Context Menu to expand the data range of the chart.

šŸ“Œ Steps:

  • Click on the ChartĀ first.
  • Then, press the right button of the mouse.
  • Choose Select Data from the Context Menu.

Expand Chart Data Range from Right-Click Context Menu

  • The Select Data Source options appear now.
  • After that, choose Salary and press the EditĀ button.

  • The Edit Series window appears.
  • Go to the Series values box and modify the range.
  • Finally, press OK.

  • Again, go back to the Select Data SourceĀ window.
  • Select the Edit option of the Horizontal Axis LabelsĀ section.

  • Axis Labels window comes now.
  • Modify the Axis Label range. Go to the box and select the range from the dataset.
  • Again, press OK.

  • Look at the chart.

The data range of the chart has expanded successfully.

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


3. Use Mouse Pointer to Expand Data Range

This is the easiest way to expand the data range of the chart. Just one step to expand the data range.

šŸ“Œ Steps:

  • Click on the chart first.
  • Note, look data range in the editable mode.
  • Place the cursor on the dataset.
  • Now, move the cursor downwards.

Mouse Pointer to Expand Data Range

  • Look at the dataset now.

The expanded data is reflected on the chart.

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


Similar Readings


4. Utilize Excel Table Command

The previous methods were static processes. We need to expand the chart manually. But there is some dynamic way to expand the charts. Excel Table is one of them. Just expand the Excel Table, and the chart will expand automatically.

šŸ“Œ Steps:

  • First, we will form an Excel Table.
  • Select Range B4:C10.
  • Go to the InsertĀ tab.
  • After that, click on the Table option.

Excel Table to expand data range

  • Create Table window appears.
  • Check My Table has headers option at this window.
  • Then, press OK.

Excel Table to expand data range

  • Excel Table has formed successfully.

  • Now, we will form the chart using this table.
  • Select the whole table.
  • Again, go to the InsertĀ tab.
  • Choose the 2-D Column from the menu.

Excel Table to expand data range

  • We can see a chart has been created successfully.

  • Now, we will expand the table.
  • We copy-paste or directly write data below the last cell of the table.
  • Have a look at the dataset.

Excel Table to expand data rangeWe can see new data is shown in the chart.

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


5. Set a Dynamic Formula with Named Range to Each Data Column

In this section, we will use a dynamic named range to expand the chart data range in Excel. We will apply a formula based on the OFFSET and COUNTA functions for the named range.

šŸ“Œ Steps:

  • We need to define the name range for each data column.
  • Click on Cell B4.
  • Go to the FormulasĀ tab.
  • Choose the Define NameĀ option.

Dynamic Formula with Named Range to expand data range

  • The New Name window appears.
  • Put a name in the NameĀ box.
  • Select the worksheet from the ScopeĀ dropdown.
  • Insert a formula on the Refers toĀ field.
=OFFSET('Named Range'!$B$5,0,0,COUNTA('Named Range'!$B:$B)-1)

Dynamic Formula with Named Range to expand data range

  • Finally, press OK.
  • Repeat this process for the second column.
  • Named as Salary and put the formula below.
=OFFSET('Named Range'!$C$5,0,0,COUNTA('Named Range'!$C:$C)-1)

Dynamic Formula with Named Range to expand data range

  • This is our chart that has already been formed using the process shown in method 1.

  • We will use apply the named range here and make it dynamic.
  • Click on the chart.
  • Press the right button of the mouse.
  • Choose the Select Data option from the Context Menu.

  • Select Data Source window appears.
  • Select Salary and then click on the EditĀ button.

  • Edit Series window appears.
  • Change the Series value reference. Put the named range reference here.

  • We back to the Select Data SourceĀ window.
  • Press the Edit option of the Horizontal Axis LabelsĀ option.

  • Modify the Axis label rangeĀ window.
  • Finally, press OK.

  • Again, press OK.

  • Now, add data to the bottom of the dataset.

As a result, a new column appears in the chart.

Read More: Selecting Data in Different Columns for an Excel Chart


Conclusion

In this article, we described 5 methods to expand the chart data range in Excel. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.


Related Articles

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo