OFFSET Function to Create & Use Dynamic Range in Excel

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.

offset dynamic range in excel overview

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, the creation & uses of dynamic named range with OFFSET function in the following sections in this article.


Download Practice Workbook

You can download the Excel workbook that we’ve used to prepare this article.


Creating & Using Dynamic Named Range with OFFSET Function

Before getting down to the creation & uses of dynamic named range with OFFSET function in Excel, let’s get introduced to the OFFSET function first.

Introduction to the OFFSET Function

  • 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.

offset introduction

Based on the data from the table, we’re going to assign the arguments that are mentioned in Column H.

offset introduction

📌 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.

offset introduction

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.


Creating Dynamic Range with OFFSET & COUNTA Functions

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.

offset dynamic range in excel

In the argument section, row height has been assigned with COUNTA(B4:B100) & that means we’re assigning the rows up to the 100th row in the spreadsheet so that when a new value will be 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.

In the picture below, this 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.

offset dynamic range in excel


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.

offset dynamic named range in excel

📌 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.

offset dynamic named range in excel

📌 Step 3:

➤ Now close the Name Manager & return to your spreadsheet.

offset dynamic named range in excel

📌 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.

offset dynamic named range in excel

Like in the picture below, you’ll see the resultant array that was stored as the reference with the OFFSET function by Name Manager.

offset dynamic named range in excel


Use 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.

use of dynamic named range with offset

📌 Step 2:

➤ Close the Name Manager & return it to your spreadsheet.

use of dynamic named range with offset

📌 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.

use of dynamic named range with offset

Similarly, by using AVERAGE, MAX & MIN functions, you can also evaluate some other data in Column H that are shown in the following picture.

use of dynamic named range with offset


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.

dynamic named range with index match

📌 Step 2:

➤ Close the Name Manager & you’re done.

dynamic named range with index match

Now you can use this dynamic named range in your spreadsheet for any sort of calculation by assigning the related functions.


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.


You May Also Like to Explore

Use of Offset Function in Excel [Offset – Match Combo, Dynamic Range]

Offset(…) Function in Excel with Examples

Excel Formula to Copy Cell Value from Another Sheet

How to Copy a Worksheet in Excel (5 Smart Ways)

How to use cell references with multiple worksheets in Excel

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo