Excel.ConditionalFormat class

An object encapsulating a conditional format's range, format, rule, and other properties.

[ API set: ExcelApi 1.6 ]

Extends

Properties

cellValue

Returns the cell value conditional format properties if the current conditional format is a CellValue type. For example to format all cells between 5 and 10. Read-only.

[ API set: ExcelApi 1.6 ]

cellValueOrNullObject

Returns the cell value conditional format properties if the current conditional format is a CellValue type. For example to format all cells between 5 and 10. Read-only.

[ API set: ExcelApi 1.6 ]

colorScale

Returns the ColorScale conditional format properties if the current conditional format is an ColorScale type. Read-only.

[ API set: ExcelApi 1.6 ]

colorScaleOrNullObject

Returns the ColorScale conditional format properties if the current conditional format is an ColorScale type. Read-only.

[ API set: ExcelApi 1.6 ]

context

The request context associated with the object. This connects the add-in's process to the Office host application's process.

custom

Returns the custom conditional format properties if the current conditional format is a custom type. Read-only.

[ API set: ExcelApi 1.6 ]

customOrNullObject

Returns the custom conditional format properties if the current conditional format is a custom type. Read-only.

[ API set: ExcelApi 1.6 ]

dataBar

Returns the data bar properties if the current conditional format is a data bar. Read-only.

[ API set: ExcelApi 1.6 ]

dataBarOrNullObject

Returns the data bar properties if the current conditional format is a data bar. Read-only.

[ API set: ExcelApi 1.6 ]

iconSet

Returns the IconSet conditional format properties if the current conditional format is an IconSet type. Read-only.

[ API set: ExcelApi 1.6 ]

iconSetOrNullObject

Returns the IconSet conditional format properties if the current conditional format is an IconSet type. Read-only.

[ API set: ExcelApi 1.6 ]

id

The Priority of the Conditional Format within the current ConditionalFormatCollection. Read-only.

[ API set: ExcelApi 1.6 ]

preset

Returns the preset criteria conditional format. See Excel.PresetCriteriaConditionalFormat for more details.

[ API set: ExcelApi 1.6 ]

presetOrNullObject

Returns the preset criteria conditional format. See Excel.PresetCriteriaConditionalFormat for more details.

[ API set: ExcelApi 1.6 ]

priority

The priority (or index) within the conditional format collection that this conditional format currently exists in. Changing this also changes other conditional formats' priorities, to allow for a contiguous priority order. Use a negative priority to begin from the back. Priorities greater than than bounds will get and set to the maximum (or minimum if negative) priority. Also note that if you change the priority, you have to re-fetch a new copy of the object at that new priority location if you want to make further changes to it. Read-only.

[ API set: ExcelApi 1.6 ]

stopIfTrue

If the conditions of this conditional format are met, no lower-priority formats shall take effect on that cell. Null on databars, icon sets, and colorscales as there's no concept of StopIfTrue for these

[ API set: ExcelApi 1.6 ]

textComparison

Returns the specific text conditional format properties if the current conditional format is a text type. For example to format cells matching the word "Text". Read-only.

[ API set: ExcelApi 1.6 ]

textComparisonOrNullObject

Returns the specific text conditional format properties if the current conditional format is a text type. For example to format cells matching the word "Text". Read-only.

[ API set: ExcelApi 1.6 ]

topBottom

Returns the Top/Bottom conditional format properties if the current conditional format is an TopBottom type. For example to format the top 10% or bottom 10 items. Read-only.

[ API set: ExcelApi 1.6 ]

topBottomOrNullObject

Returns the Top/Bottom conditional format properties if the current conditional format is an TopBottom type. For example to format the top 10% or bottom 10 items. Read-only.

[ API set: ExcelApi 1.6 ]

type

A type of conditional format. Only one can be set at a time. Read-only.

[ API set: ExcelApi 1.6 ]

Methods

delete()

Deletes this conditional format.

[ API set: ExcelApi 1.6 ]

getRange()

Returns the range the conditonal format is applied to. Throws an error if the conditional format is applied to multiple ranges. Read-only.

[ API set: ExcelApi 1.6 ]

getRangeOrNullObject()

Returns the range the conditonal format is applied to, or a null object if the conditional format is applied to multiple ranges. Read-only.

[ API set: ExcelApi 1.6 ]

load(option)

