How to Use OFFSET Function in Excel (3 Examples)

Overview of the OFFSET Function

Today I will be showing how you can extract out a particular section from a data set using the OFFSET function of Excel.

Read More: Offset(…) Function in Excel with Examples


Excel OFFSET Function (Quick View)

Overview of the OFFSET Function


Download Practice Workbook


Excel OFFSET Function (Syntax & Arguments)


Summary

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

Syntax of OFFSET Function

The Syntax of the OFFSET function is:

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

Arguments

Argument Required or Optional Value
reference Required The cell reference from where it starts off moving.
rows Required The number of rows it moves downward
cols Required The number of columns it moves rightward
[height] Optional The number of rows of the section of the data that it extracts. Default is 1.
[width] Optional The number of columns of the section of the data that it extracts. 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 number of height downward and a specified number of width rightward.

For example, the formula OFFSET(D8,-3,1,2,2) starts moving from the cell D8, 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.

OFFSET with Negative Rows Argument

  • 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 number of height downward and a specified number of width rightward.

For example, the formula OFFSET(F5,3,-3,2,2) starts moving from cell F5, 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.

OFFSET with Negative Cols Argument

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

OFFSET with Fraction Argument


Excel OFFSET Function: 3 Examples


1. Sorting Out a Whole Row from a Data Set

Look at the data set below.

A Data Set in Excel

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 out the sales record of Television in 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:

=OFFSET(B4,7,1,1,5)

OFFSET Function for Single Row

See, we have got the sales record of Television of all the years.

Explanation of the Formula

  • The OFFSET function starts moving from cell B4.
  • 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.
Formula Output Explanation
=OFFSET(B4,7,1,1,5) [489 499 503 772 1212] Starts from B4, moves 7 rows down, 1 column right, and extracts out a section of 1 row height and 5 columns width.

2. Sorting Out a Whole Column from a Data Set

Now we will sort out a whole column from the same set of data.

Let’s try to find out all the sales in the year 2018.

Here, 2018 is the 3rd year among the years.

And we will extract a list of a total of 13 products.

So our formula will be:

=OFFSET(B4,1,3,13,1)

OFFSET Function for Single Column

See, we have differentiated all the sales in the year, 2018.

Explanation of the Formula

  • It again starts moving from cell B4.
  • 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 the year 2018).
Formula Output Explanation
=OFFSET(B4,1,3,13,1) 102

665

1553

89

456

532

503

1567

1047

88

39

629

1129

Starts from cell B4, moves 1 row down, then to 3 columns right, and then collects data of 13 rows height and 1 column width.

3. Sorting Out Adjacent Multiple Rows and Multiple Columns from a Data Set

Now we will collect a section of multiple rows and multiple columns from the data set.

Let’s try to collect the sales of the products telephone, refrigerator, and television in the years 2017, 2018, and 2019.

You see, the telephone is the 5th product on the list.

And 2017 is the 2nd year.

The collected section will have 3 rows (Telephone, Refrigerator and Television).

And 3 columns (2017, 2018 and 2019).

So the formula will be:

=OFFSET(B4,5,2,3,3)

OFFSET Function for Multiple Columns and Multiple Rows

See, we have collected the sales record of Telephone, Refrigerator and Television from the years 2017, 2018, and 2019.

Explanation of the Formula

  • It again starts moving from cell B4.
  • 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 height (Telephone, Refrigerator and Television) and 3 columns width (2017, 2018 and 2019)
Formula Output Explanation
=OFFSET(B4,5,2,3,3) 387    456    693

561     532   476

499     503    772

Starts moving from cell B4, then moves 5 rows down, then 2 columns right, then collects a section of 3 rows height and 3 columns width.

Common Errors with OFFSET Function

Error When They Show
#VALUE! Shows when any argument is of the wrong data type. For example, the row argument needs to be a number. It is a text, then it will show #VALUE!.

Conclusion

Thus you can use the OFFSET function of Excel to collect any range of data from any data set. Do you have any questions? Feel free to inform us.


Further Readings

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