You are currently offline, waiting for your internet to reconnect

How to create a dynamic defined range in an Excel worksheet

Support for Office 2003 has ended

Microsoft ended support for Office 2003 on April 8, 2014. This change has affected your software updates and security options. Learn what this means for you and how to stay protected.

Summary
In Microsoft Excel, you may have a named range that must be extended to include new information. This article describes a method to create a dynamic defined name.

Note The method in this article assumes that there are no more than 200 rows of data. You can revise the defined names so that they use the appropriate number and reflect the maximum number of rows.

How to use the OFFSET formula with a defined name

To do this, follow these steps, as appropriate for the version of Excel that you are running.

Microsoft Office Excel 2007,  Microsoft Excel 2010 and Microsoft Excel 2013

  1. In a new worksheet, enter the following data.
    AB
    1MonthSales
    2Jan10
    3Feb20
    4Mar30
  2. Click the Formulas tab.
  3. In the Defined Names group, click Name Manager.
  4. Click New.
  5. In the Name box, type Date.
  6. In the Refers to box, type the following text, and then click OK:
    =OFFSET($A$2,0,0,COUNTA($A$2:$A$200),1)
  7. Click New.
  8. In the Name box, type Sales.
  9. In the Refers to box, type the following text, and then click OK:
    =OFFSET($B$2,0,0,COUNT($B$2:$B$200),1)
  10. Click Close.
  11. Clear cell B2, and then type the following formula:
    =RAND()*0+10
    Note In this formula, COUNT is used for a column of numbers. COUNTA is used for a column of text values.

    This formula uses the volatile RAND function. This formula automatically updates the OFFSET formula that is used in the defined name "Sales" when you enter new data in column B. The value 10 is used in this formula because 10 is the original value of cell B2.

Microsoft Office Excel 2003

  1. In a new worksheet, enter the following data:
    AB
    1MonthSales
    2Jan10
    3Feb20
    4Mar30
  2. On the Insert menu, point to Name, and then click Define.
  3. In the Names in workbook box, type Date.
  4. In the Refers to box, type the following text, and then click OK:
    =OFFSET($A$2,0,0,COUNTA($A$2:$A$200),1)
    .
  5. Click Add.
  6. In the Names in workbook box, type Sales.
  7. In the Refers to box, type the following text, and then click Add:
    =OFFSET($B$2,0,0,COUNT($B$2:$B$200),1)
  8. Click OK.
  9. Clear cell B2, and then type the following formula:
    =RAND()*0+10
    Note In this formula, COUNT is used for a column of numbers. COUNTA is used for a column of text values.

    This formula uses the volatile RAND function. This formula automatically updates the OFFSET formula that is used in the defined name "Sales" when you enter new data in column B. The value 10 is used in this formula because 10 is the original value of cell B2.
automatically expand XL2003 XL2007 XL2010 XL2013
Properties

Article ID: 830287 - Last Review: 11/16/2012 07:29:00 - Revision: 8.0

Microsoft Office Excel 2007, Microsoft Office Excel 2003, Microsoft Excel 2010, Microsoft Excel 2013

  • kbformula kbexpertisebeginner kbhowtomaster KB830287
Feedback