Dynamic Name Range Resize in Excel No Code Example

Dynamic Name Range Resize in Excel No Code Example

Name Range

Sometimes user may in need where he wants to resize the range of a formula dynamically based on data addition or deletion. In this article we will see how we can resize a name range.

Step 1: Consider following data where we will count number of name available in the column using COUNTA function

Step 2: Select the name from “A1:A9” and do followings to define a name range:

  1. Navigate Formulas tab
  2. Click on Name Managers under Defined names group
  3. Click on New button on the Name Manager dialog
  4. Give the Name as “NameList” and say OK
  5. Then close the dialog

Step 3: Write COUNTA formula to get the static count as shown below:

Result would look like below:

So far we have created a COUNT based on static Name range, lets make it dynamic.

Step 4: Click on Name Manager under Formulas tab and edit the name range “NameList” by clicking on Edit button and put formula as shown below:

Formula

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

Step 5: Now try to add or delete values in Column “A” and see the Number of Names column value auto gets changes:

One thought on “Dynamic Name Range Resize in Excel No Code Example

Leave a Reply

Your email address will not be published. Required fields are marked *