Examples of OFFSET Function in Excel (Formula+VBA ) 

Download Practice Workbook

Overview of OFFSET Function

The OFFSET function returns a reference to a range that is a given number of rows & columns from a given reference. The function starts with a cell reference, moves to a specific number of rows down, then to a specific number of columns right, and extracts a section out of the data set.

It returns a section of data with a specific height and width, situated at a specific number of rows down and a specific number of columns right from a given cell reference.

The function will move the specified number of rows upward from the reference cell if the rows argument is negative.

The Syntax of the OFFSET function is:

Reference indicates the cell reference from where it starts off moving.

Rows dictate the number of rows it moves downward.

Cols illustrate the number of columns it moves rightward.

Height indicates the number of rows of the section of the data that it extracts.

And width indicates the number of columns of the section of the data that it extracts.

7 Suitable Examples of OFFSET Function Using Excel Formulas

Example 1 – Extracting a Whole Row

Steps:

• Enter the following formula to extract the sales records for laptop for all the months.

The OFFSET function starts moving from cell B5. It moves 7 rows downward to find the laptop. And extracts out a section of 1-row height and 6 columns of width. This is the sales record of laptop from Jan to May.

• Press Enter.

Read More: How to Use ROW Function in Excel (With 8 Examples)

Example 2 – Extracting an Entire Column

We’ll sort out a whole column from the dataset. Let’s try to find out all the sales in March. And we will extract a list of a total of 13 products.

Steps:

• Enter the following formula.

The function starts moving from cell B5. Moves 1 row down. It moves 3 columns right to the month of March and extracts a section of 13 rows in height (all the products) and 1 column in width (only for the month of March).

• Press Enter.

Example 3 – Extracting Adjacent Multiple Rows and Columns

Collect the sales of the products Mototorcyle, Desktop, and Laptop in the months of January and February.

Steps:

• Enter the following formula.

The function starts moving from cell B5. It moves 5 rows down to the product telephone. And collects the data of 3 rows height.

• Press Enter.

Read More: How to Use ROWS Function in Excel (With 7 Easy Examples)

Example 4 – Combining OFFSET & COUNTA Functions to Create Dynamic Range

COUNTA function counts the number of cells excluding all empty cells in a range of cells. Using COUNTA functions, we’ll assign the row height & column width based on the available data in the range.

Steps:

• Enter the following formula.

Row height has been assigned with COUNTA(B4:B100) in the argument section, which means that each row in the spreadsheet is assigned up to the 100th row. This way, whenever a new value is entered under the original range of data within the 100th row, that value will also be stored using the OFFSET function. COUNTA(B4:G4) has been defined as the column width, so the six columns (B, C, D, E, F and G) are now assigned to the function based on the reference value selected in the OFFSET function.

• Press Enter.

Example 5 – Creating Dynamic Range Using Name Manager with OFFSET Function

By using Name Manager, you can set the name of the resultant array found by the OFFSET function.

Steps:

• Under the Formula tab, select Name Manager.

• A dialogue box will open.
• Press New.
• The Name Editor box will appear.

• Define the name of your dataset or the range of cells you want to offset.
• In the reference box, enter the following formula:

• Press OK & Name Manager will now show the defined name in the list along with the reference formula at the bottom.

• Select any cell in your spreadsheet & enter the defined name as the formula. You’ll find the defined name in the function list.
• Select that function & press Enter.

• The output will look like this.

Example 6 – Getting Summation of an Entire Column

Steps:

• Enter the following formula in cell J5.

• Press Enter.

Example 7 – Getting Average of Whole Column

Steps:

• Enter the following formula in cell J5.

• Press Enter.

6 Suitable Examples of Offset Function with Excel VBA

Example 1 – Selecting a Range

Steps:

⧭ Open VBA Window:

• VBA has its own separate window to work with. You have to insert the code in this window. To open the VBA window, go to the Developer tab on your ribbon. Select Visual Basic from the Code group.

⧭ Insert Module:

• VBA modules hold the code in the Visual Basic Editor. It has a.bcf file extension. We can create or edit one through the VBA editor window. To insert a module for the code, go to the Insert tab on the VBA editor. Click on Module from the drop-down.

• A new module will be created.

⧭ Insert VBA Code:

• Select the module. Enter the following code in it.

Sub selectrangeoffset()
Range("A1:A6").Offset(4, 1).Select
End Sub

• Save the code.

⧭ Run VBA Code:

• Close the Visual Basic window. Press Alt+F8.
• The Macro dialogue box will opens, select selectrangeoffset in the Macro name. Click on Run.

⧭ Output:

• You will get the following output.

Sub selectrangeoffset()

Range("A1:A6").Offset(4, 1).Select

End Sub

Example 2 – Using Negative Value in Offset Property

Steps:

⧭ Open VBA Window:

• VBA has its own separate window to work with. You have to insert the code in this window. To open the VBA window, go to the Developer tab on your ribbon. Select Visual Basic from the Code group.

⧭ Insert Module:

