How to Use the OFFSET Function in Excel

Introduction

You have a report that pulls the last six months of sales into a chart, and every month you manually update the range to shift forward. Or you maintain a summary that should always average the latest 30 entries, but the list keeps growing and the formula stays fixed. The OFFSET function solves these problems by creating references that move and resize on their own.

How OFFSET Works

OFFSET returns a reference to a range that is a specified number of rows and columns away from a starting cell. It can also define how tall and wide that range is.

The syntax is:

=OFFSET(reference, rows, cols, [height], [width])

reference: The starting cell or range. OFFSET measures everything from here.
rows: How many rows to move down (positive) or up (negative) from the starting cell.
cols: How many columns to move right (positive) or left (negative).
height: Optional. The number of rows tall the returned range should be. If omitted, it matches the height of the starting reference.
width: Optional. The number of columns wide the returned range should be. If omitted, it matches the width of the starting reference.

OFFSET does not move or change any cells. It builds a reference in memory that other functions can use.

A Basic Example

Here's a monthly revenue table for a small retail store:

	A	B
3	Month	Revenue
4	Jan	12,000
5	Feb	14,500
6	Mar	13,200
7	Apr	15,800
8	May	16,100

To grab the value three rows below B4 (which is B7, April's revenue):

=OFFSET(B4, 3, 0)

OFFSET starts at B4, moves down 3 rows, moves 0 columns, and returns the value in B7: 15,800.

Change the row argument to 4 and it returns B8 (May): 16,100. The formula itself stays in the same cell. Only the offset distance changes what it points to.

This on its own isn't more useful than typing =B7. The power shows up when you combine OFFSET with other functions so the row argument is calculated rather than hard-coded.

Expanding Ranges with Height and Width

The optional height and width arguments turn OFFSET into a range builder. Instead of pointing to a single cell, you can define a block of cells.

Using the same data:

=SUM(OFFSET(B4, 0, 0, 3, 1))

This starts at B4, doesn't move, and creates a range 3 rows tall and 1 column wide. That range is B4:B6. The SUM adds up Jan through Mar: 39,700.

Change the height to 5:

=SUM(OFFSET(B4, 0, 0, 5, 1))

Now it sums B4:B8 (all five months): 71,600.

When the height argument comes from a formula instead of a hard-coded number, the range grows and shrinks automatically.

Rolling Averages with OFFSET

A common use is calculating a rolling average that always looks at the most recent N entries. Suppose you track daily sales and want the average of the last 7 days, regardless of how many days are in the list.

	A	B
3	Day	Sales
4	Mon	820
5	Tue	950
6	Wed	780
7	Thu	1,100
8	Fri	1,250
9	Sat	1,400
10	Sun	900
11	Mon	1,050
12	Tue	1,180

To average the last 7 entries in B4:B12:

=AVERAGE(OFFSET(B4, COUNT(B4:B12) - 7, 0, 7, 1))

Here's how it works step by step:

COUNT(B4:B12) returns 9 (nine numbers in the list).
9 - 7 = 2. The range starts 2 rows below B4 (at B6).
Height of 7 means it spans B6:B12 (the last seven entries).
AVERAGE calculates across that window: 1,094.

When you add a new day to the list, COUNT returns 10, the starting row shifts down by one, and the formula automatically averages the newest 7 entries. No manual adjustment needed.

Key takeaway: Pair OFFSET with COUNT or COUNTA to create ranges that slide forward as new data arrives.

Dynamic Ranges with OFFSET and COUNTA

One of the most practical uses of OFFSET is building a named range that expands as you add rows. This is especially useful for charts and data validation lists that should include new entries without manual updates.

Suppose you have a product list with a header in A1 and items in A2:A10 today, but next week it might grow to A2:A20. You can define a named range with this formula:

=OFFSET(Sheet1!$A$2, 0, 0, COUNTA(Sheet1!$A:$A) - 1, 1)

Starts at A2 (the first data row, skipping the header in A1).
Doesn't move (0 rows, 0 columns).
Height equals COUNTA($A:$A) - 1. COUNTA counts all non-blank cells in column A (including the header), then subtracts 1 to exclude it. If there are 10 products plus a header, height is 10.
Width is 1 (single column).

To set this up:

Go to the Name Manager (Formulas tab, or Ctrl + F3).
Click New and give the range a name like ProductList.
In the "Refers to" field, paste the OFFSET formula above.
Click OK.

Now any formula, chart, or dropdown that references ProductList will automatically include new rows as you add products. No need to expand the range manually.

OFFSET vs. INDEX for Dynamic Ranges

INDEX can also create dynamic ranges and isn't volatile. The equivalent of the dynamic range above using INDEX:

=Sheet1!$A$2:INDEX(Sheet1!$A:$A, COUNTA(Sheet1!$A:$A))

Both approaches work, but they have different trade-offs:

	OFFSET	INDEX
Volatile	Yes (recalculates on every change)	No (only when inputs change)
Syntax style	"Start here, go this far"	"Give me this position in range"
Sized ranges	Built-in with height/width arguments	Requires combining with a static start
Best for	Intuitive dynamic ranges	Performance-critical workbooks

For most workbooks with reasonable data sizes, the difference is negligible. Choose whichever reads more clearly to you.

Things to Watch

OFFSET is a volatile function. Excel recalculates it every time anything in the workbook changes, even if the inputs to OFFSET haven't changed. In a small workbook, this is invisible. In a large file with thousands of OFFSET formulas, it can noticeably slow things down.

A few other things to keep in mind:

Negative offsets can break. If you offset above row 1 or left of column A, Excel returns a #REF! error. Make sure your row/col arguments can't go out of bounds.
Zero height or width errors. If your COUNT or COUNTA returns 0 (empty list), height becomes 0 or negative, and OFFSET returns #REF!. Wrap in IFERROR or add a check.
Hard to audit. Because OFFSET builds invisible references, other people reading your spreadsheet can't see what range a formula actually points to without stepping through it manually. Add a comment or name the range descriptively.
Won't show in Trace Precedents. Excel's arrow-tracing tool can't follow OFFSET references. This makes debugging trickier in complex workbooks.

If you find yourself relying on dozens of OFFSET calls to keep ranges current, consider whether Excel Tables (which expand automatically) or dynamic array functions like FILTER might solve the same problem with less overhead.

Conclusion

OFFSET creates references that shift position and change size based on calculated arguments, making it ideal for rolling calculations, auto-expanding ranges, and dynamic reporting. Combine it with COUNT or COUNTA and your formulas stay current as data grows.

For building named ranges that pair well with OFFSET, see our guide on named ranges in Excel. For a non-volatile approach to flexible lookups, check out our guide on INDEX and MATCH.