Offset(…) Function in Excel with Examples

The syntax of OFFSET(…) Function

   OFFSET( reference, rows, cols, [height], [width] )

Examples of OFFSET(…) Function

Example: =OFFSET(A5, 2, 2, 1, 1)

The above example says that the reference value is A5, then go two steps down(two rows down, you are now in cell A7), now go two steps right (two columns right, you are now in cell C7). As height and width, both are 1, no cell range will be selected.

To remember easily just use this way: A5 is the reference, add two rows and two columns with this to find out the next reference: A5+2 rows=A7 and A7+2 columns= C7. Simple.

Another Example: OFFSET(C7,5,3,1,1). New reference will be: C7+ 5 rows=C12 and C12+3 columns= F12. Our new reference will be F12 as height and width are both 1, so no cell range will be selected.

Download this file to work with OFFSET function with example.

See the following screenshot.

OFFSET Function in Excel.

OFFSET Function in Excel.

More Example: OFFSET(A5, 2, 1, 2, 2). This time height and width are 2. The new cell reference is B7 and the cell range will be now: B7: C8.

More Example: OFFSET(A5, 2, 1, 2, 3). This time height and width are 2 and 3. The new cell reference is B7 and the cell range will be now: B7: D8.

Important: This cell range you can’t show, just use this cell range to do another job like SUM, AVERAGE, and other functions. We have used SUM to use this cell range.

Do the above example practically in the sample file.

Happy Excelling 🙂

Read More: Use of Offset Function in Excel [Offset – Match Combo, Dynamic Range]


Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share here. Not only how to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned!

1 Comment

      Leave a reply