Dynamic range in Microsoft Excel is commonly used to store a wide range of data by applying the OFFSET function. This stored data with a defined name is then used for different calculations under various functions. In this article, you’ll get to know precisely how you can use this OFFSET function to store, define & use a range of cells or data in Excel.
The above screenshot is an overview of the article which represents an example of the use of the OFFSET function. You’ll learn more about the dataset, and the creation & uses of dynamic named range with OFFSET function in the following sections of this article.
Excel OFFSET Function Overview
- Objective:
Returns a reference to a range that is a given number of rows & columns from a given reference.
- Syntax:
=OFFSET(reference, rows, cols, [height], [width])
- Arguments:
reference- A cell or a range of cells. Based on this reference, the offset parameters are applied.
rows- Row number that is counted downward or upward from the reference point.
cols- Column number that is counted to the right or left from the reference value.
[height]- Height or number of the rows that will return as the resultant values.
[width]- Width or number of the columns that will return as the resultant values.
- Example:
In the picture below, there are 4 columns with some random names of computer brands, device types, model names & prices.
Based on the data from the table, we’re going to assign the arguments that are mentioned in Column H.
📌 Steps:
➤ As we’re going to find the result by using the OFFSET function in Cell H15, we have to type there:
=OFFSET(B4,5,2,4,2)➤ After pressing Enter, you’ll be shown an array of return values based on your argument selections.
So how does this function work? Inside the function, the 1st argument is Cell B4 which is known as a reference value. Now, go to the 5th row downward & 2nd column to the right from this reference cell & you’ll get Cell D9. As our row height is 2, so 4 cells to the bottom starting from D9 will return from the function. And last of all, the column height- 2 means that 4 rows will expand to the next column right to Column D. So, the final resultant array will consist of the Cell Range of D9:E12.
Read More: Dynamic Range for Multiple Columns with Excel OFFSET
Creating Dynamic Range with OFFSET & COUNTA Functions in Excel
COUNTA function counts the number of cells excluding all empty cells in a range of cells. Now using COUNTA functions, we’ll assign the row height & column width based on the available data in the range.
📌 Steps:
➤ Select Cell H4 & type:
=OFFSET(B4,0,0,COUNTA(B4:B100),COUNTA(B4:E4))➤ Press Enter & you’ll see the whole array will return as the resultant values.
In the argument section, row height has been assigned with COUNTA(B4:B100) & which means we’re assigning the rows up to the 100th row in the spreadsheet so that when a new value is inputted under the original range of data within the 100th row, that new value will also be stored by OFFSET function. Again, as the column width has been defined as COUNTA(B4:E4), so the four columns (B, C, D, E) are now assigned to the function based on the reference value selected in the OFFSET function.
The picture below, is an example of when you input a value under the original range of data, instantly the resultant value will be shown in the OFFSET table.
Using Name Manager to Create Dynamic Named Range with OFFSET & COUNTA Functions
By using Name Manager, you can define the name of the resultant array found through the OFFSET function.
📌 Step 1:
➤ Under the Formula tab, select Name Manager. A dialogue box will open.
➤ Press New & the Name Editor box will appear.
📌 Step 2:
➤ Define the name of your dataset or the range of cells you want to offset.
➤ In the reference box, type the formula:
=OFFSET(B4,0,0,COUNTA(B4:B100),COUNTA(B4:E4))➤ Press OK & Name Manager will now show the defined name in the list along with the reference formula at the bottom.
📌 Step 3:
➤ Now close the Name Manager & return to your spreadsheet.
📌 Step 4:
➤ Select any cell in your spreadsheet & start to type the defined name as the formula. You’ll find the defined name there in the function list.
➤ Select that function & press Enter.
Like in the picture below, you’ll see the resultant array that was stored as the reference with the OFFSET function by Name Manager.
Read More: Dynamic Named Range Based on Cell Value in Excel
Using of Dynamic Named Range for Calculations
After you have defined the name of the array or the range of cells selected before, now you can do different calculations based on the numerical values or apply any function to the dynamic named range of data. From our dataset, we’ll now offset the entire price list first & then do a few algebraic calculations.
📌 Step 1:
➤ Open the Name Editor again & name it Prices.
➤ In the reference function box, type the formula:
=OFFSET(E4,1,0,COUNTA(E5:E100),1)➤ Press OK & the Name Manager will show the defined name for Prices with a reference formula at the bottom.
📌 Step 2:
➤ Close the Name Manager & return it to your spreadsheet.
📌 Step 3:
➤ As we’ll find out the sum of all prices from the list, the formula with a newly defined named range in Cell H11 will be:
=SUM(Prices)➤ After pressing Enter, you’ll get the total prices of all the devices at once.
This is how the dynamic named range works for a function during calculation. You won’t have to input the cell references every time in the function bar as you’ve already defined a name for that range of cells with the Name Manager.
Similarly, by using AVERAGE, MAX & MIN functions, you can also evaluate some other data in Column H that are shown in the following picture.
Read More:Â Create Dynamic Sum Range Based on Cell Value in Excel
Alternative to OFFSET: Creating Dynamic Range with INDEX Function
A suitable alternative to the OFFSET function is the INDEX function. You can store multiple data or a range of cells with this INDEX function. Here we’re going to define the name of the list of prices once again.
📌 Step 1:
➤ Open Name Editor again & type the formula in the reference box:
=INDEX(B5:E100, 0, MATCH(E4, B4:E4, 0))➤ Press Enter & you’ll find the newly defined name in the Name Manager.
📌 Step 2:
➤ Close the Name Manager & you’re done.
Now you can use this dynamic named range in your spreadsheet for any sort of calculation by assigning the related functions.
Read More: How to Create Dynamic Range Using Excel INDEX Function
Download Practice Workbook
You can download the Excel workbook that we’ve used to prepare this article.
Concluding Words
I hope this article on the creation & uses of dynamic range will now prompt you to apply the OFFSET function effectively in your Excel spreadsheets. If you have any questions or feedback, please let me know in the comment section. You can also check out our other articles related to Excel functions on this website.
