#REF! in OFFSET: When the offset exceeds sheet limits (Row 1,048,576)

An #REF! error in an OFFSET formula means the calculated row or column target falls outside the physical boundary of the worksheet grid. Excel workbooks have hard, unchangeable boundaries: exactly 1,048,576 rows and 16,384 columns. If the starting cell plus the offset distance pushes the formula past row 1,048,576, past column 16,384 (column XFD), or before row 1 or column A (a negative coordinate), Excel cannot return a cell and returns #REF!.

Quick Answer

Check the row offset and height arguments inside your OFFSET formula. The sum of your starting row, row offset, and range height cannot exceed 1,048,576 (or 65,536 in older .xls files). If using dynamic counts like COUNTA(A:A), ensure you are not counting entire columns or adding extra rows that push the reference off the edge of the sheet.

---

The Boundaries of the Excel Grid

The OFFSET function uses five arguments:

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

Excel calculates the final boundaries using basic arithmetic:

• Final Row Boundary: Starting_Row + rows + height - 1
• Final Column Boundary: Starting_Column + cols + width - 1

If either calculation violates grid limits, the formula breaks:

Dimension	Minimum Allowed	Maximum Allowed	Error if Violated
Row	Row 1	Row 1,048,576	#REF!
Column	Column 1 (A)	Column 16,384 (XFD)	#REF!

Worksheet Grid:
Row 1 ------------------------------------------- Column A
  |                                                  |
  |               Valid Range                        |
  |                                                  |
Row 1,048,576 ----------------------------------- Column XFD (16,384)
  x Past this line = #REF!                           x Past this line = #REF!

---

The Most Common Culprit: Dynamic COUNTA on Whole Columns

The most frequent cause of #REF! in OFFSET is building dynamic ranges using whole-column references inside COUNTA.

Consider this common construction for an auto-expanding range:

=OFFSET(A2, 0, 0, COUNTA(A:A), 1)

1. Suppose column A contains header text, 500 rows of data, and notes entered at the bottom of the sheet in cell A1048570.
2. COUNTA(A:A) scans all 1,048,576 rows.
3. If users accidentally put spaces or formulas down to row 1,048,576, or if the formula is placed near the bottom of the sheet:
   • Starting row: A2 (row 2).
   • Requested height: 1,048,575.
   • Final row: 2 + 1,048,575 - 1 = 1,048,576.
4. If a single extra row is added or if the offset shifts down by 1 row (=OFFSET(A2, 1, 0, ...)), the target row attempts to hit 1,048,577.
5. Because row 1,048,577 does not exist, the formula returns #REF!.

The Compatibility Mode Trap (.xls files)

If a workbook was saved in the legacy Excel 97–2003 format (.xls), the grid limit is only 65,536 rows and 256 columns. If you write an OFFSET formula that references row 70,000, it will work in an .xlsx file, but converting or opening the file in Compatibility Mode will instantly break the formula with #REF!.

---

Diagnostic Step: Isolating the Out-of-Bounds Argument

To find out which argument is exceeding the boundary:

1. Select the cell displaying #REF!.
2. In the formula bar, highlight the rows expression:
   =OFFSET(A2, [HIGHLIGHT THIS], 0, 10, 1)
3. Press F9 to evaluate the value.
4. Highlight the height expression and press F9.
5. Press Esc to avoid saving changes.
6. Calculate: Starting Row Number + Rows Value + Height Value - 1.
7. If the result is greater than 1,048,576 or less than 1, you have identified the out-of-bounds parameter.

---

Negative Offset Violations

OFFSET allows negative numbers to move up or to the left:

=OFFSET(B10, -5, 0) $\rightarrow$ Returns B5.

However, if your offset distance exceeds the available distance to the top or left edge:

=OFFSET(B10, -12, 0)

Excel attempts to calculate row 10 - 12 = -2. Because there is no row -2, Excel returns #REF!. Always ensure negative offsets do not exceed the starting row index.

---

Modern Alternative: Replace OFFSET with INDEX or Dynamic Arrays

OFFSET is a volatile function. Every time you edit any cell in your spreadsheet, Excel recalculates every OFFSET formula in the workbook, causing severe calculation lag in large models.

Furthermore, OFFSET cannot prevent boundary crashes. Replacing OFFSET with non-volatile alternatives eliminates performance issues and out-of-bounds errors:

1. Replace with Non-Volatile INDEX

Instead of:
=OFFSET(A1, 0, 0, COUNTA(A:A), 1)

Use:
=A1:INDEX(A:A, COUNTA(A:A))

INDEX is non-volatile, executes significantly faster, and handles boundary coordinates more cleanly. For troubleshooting index coordinate limits, see #REF! in INDEX: Row/Column index exceeds range boundaries.

2. Replace with Dynamic Array Functions (Excel 365 / 2021+)

Modern Excel includes functions designed specifically for slicing ranges without coordinate calculation:

• Use TAKE(A:A, 100) to get the first 100 rows.
• Use DROP(A:A, 5) to skip the first 5 rows.
• Use FILTER to isolate valid data dynamically.

To learn why volatile functions degrade model performance, consult The Volatile Function Bloat: How INDIRECT and OFFSET kill system performance. If an expanding OFFSET formula triggers spill blockages, see #SPILL! in Volatile Functions: Why OFFSET and INDIRECT cause spill loops. For general structural repairs, read How to Fix #REF! Errors in Excel: A Comprehensive Structural Repair Guide.