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
pnpm add @univerjs/sheets-conditional-formatting @univerjs/sheets-conditional-formatting-uinpm install @univerjs/sheets-conditional-formatting @univerjs/sheets-conditional-formatting-uiyarn add @univerjs/sheets-conditional-formatting @univerjs/sheets-conditional-formatting-uibun add @univerjs/sheets-conditional-formatting @univerjs/sheets-conditional-formatting-uiUsage
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.
| Package | Desktop registration | Mobile registration |
|---|---|---|
@univerjs/sheets-conditional-formatting-ui | UniverSheetsConditionalFormattingUIPlugin | UniverSheetsConditionalFormattingMobileUIPlugin |
Preset Mode
Installation
pnpm add @univerjs/preset-sheets-conditional-formattingnpm install @univerjs/preset-sheets-conditional-formattingyarn add @univerjs/preset-sheets-conditional-formattingbun add @univerjs/preset-sheets-conditional-formattingUsage
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.
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:
| Method | Description |
|---|---|
| build | Builds the rule |
| copy | Deep-clones the current builder |
| createCfId | Creates a conditional format ID |
| getRanges | Gets the ranges of the current conditional format |
| setRanges | Sets the range the rule applies to |
| setAverage | Sets an average-value rule |
| setUniqueValues | Sets a unique-values rule |
| setDuplicateValues | Sets a duplicate-values rule |
| setRank | Sets a rank rule |
| setIconSet | Sets an icon-set rule |
| setColorScale | Sets a color-scale rule |
| setDataBar | Sets a data-bar rule |
| setBackground | Sets the background color |
| setBold | Sets bold text |
| setFontColor | Sets the font color |
| setItalic | Sets italic text |
| setStrikethrough | Sets strikethrough |
| setUnderline | Sets underline |
| whenCellEmpty | Fires when the cell is empty |
| whenCellNotEmpty | Fires when the cell is not empty |
| whenDate | Fires when the date falls in a specific period |
| whenFormulaSatisfied | Fires when the formula evaluates to true |
| whenNumberBetween | Fires when the number is between two values, or equal to one of them |
| whenNumberEqualTo | Fires when the number equals the given value |
| whenNumberGreaterThan | Fires when the number is greater than the given value |
| whenNumberGreaterThanOrEqualTo | Fires when the number is greater than or equal to the given value |
| whenNumberLessThan | Fires when the number is less than the given value |
| whenNumberLessThanOrEqualTo | Fires when the number is less than or equal to the given value |
| whenNumberNotBetween | Fires when the number is not between two values and not equal to either |
| whenNumberNotEqualTo | Fires when the number does not equal the given value |
| whenTextContains | Fires when the text contains the specified value |
| whenTextDoesNotContain | Fires when the text does not contain the specified value |
| whenTextEndsWith | Fires when the text ends with the specified value |
| whenTextEqualTo | Fires when the text equals the given value |
| whenTextStartsWith | Fires when the text starts with the specified value |
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 worksheetFRange.getConditionalFormattingRules()gets all conditional formatting rules of the range
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.
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.
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').
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 worksheetFRange.clearConditionalFormatRules()clears all conditional formatting rules of the range
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.
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.
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?