Conditional Formatting

3 Jul 202619 minutes to read

Conditional formatting helps you to format a cell or range of cells based on the conditions applied. You can enable or disable conditional formats by using the allowConditionalFormat property.

  • The default value for the allowConditionalFormat property is true.

Apply Conditional Formatting

You can apply conditional formatting by using one of the following ways,

  • Select the conditional formatting icon in the Ribbon toolbar under the Home Tab.
  • Using the conditionalFormat() method to define the condition.
  • Using the conditionalFormats in sheets model.

Conditional formatting has the following types in the spreadsheet,

Highlight Cells Rules

Highlight cells rules option in the conditional formatting enables you to highlight cells with a preset color depending on the cell’s value.

The following options can be given for the highlight cells rules as type,

  • GreaterThan, LessThan, Between, EqualTo, ContainsText, DateOccur, Duplicate, Unique.

The following preset colors can be used for formatting styles,

  • "RedFT" - Light Red Fill with Dark Red Text,
  • "YellowFT" - Yellow Fill with Dark Yellow Text,
  • "GreenFT" - Green Fill with Dark Green Text,
  • "RedF" - Red Fill,
  • "RedT" - Red Text.

Top Bottom Rules

Top bottom rules option in the conditional formatting allows you to apply formatting to the cells that satisfy a statistical condition with other cells in the range.

The following options can be given for the top bottom rules as type,

  • Top10Items, Bottom10Items, Top10Percentage, Bottom10Percentage, BelowAverage, AboveAverage.

Data Bars

You can apply data bars to represent the data graphically inside a cell. The longest bar represents the highest value and the shorter bars represent the smaller values.

The following options can be given for the data bars as type,

  • BlueDataBar, GreenDataBar, RedDataBar, OrangeDataBar, LightBlueDataBar, PurpleDataBar.

Color Scales

Using color scales, you can format your cells with two or three colors, where different color shades represent the different cell values. In the Green-Yellow-Red(GYR) Color Scale, the cell that holds the minimum value is colored as red. The cell that holds the median is colored as yellow, and the cell that holds the maximum value is colored as green. All other cells are colored proportionally.

The following options can be given for the color scales as type,

  • GYRColorScale, RYGColorScale, GWRColorScale, RWGColorScale, BWRColorScale, RWBColorScale, WRColorScale, RWColorScale, GWColorScale, WGColorScale, GYColorScale, YGColorScale.

Icon Sets

Icon sets will help you to visually represent your data with icons. Every icon represents a range of values. In the Three Arrows(colored) icon, the green arrow icon represents the values greater than 67%, the yellow arrow icon represents the values between 33% to 67%, and the red arrow icon represents the values less than 33%.

The following options can be given for the icon sets as type,

  • ThreeArrows, ThreeArrowsGray, FourArrowsGray, FourArrows, FiveArrowsGray, FiveArrows, ThreeTrafficLights1, ThreeTrafficLights2, ThreeSigns, FourTrafficLights, FourRedToBlack, ThreeSymbols, ThreeSymbols2, ThreeFlags, FourRating, FiveQuarters, FiveRating, ThreeTriangles, ThreeStars, FiveBoxes.

Custom Format

Using the custom format for conditional formatting you can set cell styles like color, background color, font style, font weight, and underline.

In the MAY and JUN columns, we have applied conditional formatting custom format.

  • In the Conditional format, custom format supported for Highlight cell rules and Top bottom rules.

Clear Rules

You can clear the defined rules by using one of the following ways,

  • Using the Clear Rules option in the Conditional Formatting button of HOME Tab in the ribbon to clear the rule from selected cells.
  • Using the clearConditionalFormat method to clear the defined rules.

The following code sample shows how to apply different types of conditional formatting, colors, and styles in the Spreadsheet

import * as React from 'react';
import { createRoot } from 'react-dom/client';
import { SpreadsheetComponent, SheetsDirective, SheetDirective, RangesDirective } from '@syncfusion/ej2-react-spreadsheet';
import { RangeDirective, ColumnsDirective, ColumnDirective, ConditionalFormatsDirective, ConditionalFormatDirective } from '@syncfusion/ej2-react-spreadsheet';
import { conditionalFormatData } from './datasource';

