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.

**Table of Contents**hide

## 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, then moves*D9***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, then moves*F6***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 fromand then*B4***3**columns right. - And then collected a section of
**2**rows high and**2**columns wide.

## How to Use OFFSET Function in Excel: 3 Suitable Examples

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

**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: ****Advanced Excel Lookup Functions**

**Similar Readings**

**How to Use VLOOKUP and HLOOKUP Combined Formula in Excel****DGET vs VLOOKUP in Excel****How to Use MATCH Function in Excel****How to Use LOOKUP Function in Excel**

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

**FORMULA BREAKDOWN**

- It again starts moving from cell
.*B5* - Moves 1 row down to the first product laptop.
- Then move
**3**columns right to the year 2018. - Then extract 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 HLOOKUP Function in Excel**

**Similar Readings**

**How to Use VLOOKUP Function in Excel****How to Use XLOOKUP Function in Excel****How to Use FILTER Function in Excel****How to Use SORT Function in Excel**

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

**FORMULA BREAKDOWN**

- It again starts moving from cell
.*B5* - Moves to
**5**rows down to the product telephone. - Then move
**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 COLUMNS Function in Excel**

**Similar Readings**

**How to Use Excel HYPERLINK Function****How to Use Excel FORMULATEXT Function****How to Use Excel UNIQUE Function****How to Use COLUMN Function in Excel**

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

**Download Practice Workbook**

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

## 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.Â If you have any questions, comments, or recommendations, kindly leave them in the comment section below.