Why doesn't Google Sheets show a #REF! error after a sort breaks a cross-row formula?

`#REF!` triggers when a formula references a cell that no longer exists - a deleted row or an out-of-bounds address. Sorting reorders data within existing row addresses; those cells still exist, they just hold different values. The formula evaluates normally against whatever is now in that row. This is what makes sort-induced model corruption dangerous: it looks like a working model delivering wrong numbers.

Does the SORT() function in Google Sheets modify the source data?

No. `=SORT('P&L'!A2:F200, 3, -1)` writes a sorted copy into the cells where the formula sits. The source range A2:F200 is unchanged, and every formula on other tabs referencing that source continues to work correctly. This is the fundamental difference from Data > Sort range, which physically moves rows in place. If you need a sorted view for a board pack or summary tab, SORT() is the right tool - it never touches the model.

Can I sort only the visible (filtered) rows without affecting hidden rows?

Technically yes, but it creates a worse problem. If you select only visible cells and run Data > Sort range on that selection, Google Sheets sorts only those rows while hidden rows stay in place. When the filter clears, visible and hidden data are mixed into two different sort orders. The safe way to get a sorted view of filtered data is `=SORT(FILTER(A2:F500, E2:E500="West"), 3, -1)` - a formula that creates the view without modifying source rows at all.

My debt schedule's opening balances are all wrong after a colleague sorted the tab. How do I fix it?

Open version history (File > Version history > See version history) and restore the last version before the sort. That's the fastest fix. If version history doesn't have a clean state, you'll need to rebuild: find the first hard-coded opening balance (usually the starting draw date), verify it's correct, then re-enter the prior-period reference formula for each subsequent row in chronological order. Then protect the range. For models where this risk is real, switching from sequential row references to EDATE-based MATCH lookups (as shown in the article above) eliminates the problem permanently.

Is this a Google Sheets bug or expected behavior?

Expected behavior, documented. Google's filter documentation makes clear that sort operations apply to the underlying data range, not to the filtered view. The sort function has no awareness of formula dependencies between rows - it moves data and expects formulas to handle it. Most production financial models deal with this by separating sortable data (transactions, reference tables) from sequential formula logic (projections, schedules), and never sorting the latter. If a range has period-over-period formula chains, treat its row order as load-bearing structure.

Google Sheets Formula Breaks After Sort in Filtered Range

Your boss catches it when the EBITDA bridge doesn't tie. Not when you're building it.

Why Sorting Breaks Cross-Row Formulas

