Ranges, Number Formats & Formatting¶

IMPORTANT: Always use US format codes. The server automatically translates to the user's locale.

Discoverability note: number display formats live on range; visual styling and auto-fit live on range_format.

Formatting Split Across Two Tools¶

If you are looking for percentage, currency, date, or text display formatting, use range, not range_format. If you are looking for auto-fit, width, height, borders, fill, or font styling, use range_format. If you need the same styling on multiple non-contiguous ranges, use format-ranges instead of repeating format-range.

Formula Errors in Range Reads¶

get-values and get-formulas return formula errors in their values arrays as canonical Excel names such as #REF!, #N/A, and #DIV/0!, not raw negative COM integers. Both results also include cell error details with the affected cell, full formula text when Excel exposes it, raw error code, explanation, and suggested fix.

Excel COM does not reliably identify the exact broken part of a reference. Use the returned cell address and full formula rather than inferring a sub-reference that Excel did not provide.

Quick Pattern: Write, Format, Auto-Fit¶

range(action: 'set-values', range_address: 'A1:D4', values: [[...], [...]])
range(action: 'set-number-format', range_address: 'C2:D4', format_code: '$#,##0.00')
range_format(action: 'auto-fit-columns', range_address: 'A:D')

Writes to Merged Cells¶

set-values and set-formulas reject writes that include merged cells unless the target is only the merged range's top-left cell. The error lists the affected merged ranges. Write to that top-left cell when changing one merged value, or unmerge the range before writing a larger grid.

Quick Pattern: Repeated Section Headers¶

Use format-ranges when the same header or section style repeats across disjoint ranges on one sheet:

range_format(action: 'format-ranges',
    range_addresses: ['A1:G1', 'A12:G12', 'A24:G24'],
    bold: true,
    fill_color: '#243F60',
    font_color: '#FFFFFF',
    horizontal_alignment: 'center')

All target ranges are validated before formatting begins. If any target range is invalid, nothing is formatted.

Quick Pattern: Header Row With Fill Colour¶

set-style('Heading 1') does not apply a fill — use format-range for coloured headers. Pass ALL properties in one call:

range_format(action: 'format-range', range_address: 'A1:D1',
    bold: true,
    fill_color: '#4472C4',
    font_color: '#FFFFFF',
    horizontal_alignment: 'center')

Quick Pattern: Semantic Status Cells¶

Use set-style when the meaning (Good/Bad/Neutral) matters and theme-awareness is useful:

range_format(action: 'set-style', range_address: 'B2:B10', style_name: 'Good')
range_format(action: 'set-style', range_address: 'C2:C10', style_name: 'Bad')

format-range Properties¶

set-style Presets¶

Built-in style names: Normal, Heading 1, Heading 2, Heading 3, Heading 4, Title, Good, Bad, Neutral, Currency, Percent, Comma

range_format(action: 'set-style', range_address: 'A1:D1', style_name: 'Heading 1')

Format Codes¶

All format codes are auto-translated to the user's locale. Use US codes (d/m/y for dates, . for decimal, , for thousands).

The Example column assumes en-US regional settings — rendering is locale-dependent. Excel interprets the , and . in a format code according to the user's locale, and the same is true of the d/m/y date codes. So $#,##0.00 displays as $1,234.56 on en-US but $1.234,56 on de-DE, and mm/dd/yyyy follows the locale's date separator. This is correct behaviour, not a bug — do not rewrite a format code because a screenshot shows swapped separators, and do not tell the user a literal rendering without accounting for their regional settings.

After applying a number format, run range_format auto-fit-columns — formatted values are wider than raw ones and will render as ##### at the default column width.

Actions¶

SetNumberFormat: Apply one format to entire range.

• format_code: Format code from table above

SetNumberFormats: Apply different formats per cell.

• formats: 2D array matching range dimensions
• Example: [["$#,##0.00", "0.00%"], ["mm/dd/yyyy", "General"]]

Threaded Comments (range_link)¶

Use modern threaded comments only when the installed desktop Excel build exposes them:

range_link(action: 'add-threaded-comment', sheet_name: 'Review', cell_address: 'B2', text: 'Check this value')
range_link(action: 'add-threaded-comment-reply', sheet_name: 'Review', cell_address: 'B2', text: 'Confirmed')
range_link(action: 'list-threaded-comments', sheet_name: 'Review', cell_address: 'B2')
range_link(action: 'delete-threaded-comment', sheet_name: 'Review', cell_address: 'B2')

These actions expose local Excel PIA comment text, author, date, and replies. Microsoft 365 service features such as @mentions, assignments, reactions, presence, sharing, and coauthoring state are not available through local Excel COM.

Related range_format Actions¶

• auto-fit-columns: Fit column widths to content after writing data
• auto-fit-rows: Fit row heights to wrapped or multi-line content
• format-range: Apply fills, fonts, borders, and alignment
• format-ranges: Apply one shared formatting payload to multiple ranges on the same worksheet
• set-style: Apply named Excel styles such as Good, Bad, or Heading 1

Hyperlink Lifecycle¶

Use range_link for cell hyperlinks:

For an internal link, omit url and pass a sub_address such as 'Summary'!A1. For partial updates, omitted values remain unchanged; pass an empty string to clear the URL, sub-address, or tooltip.