Queues up a command to load the specified properties of the object. You must call "context.sync()" before reading the properties.

toJSON()

Overrides the JavaScript toJSON() method in order to provide more useful output when an API object is passed to JSON.stringify(). (JSON.stringify, in turn, calls the toJSON method of the object that is passed to it.) Whereas the original Excel.ConditionalFormat object is an API object, the toJSON method returns a plain JavaScript object (typed as Excel.Interfaces.ConditionalFormatData) that contains shallow copies of any loaded child properties from the original object.

Property Details

cellValue

Returns the cell value conditional format properties if the current conditional format is a CellValue type. For example to format all cells between 5 and 10. Read-only.

[ API set: ExcelApi 1.6 ]

readonly cellValue: Excel.CellValueConditionalFormat;

Property Value

Examples

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const range = sheet.getRange("B21:E23");
    const conditionalFormat = range.conditionalFormats
        .add(Excel.ConditionalFormatType.cellValue);
    conditionalFormat.cellValue.format.font.color = "red";
    conditionalFormat.cellValue.rule = { formula1: "=0", operator: "LessThan" };

    await context.sync();
});

cellValueOrNullObject

Returns the cell value conditional format properties if the current conditional format is a CellValue type. For example to format all cells between 5 and 10. Read-only.

[ API set: ExcelApi 1.6 ]

readonly cellValueOrNullObject: Excel.CellValueConditionalFormat;

Property Value

colorScale

Returns the ColorScale conditional format properties if the current conditional format is an ColorScale type. Read-only.

[ API set: ExcelApi 1.6 ]

readonly colorScale: Excel.ColorScaleConditionalFormat;

Property Value

Examples

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const range = sheet.getRange("B2:M5");
    const conditionalFormat = range.conditionalFormats
        .add(Excel.ConditionalFormatType.colorScale);
    const criteria = {
        minimum: { formula: null, type: Excel.ConditionalFormatColorCriterionType.lowestValue, color: "blue" },
        midpoint: { formula: "50", type: Excel.ConditionalFormatColorCriterionType.percent, color: "yellow" },
        maximum: { formula: null, type: Excel.ConditionalFormatColorCriterionType.highestValue, color: "red" }
    };
    conditionalFormat.colorScale.criteria = criteria;

    await context.sync();
});

colorScaleOrNullObject

Returns the ColorScale conditional format properties if the current conditional format is an ColorScale type. Read-only.

[ API set: ExcelApi 1.6 ]

readonly colorScaleOrNullObject: Excel.ColorScaleConditionalFormat;

Property Value

context

The request context associated with the object. This connects the add-in's process to the Office host application's process.

context: RequestContext;

Property Value

RequestContext

custom

Returns the custom conditional format properties if the current conditional format is a custom type. Read-only.

[ API set: ExcelApi 1.6 ]

readonly custom: Excel.CustomConditionalFormat;

Property Value

Examples

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const range = sheet.getRange("B8:E13");
    const conditionalFormat = range.conditionalFormats.add(Excel.ConditionalFormatType.custom);
    conditionalFormat.custom.rule.formula = '=IF(B8>INDIRECT("RC[-1]",0),TRUE)';
    conditionalFormat.custom.format.font.color = "green";

    await context.sync();
});

customOrNullObject

Returns the custom conditional format properties if the current conditional format is a custom type. Read-only.

[ API set: ExcelApi 1.6 ]

readonly customOrNullObject: Excel.CustomConditionalFormat;

Property Value

dataBar

Returns the data bar properties if the current conditional format is a data bar. Read-only.

[ API set: ExcelApi 1.6 ]

readonly dataBar: Excel.DataBarConditionalFormat;

Property Value

Examples

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const range = sheet.getRange("B8:E13");
    const conditionalFormat = range.conditionalFormats
        .add(Excel.ConditionalFormatType.dataBar);
    conditionalFormat.dataBar.barDirection = Excel.ConditionalDataBarDirection.leftToRight;

    await context.sync();
});

dataBarOrNullObject

Returns the data bar properties if the current conditional format is a data bar. Read-only.

[ API set: ExcelApi 1.6 ]

readonly dataBarOrNullObject: Excel.DataBarConditionalFormat;

Property Value

iconSet

Returns the IconSet conditional format properties if the current conditional format is an IconSet type. Read-only.

[ API set: ExcelApi 1.6 ]

