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

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

For cols:**Type ‘3’**

For Height:**Type ‘3’**

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

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