# How to create a dynamic defined range in an Excel worksheet

## 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

In a new worksheet, enter the following data.

Number A B 1 Month Sales 2 Jan 10 3 Feb 20 4 Mar 30 Click the

**Formulas**tab.In the

**Defined Names**group, click**Name Manager**.Click

**New**.In the

**Name**box, type Date.In the

**Refers to**box, type the following text, and then click**OK**:**=OFFSET($A$2,0,0,COUNTA($A$2:$A$200),1)**Click

**New**.In the

**Name**box, type Sales.In the

**Refers to**box, type the following text, and then click**OK**:**=OFFSET($B$2,0,0,COUNT($B$2:$B$200),1)**Click

**Close**.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

In a new worksheet, enter the following data:

Number A B 1 Month Sales 2 Jan 10 3 Feb 20 4 Mar 30 On the

**Insert**menu, point to**Name**, and then click**Define**.In the

**Names in workbook**box, type Date.In the

**Refers to**box, type the following text, and then click**OK**:**=OFFSET($A$2,0,0,COUNTA($A$2:$A$200),1).**Click

**Add**.In the

**Names in workbook**box, type Sales.In the

**Refers to**box, type the following text, and then click Add:**=OFFSET($B$2,0,0,COUNT($B$2:$B$200),1)**Click

**OK**.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.