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:
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.
“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))”
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’:
- 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:
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 downward 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 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 number of columns. If omitted, the formula returns the default size of a single column.