How to Use the Microsoft® Excel OFFSET Function

How to use Excel’s OFFSET function as a formula in one cell to get the content of another cell or group of cells.

Last updated on 2021-05-14 by David Wallis.

Explaining OFFSET

In this screen shot there’s an OFFSET formula in Cell E2. It’s instructing Excel to get the content of the cell that is five rows down and one column to the right of Cell A2:

Hence the formula’s result of "Read", illustrating the action of OFFSET, the simplest syntax of which is this:

In this simple form, OFFSET returns the content of a single cell. However, it has two more arguments that you use to return the contents of more than one cell:

In this example, the formula in Cell E2 returns values, not only to E2 itself, but also to the two cells immediately below it (note that cells E3 and E4 do not contain formulas):

The formula in Cell F4, above, illustrates how you can wrap an OFFSET in another function, e.g. SUM in this example.

Negative Offsets

Positive values for the rows and colums cause OFFSET to look down and right. You may use negative values to cause it to look up and left. In this screen shot, the formulas in Cells D7 and D10 do this:

Your Support for DMW TIPS

Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.

To make a contribution by PayPal in GBP (£ sterling) —

To make a contribution by PayPal in USD ($ US) —