Excel OFFSET Function – Excel Tutorial

.

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:
OFFSET Function Syntax
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:
OFFSET FORMULA - Data-Table

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
OFFSET Formula - 1

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
OFFSET Formula - 2

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
OFFSET - Formula - Example - 3

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)

OFFSET - Formula - Example-4

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 :).

OFFSET Function - Example

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

3 Comments

  1. Kumarapush

    Hey Vishwa,
    Nice and Apt Article. Starting with an Intro, then Syntax explanation plus some examples to understand the formula with exception cases.

    By the way, I got this doubt. Is there any way that I can find the address reference returned by this formula?

    For example. OFFSET (D5,5,3) returns reference to F10. Is there any way that I can get the result as “F10”?. Because if I put “=OFFSET(D5,5,3)” it will return only 27. !!!

    Anyways, thanks for this nice explanation. Of course, I learned something new today with a Coffee. :-).

    Reply
    • Vishwamitra Mishra

      Dear Kumarapush,

      Thanks for such a lovely words. I am glad that i was able to explain you in a simpler way.
      And ofcourse you left me to investigate to find the solution for your question. With OFFSET alone it is not possible to return the reference in a cell but there could be a way to get it and display this. I will let you know, if I find any solution for this 🙂

      Reply
  2. Abhijit Vipat

    Dear Vishwa,

    I am impressed with the work you have started on excel learning. I am new to Excel Macros and wants to learn Macro from basics. Can you suggest me any book for it or will you able to help me. Your blog is superb

    Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest