fbpx

OFFSET Function in Excel

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.

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:

Enter Sum formula ahead of Offset Formula

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 Formula

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:Enter Sum formula ahead of Offset Formula

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 MARCHe. 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’:

Average of last 4 rows

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

Add value in the last row

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.