excel sort

Excel: Sort

Sort rows in a worksheet or a specific range by one or more columns.

Path: /{SheetName} (sheet-level) or /{SheetName}/A1:D50 (range-level)

Properties

Property	Default	Description
sort	(required)	Sort spec: "COL [DIR][, COL [DIR] ...]". Column is a letter (A, B, AA…up to XFD). Direction is asc or desc (optional, defaults to asc).
sortHeader	false	Treat the first row of the sort range as a header row (excluded from reorder).

Sort spec syntax

COL [DIR] [, COL [DIR] ...]

• COL — column letter(s): A, B, AA, XFD. Column names (e.g. "Salary") are not accepted; use letters.
• DIR — asc (ascending, default) or desc (descending). Optional; omit for ascending.
• Comma-separated for multi-key sort (first key is primary, rest are tiebreakers).

Examples

Sheet-level sort (auto-detects full used range)

# Sort by column B ascending (default direction)
officecli set data.xlsx /Sheet1 --prop sort="B"

# Sort by column C descending
officecli set data.xlsx /Sheet1 --prop sort="C desc"

# Sort with header row excluded from reorder
officecli set data.xlsx /Sheet1 --prop sort="C desc" --prop sortHeader=true

# Multi-key: department ascending, then salary descending
officecli set data.xlsx /Sheet1 --prop sort="A asc, D desc" --prop sortHeader=true

Range-level sort (explicit range path)

# Sort rows 2-50 in columns A:F by column B descending
officecli set data.xlsx /Sheet1/A2:F50 --prop sort="B desc"

# Multi-key on a specific range
officecli set data.xlsx /Sheet1/A1:H100 --prop sort="C asc, E desc" --prop sortHeader=true

Clear sort state

# Remove sortState metadata (does not re-sort data)
officecli set data.xlsx /Sheet1 --prop sort=none

Sort behavior

Aspect	Behavior
Sort order	Numbers before text, empty cells last. Case-insensitive text comparison (matches Excel's default caseSensitive=false). NaN and Infinity are classified as text.
Header row	When sortHeader=true, the first row of the range is excluded from reorder. Default is false (all rows participate).
Range detection	Sheet-level (/{SheetName}) auto-detects the full used range (row 1 to last row, column A to last populated column). Range-level uses the explicit path.
Reversed ranges	Normalized automatically (C5:A1 → A1:C5).
SortState metadata	Written after sort per CT_Worksheet schema order. Placed after autoFilter (if present) or sheetData.
Calc chain	Deleted after sort to prevent stale formula values.

Sidecar data movement

Sort physically moves rows and rewrites associated metadata so sidecar data follows its row:

Sidecar	Behavior
Hyperlinks	Cell ref rewritten to new row position
Comments (legacy + threaded)	Cell anchor ref rewritten
Data validations	sqref tokens rewritten per cell
Conditional formatting	sqref ranges rewritten
Drawing anchors	fromRow / toRow adjusted to follow the row's new position
Protected ranges	sqref rewritten

Rejection rules

Sort refuses to proceed and throws an error in these cases:

Condition	Error
Range contains merged cells	Cannot sort range containing merged cells — unmerge first or narrow the range
Range contains formulas	Cannot sort range containing formulas — sort would corrupt relative row references in formula text; paste-as-values first
Range contains shared formulas	Cannot sort range containing shared formulas — rewrite as per-cell formulas first
Protected sheet	Cannot sort a protected sheet — unprotect first, or set sheetProtection@sort="false" to allow sort while protected
merge + sort in same call	Rejected up front to prevent half-written state — split into two calls
Invalid column letter	Column names (e.g. "Salary") rejected; only column letters (A–XFD) accepted
Column outside range	Sort key column must lie within the sort range
Direction-only spec	sort=asc or sort=desc rejected (missing column letter)
Empty sort value on range path	sort value cannot be empty (sheet-level empty/none clears sortState)

Notes

• Sort comparison is case-insensitive by default, matching Excel's caseSensitive=false.
• Single-row or empty data regions are no-ops (no sortState written).
• Formulas outside the sort range that reference cells inside it will go stale after sort — same limitation as Excel's own row-move behavior.
• The sort property is also accepted on range-level paths in SetRange (e.g., /Sheet1/A1:D50 --prop sort="B desc"), not just sheet-level.

See Also

• Sheet - Sheet properties (freeze, zoom, tabColor, name, etc.)
• Cell set - Cell-level properties
• Excel Reference - All Excel elements

---

Based on OfficeCLI v1.0.64