Excel How to use OFFSET function

Click here to download the Excel workbook which contains the below examples.

Description

OFFSET Function: Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. The reference that is returned can be a single cell or a range of cells. You can specify the number of rows and the number of columns to be returned. If you use and Height and width then the result of the function will be an array (multiple Cells/ multiple values or a range)

Syntax

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

Detailed explanation of each argument

  • Reference (Compulsory Argument): The reference from which you want to base the offset. Reference must refer to a cell or range of adjacent cells; otherwise, OFFSET will give you the #VALUE! error value.
  • Rows (Compulsory Argument): The number of rows, up or down. Rows can be positive (which means below the reference row) or negative (which means above the reference row).
  • Cols (Compulsory Argument): The number of columns, to the left or right. Cols can be positive (which means to the right of the reference row) or negative (which means to the left of the reference row).
  • Height (Optional Argument): In this argument update the number of rows, that you want the returned reference to be. Height must be a positive number.
  • Width (Optional Argument): In this argument update the number of columns, that you want the returned reference to be. Width must be a positive number.

Example 1 of OFFSET function

How to use Excel OFFSET Function
Excel OFFSET Function Example 1 Screen print

Formula1 in Cell E11 is =OFFSET(B10,2,0)

Explanation Formula 1 in Example 1

  • Reference: So in the above formula, you can see we have given reference as B10 and then we are saying offset 2 rows and 0 columns from cell B10.
  • row & col: It means move 2 rows down and 0 columns movement from Cell B10 so it will select B12 because we do not have columns movement we are still in B column only.
  • Result: Now whatever content we have in cell B12 will be shown as a result. So the result is Product B.

Formula2 in Cell E12 is =OFFSET(B10,2,1)

Explanation Formula 2 in Example1

  • Reference: So in the above formula, you can see we have given reference as B10 and then we are saying offset 2 rows and 1 column from cell B10.
  • row & col: It means move 2 rows down and 1 column movement from Cell B10 so it will select C12 compared above formula here we are moving columns as well.
  • Result: Now whatever content we have in cell C12 will be shown as a result. So the result is ₹ 25,000.00.

Example 2 of OFFSET function

How to use Excel OFFSET Function
Excel OFFSET Function Example 2 Screen print

Formula in Cell F23 is =SUM(OFFSET(B20,2,1,6,1))

Explanation Formula in Example 2

In Example 2 we have using SUM Function on top of the OFFSET function. In below steps only OFFSET function is explained assuming that you are aware of how to use SUM function(it will do Addition of multiple numbers).

  • Reference: So in the above formula, you can see we have given reference as B20 and then we are saying offset 2 rows and 1 column from cell B20.
  • rows: In rows argument, we have updated 2 it means move 2 rows down from Cell B20
  • col: In columns argument, we have updated 1 it means move 1 column to right movement from Cell B20 so it will select C22.
  • [height]: In height argument, we have given 6 it means total 6 rows down from Cell C22 so it will get data till CellC27.
  • [width]: In height argument, we have given 1 it means total 1 column from Cell C22 so there won’t be any change in column reference. If it is more than 1 then we see the change in column range as well.
  • Result: If we use height & width arguments in the OFFSET function the result will be multiple values (25000, 35000, 15000, 85000,75000, 95000). In Excel, it is called an array so OFFSET is an array function. As the result is multiple values we can use the SUM function on top of the OFFSET function to add the values.
  • Like this, we can use multiple functions on top of the OFFSET function.

Example 2 Result without SUM function

How to use Excel OFFSET Function
Excel OFFSET function example 2 without SUM function

Formula in Cell F37 is =OFFSET(B34,2,1,6,1)

  • Result of example 2 formula without SUM Function please see above screen print.
  • This result is an error called #SPILL error which is newly added in the Excel, we cannot see now but while writing the formula you can see it.
  • #SPILL means overflow as our result is not a single cell we got array that is the reason it is #SPILL error.

Click here to download the Excel workbook which contains the above examples.