function App() {
    const spreadsheetRef = React.useRef(null);
    React.useEffect(() => {
        let spreadsheet = spreadsheetRef.current;
        if (spreadsheet) {
            spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A1:N1');
            spreadsheet.conditionalFormat({ type: 'RYGColorScale', range: 'E2:E9' });
            spreadsheet.conditionalFormat({ type: 'ThreeArrows', range: 'H2:H9' });
            spreadsheet.conditionalFormat({
                type: 'Top10Items', value: '1',
                format: { style: { color: '#ffffff', backgroundColor: '#009999', fontWeight: 'bold' } }, range: 'F2:F9'
            });
            spreadsheet.conditionalFormat({
                type: 'Bottom10Items', value: '1',
                format: { style: { color: '#ffffff', backgroundColor: '#c68d53', fontWeight: 'bold' } }, range: 'G2:G9'
            });
        }
    }, []);

    return (
        <div>
            <SpreadsheetComponent ref={spreadsheetRef} showFormulaBar={false}>
                <SheetsDirective>
                    <SheetDirective name='Car Sales Record'>
                        <RangesDirective>
                            <RangeDirective dataSource={conditionalFormatData}></RangeDirective>
                        </RangesDirective>
                        <ConditionalFormatsDirective>
                            <ConditionalFormatDirective type='GreaterThan' cFColor='RedFT' value='700' range='B2:B9'></ConditionalFormatDirective>
                            <ConditionalFormatDirective type='Bottom10Items' cFColor='YellowFT' value='4' range='C2:C9'></ConditionalFormatDirective>
                            <ConditionalFormatDirective type='BlueDataBar' range='D2:D9'></ConditionalFormatDirective>
                        </ConditionalFormatsDirective>
                        <ColumnsDirective>
                            <ColumnDirective width={120}></ColumnDirective>
                        </ColumnsDirective>
                    </SheetDirective>
                </SheetsDirective>
            </SpreadsheetComponent>
        </div>
    );
};
export default App;

const root = createRoot(document.getElementById('root'));
root.render(<App />);
import * as React from 'react';
import { createRoot } from 'react-dom/client';
import { SpreadsheetComponent, SheetsDirective, SheetDirective, RangesDirective } from '@syncfusion/ej2-react-spreadsheet';
import { RangeDirective, ColumnsDirective, ColumnDirective, ConditionalFormatsDirective, ConditionalFormatDirective } from '@syncfusion/ej2-react-spreadsheet';
import { conditionalFormatData } from './datasource';

function App() {
    const spreadsheetRef = React.useRef<SpreadsheetComponent>(null);
    React.useEffect(() => {
        let spreadsheet = spreadsheetRef.current;
        if (spreadsheet) {
            spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A1:N1');
            spreadsheet.conditionalFormat({ type: 'RYGColorScale', range: 'E2:E9' });
            spreadsheet.conditionalFormat({ type: 'ThreeArrows', range: 'H2:H9' });
            spreadsheet.conditionalFormat({
                type: 'Top10Items', value: '1',
                format: { style: { color: '#ffffff', backgroundColor: '#009999', fontWeight: 'bold' } }, range: 'F2:F9'
            });
            spreadsheet.conditionalFormat({
                type: 'Bottom10Items', value: '1',
                format: { style: { color: '#ffffff', backgroundColor: '#c68d53', fontWeight: 'bold' } }, range: 'G2:G9'
            });
        }
    }, []);

    return (
        <div>
            <SpreadsheetComponent ref={spreadsheetRef} showFormulaBar={false}>
                <SheetsDirective>
                    <SheetDirective name='Car Sales Record'>
                        <RangesDirective>
                            <RangeDirective dataSource={conditionalFormatData}></RangeDirective>
                        </RangesDirective>
                        <ConditionalFormatsDirective>
                            <ConditionalFormatDirective type='GreaterThan' cFColor='RedFT' value='700' range='B2:B9'></ConditionalFormatDirective>
                            <ConditionalFormatDirective type='Bottom10Items' cFColor='YellowFT' value='4' range='C2:C9'></ConditionalFormatDirective>
                            <ConditionalFormatDirective type='BlueDataBar' range='D2:D9'></ConditionalFormatDirective>
                        </ConditionalFormatsDirective>
                        <ColumnsDirective>
                            <ColumnDirective width={120}></ColumnDirective>
                        </ColumnsDirective>
                    </SheetDirective>
                </SheetsDirective>
            </SpreadsheetComponent>
        </div>
    );
};
export default App;

const root = createRoot(document.getElementById('root')!);
root.render(<App />);
/**
 * Conditional formatting data source
 */
export let conditionalFormatData = [
    { 'EV Model': 'BMW I3', 'JAN': 1224, 'FEB': 423, 'MAR': 585, 'APR': 367, 'MAY': 729, 'JUN': 733, 'TOTAL': '=SUM(B2:G2)' },
    { 'EV Model': 'Tesla Model S', 'JAN': 975, 'FEB': 763, 'MAR': 723, 'APR': 483, 'MAY': 983, 'JUN': 589, 'TOTAL': '=SUM(B3:G3)' },
    { 'EV Model': 'Chevrolet Volt', 'JAN': 113, 'FEB': 289, 'MAR': 675, 'APR': 458, 'MAY': 391, 'JUN': 198, 'TOTAL': '=SUM(B4:G4)' },
    { 'EV Model': 'Jaguar I-PACE', 'JAN': 78, 'FEB': 177, 'MAR': 244, 'APR': 99, 'MAY': 312, 'JUN': 129, 'TOTAL': '=SUM(B5:G5)' },
    { 'EV Model': 'Tesla Model X', 'JAN': 978, 'FEB': 1108, 'MAR': 1604, 'APR': 879, 'MAY': 1070, 'JUN': 1001, 'TOTAL': '=SUM(B6:G6)' },
    { 'EV Model': 'Nissan LEAF', 'JAN': 229, 'FEB': 978, 'MAR': 1202, 'APR': 822, 'MAY': 135, 'JUN': 878, 'TOTAL': '=SUM(B7:G7)' },
    { 'EV Model': 'Honda Clarity EV', 'JAN': 671, 'FEB': 1302, 'MAR': 466, 'APR': 989, 'MAY': 679, 'JUN': 891, 'TOTAL': '=SUM(B8:G8)' },
    { 'EV Model': 'Toyota Prius Prime', 'JAN': 978, 'FEB': 1362, 'MAR': 1872, 'APR': 678, 'MAY': 900, 'JUN': 867, 'TOTAL': '=SUM(B9:G9)' }
];
/**
 * Conditional formatting data source
 */
export let conditionalFormatData: Object[] = [
    { 'EV Model': 'BMW I3', 'JAN': 1224, 'FEB': 423, 'MAR': 585, 'APR': 367, 'MAY': 729, 'JUN': 733, 'TOTAL': '=SUM(B2:G2)' },
    { 'EV Model': 'Tesla Model S', 'JAN': 975, 'FEB': 763, 'MAR': 723, 'APR': 483, 'MAY': 983, 'JUN': 589, 'TOTAL': '=SUM(B3:G3)' }, 
    { 'EV Model': 'Chevrolet Volt', 'JAN': 113, 'FEB': 289, 'MAR': 675, 'APR': 458, 'MAY': 391, 'JUN': 198, 'TOTAL': '=SUM(B4:G4)' },
    { 'EV Model': 'Jaguar I-PACE', 'JAN': 78, 'FEB': 177, 'MAR': 244, 'APR': 99, 'MAY': 312, 'JUN': 129, 'TOTAL': '=SUM(B5:G5)' },
    { 'EV Model': 'Tesla Model X', 'JAN': 978, 'FEB': 1108, 'MAR': 1604, 'APR': 879, 'MAY': 1070, 'JUN': 1001, 'TOTAL': '=SUM(B6:G6)' },
    { 'EV Model': 'Nissan LEAF', 'JAN': 229, 'FEB': 978, 'MAR': 1202, 'APR': 822, 'MAY': 135, 'JUN': 878, 'TOTAL': '=SUM(B7:G7)' },
    { 'EV Model': 'Honda Clarity EV', 'JAN': 671, 'FEB': 1302, 'MAR': 466, 'APR': 989, 'MAY':679, 'JUN': 891, 'TOTAL': '=SUM(B8:G8)' },
    { 'EV Model': 'Toyota Prius Prime', 'JAN': 978, 'FEB': 1362, 'MAR': 1872, 'APR': 678, 'MAY': 900, 'JUN': 867, 'TOTAL': '=SUM(B9:G9)' }
];

Limitations of Conditional formatting

The following features have some limitations in Conditional Formatting:

  • Insert row/column between the conditional formatting.
  • Conditional formatting with formula support.
  • Copy and paste the conditional formatting applied cells.
  • Custom rule support.

Note

You can refer to our React Spreadsheet Editor feature tour page for feature highlights. You can also explore our React Spreadsheet example to learn how to present and manipulate data.

See Also