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.

OFFSET 1

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.

OFFSET 2

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

OFFSET 3

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.

OFFSET 4

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

OFFSET 5

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):

OFFSET 6

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.

PRYOR+ 7-DAYS OF FREE TRAINING


Courses in Customer Service, Excel, HR, Leadership, OSHA and more. No credit card. No commitment. Individuals and teams.


Choose a Seminar and Save $10:

Microsoft<small><sup>®</sup></small> Excel<small><sup>®</sup></small>: Beyond the Basics

Microsoft® Excel®: Beyond the Basics

Dec. 8

Eastern Time Zone

Dec. 9

Pacific Time Zone

Dec. 14

Central Time Zone

Dec. 16

Eastern Time Zone

Dec. 20

Pacific Time Zone

Dec. 21

Central Time Zone

Jan. 5

Eastern Time Zone

Jan. 6

Central Time Zone

Jan. 10

Central Time Zone

Jan. 12

Eastern Time Zone

Jan. 13

Pacific Time Zone

Jan. 18

Pacific Time Zone

Jan. 19

Central Time Zone

Jan. 20

Eastern Time Zone

Jan. 24

Eastern Time Zone

Jan. 25

Central Time Zone

Jan. 26

Pacific Time Zone

Jan. 31

Central Time Zone

Feb. 2

Pacific Time Zone

Feb. 3

Eastern Time Zone

Feb. 7

Eastern Time Zone

Feb. 8

Central Time Zone

Feb. 10

Pacific Time Zone

Feb. 14

Pacific Time Zone

Feb. 15

Eastern Time Zone

Feb. 17

Central Time Zone

Feb. 22

Eastern Time Zone

Feb. 23

Pacific Time Zone

Feb. 24

Central Time Zone

Feb. 28

Central Time Zone

Mar. 2

Eastern Time Zone

Mar. 7

Pacific Time Zone

Mar. 8

Eastern Time Zone

Mar. 10

Central Time Zone

Mar. 15

Central Time Zone

Mar. 16

Pacific Time Zone

Mar. 17

Eastern Time Zone

Mar. 21

Pacific Time Zone

Mar. 23

Central Time Zone

Mar. 24

Eastern Time Zone

Mar. 28

Central Time Zone

Mar. 29

Eastern Time Zone

Mar. 31

Pacific Time Zone

Microsoft<small><sup>®</sup></small> Excel<small><sup>®</sup></small> Basics

Microsoft® Excel® Basics

Dec. 8

Pacific Time Zone

Dec. 13

Central Time Zone

Dec. 15

Eastern Time Zone

Dec. 19

Pacific Time Zone

Dec. 20

Central Time Zone

Jan. 4

Eastern Time Zone

Jan. 5

Central Time Zone

Jan. 9

Central Time Zone

Jan. 11

Eastern Time Zone

Jan. 12

Pacific Time Zone

Jan. 17

Pacific Time Zone

Jan. 18

Central Time Zone

Jan. 19

Eastern Time Zone

Jan. 23

Eastern Time Zone

Jan. 24

Central Time Zone

Jan. 25

Pacific Time Zone

Jan. 30

Central Time Zone

Feb. 1

Pacific Time Zone

Feb. 2

Eastern Time Zone

Feb. 6

Eastern Time Zone

Feb. 7

Central Time Zone

Feb. 9

Pacific Time Zone

Feb. 13

Pacific Time Zone

Feb. 14

Eastern Time Zone

Feb. 16

Central Time Zone

Feb. 21

Eastern Time Zone

Feb. 22

Pacific Time Zone

Feb. 23

Central Time Zone

Feb. 27

Central Time Zone

Mar. 1

Eastern Time Zone

Mar. 6

Pacific Time Zone

Mar. 7

Eastern Time Zone

Mar. 9

Central Time Zone

Mar. 14

Central Time Zone

Mar. 15

Pacific Time Zone

Mar. 16

Eastern Time Zone

Mar. 20

Pacific Time Zone

Mar. 22

Central Time Zone

Mar. 23

Eastern Time Zone

Mar. 27

Central Time Zone

Mar. 28

Eastern Time Zone

Mar. 30

Pacific Time Zone

Advanced Microsoft<small><sup>®</sup></small> Excel<small><sup>®</sup></small>-Macros, PivotTables, Charts and More

Advanced Microsoft® Excel®-Macros, PivotTables, Charts and More

Dec. 9

Central Time Zone

Dec. 15

Eastern Time Zone

Dec. 21

Pacific Time Zone

Jan. 4

Central Time Zone

Jan. 9

Pacific Time Zone

Jan. 10

Eastern Time Zone

Jan. 17

Central Time Zone

Jan. 18

Eastern Time Zone

Jan. 26

Central Time Zone

Jan. 27

Pacific Time Zone

Jan. 30

Eastern Time Zone

Feb. 3

Central Time Zone

Feb. 7

Pacific Time Zone

Feb. 8

Eastern Time Zone

Feb. 15

Central Time Zone

Feb. 16

Eastern Time Zone

Feb. 21

Central Time Zone

Feb. 23

Pacific Time Zone

Feb. 28

Eastern Time Zone

Mar. 3

Pacific Time Zone

Mar. 7

Pacific Time Zone

Mar. 8

Central Time Zone

Mar. 13

Eastern Time Zone

Mar. 16

Central Time Zone

Mar. 22

Pacific Time Zone

Mar. 30

Central Time Zone

Mar. 31

Eastern Time Zone

Related Formulas Articles

PRYOR+ 7-DAYS OF FREE TRAINING


Courses in Customer Service, Excel, HR, Leadership, OSHA and more. No credit card. No commitment. Individuals and teams.

Categories