Creating automatically extended plot ranges

Excel 2016
If you often need to adjust your data ranges so that your charts plot an updated data range, you may be interested in a trick that forces Excel to update the chart's data range whenever you add new data to your spreadsheet.
Chart in Excel 365
The Chart with automatically extended plot ranges in Excel 365

You can force Excel to update a new or existing chart automatically when you add new data. To do so, follow the next steps:

Prepare data

   1.   On the Formulas tab, in the Defined Names group, click Define Name:

Define Name in Excel 365

Define data for the horizontal (Category) axis

   2.   In the New Name dialog box:

  • In the Name field, enter Data,
  • In the Refers to field, enter this formula:

    = OFFSET (<Sheet name>!<start cell>, 0, 0, COUNTA (<Sheet name>!<Column name>:<Column name>) - 1)

    New name in Excel 365

    The OFFSET () function returns a cell or range of cells that is a specified number of rows and columns from a cell or range of cells:

    = OFFSET (<reference>, <row>, <column>, [height], [width])

    Note: See below how to use the range from row instead of column.

  • Click the OK button to close the New name dialog box.

See more about creating and using named ranges.

Notice that the OFFSET () function refers to the first data point (cell B3) and uses the COUNTA () function to get the number of data points in the column.

Define data for the vertical (Value) axis

   3.   Define a new data range in the New Name dialog box:

  • In the Name field, type Sales,
  • In the Refers to field, enter the appropriate formula for Sales data:
    New name in Excel 365
  • Click the OK button to close the New Name dialog box.

Modify a chart

   4.   Select the chart and do one of the following:

  • On the Chart Design tab, in the Data group, choose Select Data:
    Select Data in Excel 365
  • Right-click in the chart area and choose Select Data... in the popup menu:
    Select Data in popup menu Excel 365

   5.   On the Select Data Source dialog box, change the range of data series and axis with the names that you defined in Steps 2 and 3:

   5.1.   Under Legend Entries (Series):

  • Select the data series that should be updated automatically and click the Edit button.
  • In the Edit Series dialog box, type the previously defined data range name for the data series (step 3).

    For this example:

    Select Data Source dialog box in Excel 365

    Series values: <Excel-file name>!Sales or <Excel-file name>!Sales

    Note: If you type the sheet name, Excel automatically changes it to the file name:

    Edit Series dialog box in Excel 365

   5.2.   Under Horizontal (Category) Axis Labels:

  • Click the Edit button.
  • In the Axis Labels dialog box, type the previously defined data range name for the axis (step 2).

    For this example:

    Axis Labels dialog box in Excel 365

    Axis label range: <Excel-file name>!Data

    Note: If you type the sheet name, Excel automatically changes it to the file name:

    Axis labels in Excel 365

After you perform these steps, when you add data to columns B and C, the chart updates automatically to show the new data (see at the top of this tip). To use this technique for your own data, ensure:

  • that the first argument for the OFFSET () function refers to the first data point,
  • that the argument for COUNTA () refers to the entire column of data.

Also, if the columns used for the data contain any other entries, COUNTA () will return an incorrect value.

Notes:

  1. If the axis doesn't change automatically, check:
    • The formulas in the chart,
    • The defined minimum and maximum values for the axis - Excel can't change custom values:
      Format Axis Options in Excel 365

    To allow Excel to change axis ranges, click the Reset button.

  2. The COUNTA () function was used in this example to avoid counting blank cells.

    Cells with formulas or filled with irrelevant information, such as comments, will also be counted as non-empty. Because of this, we used the COUNTA () - 1 to exclude the cell with column name (in this example: Data and Sales).

    In this case, replacing the COUNTA () function with another one is necessary. For example:

    • COUNT () counts only cells with numbers, but it also has issues depending on the data. In some cases, the COUNT () function can calculate formula results you don't want to show in the chart.
    • COUNTIF () can count cells that contain numbers. For example:

      COUNTIF (<data range>, ">0").

  3. If you need to use the data from rows instead of columns, change the formula code to:

    = OFFSET (<Sheet name>!<start cell>, 0, 0, 1, COUNTA (<Sheet name>!<Row name>:<Row name>) - 1):

    Chart in Excel 365
    The Chart with automatically extended plot ranges in Excel 365

See also this tip in French: Comment créer les graphiques avec les plages de tracé étendues automatiquement.

Please, disable AdBlock and reload the page to continue

Today, 30% of our visitors use Ad-Block to block ads.We understand your pain with ads, but without ads, we won't be able to provide you with free content soon. If you need our content for work or study, please support our efforts and disable AdBlock for our site. As you will see, we have a lot of helpful information to share.