It is a useful concept which saves you a lot of time when you are working with multiple formulas in bigger data range. The Dynamic named Ranges means the range that isn’t fixed and it can be increased or decreased automatically as per the size of the data. This can be achieved using named ranges feature of Excel without using VBA macros. We will understand this better with a simple example.
Using Named Formula Ranges in Excel
Below is the sample data of a sales shop which continuously updates on daily basis.
You want to create a name for the data in Column A without referring to empty cells. In simple words, reference to the Dateofpurchase changes every day as you add a new transaction. In this scenario you can create a dynamic named formula that changes automatically as you enter new data or delete existing data.
Steps to Create Dynamic Named Formula
1. Goto Formulas->Defined names-Define Name to display the New Name Dialog box.
2. Enter DateofPurchase in the Named Field.
3. Enter the following formula in the Refers to Field and Click Ok to Close Name Dialog box.
=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A),1)
The above step creates a named Formula that uses the excel OFFSET and COUNTA functions to return a range that changes based on the number of non empty cells in column A.To test this let us use a simple COUNTA function
=COUNTA(Date_Of_Purchase)
As you can see, the value changes when you add a new transaction or delete a transaction. This is very useful when you work on large data. Because it uses dynamic memeory allocation, it increases the calculation speed of the formulas. You can see actual difference when you use this with multiple VLOOKUP formula on a large Data range.
Note:
The Above shown method works only when you have non empty data range. That is, the range should not contain blanks in between.
