# How to Handle Excel Conditional Formatting with Formula-Based Rules in OfficeCLI

> Learn to handle Excel conditional formatting with formula-based rules using OfficeCLI. Read and write formulas easily with get and set commands, ensuring validation against the Open XML SDK schema.

- Repository: [OfficeAI/OfficeCLI](https://github.com/iofficeai/OfficeCLI)
- Tags: how-to-guide
- Published: 2026-08-07

---

**OfficeCLI handles Excel conditional formatting with formula-based rules by mapping the OOXML `Expression` type to CLI properties, allowing you to read and write formula expressions via `officecli get` and `officecli set` commands while validating inputs against the Open XML SDK schema.**

Managing formula-based conditional formatting in Excel programmatically requires precise OOXML manipulation. The OfficeCLI open-source tool provides a command-line interface that abstracts these complexities, enabling developers to handle Excel conditional formatting with formula-based rules through simple path selectors. The implementation leverages the Open XML SDK to mirror Excel's internal representation of `<cfRule>` elements with `Type = Expression`.

## Understanding Formula-Based Rules in OfficeCLI

OfficeCLI implements Excel conditional formatting through the `ExcelHandler` class, which mirrors Excel’s OOXML representation. A conditional formatting rule is stored as a `<cfRule>` element inside a `<conditionalFormatting>` container on a worksheet.

When the rule’s **type** is `Expression` (the OOXML enum value `ConditionalFormatValues.Expression`), the rule is *formula‑based*. The handler surfaces this rule in two main phases: **Query** for reading existing rules and **Set** for creating or modifying them.

## Querying Existing Formula Rules

The query phase extracts formula-based rules from existing Excel files through the `PopulateCfNodeFromRule` method located in [`src/officecli/Handlers/Excel/ExcelHandler.Query.Cf.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Handlers/Excel/ExcelHandler.Query.Cf.cs).

When reading a rule, the handler performs the following steps:

- Extracts `rule.Type` and checks for `Expression`
- Reads the `<formula>` child element containing the raw expression
- Retrieves the style reference via `rule.FormatId` (mapped as `dxfId`)
- Returns a `DocumentNode` containing `type = "formula"`, `formula = "…"`, and `dxfId`

This allows the command `officecli get /SheetName/cf[N]` to display the formula text and applied style (DXF) for any formula-based conditional formatting rule.

## Creating and Modifying Formula Rules

The Set phase handles creation and modification of rules in [`src/officecli/Handlers/Excel/ExcelHandler.Set.Tables.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Handlers/Excel/ExcelHandler.Set.Tables.cs) (around line 830). When you supply a property named `formula` (or the alias `ref`), the handler creates a new `ConditionalFormattingRule` with `Type = ConditionalFormatValues.Expression` and inserts a `<formula>` child containing the supplied text.

The workflow follows this sequence:

1. **Parse the selector** – `/SheetName/cf[N]` selects the *N‑th* `<conditionalFormatting>` on the given sheet
2. **Locate the rule** – Within that container, the N‑th `<cfRule>` is targeted
3. **Read/write the formula** – For `Expression` rules, the `<formula>` child holds the user‑supplied expression
4. **Apply style** – The `dxfId` links the rule to a `<dxf>` (differential format) defined elsewhere in the stylesheet

## Validation and Input Requirements

The handler validates inputs through shared helpers in [`ExcelHandler.Set.Tables.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.Set.Tables.cs) to ensure OOXML compliance:

- **Formula syntax**: The supplied formula must **not** start with an extra `=` (Excel expects the raw expression)
- **Range validation**: Referenced ranges must be valid within the worksheet context
- **Index bounds**: The rule index must be within the valid range of existing rules

These checks guarantee that the generated OOXML file will be accepted by Excel without corruption.

## Complete Working Examples

### CLI Workflow for Formula-Based Rules

First, create a differential style (dxf) that the rule will reference:

```bash
officecli set /styles/dxf[1] fillColor=ffeb3b fontColor=000000

```

Add a formula‑based conditional formatting rule to Sheet1 that highlights cells where A1 > 5:

```bash
officecli set /Sheet1/cf[1] \
    range=A2:A10 \
    formula="=$A$1>5" \
    dxfId=1 \
    stopIfTrue=true

```

Retrieve the rule to verify its contents:

```bash
officecli get /Sheet1/cf[1]

```

Expected output structure:

```json
{
  "type": "formula",
  "formula": "=$A$1>5",
  "dxfId": 1,
  "stopIfTrue": true,
  "range": "A2:A10"
}

```

Update an existing rule by changing only the formula:

```bash
officecli set /Sheet1/cf[1] formula="=$A$1<3"

```

### C# SDK Implementation

Using the Open XML SDK directly through OfficeCLI's handler:

```csharp
var handler = new ExcelHandler(@"C:\books\sample.xlsx", editable: true);
var ws = handler.FindWorksheet("Sheet1");

// Create a new <conditionalFormatting> element
var cf = new ConditionalFormatting { 
    SequenceOfReferences = new ListValue<StringValue> { InnerText = "A2:A10" } 
};
ws.Worksheet.Append(cf);

// Create the formula-based rule
var rule = new ConditionalFormattingRule
{
    Type = ConditionalFormatValues.Expression,
    FormatId = 1U,
    StopIfTrue = true
};
rule.Append(new Formula { Text = "=$A$1>5" });
cf.Append(rule);

handler.Flush();

```

## Key Implementation Files

| File | Role |
|------|------|
| [`src/officecli/Handlers/Excel/ExcelHandler.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Handlers/Excel/ExcelHandler.cs) | Core class that opens the workbook, caches worksheets, and exposes high‑level operations |
| [`src/officecli/Handlers/Excel/ExcelHandler.Query.Cf.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Handlers/Excel/ExcelHandler.Query.Cf.cs) | Implements `PopulateCfNodeFromRule` for reading conditional format rules |
| [`src/officecli/Handlers/Excel/ExcelHandler.Set.Tables.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Handlers/Excel/ExcelHandler.Set.Tables.cs) | Handles creation/modification of rules, validates inputs, writes `<formula>` nodes |
| [`schemas/help/xlsx/cfextended.json`](https://github.com/iOfficeAI/OfficeCLI/blob/main/schemas/help/xlsx/cfextended.json) | Schema definition for extended CF properties validating allowed keys |

## Summary

- **OfficeCLI** represents formula-based conditional formatting as OOXML `Expression` type rules, mapping them to CLI-friendly `formula` properties.
- **Query operations** use `PopulateCfNodeFromRule` in [`ExcelHandler.Query.Cf.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.Query.Cf.cs) to extract formula text and style references from `<cfRule>` elements.
- **Set operations** in [`ExcelHandler.Set.Tables.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.Set.Tables.cs) create `ConditionalFormattingRule` objects with `Type = ConditionalFormatValues.Expression` and append `<formula>` children.
- **Validation** ensures formulas omit the leading `=` character and reference valid ranges, preventing corrupt Excel files.
- The **dxfId** parameter links rules to differential formats defined in the stylesheet, enabling visual formatting like colors and borders.

## Frequently Asked Questions

### What is the correct syntax for formula expressions in OfficeCLI?

OfficeCLI expects the raw Excel expression without the leading `=` character. For example, use `formula="=$A$1>5"` in the CLI command, but note that the actual OOXML stores `=$A$1>5` (the handler strips the extra `=` during validation if accidentally provided). According to the implementation in [`ExcelHandler.Set.Tables.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.Set.Tables.cs), the formula text is inserted directly into the `<formula>` element as-is.

### How does OfficeCLI validate formula-based conditional formatting rules?

The handler validates three critical aspects: the formula must not contain syntax errors that would invalidate the OOXML, any referenced cell ranges must exist within the target worksheet, and the rule index must not exceed the bounds of the conditional formatting collection. These checks occur in the shared validation helpers within [`src/officecli/Handlers/Excel/ExcelHandler.Set.Tables.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/src/officecli/Handlers/Excel/ExcelHandler.Set.Tables.cs) before any modifications are written to disk.

### Can I update existing formula rules without recreating the entire conditional format?

Yes. OfficeCLI supports partial updates through the `set` command with specific selectors. By targeting `/SheetName/cf[N]` with only the properties you want to change (such as `formula="=$A$1<3"`), the handler modifies the existing `<cfRule>` element while preserving other attributes like `dxfId` and `stopIfTrue`. This follows the standard CRUD pattern implemented across all OfficeCLI handlers.

### What is the relationship between dxfId and conditional formatting rules?

The `dxfId` (Differential Format ID) links a conditional formatting rule to a specific style definition stored in the workbook's `<dxfs>` collection. When you specify `dxfId=1`, the rule references the first differential format defined in [`styles.xml`](https://github.com/iOfficeAI/OfficeCLI/blob/main/styles.xml), which contains formatting instructions for fill color, font color, borders, or number formats. The [`schemas/help/xlsx/cfextended.json`](https://github.com/iOfficeAI/OfficeCLI/blob/main/schemas/help/xlsx/cfextended.json) schema documents this relationship, requiring that referenced `dxfId` values must exist in the stylesheet.