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.

Related Tutorials

Delete Duplicate in Excel or Remove Duplicate in Excel
November 9, 2018
Excel Formulas PDF
September 6, 2018
How To Lock Cells in Excel | Unprotect Excel
August 13, 2018
4x Faster at Excel
August 6, 2018
Separate Content of One Excel Cells into Separate Columns
August 3, 2018
How to Transpose Excel Columns to Rows | Paste Special Method
July 26, 2018
How to create sparklines in Excel
July 19, 2018
AutoSum in Excel with Shortcut
July 17, 2018
Strikethrough Shortcut in Excel & Word
July 4, 2018
INDIRECT Function with SUM, MAX, MIN & Independent Cell Value
June 29, 2018
Pivot Table Slicers In Excel
June 12, 2018
How to Split Cells in Excel using Text to Column
June 7, 2018
How to Wrap Text in Excel Automatically and Manually
June 6, 2018
How to Hide/Unhide Column in Excel
June 5, 2018
Highlight row based on cell value
June 4, 2018
Learn how to remove blank cells in Excel
June 3, 2018
How to Group Numbers, Dates & Text in Pivot table in Excel
June 1, 2018
5 Powerful Tricks to Format cells in Excel
May 31, 2018
Insert a Picture into a Cell in Excel
May 25, 2018
What is ISFORMULA Function and FORMULATEXT Function
May 21, 2018
How to Use SUBSTITUTE Function
May 21, 2018
Excel Quartile Function in Excel
May 8, 2018
How to use the Excel PERCENTILE function
May 7, 2018
Insert or Type degree symbol in Excel with Autocorrect Feature
May 7, 2018
Insert Symbol Dialog Box to Insert or Type degree symbol in Excel
May 7, 2018
25% Discount
No prize
All Courses at $200
Almost!
10% Discount
Free Ebook
No Prize
No luck today
Almost!
50% Discount
No prize
80% Discount
Get a chance to boost your knowledge!
Use this coupon code for any course that you wish for. 
Our in-house rules:
  • You can choose any course & redeem coupon
  • If you find any difficulty, mail us on [email protected]
  • Wheel Spin will end soon
  • Coupon code can be applied within 2 days.