• VBA modules hold the code in the Visual Basic Editor. It has a .bcf file extension. We can create or edit one through the VBA editor window. To insert a module for the code, go to the Insert tab on the VBA editor. Click on Module from the drop-down.

• A new module will be created.

⧭ Insert VBA Code:

• Select the module. Enter the following code in it.

Sub negativerangeoffset()
Range("F11:F16").Offset(-6, -2).Select
End Sub

• Save the code.

⧭ Run VBA Code:

• Close the Visual Basic window. Press Alt+F8.
• The Macro dialogue box will open, select negativerangeoffset in the Macro name. Click on Run.

⧭ Output:

• You will get the following output.

Sub negativerangeoffset()

Range("F11:F16").Offset(-6, -2).Select

End Sub

Example 3 – Selecting a Range with Respect to Active Cell

Steps:

⧭ Open VBA Window:

• VBA has its own separate window to work with. You have to insert the code in this window. To open the VBA window, go to the Developer tab on your ribbon. Select Visual Basic from the Code group.

⧭ Insert Module:

• VBA modules hold the code in the Visual Basic Editor. It has a.bcf file extension. We can create or edit one through the VBA editor window. To insert a module for the code, go to the Insert tab on the VBA editor. Click on Module from the drop-down.

• Aa new module will be created.

⧭ Insert VBA Code:

• Select the module. Enter the following code in it.

Sub actvcell()
Range(activecell.Offset(4, 1), activecell.Offset(9, 3)).Select
End Sub

• Save the code.

⧭ Run VBA Code:

• Close the Visual Basic window. Press Alt+F8.
• The Macro dialogue box will open, select actvcell in the Macro name. Click on Run.

⧭ Output:

• You will get the following output.

Sub actvcell()

Range(activecell.Offset(4, 1), activecell.Offset(9, 3)).Select

End Sub

Example 4 – Copying a Range

Steps:

⧭ Open VBA Window:

• VBA has its own separate window to work with. You have to insert the code in this window. To open the VBA window, go to the Developer tab on your ribbon. Select Visual Basic from the Code group.

⧭ Insert Module:

• VBA modules hold the code in the Visual Basic Editor. It has a.bcf file extension. We can create or edit one through the VBA editor window. To insert a module for the code, go to the Insert tab on the VBA editor. Click on Module from the drop-down.

• A new module will be created.

⧭ Insert VBA Code:

• Select the module. Enter the following code in it.

Sub copyrangeoffset()
Range("A1:A6").Offset(4, 1).Copy
End Sub

• Save the code.

⧭ Run VBA Code:

• Close the Visual Basic window. Press Alt+F8.
• The Macro dialogue box will open, select copyrangeoffset in the Macro name. Click on Run.

⧭ Output:

• You will get the following output.

Sub copyrangeoffset()

Range("A1:A6").Offset(4, 1).Copy

End Sub

Example 5 – Deleting a Range

Steps:

⧭ Open VBA Window:

• VBA has its own separate window to work with. You have to insert the code in this window. To open the VBA window, go to the Developer tab on your ribbon. Select Visual Basic from the Code group.

⧭ Insert Module:

• VBA modules hold the code in the Visual Basic Editor. It has a.bcf file extension. We can create or edit one through the VBA editor window. To insert a module for the code, go to the Insert tab on the VBA editor. Click on Module from the drop-down.

• A new module will be created.

⧭ Insert VBA Code:

• Select the module. Enter the following code in it.

Sub dltrangeoffset()
Range("F11:F17").Offset(-7, -2).Delete
End Sub

• Save the code.

⧭ Run VBA Code:

• Close the Visual Basic window. Press Alt+F8.
• The Macro dialogue box will open, select dltrangeoffset in the Macro name. Click on Run.

⧭ Output:

• You will get the following output.

Sub dltrangeoffset()

Range("F11:F17").Offset(-7, -2).Delete

End Sub

Example 6 – Entering Value to a Cell

Steps:

⧭ Open VBA Window:

• VBA has its own separate window to work with. You have to insert the code in this window. To open the VBA window, go to the Developer tab on your ribbon. Select Visual Basic from the Code group.

⧭ Insert Module:

• VBA modules hold the code in the Visual Basic Editor. It has a.bcf file extension. We can create or edit one through the VBA editor window. To insert a module for the code, go to the Insert tab on the VBA editor. Click on Module from the drop-down.

• A new module will be created.

⧭ Insert VBA Code:

• Select the module if it isn’t already selected. Enter the following code in it.

Sub valuerangeoffset()
Range("A1").Offset(6, 1).Value = "Laptop"
End Sub

• Save the code.

⧭ Run VBA Code:

• Close the Visual Basic window. Press Alt+F8.
• The Macro dialogue box will open, select valuerangeoffset in the Macro name. Click on Run.

⧭ Output:

• You will get the following output.

Sub valuerangeoffset()

Range("A1").Offset(6, 1).Value = "Laptop"

End Sub

Further Readings

• How to Use INDIRECT Function in Excel (12 Suitable Instances)
• Find text in an Excel range & return cell reference (3 ways)
• Excel Reference Cell in Another Sheet Dynamically