Excel Offset Formula

How do you find a single value in a table? How do you find, say, the number in the fifth column of the third row? Try out the Excel offset formula!
The OFFSET function finds a cell (or range of cells) that is a specific number of cells away from your starting point. To show OFFSET in action, let’s walk through two versions—the single-cell result, and the range result.

The Single-Cell Result

Let’s suppose you have your sales data arranged by state and by month and that you have applied the range name SalesData to the entire table.

How do you pick out the sales for Florida in the most recent month?
1. Begin the OFFSET function by specifying the starting range named SalesData.

Note that the OFFSET function begins from the upper left-hand corner of the SalesData range.

2. Count how many rows down you’ll need to move. In this case, the Florida March sales figure (24,278) is two rows down from the upper left-hand corner of the range (where the word “State” appears).

3. Count how many columns to the right you’ll need to move. In this case, the sales figure is three columns from the upper left-hand corner of the range.

4. Finally, to retrieve only a single value from the table, enter “1” for the height and “1” for the width.

This tells Excel to begin at the upper left-hand corner of the range named SalesData, move down two rows, move to the right three columns, and select the value in a single cell. The result: 24,278.
Similarly, to find the sales for Georgia in April, enter =OFFSET(SalesData,3,4,1,1).
Remember, to retrieve only a single cell, always enter “1” for the height and “1” for the width.

The Range Result

Sometimes you need to retrieve data from more than one cell. To do this:
• Follow the instructions above, but select different values for the height and width parameters.
• Use the result as a parameter in another function such as SUM or AVERAGE. Otherwise, OFFSET will return a #VALUE! error.
For example, to find the average sales for Mississippi in the first quarter (January through March):

1. Enter =SUM( followed by OFFSET( then enter the parameters:

Reference: SalesData
Rows: 4. The data begin four rows down from the upper left-hand corner of SalesData.
Cols: 1. The data begin one column to the right of the upper left-hand corner of SalesData.
Height: 1. The selected data range is one row high.
Width: 3. The selected data range is three cells wide.

2. Wrap the entire result in a SUM function.
=SUM(OFFSET(SalesData,4,1,1,3)) returns 20,734.

More Advanced Use of Excel OFFSET formula

Do you want even more flexibility in your range selections? Do you ever need to find, say, the moving average sales of a state that the user selects from a list?
You can build even more flexible formulas by combining OFFSET with VLOOKUP, MATCH, INDEX, ROWS, and COLUMNS.