offset
Summary
The Excel OFFSET function returns a reference to a range constructed with five inputs: (1) a starting point, (2) a row offset, (3) a column offset, (4) a height in rows, (5) a width in columns. OFFSET formula in excel is handy in formulas that require a dynamic range.
Purpose
Create a reference offset from given starting point
Return value
A cell reference.
Syntax
=OFFSET (reference, rows, cols, [height], [width])
Arguments
- reference - The starting point, supplied as a cell reference or range.
- rows - The number of rows to offset below the starting reference.
- cols - The number of columns to offset to the right of the starting reference.
- height - [optional] The height in rows of the returned reference.
- width - [optional] The width in columns of the returned reference.
Usage notes
The Excel OFFSET function returns a dynamic range constructed with five inputs: (1) a starting point, (2) a row offset, (3) a column offset, (4) a height in rows, (5) a width in columns.
The starting point (the reference argument) can be one cell or a range of cells. The rows and cols arguments are the number of cells to "offset" from the starting point. The height and width arguments are optional and determine the size of the range that is created. When height and width are omitted, they default to the height and width of reference.
For example, to reference C5 starting at A1, reference is A1, rows is 4 and cols is 2:
=OFFSET(A1,4,2) // returns reference to C5
To reference C1:C5 from A1, reference is A1, rows is 0, cols is 2, height is 5, and width is 1:
=OFFSET(A1,0,2,5,1) // returns reference to C1:C5
Note: width could be omitted, since it will default to 1.
It is common to see OFFSET function excel wrapped in another function that expects a range. For example, to SUM C1:C5, beginning at A1:
=SUM(OFFSET(A1,0,2,5,1)) // SUM C1:C5
The main purpose of OFFSET formula in excel is to allow formulas to dynamically adjust to available data or to user input. The OFFSET function can be used to build a dynamic named range for charts or pivot tables, to ensure that source data is always up to date.
Note: Excel documentation states height and width can't be negative, but negative values appear to have worked fine since the early 1990's. The excel OFFSET function in Google Sheets won't allow a negative value for height or width arguments.
Examples
The examples below show how OFFSET function excel can be configured to return different kinds of ranges. These screens were taken with Excel 365, so OFFSET returns a dynamic array when the result is more than one cell. In older versions of Excel, you can use the F9 key to check results returned from OFFSET.
Example #1
In the screen below, we use OFFSET function excel to return the third value (March) in the second column (West). The formula in H4 is:
=OFFSET(B3,3,2) // returns D6
Example #2
In the screen below, we use OFFSET excel function to return the last value (June) in the third column (North). The formula in H4 is:
=OFFSET(B3,6,3) // returns E9
Example #3
Below, we use OFFSET excel function to return all values in the third column (North). The formula in H4 is:
=OFFSET(B3,1,3,6) // returns E4:E9
Example #4
Below, we use OFFSET excel function to return all values for May (fifth row). The formula in H4 is:
=OFFSET(B3,5,1,1,4) // returns C8:F8
Example #5
Below, we use OFFSET formula in excel to return April, May, and June values for the West region. The formula in H4 is:
=OFFSET(B3,4,2,3,1) // returns D7:D9
Excel Course
If you’re an Excel beginner (or an intermediate) and want to learn Excel, this is the perfect place for you to start.
These free excel online courses give you access to a completely FREE Online Excel Training (26 video lessons with 12+ hours of learning).
You just need to sign-up to get access to the free excel online course. Just click free excel online courses and start watching the videos to learn Excel.
To make the most of this free training, please go through these Excel training videos in the sequence in which they have been listed. I have also divided these into 7 modules.
NOTE: In case the video looks a bit blurry, you can change the video quality to 720p (by clicking on the Settings gear icon on the bottom right of the video screen)
In case you’re an intermediate or advanced user, feel free to pick the lessons you want to watch.
Also, in case you have any questions or any feedback for me, kindly leave it in the comments area (at the bottom of this page).
In case you find the videos useful and would like to support me,