Google Sheets stores formulas at fixed row addresses. When you sort, the data physically moves to new rows. A formula like =E3 (reading the prior period's ending balance) doesn't track where its data went - it reads whatever row 3 holds after the sort.

Filters compound the problem. Filters in Google Sheets are a view mechanism, not a data partition. According to Google's Sheets support documentation, when you sort via the column header dropdown while a filter is active, the sort operates on the entire underlying data range - all rows, including hidden ones. Most analysts assume they're sorting only what they can see. They're not.

Two failure modes show up repeatedly in FP&A models:

Sequential chain breaks. Any time-series model where row N references row N-1 (opening balance equals prior period closing balance, interest accrual builds on prior principal) breaks the moment rows shuffle. Row order is the logic.

Cross-tab position references. If your Returns Analysis tab reads ='Revenue'!B47 expecting a specific SKU or period, and someone sorts the Revenue tab, B47 holds different data. No error surface anywhere.

The Exact Mechanics of a Filtered Sort

Here's the step-by-step of what happens when you use the sort arrow in a filter dropdown:

Your filter shows 80 of 500 rows - say, West region only.
You click the sort arrow to sort those visible rows by revenue, descending.
Google Sheets reorders all 500 rows by that column.
The filter re-evaluates against the new row order - the same rows may still pass (region is unchanged), but their row positions have shifted.
Every formula referencing a row number now reads different data.

The second failure mode is worse and harder to detect. Users sometimes try to sort only visible rows by manually selecting filtered cells and running Data > Sort range on just that selection. This sorts only the selected (visible) cells and leaves hidden rows untouched. When the filter clears, the visible and hidden data are misaligned at the row level - two different sort orders spliced together. Any formula crossing a row boundary reads nonsense.

Where FP&A Models Break Most Often

Debt amortization schedules. A $47.3M revolver modeled across 24 months:

Row 3: Jan-26  Opening: $47,300,000     Draws: $0          Ending: =B3+C3-D3
Row 4: Feb-26  Opening: =E3             Draws: $2,100,000  Ending: =B4+C4-D4
Row 5: Mar-26  Opening: =E4             Draws: $1,750,000  Ending: =B5+C5-D5

Sort by Draws descending and the chain breaks in one move. Row 4's opening balance =E3 still reads row 3 - but row 3 now holds whichever month had the highest draws. The opening-to-closing cascade is gone.

SKU contribution margin models. Cross-tab row references like this are everywhere in multi-tab models:

='P&L'!H12 / 'P&L'!B12

Sort the P&L tab and H12 now contains a different SKU's gross profit while B12 holds a different SKU's revenue. The formula divides two unrelated rows. Because it still produces a number, nothing flags it.

FCFF models pulling period assumptions. Compare these two approaches to pulling revenue from the P&L tab into a DCF:

Broken (position-anchored):

='P&L'!C5

Safe (lookup-based):

=SUMIFS('P&L'!C:C, 'P&L'!B:B, ">=" & Assumptions!$B$3, 'P&L'!B:B, "<=" & Assumptions!$B$4)

The SUMIFS version finds data by matching date values, not row numbers. A sort on the P&L tab changes nothing about which rows match the date range criteria.

How to Know if Your Model Is Already Corrupted

No error appears. The formulas return numbers - just wrong ones. What to check:

Add a helper column with =A4-A3 where column A is your period dates. If you get non-zero values where the step should be exactly 1 month, your rows are out of sequence. For an already-sorted model, check 3-4 opening balances manually against their prior period endings. A mismatch anywhere in the chain means the sort hit something it shouldn't have.

For cross-tab references, add a reconciliation row that sums via SUMIFS and compares to the direct-reference total. A gap that wasn't there before a sort confirms position reference corruption.

The Fix: Stop Sorting Source Data

The model stays clean if sortable ranges never contain position-anchored cross-row dependencies. Three paths to get there:

1. Use SORT() to create a view, not run an operation.

Instead of sorting the source data, output a sorted display with the SORT function:

=SORT('Transactions'!A2:F500, 3, -1)

This writes a sorted copy starting wherever you put the formula. The source range and every formula referencing it are untouched. Your board pack gets sorted contribution margins by SKU; your debt schedule keeps its period chain intact. For filtered + sorted views, combine them:

=SORT(FILTER('Transactions'!A2:F500, 'Transactions'!E2:E500="West"), 3, -1)

One formula. Sorted West-region transactions. Source data not touched. For a deeper look at the SORT function's syntax, see our SORT function guide.

2. Replace sequential row references with lookup-based formulas.

A debt schedule where each opening balance looks up the prior month's closing balance by date - not by row position:

=IFERROR(
  INDEX('Debt Schedule'!E:E,
        MATCH(EDATE(A4, -1), 'Debt Schedule'!A:A, 0)),
  Assumptions!$B$2
)

This pulls the prior month's ending balance by matching on the date value. Sort the debt schedule tabs by any column - this formula still finds February's closing balance for March's opening, because it's looking for EDATE(Mar, -1) = Feb, not for "whatever is in the row above."

3. Lock the range from sorting.

If the model structure genuinely requires sequential rows - time-series projections, waterfall builds, staged cash flow models - protect that range. Data > Protect sheets and ranges, lock the rows, add a description: "Sequential formula chain - do not sort." A locked range prevents the accidental sort that corrupts the model three weeks before board prep.

SORT() vs. Menu Sort: Quick Reference

Action	Reorders Source Rows?	Safe for Cross-Row Formulas?
Data > Sort range	Yes	No
Filter dropdown sort arrow	Yes, all rows	No
`=SORT(range, col, order)`	No	Yes
`=QUERY(range, "SELECT * ORDER BY Col3 DESC")`	No	Yes

As of June 2026, both SORT() and QUERY() with ORDER BY produce sorted output views without touching source data. Either one works for building the sorted display your stakeholders want while keeping model logic intact.

If you're inheriting a model that's already been sorted and you're not sure which formulas got scrambled, ModelMonkey can audit cross-tab references across your entire workbook and flag formulas that reference specific row positions in ranges that look like they've been sorted - it reads the workbook structure rather than making you trace tabs manually.