How To Use Excel Offset Function

Introduction to Offset Function

It is well-known that the OFFSET () function in Ms Excel returns a cell that is a particular number of rows and columns from a cell or range of cells. It’s handy in formulas that dynamically average or the sum of “last nth values”.

In this small article, I’ll explain the syntax and examples of the OFFSET function in Excel.

Syntax of OFFSET Function

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

Where,

•       referencerepresents the kick-off point, supplied as a cell reference or range.
•       rowsare the number of rows to offset below the starting reference.
•       colsare the number of columns to offset to the right of the starting reference.
•       heightmay be the height in rows of the returned reference (optional).
•       widthmay be the width of columns of the returned reference (optional).

How to use excel Offset Formula

1. For example, enter the following data into a new Excel spreadsheet. Here I used data for ABC Limited.

2. Now use the formula as = OFFSET (E6, 3, 2, 1, 1) anywhere as I9, in this case.

3. And finally, the offset result will be 900. Here, the excel OFFSET function in the example below returns the cell that is 3 rows below and 2 columns to the right of starting cell E6.

4. The OFFSET function returns a cell because the height and width are both set to 1.

Offset Function with Sum

It is well-known that the SUM function calculates the sum of the range. And you can use the Offset function with a summation. For example, I have data similar to what you see in the screenshot below.

Here, the OFFSET function in excel returns the 2 range that is 5 rows below and 1 column to the right of starting cell E6. And using the formula = SUM (OFFSET (E6, 5, 1, 1, 2)) the result is 2300.

Some Of The Excel Offset Function Common Errors

If you receive an error from the Ms Excel Offset Function, this is likely to be one of the mistakes:

• #REF! displays if the numerical range resulting from the required offset is invalid.
• #VALUE! displays if any of the supplied rows, cols, [height], or [width] arguments are non-numeric.

Conclusion

I hope, you’ve got some clear ideas on how to use Offset function in Excel. Thank you!