A dynamic named range in Excel expands automatically when new values are added to the range.

For Example, select the range A1:A6, name it ‘Data’ and then calculate the sum.

When new values add to the range, Excel does not update the sum.

To make the range dynamic, execute following steps:

1- In the **Formulas** tab, go to the **Defined Names** section and click the **Name Manager**. (Shortcut key alert: the Name Manager also open by pressing **Ctrl+F3**).

2- Click ‘**Edit**‘ and in the ‘**Refers to**‘ box type the formula ‘=OFFSET($A$1,0,0,COUNTA($A:$A),1)’. Press **Enter**.

In the above-mentioned formula, the syntax of the OFFSET function is:

=OFFSET(reference, rows, cols, [height], [width])

The function has following arguments:

**reference** – Required. The reference from which you want to base the offset. In our example it is column (**$A$1**), the dollar sign ($) is used to make the reference absolute.

**rows** – Required. The number of rows (up or down) below the starting reference. In our example, we took **0**.

**cols** – Required. The number of columns (right) to the starting point. i.e. **0** in the example.

**[height]** – Optional. The height in the number of rows of the returns reference. For this **COUNTA** function is used in the example to know the number of values in column A that are not empty.

**[width]** – Optional. The width in columns of the returned reference. i.e. **1**.

Once you finalize the formula, whenever you add value to the range, COUNTA($A:$A) increases and the range returned by the OFFSET function expands.