Using “OFFSET” in Excel

Hello All. Offset is a cool, built in function of Excel that is used to return a specific value or a range that is a specified number of rows and columns from a reference cell. 

Might sound confusing – but let’s look at few examples which will help us to understand. First, the syntax:
=Offset(Reference, Rows, Columns, [height], [width])
Please note: Anything within [] in a syntax is optional.
Reference: This can either be a cell or a range we wish to offset our results from
Rows: This value would tell excel about the number the number of rows to either move up to down from the “reference” value. Positive number in this place denotes moving down and negative numbers denotes moving up
Columns: This value would tell excel about the number of columns to either move right or left from the “reference” value. Positive number in this place denotes moving right and negative numbers denotes moving left. 
Height: This optional value would tell the number of rows the result should return
Width: This optional value would tell the number of columns the result should return. 
Let’s look at some examples:
A simple data set with monthly sales volume of the cars
Let’s look at a simple example of selecting A2 as our reference value and then 1 for our row and 1 for column:
=offset(A2,1,1)
The result should be B3 as excel would go 1 place down to the reference value and then 1 place right (Rows and columns respectively)
                      
Now, what if you give a reference and subsequent arguments, where there is no value, the result would be “0”. Example given below:
Here, reference is A2, and number of places to come down (row) is 13 and number of places to go right is 3 (column) which has blank value and hence it returns “0”.
Now, let’s look at playing with returning range of values:
For example, we need to return all the 4 car’s value for a particular month, the formula would be:
=offset(A1,1,2,1,4)
Now, let’s add more functions within Offset. For example, let’s get average of sales in a particular month across all models. Here we would return all 4 values and then average them out.
Now, let’s add up all the sales for the year for a particular model. Let’s take swift.
Here, excel moves 1 row from A1, 1 column then (for Swift), then returns 12 rows (height) and 1 column (the same column for width). The sum will add the values that total of B2:B13
Let’s look at more advanced use of Offset function. Let’s create something more dynamic – let’s return all 4 values based on the input provided in a drop down. Let’s create a drop down, do a match function and finally use Offset to return the value.
Let’s create a drop down by a simple data validation list for month values. Under Data tab -> Choose Data validation -> choose List under “Allow” -> Select the data range A2:A13
Next let’s match the date in the drop down with the actual column A and return the absolute value. Formula would be 
=MATCH(G2,Table2[[#All],[Month]],0)
Here, G2 is where we have created the drop down. 
Please note that the above formula gives the value as 4 which is the row number (including the heading row). But March month is actually 3rd. So we need to decrement by 1 when we incorporate this in our Offset function. 
Next, let’s use the offset. Same syntax / logic. We shall reference A1. For the number of places to go down, we shall use the match function. Column will be 1 as we need to move 1 place from where we would pick values. The height will be one as we need to return just one row and the width will be 4 as we need all 4 values.
Offset function may not be a straight forward one but once you realize when to use this, this becomes one of the powerful inbuilt tool available.
I hope you liked this tutorial. Please provide us your valuable feedback which will help to improve the content. Thanks for reading. 

One thought on “Using “OFFSET” in Excel

Leave a Reply

%d bloggers like this: