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

Excel OFFSET Function (Quick View)

**Table of Contents**hide

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

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.

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

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

**Excel OFFSET Function: 3 Examples**

**1. Sorting Out a Whole Row from a Data Set**

Look at the data set below.

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

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

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

**Similar Readings**

**How to find text in an Excel range & return cell reference (3 ways)****Excel Reference Cell in Another Sheet Dynamically**

**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)`

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

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

**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)`

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

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

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