Excel OFFSET function

Summary

The OFFSET function in Excel returns a reference to a range that is offset from a starting cell or range of cells by a specified number of rows and columns. It can also specify the height and width of the new reference. OFFSET is particularly useful for creating dynamic ranges, where you need a reference that can adjust based on calculations or inputs elsewhere in your worksheet.
Syntax
				
					=OFFSET(reference, rows, cols, [height], [width])
				
			
  • reference: The starting point cell or range.
  • rows: The number of rows to move down (or up if negative) from the starting reference.
  • cols: The number of columns to move right (or left if negative) from the starting reference.
  • [height]: [Optional] The height, in number of rows, for the returned reference.
  • [width]: [Optional] The width, in number of columns, for the returned reference.
Return value
Returns a cell or range reference shifted a specific number of rows and columns from a starting point.

How to use

OFFSET is used by specifying a starting cell or range and then defining how many rows and columns to offset from this point. Optionally, you can also set the size of the resulting range with height and width.

Examples

Simple OFFSET
Creating a Dynamic Cell Reference: Shifting a reference 3 rows down and 2 columns right:
				
					=OFFSET(A1, 3, 2)
				
			
This formula starts at A1 and moves 3 rows down and 2 columns right, returning the reference to cell C4.
OFFSET with Dynamic Range Size
Adjusting Range Size Dynamically: To create a range that adjusts based on input:
				
					=OFFSET(A1, 0, 0, B1, C1)
				
			
If B1 contains 5 (for height) and C1 contains 3 (for width), this formula creates a 5×3 range starting from A1.
OFFSET for Summing a Dynamic Range
Summing Over a Dynamic Range: To sum a variable number of cells:
				
					=SUM(OFFSET(A1, 0, 0, D1, 1))
				
			
Assuming D1 contains the number of rows to sum, this formula sums a range starting at A1, with a height specified in D1 and a width of 1.Assuming D1 contains the number of rows to sum, this formula sums a range starting at A1, with a height specified in D1 and a width of 1.
OFFSET and COUNTA
Creating a Dynamic Range Based on Non-Empty Values: To create a dynamic range that adjusts based on the count of non-empty cells:
				
					=OFFSET(A1, 0, 0, COUNTA(B1:B100), 1)
				
			

This formula uses the COUNTA function to count the number of non-empty cells in the range B1:B100. Then, it creates a dynamic range starting from A1, with a height equal to the count of non-empty cells and a width of 1 column. If there are 10 non-empty cells in B1:B100, OFFSET returns the range A1:A10.

This example is particularly useful in scenarios where data is added or removed over time, and you need a formula or function (like SUM, AVERAGE, etc.) to automatically adjust to the current size of the dataset.

Additional Notes

  • OFFSET is a volatile function and can cause performance issues in large or complex worksheets.
  • It can be combined with other functions like SUM, AVERAGE, or MATCH to perform dynamic calculations.
  • Care should be taken with OFFSET, as it can return references outside the range of existing data, potentially causing errors.

Related Functions

Excel INDIRECT function

The Excel INDIRECT function converts text strings into dynamic cell references, enabling flexible and adaptable data interactions.

Excel ADDRESS function

The Excel ADDRESS function creates cell addresses from row and column numbers, offering various formats for dynamic references.

Excel INDEX function

The Excel INDEX function retrieves values at a specific row and column in a range, key for dynamic data lookups and retrieval.

Content Navigation