\nDid 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 \ud83d\ude42<\/p>\n
I get so many questions around two functions of Excel THE MOST<\/strong>. One of them is this OFFSET ()<\/strong> and the other one is VLOOKUP ()<\/strong>. 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 :). When you type =OFFSET(<\/strong> in a cell in excel you see following: This is the cell or range address where your offset applied from.<\/p>\n Rows<\/strong> 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.<\/p>\n 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.<\/p>\n 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.<\/p>\n 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.<\/p>\n Below is the table which I am going to use of all the Examples below: 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<\/strong>. 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.<\/p>\n Rows<\/strong> = 5 (Because Aug is 5 Row Ahead from the Start of the Reference D5 (March) In this case I will pass reference as the First cell of the Above table i.e. D5<\/strong> 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 \nDid 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<\/strong> 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.<\/p>\n<\/blockquote>\n 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 As I explained above that OFFSET<\/strong> 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. Reference is : D5:I16<\/strong>
\n
\nIn this article I will explain about OFFSET Function only. VLOOKUP will be explained in further articles.<\/p>\nSyntax of OFFSET() Function in Excel<\/h3>\n
\n![\"OFFSET](\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2014\/05\/OFFSET-Synatx.png\" "\\"OFFSET")
\nAs you can see in the above picture following are the arguments which are required for OFFSET<\/strong> Function. All the arguments and their meaning is explained below:<\/p>\nReference (Mandatory)<\/h3>\n
Row (Mandatory)<\/h3>\n
Cols (Mandatory)<\/h3>\n
Height (Optional)<\/h3>\n
Width (Optional)<\/h3>\n
Example:<\/h3>\n
\n![\"OFFSET](\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2014\/05\/Data-Table1.png\" "\\"OFFSET")
<\/p>\nDisplay a Single Cell Value using OFFSET()<\/h2>\n
Type 1. Range as Reference<\/h2>\n
\nReference<\/strong> = D5:I16 (Range as Reference)<\/strong><\/p>\n
\nCols<\/strong> = 3 (Because 2012 is 3 columns ahead from the start of Reference D5 (2009)
\nHeight<\/strong> = 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)
\nWidth<\/strong>= 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)
\n<\/i>
\nHence the below Formula will give you Sales value of Aug 2012
\n![\"OFFSET](\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2014\/05\/Formula-1.png\" "\\"OFFSET")
<\/p>\nType 2. Single Cell as Reference<\/h2>\n
\n
\nReference<\/strong> = D5 (A single Cell address)<\/strong>
\nRows<\/strong> = 5 (Same as Type 1 – No Change)
\nCols<\/strong> = 3 (Same as Type 1 – No Change)
\nHeight<\/strong> = You can omit this parameter because in reference address only one Row is there so no need to specify it)
\nWidth<\/strong>= You can omit this parameter because in reference address only one column is there so no need to specify it)
\n<\/i><\/p>\n
\n![\"OFFSET](\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2014\/05\/Formula-2.png\" "\\"OFFSET")
<\/p>\n
\n![\"OFFSET](\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2014\/05\/OFFSET-Example-3.png\" "\\"OFFSET")
<\/p>\nWhat is this? It is throwing Value Error!! What’s wrong with my Formula?<\/h3>\n
\nHere in this case OFFSET ()<\/strong> is returning more than one cell (Range) hence it is throwing Value Error. This is the reason why OFFSET<\/strong> 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()<\/strong> etc.<\/p>\n
\nTherefore if you want the Total Sales of May 2010 to July 2012<\/strong> then you can use the formula with following parameters:<\/p>\n
\nRow = 2 (Because May is 2 Row Ahead from the Start of the Reference D5 (March)
\nColumn = 1 (Because 2010 is 1 columns ahead from the start of Reference D5 (2009)
\nHeight = 3 (We need to find the Sum of Sales for May, June and July = 3 Rows)
\nWidth= 3 (We need to find the sum of Sales for 2010, 2011 and 2012 = 3 columns)<\/p>\n![\"OFFSET](\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2014\/05\/OFFSET-Example-4.png\" "\\"OFFSET")
<\/p>\nHow the above formula Worked?<\/h2>\n
![\"OFFSET](\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2012\/06\/download_page_button.jpg\" "\\"OFFSET")
<\/a><\/p>\n