readonly iconSet: Excel.IconSetConditionalFormat;

Property Value

Examples

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const range = sheet.getRange("B8:E13");
    const conditionalFormat = range.conditionalFormats
        .add(Excel.ConditionalFormatType.iconSet);
    const iconSetCF = conditionalFormat.iconSet;
    iconSetCF.style = Excel.IconSet.threeTriangles;

    /*
        The iconSetCF.criteria array is automatically prepopulated with
        criterion elements whose properties have been given default settings.
        You can't write to each property of a criterion directly. Instead,
        replace the whole criteria object.

        With a "three*" icon set style, such as "threeTriangles", the third
        element in the criteria array (criteria[2]) defines the "top" icon;
        e.g., a green triangle. The second (criteria[1]) defines the "middle"
        icon, The first (criteria[0]) defines the "low" icon, but it
        can often be left empty as this method does below, because every
        cell that does not match the other two criteria always gets the low
        icon.            
    */
    iconSetCF.criteria = [
        {} as any,
        {
            type: Excel.ConditionalFormatIconRuleType.number,
            operator: Excel.ConditionalIconCriterionOperator.greaterThanOrEqual,
            formula: "=700"
        },
        {
            type: Excel.ConditionalFormatIconRuleType.number,
            operator: Excel.ConditionalIconCriterionOperator.greaterThanOrEqual,
            formula: "=1000",
        }
    ];

    await context.sync();
});

iconSetOrNullObject

Returns the IconSet conditional format properties if the current conditional format is an IconSet type. Read-only.

[ API set: ExcelApi 1.6 ]

readonly iconSetOrNullObject: Excel.IconSetConditionalFormat;

Property Value

id

The Priority of the Conditional Format within the current ConditionalFormatCollection. Read-only.

[ API set: ExcelApi 1.6 ]

readonly id: string;

Property Value

string

preset

Returns the preset criteria conditional format. See Excel.PresetCriteriaConditionalFormat for more details.

[ API set: ExcelApi 1.6 ]

readonly preset: Excel.PresetCriteriaConditionalFormat;

Property Value

Examples

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const range = sheet.getRange("B2:M5");
    const conditionalFormat = range.conditionalFormats
        .add(Excel.ConditionalFormatType.presetCriteria);
    conditionalFormat.preset.format.font.color = "white";
    conditionalFormat.preset.rule = { criterion: Excel.ConditionalFormatPresetCriterion.oneStdDevAboveAverage };

    await context.sync();
});

presetOrNullObject

Returns the preset criteria conditional format. See Excel.PresetCriteriaConditionalFormat for more details.

[ API set: ExcelApi 1.6 ]

readonly presetOrNullObject: Excel.PresetCriteriaConditionalFormat;

Property Value

priority

The priority (or index) within the conditional format collection that this conditional format currently exists in. Changing this also changes other conditional formats' priorities, to allow for a contiguous priority order. Use a negative priority to begin from the back. Priorities greater than than bounds will get and set to the maximum (or minimum if negative) priority. Also note that if you change the priority, you have to re-fetch a new copy of the object at that new priority location if you want to make further changes to it. Read-only.

[ API set: ExcelApi 1.6 ]

priority: number;

Property Value

number

stopIfTrue

If the conditions of this conditional format are met, no lower-priority formats shall take effect on that cell. Null on databars, icon sets, and colorscales as there's no concept of StopIfTrue for these

[ API set: ExcelApi 1.6 ]

stopIfTrue: boolean;

Property Value

boolean

textComparison

Returns the specific text conditional format properties if the current conditional format is a text type. For example to format cells matching the word "Text". Read-only.

[ API set: ExcelApi 1.6 ]

readonly textComparison: Excel.TextConditionalFormat;

Property Value

Examples

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const range = sheet.getRange("B16:D18");
    const conditionalFormat = range.conditionalFormats
        .add(Excel.ConditionalFormatType.containsText);
    conditionalFormat.textComparison.format.font.color = "red";
    conditionalFormat.textComparison.rule = { operator: Excel.ConditionalTextOperator.contains, text: "Delayed" };

    await context.sync();
});

textComparisonOrNullObject

Returns the specific text conditional format properties if the current conditional format is a text type. For example to format cells matching the word "Text". Read-only.

[ API set: ExcelApi 1.6 ]

readonly textComparisonOrNullObject: Excel.TextConditionalFormat;

