# XLSX Conditional Formatting Rule

Batch-apply conditional formatting rules such as threshold highlights, data bars, and color scales to Excel worksheets

> Canonical page: https://test.elysiatools.com/en/tools/xlsx-conditional-formatting-rule

- **Category:** Format Conversion

- **Keywords:** xlsx, excel, conditional formatting, data bar, color scale, highlight, exceljs

## Overview

Apply conditional formatting in batch.

- Supports threshold rules like value > 100 in red
- Supports data bars and 2/3-color scales
- Supports text contains and formula expression rules
- Uses ExcelJS native conditional formatting support

## Inputs

- **Workbook File** (file)
- **Rules JSON** (textarea): \[ { "sheetName": "Sheet1", "ref": "B2:B20", "type": "cellIs", "operator": "greaterThan", "formulae": \[100\], "fillColor": "#FECACA", "fontColor": "#B91C1C" }, { "sheetName": "Sheet1", "ref": "C2:C20", "type": "dataBar" }, { "sheetName": "Sheet1", "ref": "D2:D20", "type": "colorScale" } \]

## When to use

- When you need to apply consistent formatting rules across multiple sheets or large data ranges.
- When you want to automate the visual identification of outliers, targets, or performance metrics.
- When you need to standardize the appearance of reports by applying data bars or color scales based on specific cell values.

## How it works

- Upload your Excel workbook file (.xlsx or .xls).
- Define your formatting logic in the Rules JSON field, specifying the sheet name, cell range, and rule type.
- Submit the configuration to process the file and generate a new version with the applied formatting.
- Download the updated workbook containing your applied conditional styles.

## Use cases

- Automating risk alerts by highlighting cells that fall below a specific safety or performance threshold.
- Generating visual KPI dashboards that use data bars to represent progress toward goals.
- Standardizing data quality reports by applying color scales to identify high and low values across large datasets.

## Frequently asked questions

### What types of formatting rules are supported?

The tool supports threshold-based cell highlighting, data bars, color scales, and text-contains rules.

### Can I apply multiple rules to the same range?

Yes, you can define an array of rules in the JSON configuration to apply multiple formatting layers to the same or different ranges.

### Is my original file modified?

No, the tool generates a new processed file, leaving your original source file unchanged.

### What format should the Rules JSON follow?

The JSON should be an array of objects, each containing keys for sheetName, ref (range), type, and specific parameters like fillColor or operator.

### Are there limits on the file size?

The tool supports files up to 20MB to ensure efficient processing.

## Related tools

- [Excel to JSON Converter](https://test.elysiatools.com/en/tools/excel-to-json): Convert Excel file to JSON format, extracting data from the first worksheet
- [XLSX Chart Embedder](https://test.elysiatools.com/en/tools/xlsx-chart-embedder): Embed generated chart images into Excel workbooks using workbook data ranges for bar or pie chart reports
- [XLSX Parquet Exporter](https://test.elysiatools.com/en/tools/xlsx-parquet-exporter): Export worksheet data to Parquet and/or NDJSON for data lake and data warehouse pipelines
- [XLSX Print Setup Pack](https://test.elysiatools.com/en/tools/xlsx-print-setup-pack): Apply paper, margins, scaling, repeating titles, and header/footer print settings to Excel workbooks in one pass
- [XLSX JSON Transformer](https://test.elysiatools.com/en/tools/xlsx-json-transformer): Bidirectional conversion between JSON arrays and Excel sheets, with nested object flattening and restoration
- [XLSX Sheet To JSON Schema](https://test.elysiatools.com/en/tools/xlsx-sheet-to-json-schema): Infer JSON Schema from worksheet headers and sample rows with types, enum candidates, and basic constraints
- [XLSX Data Validation Dropdown](https://test.elysiatools.com/en/tools/xlsx-data-validation-dropdown): Batch-create dropdowns, dependent cascading dropdowns, and input prompts in Excel workbooks using data validation rules
- [XLSX Formula Injector](https://test.elysiatools.com/en/tools/xlsx-formula-injector): Fill formulas down worksheet columns and optionally lock formula cells for protected, template-friendly Excel workbooks

## Samples

- [XLSX Samples](https://test.elysiatools.com/en/samples/xlsx-samples): Sample XLSX spreadsheets for worksheet parsing and data extraction testing
- [XLS Samples](https://test.elysiatools.com/en/samples/xls-samples): Sample legacy Excel (XLS) files for binary spreadsheet parsing and import tests
- [Android Serialization Java Samples](https://test.elysiatools.com/en/samples/android-serialization-java): Android Java serialization examples including JSON serialization, deserialization, and XML parsing
- [Android Serialization Kotlin Samples](https://test.elysiatools.com/en/samples/android-serialization-kotlin): Android Kotlin serialization examples including JSON serialization, deserialization, and XML parsing

## Related content

- [Professional Assembly of an Excel Workbook](https://test.elysiatools.com/en/hubs/xlsx-workbook-professional-assembly): Furnish a bare spreadsheet into a workbook people can use without asking you questions — upgrade the legacy file, fill formulas down with the cells locked, name the ranges, gate inputs with validation dropdowns, let conditional formatting flag outliers, freeze the headers, build the index sheet, and set the workbook up to print.
