Dear Friends,

What is OFFSET () Function?

OFFSET() is a built-in function in excel which returns a range reference which is offset a number of Rows and Columns from a specific Cell or range. Most importantly this is a function which returns Range Address and not the Value.
Did you get it what it is used for? No? Don’t worry it is not as difficult as it seems from the definition :P. It will be more clear once you go through with the explanation and examples given below about this Microsoft Excel function. This is my promise to you 🙂

I get so many questions around two functions of Excel THE MOST. One of them is this OFFSET () and the other one is VLOOKUP (). In fact these two functions used to be mysterious for me as well when I had started exploring about Excel Functions. But I promise you, they are not difficult to understand. I will try to explain them in a very simple manner :).
 
In this article I will explain about OFFSET Function only. VLOOKUP will be explained in further articles.

Syntax of OFFSET() Function in Excel

When you type =OFFSET( in a cell in excel you see following:

As you can see in the above picture following are the arguments which are required for OFFSET Function. All the arguments and their meaning is explained below:

Reference (Mandatory)

This is the cell or range address where your offset applied from.

Row (Mandatory)

Rows is the Number of Rows by what you want to apply Offset. It means by how many rows you want to shift from the Reference in the above parameter mentioned.

Cols (Mandatory)

is the Number of Columns by what you want to apply Offset. It means by how many Columns you want to shift from the reference in the above parameter mentioned.

Height (Optional)

Height is the number for Rows which you want as a Returned Range from this Function. This is an optional parameter. If you omit this then it takes the same number of rows as Reference parameter passed.

Width (Optional)

Width is the number for Columns which you want as a Returned Range from this Function. This is an optional parameter. If you omit this then it takes the same number of Columns as Reference parameter passed.

Example:

Below is the table which I am going to use of all the Examples below:

Display a Single Cell Value using OFFSET()

Suppose from the Above table you want to find the Sales of Aug 2012 (Only Single Cell Value) where the Selected Range is D5:I16. This can be done by two different parameters passed. You can refer the type 1 and Type 2 – both will return the Sales value of Aug 2012.

Type 1. Range as Reference

Reference = D5:I16 (Range as Reference)

Rows = 5 (Because Aug is 5 Row Ahead from the Start of the Reference D5 (March)
Cols = 3 (Because 2012 is 3 columns ahead from the start of Reference D5 (2009)
Height = 1 (We can not leave this blank because in that case it will take the height of the data selected (i.e. D5 to D16 = 12)
Width= 1 (We can not leave this blank because in that case it will take the Width of the data selected (i.e. D5 to I5 = 6)

Hence the below Formula will give you Sales value of Aug 2012

Type 2. Single Cell as Reference

In this case I will pass reference as the First cell of the Above table i.e. D5

Reference = D5 (A single Cell address)
Rows = 5 (Same as Type 1 – No Change)
Cols = 3 (Same as Type 1 – No Change)
Height = You can omit this parameter because in reference address only one Row is there so no need to specify it)
Width= You can omit this parameter because in reference address only one column is there so no need to specify it)

With the parameter formula will will look different but it will refer to the same cell hence it will give you the same result as shown below image

Did you notice any contradictory statement made by me in this article so far? At the start of the Article, as I told that this Function returns a Reference (Address) but in the above example you can see that it is returning a value. Isn’t it? But actually it is not returning a value rather it is returning the reference of the Cell but as you know if you pass reference of a single cell in other cell with an Equal To Sign then the other Cell displays the value of first cell in it. Because of this you are able to see the value here but actually it is returning the reference ONLY. This will be more clear with my next example.

With the above Example Type 1, what will happen if I pass greater than 1 (2,3 etc.) height and width. What are you guessing? It should provide the Subset of the Main table with height and width passed as a parameter. Right? Ok then let’s try this

What is this? It is throwing Value Error!! What’s wrong with my Formula?

As I explained above that OFFSET is a function which returns the reference of the Range or Cell. In Type 1 and 2, it was not throwing any error because only one cell was coming as a result hence it was showing the value of that reference as I have already explained above.
Here in this case OFFSET () is returning more than one cell (Range) hence it is throwing Value Error. This is the reason why OFFSET is being used as an input for other Functions which takes references (Range) as an Input and results the value like SUM(),AVG(),MAX(), MIN() etc.

 
Therefore if you want the Total Sales of May 2010 to July 2012 then you can use the formula with following parameters:

Reference is : D5:I16
Row = 2 (Because May is 2 Row Ahead from the Start of the Reference D5 (March)
Column = 1 (Because 2010 is 1 columns ahead from the start of Reference D5 (2009)
Height = 3 (We need to find the Sum of Sales for May, June and July = 3 Rows)
Width= 3 (We need to find the sum of Sales for 2010, 2011 and 2012 = 3 columns)

How the above formula Worked?

First of all OFFSET formula OFFSET(D5:I16,2,1,3,3) returned the Range as E7:G9. This range reference is passed to SUM () Formula and you know SUM will add all the values in that Range and provide the result. Simple… 🙂
 

Can the parameters be in Negative?

Yes but not all the parameters. Rows and Cols can be in negative. Reference cell is considered at Zeroth position. Hence if you want to offset rows in right side of the reference then it will be positive number and if you want to offset rows in left hand side then it should be in negative number.
Same case with Cols parameter as well. If you want to offset your column downwards from your reference cell then it will be positive. You should be Negative if you want to offset your column upwards from the reference cell.

Do you still have doubt regarding OFFSET FUNCTION? Any help?

Let me know through comment or mail me. Do provide your feedback to improve the content of the articles 🙂
 

Want to Play around and Practice this Formula?

Then what are you waiting for? Download the below sheet, Get a cup of Coffee and keep playing with it and provide your experience with OFFSET function :).