Conditional Formatting

Conditional formatting changes the format of a cell automatically when a condition holds, so that important data and trends stand out. It supports multiple conditions and formatting options, which makes the data easier to read at a glance.

Plugin Mode

Installation

Shell
pnpm add @univerjs/sheets-conditional-formatting @univerjs/sheets-conditional-formatting-ui

Usage

TypeScript
import { LocaleType, mergeLocales, Univer } from '@univerjs/core'import { UniverSheetsConditionalFormattingPlugin } from '@univerjs/sheets-conditional-formatting'import { UniverSheetsConditionalFormattingUIPlugin } from '@univerjs/sheets-conditional-formatting-ui'import SheetsConditionalFormattingUIEnUS from '@univerjs/sheets-conditional-formatting-ui/locale/en-US'import '@univerjs/sheets-conditional-formatting-ui/lib/index.css'import '@univerjs/sheets-conditional-formatting/facade'const univer = new Univer({  locale: LocaleType.EN_US,  locales: {    [LocaleType.EN_US]: mergeLocales(      SheetsConditionalFormattingUIEnUS,     ),  },})univer.registerPlugin(UniverSheetsConditionalFormattingPlugin)univer.registerPlugin(UniverSheetsConditionalFormattingUIPlugin)

Mobile support

Configure the mobile editor first. In the plugin-mode setup on this page, replace the desktop registrations below with the mobile exports from the same packages, and complete the replacement before opening a document. Keep the model plugins, styles, locales, and Facade imports, and do not register both variants.

PackageDesktop registrationMobile registration
@univerjs/sheets-conditional-formatting-uiUniverSheetsConditionalFormattingUIPluginUniverSheetsConditionalFormattingMobileUIPlugin

Preset Mode

Installation

Shell
pnpm add @univerjs/preset-sheets-conditional-formatting

Usage

TypeScript
import { UniverSheetsConditionalFormattingPreset } from '@univerjs/preset-sheets-conditional-formatting'import UniverPresetSheetsConditionalFormattingEnUS from '@univerjs/preset-sheets-conditional-formatting/locales/en-US'import { UniverSheetsCorePreset } from '@univerjs/preset-sheets-core'import UniverPresetSheetsCoreEnUS from '@univerjs/preset-sheets-core/locales/en-US'import { createUniver, LocaleType, mergeLocales } from '@univerjs/presets'import '@univerjs/preset-sheets-core/lib/index.css'import '@univerjs/preset-sheets-conditional-formatting/lib/index.css'const { univerAPI } = createUniver({  locale: LocaleType.EN_US,  locales: {    [LocaleType.EN_US]: mergeLocales(      UniverPresetSheetsCoreEnUS,      UniverPresetSheetsConditionalFormattingEnUS,     ),  },  presets: [    UniverSheetsCorePreset(),    UniverSheetsConditionalFormattingPreset(),   ],})

Facade API

Complete Facade API type definitions are in FacadeAPI.

Importing

Plugin mode note

The Facade package is imported manually only in plugin mode. Preset mode already includes it, so no additional import is required.

TypeScript
import '@univerjs/sheets-conditional-formatting/facade'

Create Conditional Formatting Rules

FWorksheet.newConditionalFormattingRule() returns an FConditionalFormattingBuilder. Call its methods in a chain to define the rule.

Member methods on FConditionalFormattingBuilder include:

MethodDescription
buildBuilds the rule
copyDeep-clones the current builder
createCfIdCreates a conditional format ID
getRangesGets the ranges of the current conditional format
setRangesSets the range the rule applies to
setAverageSets an average-value rule
setUniqueValuesSets a unique-values rule
setDuplicateValuesSets a duplicate-values rule
setRankSets a rank rule
setIconSetSets an icon-set rule
setColorScaleSets a color-scale rule
setDataBarSets a data-bar rule
setBackgroundSets the background color
setBoldSets bold text
setFontColorSets the font color
setItalicSets italic text
setStrikethroughSets strikethrough
setUnderlineSets underline
whenCellEmptyFires when the cell is empty
whenCellNotEmptyFires when the cell is not empty
whenDateFires when the date falls in a specific period
whenFormulaSatisfiedFires when the formula evaluates to true
whenNumberBetweenFires when the number is between two values, or equal to one of them
whenNumberEqualToFires when the number equals the given value
whenNumberGreaterThanFires when the number is greater than the given value
whenNumberGreaterThanOrEqualToFires when the number is greater than or equal to the given value
whenNumberLessThanFires when the number is less than the given value
whenNumberLessThanOrEqualToFires when the number is less than or equal to the given value
whenNumberNotBetweenFires when the number is not between two values and not equal to either
whenNumberNotEqualToFires when the number does not equal the given value
whenTextContainsFires when the text contains the specified value
whenTextDoesNotContainFires when the text does not contain the specified value
whenTextEndsWithFires when the text ends with the specified value
whenTextEqualToFires when the text equals the given value
whenTextStartsWithFires when the text starts with the specified value
TypeScript
const fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getActiveSheet()// Create a conditional formatting rule that sets the cell format to italic, red background, and green font color when the cell is not empty.const fRange = fWorksheet.getRange('A1:T100')const rule = fWorksheet.newConditionalFormattingRule()  .whenCellNotEmpty()  .setRanges([fRange.getRange()])  .setItalic(true)  .setBackground('red')  .setFontColor('green')  .build()fWorksheet.addConditionalFormattingRule(rule)

Get Conditional Formatting Rules

  • FWorksheet.getConditionalFormattingRules() gets all conditional formatting rules of the worksheet
  • FRange.getConditionalFormattingRules() gets all conditional formatting rules of the range
