How to Use OFFSET Function in Excel (3 Suitable Examples)

Microsoft Excel provides various functions to maximize and speed up your productivity. Today I will be showing how you can extract a particular section from a data set using the OFFSET function of Excel.


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice it by yourself.


Introduction to Excel OFFSET Function

Objective

  • It starts off from a particular cell reference, moves to a specific number of rows down, then to a specific number of columns right, and then extracts out a section from the data set having a specific height and width.
  • It is an Array Function. So you have to press Ctrl + Shift + Enter to insert this function unless you are in Office 365.

Syntax

=OFFSET(reference,rows,cols,[height],[width])

Arguments

Arguments Required or Optional Value
reference Required The cell reference from where it starts off moving.
rows Required The number of rows moves downward.
cols Required The number of columns moves rightward.
[height] Optional The number of rows of the section of the data that it extracts. The default is 1.
[width] Optional  The number of columns of the section of the data that it extracts. The default is 1.

Return Value

  • It returns a section from a data set with a specific height and a specific width, situated at a specific number of rows down and a specific number of columns right from a given cell reference.
Notes
  • If the rows argument is a negative number, the function will move the specified number of rows upward from the reference cell instead of moving downward.
  • But after reaching the destination, it will always collect a section of the specified height downward and a specified width rightward.
  • For example, the formula OFFSET(D9,-3,1,2,2) starts moving from the cell D9, then moves 3 rows upward, then moves 1 column rightward.
  • But after reaching the destination cell, it collects a section of 2 rows height from downward and then 2 columns width from rightward.

  • If the cols argument is a negative number, the function will move the specified number of columns left from the reference cell instead of moving right.
  • But after reaching the destination, it will always collect a section of the specified height downward and a specified width rightward.
  • For example, the formula OFFSET(F6,3,-3,2,2) starts moving from cell F6, then moves 3 rows downward, then moves 3 columns leftward.
  • But after reaching the destination cell, it collects a section of 2 rows height from downward and then 2 columns width from rightward.

  • If any of the four arguments rows, cols, [height], or [width] is a fraction, Excel will automatically convert it to an integer.
  • For example, in the formula OFFSET(B4,3.7,3,2,2), the row argument is a fraction, 7. Excel has converted it to 3, and then moved 3 rows down from B4 and then 3 columns right.
  • And then collected a section of 2 rows high and 2 columns wide.


3 Suitable Examples to Use OFFSET Function Excel

This article will demonstrate to you how to use the OFFSET function. Here, we will sort out a whole column of a data set, a whole row of a data set, and adjacent multiple rows and multiple columns of a data set.


Example 1: Applying Excel OFFSET Function for Sorting Out a Whole Row

In this section, we will demonstrate how to extract all the values for a whole row by using the OFFSET function. So, to know the method, you can follow the below steps accordingly.

Steps:

  • We have a sales record of 5 years of 13 products of a company named Mars Group.
  • Now we will try to sort out a whole row using the OFFSET function.
  • Let’s try to extract the sales record of Television for all the years.
  • See, television is the 7th product on the product list.
  • And we have a data section to collect that spans over 5 years ( 5 columns).
  • So, our formula will be shown below.
=OFFSET(B5,7,1,1,5)
  • Then, hit CTRL+SHIFT+ENTER.

Applying Excel OFFSET Function for Sorting Out a Whole Row

FORMULA BREAKDOWN
  • The OFFSET function starts moving from cell B5.
  • Then it moves 7 rows downward to find the television.
  • And then it moves 1 column rightward to land in the first year, 2016.
  • Then it extracts out a section of 1-row height and 5 columns width. This is the sales record of television from 2016 to 2020.
  • Finally, you will see that we have got the sales record of Television of all the years.

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


Example 2: Sorting Out a Whole Column by Using OFFSET Function in Excel

In this section, we will demonstrate how to extract all the values for a whole column by applying the OFFSET function. So, to know the method,  you can follow the below steps accordingly.

Steps:

  • Firstly, we will sort out a whole column from the same set of data.
  • After that, let’s try to find out all the sales in the year 2018.
  • Here, 2018 is the 3rd year in the year.
  • And we will extract a list of a total of 13
  • So, write down the following formula here.
=OFFSET(B5,1,3,13,1)
  • Then, press CTRL+SHIFT+ENTER.

Sorting Out a Whole Column by Using OFFSET Function in Excel

FORMULA BREAKDOWN
  • It again starts moving from cell B5.
  • Moves 1 row down to the first product laptop.
  • Then moves 3 columns right to the year 2018.
  • Then extracts a section of 13 rows height (all the products) and 1 column width (only of
  • Lastly, you will see that we have differentiated all the sales in the year, 2018.

Read More: How to Use COLUMN Function in Excel (4 Easy Examples)


Example 3: Using OFFSET Function to Sort Out Adjacent Multiple Rows and Multiple Columns

In this section, we will demonstrate how to extract all the values for adjacent multiple rows and multiple columns by utilizing the OFFSET function. So, to know the method,  you can follow the below steps accordingly.

Steps:

  • Firstly, we will collect a section of multiple rows and multiple columns from the data set.
  • Then, let’s try to collect the sales of the products telephone, refrigerator, and television in the years 2017, 2018, and 2019.
  • After that, you see, the telephone is the 5th product on the list, and 2017 is the 2nd year.
  • Here, the collected section will have 3 rows (Telephone, Refrigerator, and Television) and 3 columns (2017, 2018, and 2019).
  • So, write down the following formula here.
=OFFSET(B4,5,2,3,3)
  • Then, press CTRL+SHIFT+ENTER.

Using Excel OFFSET Function to Sort Out Adjacent Multiple Rows and Multiple Columns

FORMULA BREAKDOWN
  • It again starts moving from cell B5.
  • Moves to 5 rows down to the product telephone.
  • Then moves 2 columns rightward to the year 2017.
  • Then collects the data of 3 rows in height (Telephone, Refrigerator, and Television) and 3 columns in width (2017, 2018, and 2019).
  • See, we have collected the sales record of Telephone, Refrigerators, and Televisions from the years 2017, 2018, and 2019.

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


Common Errors with the OFFSET Function
  • #VALUE shows when any argument is of the wrong data type. For example, the row argument needs to be a number. If it is a text, then it will show #VALUE

Conclusion

In this article, we’ve covered 3 Suitable examples of how to use the OFFSET function in Excel. We sincerely hope you enjoyed and learned a lot from this article. Additionally, if you want to read more articles on Excel, you may visit our website, Exceldemy. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.


Further Readings

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo