OFFSET function in Excel
The OFFSET function helps you point to a cell or range of cells in Excel that is offset by a chosen number of rows and columns from the original reference.
💎 Return a Cell
Now, we start with the OFFSET function to find a cell in Excel. Here we go.
1. This OFFSET function returns the cell that lies 3 rows lower and 2 columns to the right of cell A2. If the height and width are set to 1, the OFFSET function returns just one cell.
Result:
In the OFFSET function, the last two arguments are optional. If height and width are not given, the new reference will use the same size as the first reference.
2. For example, the OFFSET function returns the cell placed 12 rows below A2, in the same column.
Result:
💎 Return a Range
Now, we will use the OFFSET function in Excel to return a range of cells.
1. This OFFSET function returns a range of 1 row and 2 columns, located 8 rows below and 1 column to the right of cell A2. The SUM function returns the total value of the selected range.
Result:
The last two arguments of the OFFSET function can be skipped as explained earlier. If you do not provide height and width, the new reference will be the same size as the original reference.
2. Using the OFFSET function below, you get a range of 4 rows below B2:C2 in the same columns. The SUM function returns the total value of the selected range.
Result:
3. The OFFSET function below gives the range that starts 4 rows down and in the same columns as B2:C2. This time without a SUM function.
Result:
Note: Select the range E8:F8 (or a range of equal size) before entering this formula. Finish by pressing CTRL + SHIFT + ENTER. When the formula bar displays curly braces {}, it indicates an array formula. Select cells E8 to F8, then press Delete to erase the array formula.
4. Excel 365 and Excel 2021 users can simply select cell E8, type the OFFSET function, and then press Enter. Bye-bye curly braces.
Note: When you type the OFFSET function in E8, it fills several cells. Wow! This behavior in Excel 365/2021 is called spilling.
💎 Negative Offset
If you want to return a cell or range located above or to the left, enter negative numbers. The OFFSET function here returns the cell that is 12 rows up and 1 column left from cell C14.
Result:
6/15 Completed! Learn more about lookup & reference ➝
Next Chapter: Financial Functions