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.

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

Premium Project Management Templates

120+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

PREMIUM TEMPLATES
LIMITED TIME OFFER
ON SALE80% OFF
BROWSE ALL TEMPLATES

Advanced Project Planning Templates

Excel Templates

VIEW DETAILS

120+ Project Management Templates Pack

Excel | PowerPoint | Word

VIEW DETAILS

ULTIMATE RESOURCE MANAGEMENT TEMPLATE

Excel Template

VIEW DETAILS

50+ Essential Project Management Templates

Excel | PowerPoint | Word

VIEW DETAILS

Project Portfolio Management Templates

Excel | PowerPoint Templates

VIEW DETAILS

50+ Excel Project Management Templates

Excel Templates

VIEW DETAILS

By Published On: April 23rd, 2013Categories: Excel FormulasTags:

Share This Story, Choose Your Platform!

About the Author: Valli

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.