Excel OFFSET Height: How the Argument Works in Formulas

Formulas & functions · Updated November 18, 2026

In Excel, OFFSET’s height argument sets how many rows the returned reference includes; it does not control how far the reference moves. The separate rows argument controls vertical movement. Height is optional, but when supplied it must be a positive number.

What the height argument means

OFFSET is a reference function: it starts from a cell or range, moves a specified number of rows and columns, then returns a reference to a range. Its arguments, in order, are the starting reference, rows to move, columns to move, optional height, and optional width. Height is measured in worksheet rows. Width is measured in worksheet columns.

For example, a height of 3 means the resulting reference spans three rows. If the starting point is A1 and there is no movement, a height of 3 and width of 1 describes A1:A3. If rows is 2 instead, the reference starts two rows below A1; height still determines how many rows the returned range spans. Keeping those jobs separate is the key to understanding the function.

If you omit height, OFFSET uses the same number of rows as the original reference. If you omit width, it uses the original reference’s width. For a single starting cell, omitting both therefore gives a one-cell reference. If you specify height but omit width, the resulting reference has the specified number of rows and retains the original reference’s width.

How height differs from the rows argument

The names are easy to mix up because both arguments relate to rows. Think of rows as the distance to travel and height as the size of the destination range. A positive rows value moves down; a negative value moves up. Height describes the resulting range’s row count and must be positive. It does not mean move down by that many rows.

With values 10, 20, 30, 40, and 50 in A1:A5, this formula sums three cells beginning one row below A1:

With 10, 20, 30, 40, and 50 in A1:A5, =SUM(OFFSET(A1,1,0,3,1)) refers to A2:A4 and returns 90.

Here, 1 is the number of rows to move, 0 means stay in the same column, 3 is the returned range’s height, and 1 is its width. A frequent mistake is putting 3 in the rows position when the goal is to include three cells. That moves the starting point down three rows instead; it does not request a three-row range.

A simple example of changing range height

Suppose a worksheet has five sales amounts in A1:A5: 10, 20, 30, 40, and 50. To total the first three cells, set the starting reference to A1, use zero movement, and set the height to 3 and width to 1.

With those five amounts in A1:A5, =SUM(OFFSET(A1,0,0,3,1)) sums A1:A3 and returns 60.

Changing the height from 3 to 5 includes all five amounts and gives 150. Changing the starting reference or the movement arguments changes where the range begins; changing height changes how many rows it covers. OFFSET itself returns a reference, not a total. Wrapping it in SUM makes Excel add the values in that referenced range. Other functions that accept references, such as AVERAGE or COUNT, can use the same approach.

Using height to make a range expand

A common reason to use a variable height is to include a growing list of entries without editing a formula each time. COUNTA can count filled cells and provide that count as OFFSET’s height. The method relies on data being contiguous: a blank inside the list can make a count-based height stop short of later entries, and unrelated content in the counted cells can make the height too large.

Assume A1 contains a heading, A2:A6 contain the amounts 10, 20, 30, 40, and 50, and A7:A100 are empty. With the data kept contiguous, this formula totals the occupied amount cells:

With a heading in A1, contiguous amounts in A2:A6, and no other entries in A2:A100, =SUM(OFFSET(A2,0,0,COUNTA(A2:A100),1)) refers to A2:A6 and returns 150.

As more amounts are entered in A7:A100, the count and resulting range grow. This example assumes there is at least one amount: if the counted range is empty, COUNTA returns zero, which is not a valid positive height. It also assumes there are no gaps in the list. If gaps are possible, use a method that finds the last occupied row rather than assuming the count equals the list’s length.

For a list that must support occasional blanks, an INDEX-based range can be an alternative. Suppose A2:A100 contains a contiguous list of amounts with no blank cells between the first entry and the last, and at least one entry is present. =SUM(A2:INDEX(A2:A100,COUNTA(A2:A100))) sums through the last counted entry; with 10, 20, 30, 40, and 50 in A2:A6 and the rest of the range empty, it returns 150. This also depends on contiguous data, but avoids using OFFSET.

Choosing the right height and width

Choose height by asking how many worksheet rows the returned range should contain, not how many rows separate it from its starting point. Choose width in the same way for columns. A range with height 4 and width 2 covers four rows and two columns. When the original reference already has the dimensions you want, leaving height or width out can make the formula simpler.

  • Use a height of 1 when the result should be one row tall.
  • Use a larger positive height when a calculation needs several rows of values.
  • Use a width greater than 1 when the resulting range should include multiple adjacent columns.
  • Check the starting reference and movement separately when the range begins in the wrong place.

Height and width must be positive when supplied. A zero or negative height is not a way to create an empty range or reverse the direction. Use the rows and columns movement arguments for direction; use a valid positive size for the resulting range.

Common errors and calculation behavior

OFFSET returns #REF! when the requested reference would extend beyond the worksheet’s row or column limits. For example, moving down from a cell near the bottom of a sheet and asking for a range taller than the remaining rows cannot produce a valid reference. Check both the movement and the requested height and width when this error appears.

A second source of unexpected results is a height that does not match the data. A height that is too small leaves out entries; one that is too large may include blank cells or unrelated information. Review the range that the formula is intended to include, especially when the size is calculated with COUNTA or another expression.

OFFSET is a volatile function, meaning Excel recalculates it whenever a calculation occurs, even if the cells it refers to have not changed. A few OFFSET formulas are often manageable, but many volatile formulas in a large workbook can add calculation work. If a workbook feels slow, look for repeated volatile functions and consider whether a nonvolatile range construction or an Excel Table can do the job.

Alternatives for expanding data ranges

For a growing dataset, an Excel Table is often easier to maintain than a formula-built range. Select the data and choose Insert > Table, then use the table’s column references in formulas. Tables expand as new rows are added, and their references are easier to read than a nested OFFSET calculation. This is especially useful for lists that other people will update.

INDEX can also be used to build a reference without OFFSET’s volatility, as in the earlier example. It still needs a dependable way to identify the last row, and a count-based approach still assumes there are no gaps. If the list has a known maximum size, a fixed range may be simplest; if it grows regularly, a Table usually reduces maintenance. Choose based on the worksheet’s layout and how the data is entered, rather than using OFFSET automatically.

Frequently Asked Questions

What does height mean in Excel OFFSET?
Height is the number of rows in the reference OFFSET returns. It sets the size of the resulting range, not how far the function moves from its starting reference.

Can OFFSET height be zero?
No. When specified, height must be positive. A zero height does not create an empty reference; it produces an invalid argument. Use a different formula design if a calculation needs to handle an empty list.

What happens if I leave height blank?
OFFSET uses the height of the original reference. If that reference is one cell, the resulting reference is one row tall. Width behaves similarly when omitted.

How do I make OFFSET height dynamic?
Supply a counting or row-finding expression as the height argument, then use the returned reference inside a function such as SUM. Make sure the expression returns a positive row count and matches the actual data layout.

Is OFFSET volatile in Excel?
Yes. Excel recalculates OFFSET whenever calculation occurs, even when its referenced cells have not changed. For large or calculation-heavy workbooks, consider an Excel Table or an INDEX-based reference instead.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.