Property Value

topBottom

Returns the Top/Bottom conditional format properties if the current conditional format is an TopBottom type. For example to format the top 10% or bottom 10 items. Read-only.

[ API set: ExcelApi 1.6 ]

readonly topBottom: Excel.TopBottomConditionalFormat;

Property Value

topBottomOrNullObject

Returns the Top/Bottom conditional format properties if the current conditional format is an TopBottom type. For example to format the top 10% or bottom 10 items. Read-only.

[ API set: ExcelApi 1.6 ]

readonly topBottomOrNullObject: Excel.TopBottomConditionalFormat;

Property Value

type

A type of conditional format. Only one can be set at a time. Read-only.

[ API set: ExcelApi 1.6 ]

readonly type: Excel.ConditionalFormatType | "Custom" | "DataBar" | "ColorScale" | "IconSet" | "TopBottom" | "PresetCriteria" | "ContainsText" | "CellValue";

Property Value

Excel.ConditionalFormatType | "Custom" | "DataBar" | "ColorScale" | "IconSet" | "TopBottom" | "PresetCriteria" | "ContainsText" | "CellValue"

Examples

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const worksheetRange = sheet.getRange();
    worksheetRange.conditionalFormats.load("type");

    await context.sync();

    let cfRangePairs: { cf: Excel.ConditionalFormat, range: Excel.Range }[] = [];
    worksheetRange.conditionalFormats.items.forEach(item => {
        cfRangePairs.push({
            cf: item,
            range: item.getRange().load("address")
        });
    });

    await context.sync();

    if (cfRangePairs.length > 0) {
        cfRangePairs.forEach(item => {
            console.log(item.cf.type);
        });
    } else {
        console.log("No conditional formats applied.");
    }
});

Method Details

delete()

Deletes this conditional format.

[ API set: ExcelApi 1.6 ]

delete(): void;

Returns

void

getRange()

Returns the range the conditonal format is applied to. Throws an error if the conditional format is applied to multiple ranges. Read-only.

[ API set: ExcelApi 1.6 ]

getRange(): Excel.Range;

Returns

Examples

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const worksheetRange = sheet.getRange();
    worksheetRange.conditionalFormats.load("type");

    await context.sync();

    let cfRangePairs: { cf: Excel.ConditionalFormat, range: Excel.Range }[] = [];
    worksheetRange.conditionalFormats.items.forEach(item => {
        cfRangePairs.push({
            cf: item,
            range: item.getRange().load("address")
        });
    });

    await context.sync();

    if (cfRangePairs.length > 0) {
        cfRangePairs.forEach(item => {
            console.log(item.cf.type);
        });
    } else {
        console.log("No conditional formats applied.");
    }
});

getRangeOrNullObject()

Returns the range the conditonal format is applied to, or a null object if the conditional format is applied to multiple ranges. Read-only.

[ API set: ExcelApi 1.6 ]

getRangeOrNullObject(): Excel.Range;

Returns

load(option)

Queues up a command to load the specified properties of the object. You must call "context.sync()" before reading the properties.

load(option?: string | string[]): Excel.ConditionalFormat;

Parameters

option
string | string[]

A comma-delimited string or an array of strings that specify the properties to load.

Returns

Remarks

In addition to this signature, this method has the following signatures:

load(option?: { select?: string; expand?: string; }): Excel.ConditionalFormat - Where option.select is a comma-delimited string that specifies the properties to load, and options.expand is a comma-delimited string that specifies the navigation properties to load.

load(option?: { select?: string; expand?: string; top?: number; skip?: number }): Excel.ConditionalFormat - Only available on collection types. It is similar to the preceding signature. Option.top specifies the maximum number of collection items that can be included in the result. Option.skip specifies the number of items that are to be skipped and not included in the result. If option.top is specified, the result set will start after skipping the specified number of items.

toJSON()

Overrides the JavaScript toJSON() method in order to provide more useful output when an API object is passed to JSON.stringify(). (JSON.stringify, in turn, calls the toJSON method of the object that is passed to it.) Whereas the original Excel.ConditionalFormat object is an API object, the toJSON method returns a plain JavaScript object (typed as Excel.Interfaces.ConditionalFormatData) that contains shallow copies of any loaded child properties from the original object.

toJSON(): Excel.Interfaces.ConditionalFormatData;

Returns