# How to Apply Excel Conditional Formatting Rules Like Data Bars and Color Scales with OfficeCLI > Learn to apply Excel conditional formatting like data bars and color scales using OfficeCLI. Effortlessly generate rules and customize gradients via the command line. - Repository: [OfficeAI/OfficeCLI](https://github.com/iofficeai/OfficeCLI) - Tags: how-to-guide - Published: 2026-08-09 --- **OfficeCLI generates Excel conditional formatting by manipulating Open XML structures through the ExcelHandler dispatcher, enabling data bars with automatic value scaling and color scales with multi-point gradients via the `add` command with `--type conditionalformatting`.** OfficeCLI from the iOfficeAI/OfficeCLI repository provides direct control over Excel's visual formatting by working with the underlying Open XML representation. When you need to apply Excel conditional formatting rules like data bars and color scales with OfficeCLI, the tool bypasses the Excel UI and writes the raw `` elements that Excel renders when the workbook opens. ## How OfficeCLI Structures Conditional Formatting OfficeCLI routes all conditional formatting requests through the **ExcelHandler** class in [`ExcelHandler.Add.Cf.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.Add.Cf.cs). When you invoke the `add` command with `--type conditionalformatting`, the dispatcher at lines 20-34 examines the `type` property and forwards the request to specialized handlers. The CLI constructs `ConditionalFormattingRule` elements and populates the worksheet's `` collection, adding corresponding entries to the `` table for visual styling. ## Applying Data Bars to Excel Ranges Data bars provide visual length-based comparisons within cells. OfficeCLI supports both standard OOXML 2007 data bars and Office 2010 extensions for advanced negative-value rendering. ### Command Syntax for Data Bars Use the `type=dataBar` property to trigger the `AddDataBar` implementation (lines 84-86). The following command applies a blue data bar to column A with automatic min/max calculation: ```bash officecli add book.xlsx /DataBars \ --type conditionalformatting \ --prop type=dataBar \ --prop ref=A2:A11 \ --prop color=638EC6 \ --prop min=auto \ --prop max=auto \ --prop showValue=true ``` ### XML Implementation and Extensions In [`ExcelHandler.Add.Cf.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.Add.Cf.cs), the `AddDataBar` method builds a `ConditionalFormattingRule` containing a `DataBar` child element. For advanced features like negative value colors and axis positioning, the handler adds the `x14:dataBar` extension (lines 173-189). The `ParseHelpers.NormalizeArgbColor` helper converts color strings to the ARGB format required by Excel's XML schema. ### Auto-Scaling Behavior When you specify `min=auto` or `max=auto`, the handler omits the numeric `cfvo` value in the XML (lines 34-42), instructing Excel to calculate the bounds automatically based on the range's data. This matches Excel's native "Automatic" minimum and maximum settings. ## Creating Color Scale Rules Color scales apply gradient fills based on cell values, supporting both two-color and three-color configurations. ### Two-Color and Three-Color Scales For a simple gradient, specify `minColor` and `maxColor`. To add a midpoint, include `midColor` and `midpoint` properties. The generic `Add` path in [`ExcelHandler.Add.Cf.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.Add.Cf.cs) creates the `` element based on these parameters. ```bash # Two-color scale (white to green) officecli add book.xlsx /ColorScales \ --type conditionalformatting \ --prop type=colorScale \ --prop ref=B2:B11 \ --prop minColor=FFFFFF \ --prop maxColor=63BE7B # Three-color scale with 50% midpoint officecli add book.xlsx /ColorScales \ --type conditionalformatting \ --prop type=colorScale \ --prop ref=C2:C11 \ --prop minColor=F8696B \ --prop midColor=FFEB84 \ --prop maxColor=63BE7B \ --prop midPoint=50 ``` ### Querying Existing Color Scales When reading workbooks, the `PopulateCfNodeFromRule` method in [`ExcelHandler.Query.Cf.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.Query.Cf.cs) (lines 99-115) extracts color information from the `ColorScale` child element. It surfaces properties including `minColor`, `midColor`, `maxColor`, and `midpoint`, allowing you to inspect existing formatting with the `officecli get` command. ## Validation Schemas and Properties OfficeCLI validates all conditional formatting arguments against JSON schemas before processing. The [`schemas/help/xlsx/databar.json`](https://github.com/iOfficeAI/OfficeCLI/blob/main/schemas/help/xlsx/databar.json) defines valid data bar properties including `color`, `min`, `max`, `showValue`, `negativeColor`, and `axisPosition`. For color scales, [`schemas/help/xlsx/colorscale.json`](https://github.com/iOfficeAI/OfficeCLI/blob/main/schemas/help/xlsx/colorscale.json) specifies `minColor`, `midColor`, `maxColor`, and `midpoint` requirements. These schemas ensure that CLI arguments map correctly to the internal XML structures. ## Complete Working Example The following script from [`examples/excel/conditional-formatting.sh`](https://github.com/iOfficeAI/OfficeCLI/blob/main/examples/excel/conditional-formatting.sh) demonstrates the full workflow: ```bash # Create and open workbook officecli create book.xlsx officecli open book.xlsx # Data bar with negative value support officecli add book.xlsx /DataBars \ --type conditionalformatting \ --prop type=dataBar \ --prop ref=A2:A11 \ --prop color=638EC6 \ --prop min=auto \ --prop max=auto \ --prop negativeColor=FF0000 \ --prop axisColor=000000 \ --prop axisPosition=middle \ --prop showValue=true # Three-color scale officecli add book.xlsx /ColorScales \ --type conditionalformatting \ --prop type=colorScale \ --prop ref=C2:C11 \ --prop minColor=F8696B \ --prop midColor=FFEB84 \ --prop maxColor=63BE7B \ --prop midPoint=50 # Finalize officecli close book.xlsx officecli validate book.xlsx ``` ## Summary - OfficeCLI manipulates raw Open XML to create conditional formatting rules without opening Excel. - The **ExcelHandler.Add.Cf.cs** dispatcher routes `type=dataBar` to `AddDataBar` and `type=colorScale` to the generic color scale handler. - Data bars support automatic scaling via omitted `cfvo` values and Office 2010 extensions for negative values. - Color scales support both two-point and three-point gradients with configurable midpoint percentages. - All properties are validated against JSON schemas ([`databar.json`](https://github.com/iOfficeAI/OfficeCLI/blob/main/databar.json) and [`colorscale.json`](https://github.com/iOfficeAI/OfficeCLI/blob/main/colorscale.json)) before XML generation. ## Frequently Asked Questions ### How does OfficeCLI handle automatic min/max values for data bars? When you specify `min=auto` or `max=auto`, the CLI omits the numeric `cfvo` element in the Open XML (as seen in [`ExcelHandler.Add.Cf.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.Add.Cf.cs) lines 34-42). This signals Excel to calculate the minimum and maximum values dynamically based on the data range when the file opens. ### Can I apply conditional formatting to multiple non-contiguous ranges? The `ref` property accepts standard Excel range notation. While the examples show contiguous ranges like `A2:A11`, you can specify multiple areas using comma-separated references (e.g., `A2:A11,C2:C11`) provided the ranges are valid according to the Open XML specification. ### What is the difference between the OOXML 2007 and 2010 data bar extensions? OfficeCLI generates a standard `DataBar` element for basic functionality. When you specify advanced properties like `negativeColor` or `axisPosition`, the handler in [`ExcelHandler.Add.Cf.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.Add.Cf.cs) (lines 173-189) additionally creates an `x14:dataBar` extension element. This extension enables Excel 2010 and later to render negative bars, custom axis colors, and length constraints that the 2007 specification does not support. ### How do I verify that my conditional formatting was applied correctly? Use the `officecli get` command to query the worksheet's conditional formatting rules. The `PopulateCfNodeFromRule` method in [`ExcelHandler.Query.Cf.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.Query.Cf.cs) reads the `` or `` XML elements and returns human-readable properties like `minColor`, `color`, and `midpoint`, allowing you to verify the rule configuration without opening Excel.