Find special cells within a range using the Excel JavaScript API

Use the Excel JavaScript API to quickly locate cells with formulas, constants, errors, or other characteristics so you can audit, refactor, or apply formatting efficiently. This article shows how to use Range.getSpecialCells and Range.getSpecialCellsOrNullObject, when to choose each, and how to further narrow results with cell value types. For the full set of properties and methods that the Range object supports, see Excel.Range class.

Quick reference

Find ranges with special cells

The Range.getSpecialCells and Range.getSpecialCellsOrNullObject methods find ranges based on the characteristics of their cells and the types of values of their cells. Both of these methods return RangeAreas objects. Here are the signatures of the methods from the TypeScript data types file:

getSpecialCells(cellType: Excel.SpecialCellType, cellValueType?: Excel.SpecialCellValueType): Excel.RangeAreas;

getSpecialCellsOrNullObject(cellType: Excel.SpecialCellType, cellValueType?: Excel.SpecialCellValueType): Excel.RangeAreas;

The following code sample uses getSpecialCells to find all cells with formulas. Please note:

• The search scope is restricted for better performance by calling worksheet.getUsedRange() first.
• getSpecialCells returns a single RangeAreas object, so non‑contiguous matches can be formatted in one operation.

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();
    let usedRange = sheet.getUsedRange();
    let formulaRanges = usedRange.getSpecialCells(Excel.SpecialCellType.formulas);
    formulaRanges.format.fill.color = "pink";

    await context.sync();
});

If no cells with the targeted characteristic exist in the range, getSpecialCells throws an ItemNotFound error. This diverts the flow of control to a catch block, if there is one. If there isn't a catch block, the error halts the method.

If you expect that cells with the targeted characteristic should always exist, you'll likely want your code to throw an error if those cells aren't there. If it's a valid scenario that there aren't any matching cells, your code should check for this possibility and handle it gracefully without throwing an error. You can achieve this behavior with the getSpecialCellsOrNullObject method and its returned isNullObject property. The following code sample uses this pattern. About this code, note:

• The getSpecialCellsOrNullObject method always returns a proxy object, so it's never null in the ordinary JavaScript sense. But if no matching cells are found, the isNullObject property of the object is set to true.
• It calls context.sync before it tests the isNullObject property. This is a requirement with all *OrNullObject methods and properties, because you always have to load and sync a property in order to read it. However, it's not necessary to explicitly load the isNullObject property. It's automatically loaded by the context.sync even if load is not called on the object. For more information, see *OrNullObject methods and properties.
• You can test this code by first selecting a range that has no formula cells and running it. Then select a range that has at least one cell with a formula and run it again.

await Excel.run(async (context) => {
    let range = context.workbook.getSelectedRange();
    let formulaRanges = range.getSpecialCellsOrNullObject(Excel.SpecialCellType.formulas);
    await context.sync();
        
    if (formulaRanges.isNullObject) {
        console.log("No cells have formulas");
    }
    else {
        formulaRanges.format.fill.color = "pink";
    }
    
    await context.sync();
});

For simplicity, the remaining samples in this article use getSpecialCells.

Narrow the target cells with cell value types

The Range.getSpecialCells() and Range.getSpecialCellsOrNullObject() methods accept an optional second parameter used to further narrow down the targeted cells. This second parameter is an Excel.SpecialCellValueType you use to specify that you only want cells that contain certain types of values.

Test for a single cell value type

The Excel.SpecialCellValueType enum has these four basic types (in addition to the other combined values described later in this section):

• Excel.SpecialCellValueType.errors
• Excel.SpecialCellValueType.logical (which means Boolean)
• Excel.SpecialCellValueType.numbers
• Excel.SpecialCellValueType.text

The next sample finds numerical constants and colors them pink.

Key points:

• Only literal numeric constants are targeted (not formulas that evaluate to numbers, nor Booleans, text, or error cells).
• To test, populate the sheet with literal number values, other kinds of literal values, and formulas.

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();
    let usedRange = sheet.getUsedRange();
    let constantNumberRanges = usedRange.getSpecialCells(
        Excel.SpecialCellType.constants,
        Excel.SpecialCellValueType.numbers);
    constantNumberRanges.format.fill.color = "pink";

    await context.sync();
});

Test for multiple cell value types

Sometimes you need to operate on more than one cell value type, such as all text-valued and all Boolean-valued (Excel.SpecialCellValueType.logical) cells. The Excel.SpecialCellValueType enum has values with combined types. For example, Excel.SpecialCellValueType.logicalText targets all Boolean and all text-valued cells. Excel.SpecialCellValueType.all is the default value, which does not limit the cell value types returned. The following code sample colors all cells with formulas that produce number or Boolean value.

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();
    let usedRange = sheet.getUsedRange();
    let formulaLogicalNumberRanges = usedRange.getSpecialCells(
        Excel.SpecialCellType.formulas,
        Excel.SpecialCellValueType.logicalNumbers);
    formulaLogicalNumberRanges.format.fill.color = "pink";

    await context.sync();
});

Next steps

• Combine special-cell queries with string search for richer auditing.
• Apply formatting, comments, or data validation to the resulting RangeAreas object.

See also

• Excel JavaScript object model in Office Add-ins
• Work with cells using the Excel JavaScript API