# 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.