**How to use OFFSET function in excel?**

OFFSET function returns a reference to a range or cell, when we give offset a number of rows and columns from another range or cell. Number of row and column can be negative and positive.

OFFSET very useful function in Excel. We use offset function for creating a rectangular range in Excel.

**Inbuilt Function**

OFFSET function is an inbuilt function in Microsoft Excel which is categorized as **LOOKUP & REFRENCES **Function.

**Syntax ****– **There are five arguments in OFFSET Function

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

**(A). Reference** – This is starting point (**Range or Cells**), where we apply offset. This is mandatory arguments for OFFSET.

**(B). Rows** – The number of Rows applied as offset to the range or cell. We can give number of rows as negative and positive. This is mandatory arguments for OFFSET.

**(C). Cols** – The number of columns applied as offset to the range or cell. We can give numbers of column as negative and positive. This is mandatory arguments for OFFSET.

**(D). Height** – Height is the number of rows. This is optional arguments.

**(E). Width** – Width is the number of columns. This is optional arguments.

** **

**How to use OFFSET function in excel?**

**OFFSET Examples**

**Example – 1**

In this example, How to change direction of Range value from OFFSET.

We use this formula =OFFSET($A$1,COUNTA(A:A)-ROW(A1),0) for solve this question

**(A). Reference** – $A$1 is Reference

**(B). Rows** – We find Row offset from COUNTA(A:A)-ROW(A1).

**(C). Cols** – Column offset is 0.

After that OFFSET Function reverse the value direction in cells.

**How to use OFFSET function in excel?**

**Example – 2**

In this example, How to find each value from OFFSET.

We use this formula =OFFSET($A$1,MATCH(D2,$A$2:$A$16,0),1) for solve this question

**(A). Reference** – $A$1 is Reference

**(B). Rows** – We find Row offset from MATCH(D2,$A$2:$A$16,0). MATCH function returns the row number of each lookup value.

**(C). Cols** – Column offset is 1.

** **

**How to use OFFSET function in excel?**

**Example – 3**

In this example, We find average of all last 3 month from OFFSET.

We use AVERAGE, COUNTA and OFFFSET function and create this formula =AVERAGE(OFFSET(A2,0,COUNTA(B2:M2)-2,1,3)) for solve this example.

**(A). Reference** – $A2 is Reference.

**(B). Rows** – Number of Row offset is 0.

**(C). Cols** – We find Column offset from COUNTA(B2:M2)-2.

**(D). Height** – Height is 1 because we have evaluate average for only one row.

**(D). Width** – Width is 3 because we have to evaluate average for last three month.

After that AVERAGE function Returns average of number which is evaluated by OFFSET Function.

**How to use OFFSET function in excel?**

**Example – 4**

In this example, We have to add Sum of all last 3 month from OFFSET.

We use SUM, COUNTA and OFFFSET function and create this formula =SUM(OFFSET(A2,0,COUNTA(B2:M2)-2,1,3)) for solve this example.

**(A). Reference** – $A2 is Reference.

**(B). Rows** – Number of Row offset is 0.

**(C). Cols** – We find Column offset from COUNTA(B2:M2)-2.

**(D). Height** – Height is 1 because we have to add number for only one row.

**(D). Width** – Width is 3 because we have to add number for last three month.

After that SUM function add number which is evaluated by OFFSET Function.

**Download Workbook**

### How to use Vlookup in excel with example?