Home » Excel-Built-In-Functions » Excel-Offset-Function




The Excel OFFSET function returns range of cells that is a specified number of rows and columns from an initial supplied range. The user can specify the size of the returned cell range.

The format of the function is:


where the arguments are as follows:

reference	The initial cell range (this can be either a single cell or multiple cells)
rows	the number of rows from the start (upper left) of the initial supplied range, to the the start of the returned (offset) range
cols	the number of columns from the start (upper left) of the initial supplied range, to the the start of the returned (offset) range
height	the height of the returned range
width	the width of the returned range

The height and width arguments are optional. If these are not supplied, then the returned range is the same height and width as the initial supplied cell range, (reference). Note that if the requested returned range goes off the edge of the spreadsheet, the function returns an error.

Example 1

The following spreadsheets shows several examples of the OFFSET function being used to reference different cell ranges. In each example, the initial supplied range is highlighted in green and the resulting range that is returned by the function is shown in red.

	=OFFSET( B1:B6, 4, 2 ) (note height and width arguments are omitted and so the returned range has the same dimensions as the original range)
	=OFFSET( D3, 1, -2 )
	=OFFSET( B2, 2, 1, 1, 4 )
	=OFFSET( B2, 3, 0, 3, 2 )

Note that in the above examples, the function returns a cell range. Therefore, if you attempt to display the result in a cell, you will get an error message (eg. the first example above returns the range D5:D10 - but if you typed "=D5:D10" into a cell you would get an error).

The following Example shows a practical use of the OFFSET function, where the results of the function are used in a further function.

Example 2

The above examples all return cell ranges, but do not show you ways in which you might make practical use of the OFFSET function. This is addressed in the following spreadsheet, which uses the OFFSET function to return different cell ranges, depending on the weekday shown in the cell B1 and then uses the returned range in a further calculation :



In the above spreadsheet, the OFFSET function is used in cells B2 and B3, to reference the sales figures for the weekday selected in cell B1. In both cells, the function uses the same arguments.

• The initial cell range is cell A5. This is the cell from which the offset is applied.
  
  
• The MATCH Function is used to identify which row (from A6 to A12) contains the weekday shown in cell B1. This will give the result, 1 if B1="Sunday", 2 if B1="Monday", etc. This number then supplies the rows argument for the OFFSET. (ie. the number of rows to offset from the initial cell, A5).
  
  
• The cols argument is set to 1, so the start of the returned cell range is offset 1 column from the start cell, A5 (ie. the returned cell range always starts in column B).
  
  
• The height argument is set to 1, so the returned range is 1 cell high
  
  
• The width argument is set to 4, so the returned range is 4 cells wide
  
  

These arguments cause the OFFSET function to return the range of cells containing the sales figures for the weekday shown in cell B2. The results from this function are then fed into the SUM Function (in cell B2) and the AVERAGE Function (in cell B3), so that these 2 cells show the SUM and AVERAGE of the sales figures for the selected weekday. The results for the weekday 'Tuesday' are show on the right.