OFFSET past the edge of the sheet errors differently in LibreOffice

OFFSET is the engine behind a lot of dynamic ranges, rolling windows and scroll-through dashboards, and the one thing all of those have in common is that sooner or later the offset runs off the edge of the sheet. Microsoft is explicit about what happens then: “If rows and cols offset reference over the edge of the worksheet, OFFSET returns the #REF! error value.” LibreOffice Calc returns #VALUE! instead.

Executed results

Setups per row: B3 = 99 for the first row; A2:A4 = 10, 20, 30 for the second; B2 = 5 for the last.

The Excel column is the documented-expected value from our test corpus, sourced from Microsoft's OFFSET reference page; we did not run desktop Excel. The LibreOffice column is what our harness computed in LibreOffice Calc 25.8.7.3. Three of the four cases match exactly, which is the useful headline: OFFSET itself is not a porting hazard, its failure mode is. Excel for the web — a separate application from the desktop product, and the third engine we execute — returned #REF! for =OFFSET(A1,-1,0) when we recalculated the corpus on OneDrive on 2026-09-01, matching the documented column and Google Sheets, so #VALUE! is LibreOffice’s alone.

Consistent across LibreOffice versions

=OFFSET(A1,-1,0) returned #VALUE! in 24.2.0.3, 24.8.7.2, 25.2.0.3 and 25.8.7.3 alike, and the three well-behaved cases returned 99, 60 and 5 in every build. Nothing changed between releases here.

Why it happens

Excel classifies an offset that lands outside the sheet as a reference failure, so the cell shows #REF!. LibreOffice classifies the negative row offset as an argument that is out of its allowed range, and out-of-range arguments in LibreOffice surface as #VALUE! — the same treatment it gives an out-of-range lookup index (see VLOOKUP with a bad column index) and out-of-domain maths such as =SQRT(-16). The consequence is worth stating plainly: #REF! is much rarer in LibreOffice than a formula author from Excel expects, so error handling keyed to it quietly stops firing.

How to migrate safely

Do not test for the error code. =IFERROR(OFFSET(...),0) catches every engine's version; a check written specifically for #REF! catches only the Microsoft one. Guarding the bounds yourself is better still, because then neither engine errors: =IF(ROW(anchor)+rows<1,"",OFFSET(anchor,rows,cols)) refuses the impossible move before the function sees it.

The structural fix is to stop using OFFSET for dynamic ranges. INDEX builds the same references without a volatile recalculation on every edit, and every INDEX case in our corpus matched in LibreOffice 25.8.7.3 — so =SUM(INDEX(A:A,start):INDEX(A:A,finish)) is both the faster and the more portable pattern. When you do keep OFFSET, treat any formula whose row or column argument can go negative as a review item during migration; that is precisely the set of formulas whose error identity changes.

Honest limits

The Excel column is Microsoft's documented behaviour recorded in our test corpus, not a value we executed in Excel. The Google Sheets column is executed output from a Drive import on 2026-08-29: Sheets returns #REF! for the out-of-bounds case, matching Excel’s documentation, so LibreOffice is the only engine that answers #VALUE!. The LibreOffice values are executed output from LibreOffice Calc 25.8.7.3 and were reproduced in our three earlier version runs.

Check before you migrate

A note on which Excel this is. The Excel column in the tables above is Microsoft’s documented behaviour for desktop Excel, as recorded in our test corpus — we do not run desktop Excel, and no value in that column is a measurement. Excel for the web is a different application with its own calculation engine, and that one we do run (recalculated on OneDrive, 2026-09-01). Its measured results are published on each function’s own page rather than in these guide tables. Because we have no desktop run to compare against, a disagreement between an Excel-web measurement and the documented column is genuinely ambiguous: it may mean the web engine diverges from the desktop one, or that the documentation is wrong about both. We do not claim to know which.