TypeScript
const fWorkbook = univerAPI.getActiveWorkbook()// Get all conditional formatting rules of the active worksheetconst fWorksheet = fWorkbook.getActiveSheet()const rulesOfSheet = fWorksheet.getConditionalFormattingRules()// Get all conditional formatting rules of the range A1:T100const fRange = fWorksheet.getRange('A1:T100')const rulesOfRange = fRange.getConditionalFormattingRules()

Remove Conditional Formatting Rules

FWorksheet.deleteConditionalFormattingRule(cfId) removes a rule from the worksheet. cfId is the ID of the rule.

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getActiveSheet()const rules = fWorksheet.getConditionalFormattingRules()// Remove the first conditional formatting rulefWorksheet.deleteConditionalFormattingRule(rules[0]?.cfId)

Update Conditional Formatting Rules

FWorksheet.setConditionalFormattingRule(cfId, rule) replaces a rule in the worksheet. cfId is the ID of the rule to replace, and rule is the new rule.

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getActiveSheet()// Create a conditional formatting rule that sets the cell format to italic, red background, and green font color when the cell is not empty.const fRange = fWorksheet.getRange('A1:T100')const rule = fWorksheet.newConditionalFormattingRule()  .whenCellNotEmpty()  .setRanges([fRange.getRange()])  .setItalic(true)  .setBackground('red')  .setFontColor('green')  .build()fWorksheet.addConditionalFormattingRule(rule)// Modify the first rule to apply to a new rangeconst rules = fWorksheet.getConditionalFormattingRules()const newRuleRange = fWorksheet.getRange('A1:D10')fWorksheet.setConditionalFormattingRule(rules[0]?.cfId, { ...rules[0], ranges: [newRuleRange.getRange()] })

Modify the Priority of Conditional Formatting Rules

FWorksheet.moveConditionalFormattingRule(cfId, toCfId, type) changes the position of a rule in the priority order. cfId is the rule to move, toCfId is the rule it moves relative to, and type is the move type (e.g., 'before', 'after').

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getActiveSheet()const rules = fWorksheet.getConditionalFormattingRules()// Move the third rule before the first ruleconst rule = rules[2]const targetRule = rules[0]fWorksheet.moveConditionalFormattingRule(rule?.cfId, targetRule?.cfId, 'before')

Clear All Conditional Formatting Rules

  • FWorksheet.clearConditionalFormatRules() clears all conditional formatting rules of the worksheet
  • FRange.clearConditionalFormatRules() clears all conditional formatting rules of the range
TypeScript
const fWorkbook = univerAPI.getActiveWorkbook()// Clear all conditional formatting rules of the active worksheetconst fWorksheet = fWorkbook.getActiveSheet()fWorksheet.clearConditionalFormatRules()// Clear all conditional formatting rules of the range A1:T100const fRange = fWorksheet.getRange('A1:T100')fRange.clearConditionalFormatRules()

Reuse rule descriptions and previews

To build a custom rule list, import getRuleDescribe and ConditionalFormattingRulePreview from @univerjs/sheets-conditional-formatting-ui. Pass the entire rule to getRuleDescribe(rule, localeService), and pass only its configuration (rule.rule) to the preview. The preview renders the configured appearance and does not evaluate worksheet cells. Render it within Univer's React context.

CF_PANEL_KEY identifies the built-in panel, so component overrides can target it. Its IConditionFormattingPanelProps.onEditorExit callback runs when editing ends: after a submit, after a cancel, or when the worksheet is activated or removed. It does not indicate whether the save succeeded or whether the sidebar closed.

TSX
import type { IConditionFormattingRule } from '@univerjs/sheets-conditional-formatting'import { LocaleService } from '@univerjs/core'import { ConditionalFormattingRulePreview, getRuleDescribe } from '@univerjs/sheets-conditional-formatting-ui'import { useDependency } from '@univerjs/ui'interface IRuleSummaryProps {  rule: IConditionFormattingRule}function RuleSummary({ rule }: IRuleSummaryProps) {  const localeService = useDependency(LocaleService)  return (    <div>      <span>{getRuleDescribe(rule, localeService)}</span>      <ConditionalFormattingRulePreview rule={rule.rule} />    </div>  )}

Rule priority and Stop If True

Rules apply in priority order. For a given cell, a matching highlight rule that carries stopIfTrue: true suppresses lower-priority rules, even when the matching rule sets no style. If the rule does not match, evaluation continues. Results from higher-priority rules remain in effect. The rule editor provides this option for highlight rules only; color scales, data bars, and icon sets do not have it.

The example adds the lower-priority background rule first because a newly added rule is placed at the top of the list. When stopping is enabled, 90 appears in bold without the green background, while 60 still receives the background. stopIfTrue belongs to the outer rule object, not to rule.

TypeScript
const sheet = univerAPI.getActiveWorkbook()?.getActiveSheet()if (!sheet) throw new Error('No active worksheet')const ranges = [sheet.getRange('B2:B10').getRange()]const backgroundRule = sheet.newConditionalFormattingRule()  .whenNumberGreaterThanOrEqualTo(50)  .setBackground('#DCFCE7')  .setRanges(ranges)  .build()const priorityRule = sheet.newConditionalFormattingRule()  .whenNumberGreaterThanOrEqualTo(80)  .setBold(true)  .setRanges(ranges)  .build()sheet.addConditionalFormattingRule(backgroundRule)sheet.addConditionalFormattingRule({ ...priorityRule, stopIfTrue: true })

How is this guide?

© 2026 DreamNum Co., Ltd.