Sort behavior

How Excel’s “Sort” operation (Data tab) works, and how Apiary reproduces it.

What moves with the sorted rows

• Formulas move with their cells. The formula text itself is relocated. Bare relative references adjust to reflect the new row position (e.g. =$E$1+A2 in row 2, moved to row 1, becomes =$E$1+A1). Absolute references (with $) stay as-is. Sheet-prefixed references — even referencing the current sheet — also stay as-is (e.g. =Sheet1!A2 remains =Sheet1!A2 after sort, even when sorting Sheet1).
• Styles and formatting move with cells. Bold, italic, underline, font color, background color, number format — all travel with the data.
• Row heights stay in place. Unlike cell styles, row heights are not moved by sort. The data moves into different rows, but the row heights remain at their original positions.

What does not change

• External references are not rewritten. A formula outside the sort range that says =A1 still says =A1 after sorting. Since the value at A1 may now be different (a different row’s data landed there), the external formula sees the new value. This is intentional Excel behavior.

Cross-row references within the sort range

Excel treats each row’s move independently. A formula’s references are adjusted for its own positional change but not for where other rows went. This means cross-row references within the sorted range can break, yielding #REF! errors. (Example: =B1+B2 in a row that moved can become =#REF!+B1.)

Blank cells

Blanks always sort to the bottom, regardless of whether the sort is ascending or descending.

Mixed types

In ascending order: Numbers first (sorted numerically), then Strings (sorted alphabetically, case-insensitive), then Booleans (FALSE before TRUE), then Errors (treated as equal among themselves), then Blanks.

In descending order: the groups reverse (only Blanks remain at the bottom regardless of direction), and within each group the order reverses.

Hidden rows

Hidden rows within the sort range are excluded from the sort entirely. They stay in place with their original values and remain hidden. Only visible rows participate in the sort: their values are reordered among the visible row positions, skipping over any hidden rows. This means the overall sequence of values (including hidden rows) may not be in strict sorted order after sorting.

Merged cells

Excel refuses to sort a range containing merged cells (throws an error). The data is left unchanged.

Stability

Excel’s sort is stable: rows with equal sort-key values preserve their original relative order.

Multi-key sorting

Up to 64 sort keys (since Excel 2007); each can be ascending or descending independently.

Header row

The optional “My data has headers” setting excludes the first row from sorting.

Empty rows in sort range

Empty rows within the sort range are treated as all-blank and sort to the bottom.

How Apiary reproduces this

The Workbook.sortCells method follows the same semantics:

• Collation uses operatorCollate (the same cross-type comparison used elsewhere in Apiary) with a wrapper (editSortCollate) that places blanks always at the bottom and reverses the error group position with sort direction, matching Excel’s order.
• Formula adjustment uses copy-paste semantics: bare relative row references are offset by the cell’s row delta, while absolute references (with $) and sheet-prefixed references are unchanged. This is implemented by walking the parsed formula AST (offsetFormulaRows), matching how Excel adjusts formulas during sort. Each row’s formulas are adjusted for the row’s own positional change only — matching Excel’s behavior including the possibility of broken cross-row references within the sorted range.
• External references are deliberately not rewritten, so they continue to point to the same cell addresses and naturally see updated values after recalculation.
• Merged cells and multi-row spill ranges are detected and cause a thrown error, matching Excel’s refusal to sort such ranges. A single-row spill is relocatable — it fits in one sortable row — but only when its anchor lies within the sorted columns, since sortCells moves the anchor to the row’s new position and lets recalculation re-spill it there (a body extending past the range’s right edge re-materializes at the new anchor). A single-row spill whose anchor sits outside the sorted columns (reaching into the range from the left) is refused: sortCells touches only the requested columns, so it cannot move that anchor. Excel’s GUI Data ▸ Sort instead auto-expands the selection to whole rows and moves the whole spill; sortCells is deliberately stricter here, sorting exactly the columns it was given rather than reordering ones the caller did not request.
• Hidden rows (row height 0) are excluded from sorting and left in place, matching Excel.
• Stability is guaranteed by the ECMAScript specification, which requires Array.prototype.sort to be stable.