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.
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.
- 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.
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.
- 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: Use of Offset Function in Excel [Offset – Match Combo, Dynamic Range]
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.
- 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.
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.
- 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.
- #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.
<< Go Back to Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
