How to use the OFFSET function in Excel
This tutorial on how to use the OFFSET function in Excel is suitable for users of Excel 2013/2016/2019 and Microsoft 365.
Objective
Use the OFFSET function to return a cell or range of cells that is a specified number of rows and columns from a cell or range of cells.
The OFFSET Function Explained
The OFFSET function is part of the Lookup and Reference group of functions in Excel. It enables you to return a result from a specific cell or range of cells that is a specified number of rows and columns away from a cell you have specified.
Syntax:
=OFFSET(reference, rows, cols, [height], [width])
Remember that the arguments in [] are optional.
Example 1 – Basic OFFSET
In this example, I have Sales figures for the ‘North,’ ‘South,’ ‘East’ and ‘West’ regions over 12 months. I have used the OFFSET function to obtain the sales figures for ‘July’ for the ‘West’ region.
- Type =OFFSET(A2,7,4,1,1)
This formula’s starting point is cell A2. It counts down seven rows. Then across four columns. The height is one cell, and the width is one cell.
Example 2 – OFFSET with SUM
In this example, I have used the OFFSET function to return the 1 x 2 range that is two rows below and two columns to the right of cell A2. The SUM function then calculates the sum of the 1 x 2 range.
- Type =SUM(OFFSET(A2,2,2,1,2))
The formula’s starting point is cell A2. It counts down two rows and across two columns. The height is one cell, and the width is two cells. The SUM function then sums the numbers in the 2-cell range.
Example 3 – OFFSET with Negative Numbers
The OFFSET function also works when using negative numbers. In this example, I have used the OFFSET function to return the value for ‘July’ for the ‘South’ region. The starting cell in this example is A13.
The formula’s starting point is cell A13. As I am looking for a figure further up the list, I can use the negative value of -4 to count back four rows. It then counts across two columns, the height is one, and the width is one.
Example 4 – Returning multiple values with OFFSET
In this example, I have used the OFFSET function to return multiple values.
The formula’s starting point is cell A3. It counts down three rows. It counts across two columns. The height is six cells, and the width is one cell.
Example 5 – Returning an entire row with OFFSET
In this example, I have used the OFFESET function to return all the values in a row.
This formula’s starting point is cell A3. It counts down six rows. It counts across 1 column. The height is one cell, and the width is four.
If I modify the height to two cells, this is the result.
For more examples of using OFFSET, check out the following links:
Ablebits – Using OFFSET function in Excel – formula examples
XELPlus – Excel OFFSET Function for Dynamic Calculations – Explained in Simple Steps
Video Tutorial
For more Free Excel tutorials from Simon Sez IT. Take a look at our Excel Resource Center.
Other Excel classes you might like:
- Logical Functions in Excel
- Designing Better Spreadsheets in Excel
- Introduction to Power Pivot & Power Query in Excel
To learn Excel with Simon Sez IT. Take a look at the Excel courses we have available.
