OFFSET Function in Excel – Formula Explained with Examples

Home/Excel Formulas/OFFSET Function in Excel – Formula Explained with Examples

OFFSET Function: Explained with Examples

OFFSET formula is very useful to travel in the Worksheet and fetch a specific range from particular Cell or Range. You can use OFFSET function to lookup a value to the left side or above a range which is not possible with VLOOKUP or HLOOKUP Functions in Excel.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


What is the use of OFFSET function?

OFFSET Function in Excel returns a reference to a range of cells that is a specified number of rows and columns from an initial supplied range

What is the syntax of OFFSET function?

OFFSET Function in Excel- Syntax

OFFSET( reference, rows, cols,

[height], [width] )

reference : reference cell/range that is to be offset (can be either a single cell or multiple cells)
rows : number of rows from the start ing position (upper-left) of the supplied reference range (positive integers travels down, negetive integers travels up)
cols : number of column from the starting position (upper-left) of the supplied reference range (positive integers travels right from the refernce, negetive integers travels left from the reference
[height]: height of the returned range, if you omit this it will retrn the same height of the given range
[width]: width of the returned range, if you omit this it will retrn the same width of the given range

OFFSET Function in Excel – Examples

OFFSET Function in Excel - Example 1

Example 1: OFFSET function returns 22 as output:
Initial supplied range is A15, it travels 1 row (down), 1 column (right). And selects one cell as the initial supplied range height is one cell. And =OFFSET(A15,1,1) formula returns 22 as output.

OFFSET - Example 2

Example 2: OFFSET function returns 200 as output:

Initial supplied range is B29, from here it travels -2 row (up), 1 column (right). And selects one cell as the initial supplied range height is one cell. And =OFFSET(B29, -2,1) formula returns 200 as output.

OFFSET - Example 3

Example 3: OFFSET function returns 12560 as output:
OFFSET: Initial supplied range is A40, from here it travels 2 row (down), 2 column (right). And it select the 4 rows and 1 column.

SUM: It consider the range returned from OFFSET function (i.e; C42:C45) and returns its SUM value 12560 as output.

Reference:

Please refer the below article for more Lookup & Reference Excel functions.
Lookup & Reference Excel Formulas

Please refer the below article for more Excel Functions.
Excel Formulas | Home



Related Resource External VBA Reference
Excel VBA Reference Project Management Reference
VBA Reference:
Excel Reference:
By |April 23rd, 2013|Excel Formulas|3 Comments

About the Author:

Excel VBA Developer having around 8 years of experience in using Excel and VBA for automating the daily tasks, reports generation and dashboards preparation. Valli is sharing to helps us automating daily tasks.

3 Comments

  1. Latha August 14, 2015 at 5:24 PM - Reply

    how i should use with header content… by finding the such word like dept

  2. Pravin September 13, 2016 at 6:49 AM - Reply

    Very easy for understanding, and helpful for learning

  3. James Conner June 1, 2017 at 11:38 AM - Reply

    Its very interesting article, thanks we liked it.

Leave A Comment


Related pages


technical interview questions for freshers with answersactivate vbasave excel vbalookup function excel 2010insert row excel shortcuthow to copy cells in excel with formulastart vba in exceldashboard ppt templateexcel macro activate workbookvba excel for loopprotecting a worksheet in excelvba code to open excel file from folderwhat is vlookup formulahow to use a lookup function in excelunprotect excel file 2010vba msgbox syntaxhow to label columns in excelinsert macro excelexcel macro protect sheet with passwordvba odbc connectionshortcut key for next sheet in excelunlock excel 2007 passwordvb6 ado examplehow do you do a checkmark in excelvba syntax checkerobjfso.opentextfilepivoting tables in excelactivate developer tab in exceliferror with vlookupexcel macro developeranalyst programmer interview questionsmaximum number of sheets in excel 2007vlookup returns naconditional statement in vbscriptvba excel macrovb excel tutorialselect statement vbaadd developer tab to ribbon word 2007excel 2007 hide ribbonexcel chart creationaverage function in excel 2013how to alphabetize columns in excelexcel dashboards and reports for dummiesvlookup col_index_numexcel vba rowvlookup multiple matchesmicrosoft excel if statementsvba close userformpaste values vbahow to unhide columns in excel 2007vba close fileremove duplicates from excelconvert from excel to xmlexcel delete blank rowmicrosoft excel countif functionvba scrollbarvba fillexcel 2007 delete blank rowsinserting pivot table in excel 2010how to add developer tab in powerpoint 2010inserting in excelvba datediffsorting sqlworkbook worksheetsinsert several rows in excelestimating spreadsheet templatenested if function excel 2010excel how to use sumifconvert xls to xml excel 2007conditional statements in excelvba conditionswhat are dml statementsfilter using vbaloops in excel vbanested if statement excel 2010excel sendmailteradata macro example