> For the complete documentation index, see [llms.txt](https://docs.rowsncolumns.app/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.rowsncolumns.app/configuration/features/conditional-formatting.md).

# Conditional formatting

CanvasGrid accepts array of `conditionalFormats` prop which can be used to inject formatting rules based on the value of a cell. Formula values are supported.

```tsx
import { SpreadsheetProvider, CanvasGrid } from "@rowsncolumns/spreadsheet";

const MySpreadsheet = () => {
  return (
    <CanvasGrid
      conditionalFormats={[
        {
          ranges: [
            {
              startRowIndex: 1,
              endRowIndex: 1000,
              startColumnIndex: 2,
              endColumnIndex: 2,
            },
          ],
          booleanRule: {
            condition: {
              type: "NUMBER_LESS",
              values: [
                {
                  userEnteredValue: "0",
                },
              ],
            },
            format: {
              backgroundColor: "red",
            },
          },
        },
      ]}
    />
  );
};

const App = () => (
  <SpreadsheetProvider>
    <MySpreadsheet />
  </SpreadsheetProvider>
);

```

### Gradient scale or heatmaps

```typescript
conditionalFormats: [
  {
    ranges: [
      {
        startRowIndex: 11,
        endRowIndex: 26,
        startColumnIndex: 7,
        endColumnIndex: 8,
      },
    ],
    gradientRule: {
      minpoint: {
        color: "#FFCF54",
        type: "MIN",
        // value: 10 Automatically calculated from range
      },
      midpoint: {
        color: "#FFE8A3",
        type: "MIN",
        // value: 12, Automatically calculated from range
      },
      maxpoint: {
        color: "#FFFFFF",
        type: "MAX",
        // value: 21, Automatically calculated from range
      },
    },
  },
],
```

### Data bars

Paint a horizontal bar inside each cell, scaled by the cell's value relative to the range min/max. Round-trips XLSX + ODS.

```typescript
conditionalFormats: [
  {
    ranges: [
      {
        startRowIndex: 1,
        endRowIndex: 20,
        startColumnIndex: 3,
        endColumnIndex: 3,
      },
    ],
    dataBarRule: {
      color: "#638EC6",
      minpoint: { type: "MIN" },
      maxpoint: { type: "MAX" },
      // Optional: separate color for negative values
      negativeColor: "#DC2626",
      // Optional: render direction ("leftToRight" / "rightToLeft")
      direction: "leftToRight",
      // Optional: hide the cell value, show only the bar
      showValue: true,
    },
  },
],
```

### Icon sets

Stamp an icon to the left of each cell based on which bucket the value falls in. Many built-in icon families ship: `3TrafficLights1`, `3TrafficLights2`, `3Arrows`, `3Symbols`, `4Rating`, `5Rating`, `5Arrows`, etc.

```typescript
conditionalFormats: [
  {
    ranges: [
      {
        startRowIndex: 1,
        endRowIndex: 20,
        startColumnIndex: 4,
        endColumnIndex: 4,
      },
    ],
    iconSetRule: {
      iconSet: "3TrafficLights1",
      // Thresholds split the value range into N buckets — for a 3-icon
      // set we need 2 thresholds (low/mid/high).
      thresholds: [
        { type: "PERCENT", value: 33 },
        { type: "PERCENT", value: 66 },
      ],
      // Optional: reverse the icon order (so red lights up on high
      // values instead of low).
      reverse: false,
      // Optional: show only the icon, hide the cell value
      showValue: true,
    },
  },
],
```

## Supported conditions

```typescript
// Supported condition types
export const CONDITION_TYPES = [
  "NUMBER_GREATER",
  "NUMBER_GREATER_THAN_EQ",
  "NUMBER_LESS",
  "NUMBER_LESS_THAN_EQ",
  "NUMBER_EQ",
  "NUMBER_NOT_EQ",
  "NUMBER_BETWEEN",
  "NUMBER_NOT_BETWEEN",
  "TEXT_CONTAINS",
  "TEXT_NOT_CONTAINS",
  "TEXT_STARTS_WITH",
  "TEXT_ENDS_WITH",
  "TEXT_EQ",
  "TEXT_IS_EMAIL",
  "TEXT_IS_URL",
  "DATE_EQ",
  "DATE_BEFORE",
  "DATE_AFTER",
  "DATE_ON_OR_BEFORE",
  "DATE_ON_OR_AFTER",
  "DATE_BETWEEN",
  "DATE_NOT_BETWEEN",
  "DATE_IS_VALID",
  "ONE_OF_RANGE",
  "ONE_OF_LIST",
  "BLANK",
  "NOT_BLANK",
  "CUSTOM_FORMULA",
  "BOOLEAN",
  "TEXT_NOT_EQ",
  "DATE_NOT_EQ",
] as const;
```

## Adding conditional format editor UI

Spreadsheet comes with a default conditional format editor.

Right click on any cell and select `Conditional format editor` . Or you can invoke the function `onRequestConditionalFormat` from useSpreadsheetState hook

<figure><img src="/files/1dASGN09BWrNJTQPAxMe" alt=""><figcaption><p>Conditional format editor</p></figcaption></figure>

```tsx
import { SpreadsheetProvider, CanvasGrid, defaultSpreadsheetTheme } from "@rowsncolumns/spreadsheet";
import { ConditionalFormatDialog, ConditionalFormatEditor } from "@rowsncolumns/spreadsheet-state"

const MySpreadsheet = () => {
  const [conditionalFormats, onChangeConditionalFormats] = useState<
      ConditionalFormatRule[]
    >([]);
  const [theme, onChangeTheme] = useState<SpreadsheetTheme>(
    defaultSpreadsheetTheme
  );
  const {
      onRequestConditionalFormat,
      createHistory,
      getSheetId,
      getSheetName,
      rowCount,
      columnCount,
      activeSheetId,
      onCreateConditionalFormattingRule,
      onUpdateConditionalFormattingRule,
      onDeleteConditionalFormattingRule,
      onPreviewConditionalFormattingRule,
  } = useSpreadsheetState()
  return (
    <>
      <CanvasGrid
        conditionalFormats={conditionalFormats}
        onRequestConditionalFormat={onRequestConditionalFormat}      
      />
  
      <ConditionalFormatDialog>
        <ConditionalFormatEditor
          sheetId={activeSheetId}
          rowCount={rowCount}
          columnCount={columnCount}
          getSheetName={getSheetName}
          getSheetId={getSheetId}
          theme={theme}
          conditionalFormats={conditionalFormats}
          onCreateRule={onCreateConditionalFormattingRule}
          onDeleteRule={onDeleteConditionalFormattingRule}
          onUpdateRule={onUpdateConditionalFormattingRule}
          onPreviewRule={onPreviewConditionalFormattingRule}
        />
      </ConditionalFormatDialog>
    </>
  );
};

const App = () => (
  <SpreadsheetProvider>
    <MySpreadsheet />
  </SpreadsheetProvider>
);

```