**OFFSET Function in Excel:** Most Useful **Built-in-Functions in Excel**.

It **returns a range** given to the no. of Rows and Columns.

The User gives the Row, Column and Reference to a range and wherever the Row and Columns meet, the **Offset Formula** **in Excel** will give the Output based on that.

**What is an OFFSET Function in Excel?**

The **Range returned by an OFFSET Function **in Excel is a Single Cell. It can even be a Range of Multiple Adjacent Cells.

**OFFSET Function** in Excel works like **VLOOKUP**. It gives you the Output through the Reference.

And when it returns the Value, **OFFSET Formula** specifies the Size which you insert as the number of Rows and Columns.

**Offset Function Excel** is not mainly used to find Independent Values. You can find **Dynamic Range of Values with OFFSET Function**.

When you’re working on **Dashboards**, KPIs and Various Charts, OFFSET Function and OFFSET with Match would be really helpful.

**How does an OFFSET Function Work in Excel?**

**Situation:** In the given example, you’ll be able to see the list of Departments such as **Sales, Marketing**, **Finance **Respectively. It is Followed by the no. of Employees that were Present in each Department in a Particular Month.

No. of Months: January to May

**Problem:** You need to find out:

- The
**SUM**of Last 3 Rows of March with**OFFSET Function** - The
**Average**of last 4 Column of Department ‘Data’ with**OFFSET Function**

### How will you find the Values?

**Solution: OFFSET Formula and ****OFFSET Match **will help you get this in minutes

**Important: Arguments in OFFSET Formula:**

**Reference-**The starting point Supplied as Cell Reference. It refers to a**cell**or a range of**cells**on a worksheet. Use it in a formula so that you can find the values or data that you want the formula to calculate.**rows-**The number of rows you need to calculate after applying OFFSET Function, from the point below the reference**cols-**The number of Columns you need to calculate after applying OFFSET Function, from the point i.e. right to the reference**height-**The required height of the Rows that is to be returned in the Reference**width-**The required width of the Columns that is to be returned in the Reference

**? OFFSET Formula to find the SUM of Last 3 Rows:**

**The Syntax:**

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

Type =OFFSET,

in the reference: **Select Department** i.e. **A1**,

Insert **COUNTA** with OFFSET **before Rows**.

**Select **the **Counta Values** i.e. **D1 to D6 for**

rows: **Type ‘-3’**

cols: **Type ‘3’**

Height: **Type ‘3’**

Width: **Type ‘1’**

The OFFSET Formula will look like **“=OFFSET(A1,COUNTA(D1:D6)-3,3,3,1)”**

NOTE1:The Column/Row Count for OFFSET Formula starts with 0.

NOTE2:Counta with OFFSET Function will Count the Value that we will add in the Non-Blank Cells.

In our Example Branding and May are the Respective Row and Column that we have left Blank.

We have selected **Row Value as 3** as we want the **SUM of 3 Row Values
**We have selected

**Column Value as 3**as the

**Count from Department to March is 0-3**

**This will give you a #VALUE! ERROR**

**Notice: **Now, this is the mistake which everyone makes.

WRONG“=OFFSET(A1,COUNTA(D1:D6)-3,3,3,1)”

**“You need to insert a Formula for SUM before OFFSET”.**

- Insert the SUM Formula before Offset Formula “=SUM(OFFSET(A1,COUNTA(D1:D6)-3,3,3,1))”

Now the OFFSET Formula in Excel is going to Look like:

RIGHT: “=SUM(OFFSET(A1,COUNTA(D1:D6)-3,3,3,1))”

This will give you the Value 115.

(115 is the SUM of Last 3 Rows of March)

**Add in 50**in the**last Column of MARCH**e. the Employees for**Branding**in the**Month of March**.

The **SUM instantly changes to 125**

As now, **The OFFSET Function** returns the Changed SUM value which is (40+35+50)

Isn’t it Magical?

*This is the benefit of using Counta Function with Offset Function in Excel.*

Let us check, how to find:

**? OFFSET Function to find The Average of last 4 Columns of the Department ‘DATA’:**

**The Syntax:**

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

- Type =AVERAGE,
**Select OFFSET**inside the Bracket,

in the reference: **Select Department** i.e. **A1**,

For rows: **Type ‘4’**. Insert the **COUNTA** with OFFSET **before Columns**)

**Select **the **Counta Values** i.e. **A5 to F5**. **Type ‘-4’** (as we are counting 4 Columns)

For Height: **Type ‘1’**

For Width: **Type ‘4’**

**NOTE1:** *The Column/Row Count for OFFSET Function starts with 0.
*

**NOTE2:**

*Counta with OFFSET will Count the Value that we will add in the Non-Blank Cells.*

We have selected **Row Value as 4** as the **Count from Department to April is 0-4**

We have selected **Column Value as 4** as we need the Average of 4 Columns with Offset Function

Now the OFFSET Formula in Excel is going to Look like:

=AVERAGE(OFFSET(A1,4,COUNTA(A5:F5)-4,1,4))

This will give you the Value 30.5

(30.5 is the Average of last 4 Columns of DATA)

**Add in 40**in the**last Row of DATA**e. the Employees for**DATA**in the**Month of May**.

The **SUM instantly changes to 35.5**

As now, The OFFSET Function returns the Changed Average value which is (30+35+37+40) i.e. sum function

*The value changes instantly as we have used the Counta Function with Offset Function in Excel.*

**Use of OFFSET function in Excel**

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

You can see there are five possible arguments, two of them optional. You can see there are five possible arguments, two of them optional.

**reference** – This required argument is the cell or range of adjacent cells we wish to offset our result from. If not a single cell, this argument must refer to an adjacent range of cells or it will return the #VALUE error.

**rows** – This required argument tells Excel the number of rows to move up or down from the ‘reference’ argument value. Positive numbers are down from the reference. Negative numbers are upward from the reference. OFFSET will return the #REF error if this argument is greater than the number of rows available.

**cols** – This argument, also required, tells Excel the number of rows to move to the left or right from the reference argument value. Positive numbers are to the right while negative numbers are to the left of the reference value. OFFSET will return the #REF error if this argument is greater than the number of columns available.

**[height]** – This is an optional argument that allows you to specify the size of the returned range in a number of rows. If omitted, the formula returns the default size of a single row.

**[width]** – This is also an optional argument. It allows you to specify the size of the returned range in a number of columns. If omitted, the formula returns the default size of a single column.