Excel.Range class

Range represents a set of one or more contiguous cells such as a cell, a row, a column, block of cells, etc. To learn more about how ranges are used throughout the API, read Work with ranges using the Excel JavaScript API and Work with ranges using the Excel JavaScript API (advanced).

[ API set: ExcelApi 1.1 ]

Extends
OfficeExtension.ClientObject

Properties

address

Represents the range reference in A1-style. Address value will contain the Sheet reference (e.g. "Sheet1!A1:B4"). Read-only.

[ API set: ExcelApi 1.1 ]

addressLocal

Represents range reference for the specified range in the language of the user. Read-only.

[ API set: ExcelApi 1.1 ]

cellCount

Number of cells in the range. This API will return -1 if the cell count exceeds 2^31-1 (2,147,483,647). Read-only.

[ API set: ExcelApi 1.1 ]

columnCount

Represents the total number of columns in the range. Read-only.

[ API set: ExcelApi 1.1 ]

columnHidden

Represents if all columns of the current range are hidden.

[ API set: ExcelApi 1.2 ]

columnIndex

Represents the column number of the first cell in the range. Zero-indexed. Read-only.

[ API set: ExcelApi 1.1 ]

conditionalFormats

Collection of ConditionalFormats that intersect the range. 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.

dataValidation

Returns a data validation object.

[ API set: ExcelApi 1.8 ]

format

Returns a format object, encapsulating the range's font, fill, borders, alignment, and other properties. Read-only.

[ API set: ExcelApi 1.1 ]

formulas

Represents the formula in A1-style notation. When setting formulas to a range, the value argument can be either a single value (a string) or a two-dimensional array. If the argument is a single value, it will be applied to all cells in the range.

[ API set: ExcelApi 1.1 ]

formulasLocal

Represents the formula in A1-style notation, in the user's language and number-formatting locale. For example, the English "=SUM(A1, 1.5)" formula would become "=SUMME(A1; 1,5)" in German. When setting formulas to a range, the value argument can be either a single value (a string) or a two-dimensional array. If the argument is a single value, it will be applied to all cells in the range.

[ API set: ExcelApi 1.1 ]

formulasR1C1

Represents the formula in R1C1-style notation. When setting formulas to a range, the value argument can be either a single value (a string) or a two-dimensional array. If the argument is a single value, it will be applied to all cells in the range.

[ API set: ExcelApi 1.2 ]

hasSpill

Represents if all cells have a spill border. Returns true if all cells have a spill border, or false if all cells do not have a spill border. Returns null if there are cells both with and without spill borders within the range.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

height

Returns the distance in points, for 100% zoom, from top edge of the range to bottom edge of the range. Read-only.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

hidden

Represents if all cells of the current range are hidden. Read-only.

[ API set: ExcelApi 1.2 ]

hyperlink

Represents the hyperlink for the current range.

[ API set: ExcelApi 1.7 ]

isEntireColumn

Represents if the current range is an entire column. Read-only.

[ API set: ExcelApi 1.7 ]

isEntireRow

Represents if the current range is an entire row. Read-only.

[ API set: ExcelApi 1.7 ]

left

Returns the distance in points, for 100% zoom, from left edge of the worksheet to left edge of the range. Read-only.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

linkedDataTypeState

Represents the data type state of each cell. Read-only.

[ API set: ExcelApi 1.9 ]

numberFormat

Represents Excel's number format code for the given range. When setting number format to a range, the value argument can be either a single value (string) or a two-dimensional array. If the argument is a single value, it will be applied to all cells in the range.

[ API set: ExcelApi 1.1 ]

numberFormatLocal

Represents Excel's number format code for the given range, based on the language settings of the user. When setting number format local to a range, the value argument can be either a single value (string) or a two-dimensional array. If the argument is a single value, it will be applied to all cells in the range. Excel does not perform any language or format coercion when getting or setting the numberFormatLocal property. Any returned text uses the locally-formatted strings based on the language specified in the system settings.

[ API set: ExcelApi 1.7 ]

rowCount

Returns the total number of rows in the range. Read-only.

[ API set: ExcelApi 1.1 ]

rowHidden

Represents if all rows of the current range are hidden.

[ API set: ExcelApi 1.2 ]

rowIndex

Returns the row number of the first cell in the range. Zero-indexed. Read-only.

[ API set: ExcelApi 1.1 ]

savedAsArray

Represents if ALL the cells would be saved as an array formula. Returns true if ALL cells would be saved as an array, or false if ALL cells would NOT be saved as an array formula. Returns null if there is a mixture of cells that would and would not be saved as an array formula.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

sort

Represents the range sort of the current range. Read-only.

[ API set: ExcelApi 1.2 ]

style

Represents the style of the current range. If the styles of the cells are inconsistent, null will be returned. For custom styles, the style name will be returned. For built-in styles, a string representing a value in the BuiltInStyle enum will be returned.

[ API set: ExcelApi 1.7 ]

text

Text values of the specified range. The Text value will not depend on the cell width. The # sign substitution that happens in Excel UI will not affect the text value returned by the API. Read-only.

[ API set: ExcelApi 1.1 ]

top

Returns the distance in points, for 100% zoom, from top edge of the worksheet to top edge of the range. Read-only.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

values

Represents the raw values of the specified range. The data returned could be of type string, number, or a boolean. Cells that contain an error will return the error string. When setting values to a range, the value argument can be either a single value (string, number or boolean) or a two-dimensional array. If the argument is a single value, it will be applied to all cells in the range.

[ API set: ExcelApi 1.1 ]

valueTypes

Represents the type of data of each cell. Read-only.

[ API set: ExcelApi 1.1 ]

width

Returns the distance in points, for 100% zoom, from left edge of the range to right edge of the range. Read-only.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

worksheet

The worksheet containing the current range. Read-only.

[ API set: ExcelApi 1.1 ]

Methods

autoFill(destinationRange, autoFillType)

Fills range from the current range to the destination range. The destination range must extend the source either horizontally or vertically. Discontiguous ranges are not supported.

[ API set: ExcelApi 1.9 ]

autoFill(destinationRange, autoFillTypeString)

Fills range from the current range to the destination range. The destination range must extend the source either horizontally or vertically. Discontiguous ranges are not supported.

[ API set: ExcelApi 1.9 ]

calculate()

Calculates a range of cells on a worksheet.

[ API set: ExcelApi 1.6 ]

clear(applyTo)

Clear range values, format, fill, border, etc.

[ API set: ExcelApi 1.1 ]

clear(applyToString)

Clear range values, format, fill, border, etc.

[ API set: ExcelApi 1.1 ]

convertDataTypeToText()

Converts the range cells with datatypes into text.

[ API set: ExcelApi 1.9 ]

convertToLinkedDataType(serviceID, languageCulture)

Converts the range cells into linked datatype in the worksheet.

[ API set: ExcelApi 1.9 ]

copyFrom(sourceRange, copyType, skipBlanks, transpose)

Copies cell data or formatting from the source range or RangeAreas to the current range. The destination range can be of different size than the source range or RangeAreas. The destination will be expanded automatically if it is smaller than the source.

[ API set: ExcelApi 1.9 ]

copyFrom(sourceRange, copyTypeString, skipBlanks, transpose)

Copies cell data or formatting from the source range or RangeAreas to the current range. The destination range can be of different size than the source range or RangeAreas. The destination will be expanded automatically if it is smaller than the source.

[ API set: ExcelApi 1.9 ]

delete(shift)

Deletes the cells associated with the range.

[ API set: ExcelApi 1.1 ]

delete(shiftString)

Deletes the cells associated with the range.

[ API set: ExcelApi 1.1 ]

find(text, criteria)

Finds the given string based on the criteria specified. If the current range is larger than a single cell, then the search will be limited to that range, else the search will cover the entire sheet starting after that cell.

[ API set: ExcelApi 1.9 ]

findOrNullObject(text, criteria)

Finds the given string based on the criteria specified. If the current range is larger than a single cell, then the search will be limited to that range, else the search will cover the entire sheet starting after that cell. If there are no matches, this function will return a null object.

[ API set: ExcelApi 1.9 ]

flashFill()

Does FlashFill to current range.Flash Fill will automatically fills data when it senses a pattern, so the range must be single column range and have data around in order to find pattern.

[ API set: ExcelApi 1.9 ]

getAbsoluteResizedRange(numRows, numColumns)

Gets a Range object with the same top-left cell as the current Range object, but with the specified numbers of rows and columns.

[ API set: ExcelApi 1.7 ]

getBoundingRect(anotherRange)

Gets the smallest range object that encompasses the given ranges. For example, the GetBoundingRect of "B2:C5" and "D10:E15" is "B2:E15".

[ API set: ExcelApi 1.1 ]

getCell(row, column)

Gets the range object containing the single cell based on row and column numbers. The cell can be outside the bounds of its parent range, so long as it stays within the worksheet grid. The returned cell is located relative to the top left cell of the range.

[ API set: ExcelApi 1.1 ]

getCellProperties(cellPropertiesLoadOptions)

Returns a 2D array, encapsulating the data for each cell's font, fill, borders, alignment, and other properties.

[ API set: ExcelApi 1.9 ]

getColumn(column)

Gets a column contained in the range.

[ API set: ExcelApi 1.1 ]

getColumnProperties(columnPropertiesLoadOptions)

Returns a single-dimensional array, encapsulating the data for each column's font, fill, borders, alignment, and other properties. For properties that are not consistent across each cell within a given column, null will be returned.

[ API set: ExcelApi 1.9 ]

getColumnsAfter(count)

Gets a certain number of columns to the right of the current Range object.

[ API set: ExcelApi 1.2 ]

getColumnsBefore(count)

Gets a certain number of columns to the left of the current Range object.

[ API set: ExcelApi 1.2 ]

getEntireColumn()

Gets an object that represents the entire column of the range (for example, if the current range represents cells "B4:E11", its getEntireColumn is a range that represents columns "B:E").

[ API set: ExcelApi 1.1 ]

getEntireRow()

Gets an object that represents the entire row of the range (for example, if the current range represents cells "B4:E11", its GetEntireRow is a range that represents rows "4:11").

[ API set: ExcelApi 1.1 ]

getImage()

Renders the range as a base64-encoded png image.

[ API set: ExcelApi 1.7 ]

getIntersection(anotherRange)

Gets the range object that represents the rectangular intersection of the given ranges.

[ API set: ExcelApi 1.1 ]

getIntersectionOrNullObject(anotherRange)

Gets the range object that represents the rectangular intersection of the given ranges. If no intersection is found, will return a null object.

[ API set: ExcelApi 1.4 ]

getLastCell()

Gets the last cell within the range. For example, the last cell of "B2:D5" is "D5".

[ API set: ExcelApi 1.1 ]

getLastColumn()

Gets the last column within the range. For example, the last column of "B2:D5" is "D2:D5".

[ API set: ExcelApi 1.1 ]

getLastRow()

Gets the last row within the range. For example, the last row of "B2:D5" is "B5:D5".

[ API set: ExcelApi 1.1 ]

getOffsetRange(rowOffset, columnOffset)

Gets an object which represents a range that's offset from the specified range. The dimension of the returned range will match this range. If the resulting range is forced outside the bounds of the worksheet grid, an error will be thrown.

[ API set: ExcelApi 1.1 ]

getResizedRange(deltaRows, deltaColumns)

Gets a Range object similar to the current Range object, but with its bottom-right corner expanded (or contracted) by some number of rows and columns.

[ API set: ExcelApi 1.2 ]

getRow(row)

Gets a row contained in the range.

[ API set: ExcelApi 1.1 ]

getRowProperties(rowPropertiesLoadOptions)

Returns a single-dimensional array, encapsulating the data for each row's font, fill, borders, alignment, and other properties. For properties that are not consistent across each cell within a given row, null will be returned.

[ API set: ExcelApi 1.9 ]

getRowsAbove(count)

Gets a certain number of rows above the current Range object.

[ API set: ExcelApi 1.2 ]

getRowsBelow(count)

Gets a certain number of rows below the current Range object.

[ API set: ExcelApi 1.2 ]

getSpecialCells(cellType, cellValueType)

Gets the RangeAreas object, comprising one or more rectangular ranges, that represents all the cells that match the specified type and value. If no special cells are found, an ItemNotFound error will be thrown.

[ API set: ExcelApi 1.9 ]

getSpecialCells(cellTypeString, cellValueType)

Gets the RangeAreas object, comprising one or more rectangular ranges, that represents all the cells that match the specified type and value. If no special cells are found, an ItemNotFound error will be thrown.

[ API set: ExcelApi 1.9 ]

getSpecialCellsOrNullObject(cellType, cellValueType)

Gets the RangeAreas object, comprising one or more ranges, that represents all the cells that match the specified type and value. If no special cells are found, a null object will be returned.

[ API set: ExcelApi 1.9 ]

getSpecialCellsOrNullObject(cellTypeString, cellValueType)

Gets the RangeAreas object, comprising one or more ranges, that represents all the cells that match the specified type and value. If no special cells are found, a null object will be returned.

[ API set: ExcelApi 1.9 ]

getSpillingToRange()

Gets the range object containing the spill range when called on an anchor cell. Fails if applied to a range with more than one cell. Read-only.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

getSpillingToRangeOrNullObject()

Gets the range object containing the spill range when called on an anchor cell. Read-only. If the range is not an anchor cell or spill range can't be found, a null object will be returned.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

getSpillParent()

Gets the range object containing the anchor cell for a cell getting spilled into. Fails if applied to a range with more than one cell. Read-only.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

getSpillParentOrNullObject()

Gets the range object containing the anchor cell for a cell getting spilled into. Read-only. If it is not a spill cell or more than once cells are give, a null object will be returned.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

getSurroundingRegion()

Returns a Range object that represents the surrounding region for the top-left cell in this range. A surrounding region is a range bounded by any combination of blank rows and blank columns relative to this range.

[ API set: ExcelApi 1.7 ]

getTables(fullyContained)

Gets a scoped collection of tables that overlap with the range.

[ API set: ExcelApi 1.9 ]

getUsedRange(valuesOnly)

Returns the used range of the given range object. If there are no used cells within the range, this function will throw an ItemNotFound error.

[ API set: ExcelApi 1.1 ]

getUsedRangeOrNullObject(valuesOnly)

Returns the used range of the given range object. If there are no used cells within the range, this function will return a null object.

[ API set: ExcelApi 1.4 ]

getVisibleView()

Represents the visible rows of the current range.

[ API set: ExcelApi 1.3 ]

group(groupOption)

Groups columns and rows for an outline.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

group(groupOptionString)

Groups columns and rows for an outline.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

hideGroupDetails(groupOption)

Hide details of the row or column group.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

hideGroupDetails(groupOptionString)

Hide details of the row or column group.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

insert(shift)

Inserts a cell or a range of cells into the worksheet in place of this range, and shifts the other cells to make space. Returns a new Range object at the now blank space.

[ API set: ExcelApi 1.1 ]

insert(shiftString)

Inserts a cell or a range of cells into the worksheet in place of this range, and shifts the other cells to make space. Returns a new Range object at the now blank space.

[ API set: ExcelApi 1.1 ]

load(option)

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

load(propertyNames)

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

load(propertyNamesAndPaths)

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

merge(across)

Merge the range cells into one region in the worksheet.

[ API set: ExcelApi 1.2 ]

removeDuplicates(columns, includesHeader)

Removes duplicate values from the range specified by the columns.

[ API set: ExcelApi 1.9 ]

replaceAll(text, replacement, criteria)

Finds and replaces the given string based on the criteria specified within the current range.

[ API set: ExcelApi 1.9 ]

select()

Selects the specified range in the Excel UI.

[ API set: ExcelApi 1.1 ]

set(properties, options)

Sets multiple properties of an object at the same time. You can pass either a plain object with the appropriate properties, or another API object of the same type.

set(properties)

Sets multiple properties on the object at the same time, based on an existing loaded object.

setCellProperties(cellPropertiesData)

Updates the range based on a 2D array of cell properties , encapsulating things like font, fill, borders, alignment, and so forth.

[ API set: ExcelApi 1.9 ]

setColumnProperties(columnPropertiesData)

Updates the range based on a single-dimensional array of column properties, encapsulating things like font, fill, borders, alignment, and so forth.

[ API set: ExcelApi 1.9 ]

setDirty()

Set a range to be recalculated when the next recalculation occurs.

[ API set: ExcelApi 1.9 ]

setRowProperties(rowPropertiesData)

Updates the range based on a single-dimensional array of row properties, encapsulating things like font, fill, borders, alignment, and so forth.

[ API set: ExcelApi 1.9 ]

showCard()

Displays the card for an active cell if it has rich value content.

[ API set: ExcelApi 1.7 ]

showGroupDetails(groupOption)

Show details of the row or column group.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

showGroupDetails(groupOptionString)

Show details of the row or column group.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

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.Range object is an API object, the toJSON method returns a plain JavaScript object (typed as Excel.Interfaces.RangeData) that contains shallow copies of any loaded child properties from the original object.

track()

Track the object for automatic adjustment based on surrounding changes in the document. This call is a shorthand for context.trackedObjects.add(thisObject). If you are using this object across ".sync" calls and outside the sequential execution of a ".run" batch, and get an "InvalidObjectPath" error when setting a property or invoking a method on the object, you needed to have added the object to the tracked object collection when the object was first created.

ungroup(groupOption)

Ungroups columns and rows for an outline.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

ungroup(groupOptionString)

Ungroups columns and rows for an outline.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

unmerge()

Unmerge the range cells into separate cells.

[ API set: ExcelApi 1.2 ]

untrack()

Release the memory associated with this object, if it has previously been tracked. This call is shorthand for context.trackedObjects.remove(thisObject). Having many tracked objects slows down the host application, so please remember to free any objects you add, once you're done using them. You will need to call "context.sync()" before the memory release takes effect.

Property Details

address

Represents the range reference in A1-style. Address value will contain the Sheet reference (e.g. "Sheet1!A1:B4"). Read-only.

[ API set: ExcelApi 1.1 ]

readonly address: string;

Property Value

string

addressLocal

Represents range reference for the specified range in the language of the user. Read-only.

[ API set: ExcelApi 1.1 ]

readonly addressLocal: string;

Property Value

string

cellCount

Number of cells in the range. This API will return -1 if the cell count exceeds 2^31-1 (2,147,483,647). Read-only.

[ API set: ExcelApi 1.1 ]

readonly cellCount: number;

Property Value

number

columnCount

Represents the total number of columns in the range. Read-only.

[ API set: ExcelApi 1.1 ]

readonly columnCount: number;

Property Value

number

columnHidden

Represents if all columns of the current range are hidden.

[ API set: ExcelApi 1.2 ]

columnHidden: boolean;

Property Value

boolean

columnIndex

Represents the column number of the first cell in the range. Zero-indexed. Read-only.

[ API set: ExcelApi 1.1 ]

readonly columnIndex: number;

Property Value

number

conditionalFormats

Collection of ConditionalFormats that intersect the range. Read-only.

[ API set: ExcelApi 1.6 ]

readonly conditionalFormats: Excel.ConditionalFormatCollection;

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

dataValidation

Returns a data validation object.

[ API set: ExcelApi 1.8 ]

readonly dataValidation: Excel.DataValidation;

Property Value

format

Returns a format object, encapsulating the range's font, fill, borders, alignment, and other properties. Read-only.

[ API set: ExcelApi 1.1 ]

readonly format: Excel.RangeFormat;

Property Value

formulas

Represents the formula in A1-style notation. When setting formulas to a range, the value argument can be either a single value (a string) or a two-dimensional array. If the argument is a single value, it will be applied to all cells in the range.

[ API set: ExcelApi 1.1 ]

formulas: any[][];

Property Value

any[][]

formulasLocal

Represents the formula in A1-style notation, in the user's language and number-formatting locale. For example, the English "=SUM(A1, 1.5)" formula would become "=SUMME(A1; 1,5)" in German. When setting formulas to a range, the value argument can be either a single value (a string) or a two-dimensional array. If the argument is a single value, it will be applied to all cells in the range.

[ API set: ExcelApi 1.1 ]

formulasLocal: any[][];

Property Value

any[][]

formulasR1C1

Represents the formula in R1C1-style notation. When setting formulas to a range, the value argument can be either a single value (a string) or a two-dimensional array. If the argument is a single value, it will be applied to all cells in the range.

[ API set: ExcelApi 1.2 ]

formulasR1C1: any[][];

Property Value

any[][]

hasSpill

Note

This API is provided as a preview for developers and may change based on feedback that we receive. Do not use this API in a production environment.

Represents if all cells have a spill border. Returns true if all cells have a spill border, or false if all cells do not have a spill border. Returns null if there are cells both with and without spill borders within the range.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

readonly hasSpill: boolean;

Property Value

boolean

height

Note

This API is provided as a preview for developers and may change based on feedback that we receive. Do not use this API in a production environment.

Returns the distance in points, for 100% zoom, from top edge of the range to bottom edge of the range. Read-only.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

readonly height: number;

Property Value

number

hidden

Represents if all cells of the current range are hidden. Read-only.

[ API set: ExcelApi 1.2 ]

readonly hidden: boolean;

Property Value

boolean

Represents the hyperlink for the current range.

[ API set: ExcelApi 1.7 ]

hyperlink: Excel.RangeHyperlink;

Property Value

Examples

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Orders");

    let productsRange = sheet.getRange("A3:A5");
    productsRange.load("values");

    await context.sync();

    // Create a hyperlink to a URL 
    // for each product name in the first table.
    for (let i = 0; i < productsRange.values.length; i++) {
        let cellRange = productsRange.getCell(i, 0);
        let cellText = productsRange.values[i][0];

        let hyperlink = {
            textToDisplay: cellText,
            screenTip: "Search Bing for '" + cellText + "'",
            address: "https://www.bing.com?q=" + cellText
        }
        cellRange.hyperlink = hyperlink;
    }

    await context.sync();
});

isEntireColumn

Represents if the current range is an entire column. Read-only.

[ API set: ExcelApi 1.7 ]

readonly isEntireColumn: boolean;

Property Value

boolean

isEntireRow

Represents if the current range is an entire row. Read-only.

[ API set: ExcelApi 1.7 ]

readonly isEntireRow: boolean;

Property Value

boolean

left

Note

This API is provided as a preview for developers and may change based on feedback that we receive. Do not use this API in a production environment.

Returns the distance in points, for 100% zoom, from left edge of the worksheet to left edge of the range. Read-only.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

readonly left: number;

Property Value

number

linkedDataTypeState

Represents the data type state of each cell. Read-only.

[ API set: ExcelApi 1.9 ]

readonly linkedDataTypeState: Excel.LinkedDataTypeState[][];

Property Value

Excel.LinkedDataTypeState[][]

numberFormat

Represents Excel's number format code for the given range. When setting number format to a range, the value argument can be either a single value (string) or a two-dimensional array. If the argument is a single value, it will be applied to all cells in the range.

[ API set: ExcelApi 1.1 ]

numberFormat: any[][];

Property Value

any[][]

numberFormatLocal

Represents Excel's number format code for the given range, based on the language settings of the user. When setting number format local to a range, the value argument can be either a single value (string) or a two-dimensional array. If the argument is a single value, it will be applied to all cells in the range. Excel does not perform any language or format coercion when getting or setting the numberFormatLocal property. Any returned text uses the locally-formatted strings based on the language specified in the system settings.

[ API set: ExcelApi 1.7 ]

numberFormatLocal: any[][];

Property Value

any[][]

rowCount

Returns the total number of rows in the range. Read-only.

[ API set: ExcelApi 1.1 ]

readonly rowCount: number;

Property Value

number

rowHidden

Represents if all rows of the current range are hidden.

[ API set: ExcelApi 1.2 ]

rowHidden: boolean;

Property Value

boolean

rowIndex

Returns the row number of the first cell in the range. Zero-indexed. Read-only.

[ API set: ExcelApi 1.1 ]

readonly rowIndex: number;

Property Value

number

savedAsArray

Note

This API is provided as a preview for developers and may change based on feedback that we receive. Do not use this API in a production environment.

Represents if ALL the cells would be saved as an array formula. Returns true if ALL cells would be saved as an array, or false if ALL cells would NOT be saved as an array formula. Returns null if there is a mixture of cells that would and would not be saved as an array formula.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

readonly savedAsArray: boolean;

Property Value

boolean

sort

Represents the range sort of the current range. Read-only.

[ API set: ExcelApi 1.2 ]

readonly sort: Excel.RangeSort;

Property Value

style

Represents the style of the current range. If the styles of the cells are inconsistent, null will be returned. For custom styles, the style name will be returned. For built-in styles, a string representing a value in the BuiltInStyle enum will be returned.

[ API set: ExcelApi 1.7 ]

style: string;

Property Value

string

Examples

await Excel.run(async (context) => {
    let worksheet = context.workbook.worksheets.getItem("Sample");
    let range = worksheet.getRange("A1:E1");

    // Apply built-in style. 
    // Styles are in the Home tab ribbon.
    range.style = Excel.BuiltInStyle.neutral;
    range.format.horizontalAlignment = "Right";

    await context.sync();
});

text

Text values of the specified range. The Text value will not depend on the cell width. The # sign substitution that happens in Excel UI will not affect the text value returned by the API. Read-only.

[ API set: ExcelApi 1.1 ]

readonly text: string[][];

Property Value

string[][]

top

Note

This API is provided as a preview for developers and may change based on feedback that we receive. Do not use this API in a production environment.

Returns the distance in points, for 100% zoom, from top edge of the worksheet to top edge of the range. Read-only.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

readonly top: number;

Property Value

number

values

Represents the raw values of the specified range. The data returned could be of type string, number, or a boolean. Cells that contain an error will return the error string. When setting values to a range, the value argument can be either a single value (string, number or boolean) or a two-dimensional array. If the argument is a single value, it will be applied to all cells in the range.

[ API set: ExcelApi 1.1 ]

values: any[][];

Property Value

any[][]

valueTypes

Represents the type of data of each cell. Read-only.

[ API set: ExcelApi 1.1 ]

readonly valueTypes: Excel.RangeValueType[][];

Property Value

Excel.RangeValueType[][]

width

Note

This API is provided as a preview for developers and may change based on feedback that we receive. Do not use this API in a production environment.

Returns the distance in points, for 100% zoom, from left edge of the range to right edge of the range. Read-only.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

readonly width: number;

Property Value

number

worksheet

The worksheet containing the current range. Read-only.

[ API set: ExcelApi 1.1 ]

readonly worksheet: Excel.Worksheet;

Property Value

Method Details

autoFill(destinationRange, autoFillType)

Fills range from the current range to the destination range. The destination range must extend the source either horizontally or vertically. Discontiguous ranges are not supported.

[ API set: ExcelApi 1.9 ]

autoFill(destinationRange: Range | string, autoFillType?: Excel.AutoFillType): void;

Parameters

destinationRange
Range | string

The destination range to autofill.

autoFillType
Excel.AutoFillType

The type of autofill. Specifies how the destination range is to be filled, based on the contents of the current range. Default is "FillDefault".

Returns

void

autoFill(destinationRange, autoFillTypeString)

Fills range from the current range to the destination range. The destination range must extend the source either horizontally or vertically. Discontiguous ranges are not supported.

[ API set: ExcelApi 1.9 ]

autoFill(destinationRange: Range | string, autoFillTypeString?: "FillDefault" | "FillCopy" | "FillSeries" | "FillFormats" | "FillValues" | "FillDays" | "FillWeekdays" | "FillMonths" | "FillYears" | "LinearTrend" | "GrowthTrend" | "FlashFill"): void;

Parameters

destinationRange
Range | string

The destination range to autofill.

autoFillTypeString
"FillDefault" | "FillCopy" | "FillSeries" | "FillFormats" | "FillValues" | "FillDays" | "FillWeekdays" | "FillMonths" | "FillYears" | "LinearTrend" | "GrowthTrend" | "FlashFill"

The type of autofill. Specifies how the destination range is to be filled, based on the contents of the current range. Default is "FillDefault".

Returns

void

calculate()

Calculates a range of cells on a worksheet.

[ API set: ExcelApi 1.6 ]

calculate(): void;

Returns

void

clear(applyTo)

Clear range values, format, fill, border, etc.

[ API set: ExcelApi 1.1 ]

clear(applyTo?: Excel.ClearApplyTo): void;

Parameters

applyTo
Excel.ClearApplyTo

Optional. Determines the type of clear action. See Excel.ClearApplyTo for details.

Returns

void

Examples

// Below example clears format and contents of the range. 
Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "D:F";
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    range.clear();
    return ctx.sync(); 
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

clear(applyToString)

Clear range values, format, fill, border, etc.

[ API set: ExcelApi 1.1 ]

clear(applyToString?: "All" | "Formats" | "Contents" | "Hyperlinks" | "RemoveHyperlinks"): void;

Parameters

applyToString
"All" | "Formats" | "Contents" | "Hyperlinks" | "RemoveHyperlinks"

Optional. Determines the type of clear action. See Excel.ClearApplyTo for details.

Returns

void

convertDataTypeToText()

Converts the range cells with datatypes into text.

[ API set: ExcelApi 1.9 ]

convertDataTypeToText(): void;

Returns

void

convertToLinkedDataType(serviceID, languageCulture)

Converts the range cells into linked datatype in the worksheet.

[ API set: ExcelApi 1.9 ]

convertToLinkedDataType(serviceID: number, languageCulture: string): void;

Parameters

serviceID
number

The Service ID which will be used to query the data.

languageCulture
string

Language Culture to query the service for.

Returns

void

copyFrom(sourceRange, copyType, skipBlanks, transpose)

Copies cell data or formatting from the source range or RangeAreas to the current range. The destination range can be of different size than the source range or RangeAreas. The destination will be expanded automatically if it is smaller than the source.

[ API set: ExcelApi 1.9 ]

copyFrom(sourceRange: Range | RangeAreas | string, copyType?: Excel.RangeCopyType, skipBlanks?: boolean, transpose?: boolean): void;

Parameters

sourceRange
Range | RangeAreas | string

The source range or RangeAreas to copy from. When the source RangeAreas has multiple ranges, it must in the outline form which can be created by removing full rows or columns from a rectangular range.

copyType
Excel.RangeCopyType

The type of cell data or formatting to copy over. Default is "All".

skipBlanks
boolean

True if to skip blank cells in the source range. Default is false.

transpose
boolean

True if to transpose the cells in the destination range. Default is false.

Returns

void

Examples

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    sheet.getRange("F2").values = [["Copied Formula"]];
    sheet.getRange("F2").format.autofitColumns();

    // copy a range preserving the formulas
    // note: non-formula values are copied over as is
    sheet.getRange("G2").copyFrom("A1:E1", Excel.RangeCopyType.formulas);
    await context.sync();
});

copyFrom(sourceRange, copyTypeString, skipBlanks, transpose)

Copies cell data or formatting from the source range or RangeAreas to the current range. The destination range can be of different size than the source range or RangeAreas. The destination will be expanded automatically if it is smaller than the source.

[ API set: ExcelApi 1.9 ]

copyFrom(sourceRange: Range | RangeAreas | string, copyTypeString?: "All" | "Formulas" | "Values" | "Formats", skipBlanks?: boolean, transpose?: boolean): void;

Parameters

sourceRange
Range | RangeAreas | string

The source range or RangeAreas to copy from. When the source RangeAreas has multiple ranges, it must in the outline form which can be created by removing full rows or columns from a rectangular range.

copyTypeString
"All" | "Formulas" | "Values" | "Formats"

The type of cell data or formatting to copy over. Default is "All".

skipBlanks
boolean

True if to skip blank cells in the source range. Default is false.

transpose
boolean

True if to transpose the cells in the destination range. Default is false.

Returns

void

delete(shift)

Deletes the cells associated with the range.

[ API set: ExcelApi 1.1 ]

delete(shift: Excel.DeleteShiftDirection): void;

Parameters

shift
Excel.DeleteShiftDirection

Specifies which way to shift the cells. See Excel.DeleteShiftDirection for details.

Returns

void

delete(shiftString)

Deletes the cells associated with the range.

[ API set: ExcelApi 1.1 ]

delete(shiftString: "Up" | "Left"): void;

Parameters

shiftString
"Up" | "Left"

Specifies which way to shift the cells. See Excel.DeleteShiftDirection for details.

Returns

void

Examples

Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "D:F";
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    range.delete("Left");
    return ctx.sync(); 
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

find(text, criteria)

Finds the given string based on the criteria specified. If the current range is larger than a single cell, then the search will be limited to that range, else the search will cover the entire sheet starting after that cell.

[ API set: ExcelApi 1.9 ]

find(text: string, criteria: Excel.SearchCriteria): Excel.Range;

Parameters

text
string

The string to find.

criteria
Excel.SearchCriteria

Additional search criteria, including the search direction and whether the search needs to match the entire cell or be case sensitive.

Returns

The Range which matched the search criteria.

Examples

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const table = sheet.tables.getItem("ExpensesTable");
    const searchRange = table.getRange();

    // NOTE: If no match is found, an ItemNotFound error
    // is thrown when Range.find is evaluated.
    const foundRange = searchRange.find($("#searchText").text(), {
        completeMatch: completeMatch,
        matchCase: matchCase,
        searchDirection: searchDirection
    });
    
    foundRange.load("address");
    await context.sync();


    console.log(foundRange.address);
});

findOrNullObject(text, criteria)

Finds the given string based on the criteria specified. If the current range is larger than a single cell, then the search will be limited to that range, else the search will cover the entire sheet starting after that cell. If there are no matches, this function will return a null object.

[ API set: ExcelApi 1.9 ]

findOrNullObject(text: string, criteria: Excel.SearchCriteria): Excel.Range;

Parameters

text
string

The string to find.

criteria
Excel.SearchCriteria

Additional search criteria, including the search direction and whether the search needs to match the entire cell or be case sensitive.

Returns

The Range which matched the search criteria.

Examples

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const table = sheet.tables.getItem("ExpensesTable");
    const searchRange = table.getRange();
    const foundRange = searchRange.findOrNullObject($("#searchText").text(), {
        completeMatch: completeMatch,
        matchCase: matchCase,
        searchDirection: searchDirection
    });
    
    foundRange.load("address");
    await context.sync();

    if (foundRange.isNullObject) {
        console.log("Text not found");
    } else {
        console.log(foundRange.address);
    }
});

flashFill()

Does FlashFill to current range.Flash Fill will automatically fills data when it senses a pattern, so the range must be single column range and have data around in order to find pattern.

[ API set: ExcelApi 1.9 ]

flashFill(): void;

Returns

void

getAbsoluteResizedRange(numRows, numColumns)

Gets a Range object with the same top-left cell as the current Range object, but with the specified numbers of rows and columns.

[ API set: ExcelApi 1.7 ]

getAbsoluteResizedRange(numRows: number, numColumns: number): Excel.Range;

Parameters

numRows
number

The number of rows of the new range size.

numColumns
number

The number of columns of the new range size.

Returns

getBoundingRect(anotherRange)

Gets the smallest range object that encompasses the given ranges. For example, the GetBoundingRect of "B2:C5" and "D10:E15" is "B2:E15".

[ API set: ExcelApi 1.1 ]

getBoundingRect(anotherRange: Range | string): Excel.Range;

Parameters

anotherRange
Range | string

The range object or address or range name.

Returns

Examples

Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "D4:G6";
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    var range = range.getBoundingRect("G4:H8");
    range.load('address');
    return ctx.sync().then(function() {
        console.log(range.address); // Prints Sheet1!D4:H8
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

getCell(row, column)

Gets the range object containing the single cell based on row and column numbers. The cell can be outside the bounds of its parent range, so long as it stays within the worksheet grid. The returned cell is located relative to the top left cell of the range.

[ API set: ExcelApi 1.1 ]

getCell(row: number, column: number): Excel.Range;

Parameters

row
number

Row number of the cell to be retrieved. Zero-indexed.

column
number

Column number of the cell to be retrieved. Zero-indexed.

Returns

Examples

Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "A1:F8";
    var worksheet = ctx.workbook.worksheets.getItem(sheetName);
    var range = worksheet.getRange(rangeAddress);
    var cell = range.cell(0,0);
    cell.load('address');
    return ctx.sync().then(function() {
        console.log(cell.address);
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

getCellProperties(cellPropertiesLoadOptions)

Returns a 2D array, encapsulating the data for each cell's font, fill, borders, alignment, and other properties.

[ API set: ExcelApi 1.9 ]

getCellProperties(cellPropertiesLoadOptions: CellPropertiesLoadOptions): OfficeExtension.ClientResult<CellProperties[][]>;

Parameters

cellPropertiesLoadOptions
Excel.CellPropertiesLoadOptions

An object that represents which cell properties to load.

Returns

OfficeExtension.ClientResult<CellProperties[][]>

A 2D array where each item represents the requested properties of the corresponding cell.

Examples

await Excel.run(async (context) => {
    const cell = context.workbook.getActiveCell();

    // Define the cell properties to get by setting the matching LoadOptions to true.
    const propertiesToGet = cell.getCellProperties({
        address: true,
        format: {
            fill: {
                color: true
            },
            font: {
                color: true
            }
        },
        style: true
    });

    // Sync to get the data from the workbook.
    await context.sync();
    const cellProperties = propertiesToGet.value[0][0];
    console.log(
        `Address: ${cellProperties.address}\nStyle: ${cellProperties.style}\nFill Color: ${cellProperties.format.fill.color}\nFont Color: ${cellProperties.format.font.color}`);
});

getColumn(column)

Gets a column contained in the range.

[ API set: ExcelApi 1.1 ]

getColumn(column: number): Excel.Range;

Parameters

column
number

Column number of the range to be retrieved. Zero-indexed.

Returns

Examples

Excel.run(function (ctx) { 
    var sheetName = "Sheet19";
    var rangeAddress = "A1:F8";
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress).getColumn(1);
    range.load('address');
    return ctx.sync().then(function() {
        console.log(range.address); // prints Sheet1!B1:B8
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

getColumnProperties(columnPropertiesLoadOptions)

Returns a single-dimensional array, encapsulating the data for each column's font, fill, borders, alignment, and other properties. For properties that are not consistent across each cell within a given column, null will be returned.

[ API set: ExcelApi 1.9 ]

getColumnProperties(columnPropertiesLoadOptions: ColumnPropertiesLoadOptions): OfficeExtension.ClientResult<ColumnProperties[]>;

Parameters

columnPropertiesLoadOptions
Excel.ColumnPropertiesLoadOptions

An object that represents which column properties to load.

Returns

OfficeExtension.ClientResult<ColumnProperties[]>

An array where each item represents the requested properties of the corresponding column.

getColumnsAfter(count)

Gets a certain number of columns to the right of the current Range object.

[ API set: ExcelApi 1.2 ]

getColumnsAfter(count?: number): Excel.Range;

Parameters

count
number

Optional. The number of columns to include in the resulting range. In general, use a positive number to create a range outside the current range. You can also use a negative number to create a range within the current range. The default value is 1.

Returns

getColumnsBefore(count)

Gets a certain number of columns to the left of the current Range object.

[ API set: ExcelApi 1.2 ]

getColumnsBefore(count?: number): Excel.Range;

Parameters

count
number

Optional. The number of columns to include in the resulting range. In general, use a positive number to create a range outside the current range. You can also use a negative number to create a range within the current range. The default value is 1.

Returns

getEntireColumn()

Gets an object that represents the entire column of the range (for example, if the current range represents cells "B4:E11", its getEntireColumn is a range that represents columns "B:E").

[ API set: ExcelApi 1.1 ]

getEntireColumn(): Excel.Range;

Returns

Examples

// Note: the grid properties of the Range (values, numberFormat, formulas) 
// contains null since the Range in question is unbounded.
Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "D:F";
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    var rangeEC = range.getEntireColumn();
    rangeEC.load('address');
    return ctx.sync().then(function() {
        console.log(rangeEC.address);
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

getEntireRow()

Gets an object that represents the entire row of the range (for example, if the current range represents cells "B4:E11", its GetEntireRow is a range that represents rows "4:11").

[ API set: ExcelApi 1.1 ]

getEntireRow(): Excel.Range;

Returns

Examples

// Gets an object that represents the entire row of the range 
// (for example, if the current range represents cells "B4:E11", 
// its GetEntireRow is a range that represents rows "4:11").
Excel.run(function (ctx) {
    var sheetName = "Sheet1";
    var rangeAddress = "D:F"; 
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    var rangeER = range.getEntireRow();
    rangeER.load('address');
    return ctx.sync().then(function() {
        console.log(rangeER.address);
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

getImage()

Renders the range as a base64-encoded png image.

[ API set: ExcelApi 1.7 ]

getImage(): OfficeExtension.ClientResult<string>;

Returns

OfficeExtension.ClientResult<string>

getIntersection(anotherRange)

Gets the range object that represents the rectangular intersection of the given ranges.

[ API set: ExcelApi 1.1 ]

getIntersection(anotherRange: Range | string): Excel.Range;

Parameters

anotherRange
Range | string

The range object or range address that will be used to determine the intersection of ranges.

Returns

Examples

Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "A1:F8";
    var range = 
        ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress).getIntersection("D4:G6");
    range.load('address');
    return ctx.sync().then(function() {
        console.log(range.address); // prints Sheet1!D4:F6
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

getIntersectionOrNullObject(anotherRange)

Gets the range object that represents the rectangular intersection of the given ranges. If no intersection is found, will return a null object.

[ API set: ExcelApi 1.4 ]

getIntersectionOrNullObject(anotherRange: Range | string): Excel.Range;

Parameters

anotherRange
Range | string

The range object or range address that will be used to determine the intersection of ranges.

Returns

Examples

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const salesTable = sheet.tables.getItem("SalesTable");
    const dataRange = salesTable.getDataBodyRange();

    // We want the most recent quarter that has data, so
    // exclude quarters without data and get the last of
    // the remaining columns.
    const usedDataRange = dataRange.getUsedRange(true /* valuesOnly */);
    const currentQuarterRange = usedDataRange.getLastColumn();

    // Asian and European teams have separate contests.
    const asianSalesRange = sheet.getRange("A2:E4");
    const europeanSalesRange = sheet.getRange("A5:E7");

    // The data for each chart is the intersection of the
    // current quarter column and the rows for the continent.
    const asianContestRange = asianSalesRange.getIntersectionOrNullObject(currentQuarterRange);
    const europeanContestRange = europeanSalesRange.getIntersectionOrNullObject(currentQuarterRange);

    // Must sync before you can test the output of *OrNullObject
    // method/property.
    await context.sync();

    if (asianContestRange.isNullObject) {
        // See the declaration of this method for how to
        // test this code path.
        reportMissingData("Asian");
    } else {
        createContinentChart(
            sheet,
            "Asian",
            asianContestRange,
            "A9",
            "F24"
        );
    }

    if (europeanContestRange.isNullObject) {
        // See the declaration of this method for how to
        // test this code path.
        reportMissingData("European");
    } else {
        createContinentChart(
            sheet,
            "European",
            europeanContestRange,
            "A25",
            "F40"
        );
    }

    await context.sync();
});

getLastCell()

Gets the last cell within the range. For example, the last cell of "B2:D5" is "D5".

[ API set: ExcelApi 1.1 ]

getLastCell(): Excel.Range;

Returns

Examples

Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "A1:F8";
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress).getLastCell();
    range.load('address');
    return ctx.sync().then(function() {
        console.log(range.address); // prints Sheet1!F8
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

getLastColumn()

Gets the last column within the range. For example, the last column of "B2:D5" is "D2:D5".

[ API set: ExcelApi 1.1 ]

getLastColumn(): Excel.Range;

Returns

Examples

Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "A1:F8";
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress).getLastColumn();
    range.load('address');
    return ctx.sync().then(function() {
        console.log(range.address); // prints Sheet1!F1:F8
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

getLastRow()

Gets the last row within the range. For example, the last row of "B2:D5" is "B5:D5".

[ API set: ExcelApi 1.1 ]

getLastRow(): Excel.Range;

Returns

Examples

Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "A1:F8";
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress).getLastRow();
    range.load('address');
    return ctx.sync().then(function() {
        console.log(range.address); // prints Sheet1!A8:F8
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

getOffsetRange(rowOffset, columnOffset)

Gets an object which represents a range that's offset from the specified range. The dimension of the returned range will match this range. If the resulting range is forced outside the bounds of the worksheet grid, an error will be thrown.

[ API set: ExcelApi 1.1 ]

getOffsetRange(rowOffset: number, columnOffset: number): Excel.Range;

Parameters

rowOffset
number

The number of rows (positive, negative, or 0) by which the range is to be offset. Positive values are offset downward, and negative values are offset upward.

columnOffset
number

The number of columns (positive, negative, or 0) by which the range is to be offset. Positive values are offset to the right, and negative values are offset to the left.

Returns

Examples

Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "D4:F6";
    var range = 
        ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress).getOffsetRange(-1,4);
    range.load('address');
    return ctx.sync().then(function() {
        console.log(range.address); // prints Sheet1!H3:K5
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

getResizedRange(deltaRows, deltaColumns)

Gets a Range object similar to the current Range object, but with its bottom-right corner expanded (or contracted) by some number of rows and columns.

[ API set: ExcelApi 1.2 ]

getResizedRange(deltaRows: number, deltaColumns: number): Excel.Range;

Parameters

deltaRows
number

The number of rows by which to expand the bottom-right corner, relative to the current range. Use a positive number to expand the range, or a negative number to decrease it.

deltaColumns
number

The number of columns by which to expand the bottom-right corner, relative to the current range. Use a positive number to expand the range, or a negative number to decrease it.

Returns

getRow(row)

Gets a row contained in the range.

[ API set: ExcelApi 1.1 ]

getRow(row: number): Excel.Range;

Parameters

row
number

Row number of the range to be retrieved. Zero-indexed.

Returns

Examples

Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "A1:F8";
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress).getRow(1);
    range.load('address');
    return ctx.sync().then(function() {
        console.log(range.address); // prints Sheet1!A2:F2
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

getRowProperties(rowPropertiesLoadOptions)

Returns a single-dimensional array, encapsulating the data for each row's font, fill, borders, alignment, and other properties. For properties that are not consistent across each cell within a given row, null will be returned.

[ API set: ExcelApi 1.9 ]

getRowProperties(rowPropertiesLoadOptions: RowPropertiesLoadOptions): OfficeExtension.ClientResult<RowProperties[]>;

Parameters

rowPropertiesLoadOptions
Excel.RowPropertiesLoadOptions

An object that represents which row properties to load.

Returns

OfficeExtension.ClientResult<RowProperties[]>

An array where each item represents the requested properties of the corresponding row.

getRowsAbove(count)

Gets a certain number of rows above the current Range object.

[ API set: ExcelApi 1.2 ]

getRowsAbove(count?: number): Excel.Range;

Parameters

count
number

Optional. The number of rows to include in the resulting range. In general, use a positive number to create a range outside the current range. You can also use a negative number to create a range within the current range. The default value is 1.

Returns

getRowsBelow(count)

Gets a certain number of rows below the current Range object.

[ API set: ExcelApi 1.2 ]

getRowsBelow(count?: number): Excel.Range;

Parameters

count
number

Optional. The number of rows to include in the resulting range. In general, use a positive number to create a range outside the current range. You can also use a negative number to create a range within the current range. The default value is 1.

Returns

getSpecialCells(cellType, cellValueType)

Gets the RangeAreas object, comprising one or more rectangular ranges, that represents all the cells that match the specified type and value. If no special cells are found, an ItemNotFound error will be thrown.

[ API set: ExcelApi 1.9 ]

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

Parameters

cellType
Excel.SpecialCellType

The type of cells to include.

cellValueType
Excel.SpecialCellValueType

If cellType is either Constants or Formulas, this argument is used to determine which types of cells to include in the result. These values can be combined together to return more than one type. The default is to select all constants or formulas, no matter what the type.

Returns

Examples

await Excel.run(async (context) => {

    const sheet = context.workbook.worksheets.getActiveWorksheet();
    const usedRange = sheet.getUsedRange();

    // Find the ranges with either text or logical (boolean) values.
    const formulaRanges = usedRange.getSpecialCells("Constants", "LogicalText");
    formulaRanges.format.fill.color = "orange";

    return context.sync();
});

getSpecialCells(cellTypeString, cellValueType)

Gets the RangeAreas object, comprising one or more rectangular ranges, that represents all the cells that match the specified type and value. If no special cells are found, an ItemNotFound error will be thrown.

[ API set: ExcelApi 1.9 ]

getSpecialCells(cellTypeString: "ConditionalFormats" | "DataValidations" | "Blanks" | "Constants" | "Formulas" | "SameConditionalFormat" | "SameDataValidation" | "Visible", cellValueType?: "All" | "Errors" | "ErrorsLogical" | "ErrorsNumbers" | "ErrorsText" | "ErrorsLogicalNumber" | "ErrorsLogicalText" | "ErrorsNumberText" | "Logical" | "LogicalNumbers" | "LogicalText" | "LogicalNumbersText" | "Numbers" | "NumbersText" | "Text"): Excel.RangeAreas;

Parameters

cellTypeString
"ConditionalFormats" | "DataValidations" | "Blanks" | "Constants" | "Formulas" | "SameConditionalFormat" | "SameDataValidation" | "Visible"

The type of cells to include.

cellValueType
"All" | "Errors" | "ErrorsLogical" | "ErrorsNumbers" | "ErrorsText" | "ErrorsLogicalNumber" | "ErrorsLogicalText" | "ErrorsNumberText" | "Logical" | "LogicalNumbers" | "LogicalText" | "LogicalNumbersText" | "Numbers" | "NumbersText" | "Text"

If cellType is either Constants or Formulas, this argument is used to determine which types of cells to include in the result. These values can be combined together to return more than one type. The default is to select all constants or formulas, no matter what the type.

Returns

getSpecialCellsOrNullObject(cellType, cellValueType)

Gets the RangeAreas object, comprising one or more ranges, that represents all the cells that match the specified type and value. If no special cells are found, a null object will be returned.

[ API set: ExcelApi 1.9 ]

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

Parameters

cellType
Excel.SpecialCellType

The type of cells to include.

cellValueType
Excel.SpecialCellValueType

If cellType is either Constants or Formulas, this argument is used to determine which types of cells to include in the result. These values can be combined together to return more than one type. The default is to select all constants or formulas, no matter what the type.

Returns

getSpecialCellsOrNullObject(cellTypeString, cellValueType)

Gets the RangeAreas object, comprising one or more ranges, that represents all the cells that match the specified type and value. If no special cells are found, a null object will be returned.

[ API set: ExcelApi 1.9 ]

getSpecialCellsOrNullObject(cellTypeString: "ConditionalFormats" | "DataValidations" | "Blanks" | "Constants" | "Formulas" | "SameConditionalFormat" | "SameDataValidation" | "Visible", cellValueType?: "All" | "Errors" | "ErrorsLogical" | "ErrorsNumbers" | "ErrorsText" | "ErrorsLogicalNumber" | "ErrorsLogicalText" | "ErrorsNumberText" | "Logical" | "LogicalNumbers" | "LogicalText" | "LogicalNumbersText" | "Numbers" | "NumbersText" | "Text"): Excel.RangeAreas;

Parameters

cellTypeString
"ConditionalFormats" | "DataValidations" | "Blanks" | "Constants" | "Formulas" | "SameConditionalFormat" | "SameDataValidation" | "Visible"

The type of cells to include.

cellValueType
"All" | "Errors" | "ErrorsLogical" | "ErrorsNumbers" | "ErrorsText" | "ErrorsLogicalNumber" | "ErrorsLogicalText" | "ErrorsNumberText" | "Logical" | "LogicalNumbers" | "LogicalText" | "LogicalNumbersText" | "Numbers" | "NumbersText" | "Text"

If cellType is either Constants or Formulas, this argument is used to determine which types of cells to include in the result. These values can be combined together to return more than one type. The default is to select all constants or formulas, no matter what the type.

Returns

getSpillingToRange()

Note

This API is provided as a preview for developers and may change based on feedback that we receive. Do not use this API in a production environment.

Gets the range object containing the spill range when called on an anchor cell. Fails if applied to a range with more than one cell. Read-only.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

getSpillingToRange(): Excel.Range;

Returns

getSpillingToRangeOrNullObject()

Note

This API is provided as a preview for developers and may change based on feedback that we receive. Do not use this API in a production environment.

Gets the range object containing the spill range when called on an anchor cell. Read-only. If the range is not an anchor cell or spill range can't be found, a null object will be returned.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

getSpillingToRangeOrNullObject(): Excel.Range;

Returns

getSpillParent()

Note

This API is provided as a preview for developers and may change based on feedback that we receive. Do not use this API in a production environment.

Gets the range object containing the anchor cell for a cell getting spilled into. Fails if applied to a range with more than one cell. Read-only.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

getSpillParent(): Excel.Range;

Returns

getSpillParentOrNullObject()

Note

This API is provided as a preview for developers and may change based on feedback that we receive. Do not use this API in a production environment.

Gets the range object containing the anchor cell for a cell getting spilled into. Read-only. If it is not a spill cell or more than once cells are give, a null object will be returned.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

getSpillParentOrNullObject(): Excel.Range;

Returns

getSurroundingRegion()

Returns a Range object that represents the surrounding region for the top-left cell in this range. A surrounding region is a range bounded by any combination of blank rows and blank columns relative to this range.

[ API set: ExcelApi 1.7 ]

getSurroundingRegion(): Excel.Range;

Returns

getTables(fullyContained)

Gets a scoped collection of tables that overlap with the range.

[ API set: ExcelApi 1.9 ]

getTables(fullyContained?: boolean): Excel.TableScopedCollection;

Parameters

fullyContained
boolean

If true, returns only tables that are fully contained within the range bounds. The default value is false.

Returns

getUsedRange(valuesOnly)

Returns the used range of the given range object. If there are no used cells within the range, this function will throw an ItemNotFound error.

[ API set: ExcelApi 1.1 ]

getUsedRange(valuesOnly?: boolean): Excel.Range;

Parameters

valuesOnly
boolean

Considers only cells with values as used cells. [Api set: ExcelApi 1.2]

Returns

Examples

Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "D:F";
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    var rangeUR = range.getUsedRange();
    rangeUR.load('address');
    return ctx.sync().then(function() {
        console.log(rangeUR.address);
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});
await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const salesTable = sheet.tables.getItem("SalesTable");
    const dataRange = salesTable.getDataBodyRange();

    // We want the most recent quarter that has data, so
    // exclude quarters without data and get the last of
    // the remaining columns.
    const usedDataRange = dataRange.getUsedRange(true /* valuesOnly */);
    const currentQuarterRange = usedDataRange.getLastColumn();

    // Asian and European teams have separate contests.
    const asianSalesRange = sheet.getRange("A2:E4");
    const europeanSalesRange = sheet.getRange("A5:E7");

    // The data for each chart is the intersection of the
    // current quarter column and the rows for the continent.
    const asianContestRange = asianSalesRange.getIntersectionOrNullObject(currentQuarterRange);
    const europeanContestRange = europeanSalesRange.getIntersectionOrNullObject(currentQuarterRange);

    // Must sync before you can test the output of *OrNullObject
    // method/property.
    await context.sync();

    if (asianContestRange.isNullObject) {
        // See the declaration of this method for how to
        // test this code path.
        reportMissingData("Asian");
    } else {
        createContinentChart(
            sheet,
            "Asian",
            asianContestRange,
            "A9",
            "F24"
        );
    }

    if (europeanContestRange.isNullObject) {
        // See the declaration of this method for how to
        // test this code path.
        reportMissingData("European");
    } else {
        createContinentChart(
            sheet,
            "European",
            europeanContestRange,
            "A25",
            "F40"
        );
    }

    await context.sync();
});

getUsedRangeOrNullObject(valuesOnly)

Returns the used range of the given range object. If there are no used cells within the range, this function will return a null object.

[ API set: ExcelApi 1.4 ]

getUsedRangeOrNullObject(valuesOnly?: boolean): Excel.Range;

Parameters

valuesOnly
boolean

Considers only cells with values as used cells.

Returns

Examples

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const salesTable = sheet.tables.getItem("SalesTable");
    const dataRange = salesTable.getDataBodyRange();

    // Pass true so only cells with values count as used
    const usedDataRange = dataRange.getUsedRangeOrNullObject(
        true /* valuesOnly */
    );

    //Must sync before reading value returned from *OrNullObject method/property.
    await context.sync();

    if (usedDataRange.isNullObject) {
        console.log("Need Data to Make Chart");
        console.log("To create a meaningful chart, press 'Fill the table' (or add names to the Product column and numbers to some of the other cells). Then press 'Try to create chart' again.");
    } else {
        const chart = sheet.charts.add(
            Excel.ChartType.columnClustered,
            dataRange,
            "Columns"
        );
        chart.setPosition("A15", "F30");
        chart.title.text = "Quarterly sales chart";
        chart.legend.position = "Right";
        chart.legend.format.fill.setSolidColor("white");
        chart.dataLabels.format.font.size = 15;
        chart.dataLabels.format.font.color = "black";
    }

    await context.sync();
});

getVisibleView()

Represents the visible rows of the current range.

[ API set: ExcelApi 1.3 ]

getVisibleView(): Excel.RangeView;

Returns

group(groupOption)

Note

This API is provided as a preview for developers and may change based on feedback that we receive. Do not use this API in a production environment.

Groups columns and rows for an outline.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

group(groupOption: Excel.GroupOption): void;

Parameters

groupOption
Excel.GroupOption

Specifies how the range can be grouped by rows or columns. An InvalidArgument error is thrown when the group option differs from the range's isEntireRow or isEntireColumn property (i.e., range.isEntireRow is true and groupOption is "ByColumns" or range.isEntireColumn is true and groupOption is "ByRows").

Returns

void

group(groupOptionString)

Note

This API is provided as a preview for developers and may change based on feedback that we receive. Do not use this API in a production environment.

Groups columns and rows for an outline.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

group(groupOptionString: "ByRows" | "ByColumns"): void;

Parameters

groupOptionString
"ByRows" | "ByColumns"

Specifies how the range can be grouped by rows or columns. An InvalidArgument error is thrown when the group option differs from the range's isEntireRow or isEntireColumn property (i.e., range.isEntireRow is true and groupOption is "ByColumns" or range.isEntireColumn is true and groupOption is "ByRows").

Returns

void

hideGroupDetails(groupOption)

Note

This API is provided as a preview for developers and may change based on feedback that we receive. Do not use this API in a production environment.

Hide details of the row or column group.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

hideGroupDetails(groupOption: Excel.GroupOption): void;

Parameters

groupOption
Excel.GroupOption

Specifies whether to hide details of grouped rows or grouped columns.

Returns

void

hideGroupDetails(groupOptionString)

Note

This API is provided as a preview for developers and may change based on feedback that we receive. Do not use this API in a production environment.

Hide details of the row or column group.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

hideGroupDetails(groupOptionString: "ByRows" | "ByColumns"): void;

Parameters

groupOptionString
"ByRows" | "ByColumns"

Specifies whether to hide details of grouped rows or grouped columns.

Returns

void

insert(shift)

Inserts a cell or a range of cells into the worksheet in place of this range, and shifts the other cells to make space. Returns a new Range object at the now blank space.

[ API set: ExcelApi 1.1 ]

insert(shift: Excel.InsertShiftDirection): Excel.Range;

Parameters

shift
Excel.InsertShiftDirection

Specifies which way to shift the cells. See Excel.InsertShiftDirection for details.

Returns

Examples

Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "F5:F10";
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    range.insert();
    return ctx.sync(); 
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

insert(shiftString)

Inserts a cell or a range of cells into the worksheet in place of this range, and shifts the other cells to make space. Returns a new Range object at the now blank space.

[ API set: ExcelApi 1.1 ]

insert(shiftString: "Down" | "Right"): Excel.Range;

Parameters

shiftString
"Down" | "Right"

Specifies which way to shift the cells. See Excel.InsertShiftDirection for details.

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?: Excel.Interfaces.RangeLoadOptions): Excel.Range;

Parameters

Returns

Remarks

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

load(option?: string | string[]): Excel.Range - Where option is a comma-delimited string or an array of strings that specify the properties to load.

load(option?: { select?: string; expand?: string; }): Excel.Range - 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.Range - 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.

load(propertyNames)

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

load(propertyNames?: string | string[]): Excel.Range;

Parameters

propertyNames
string | string[]

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

Returns

Examples

// Below example uses range address to get the range object.
Excel.run(function (ctx) {
    var sheetName = "Sheet1";
    var rangeAddress = "A1:F8"; 
    var worksheet = ctx.workbook.worksheets.getItem(sheetName);
    var range = worksheet.getRange(rangeAddress);
    range.load('cellCount');
    return ctx.sync().then(function() {
        console.log(range.cellCount);
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});
// Below example uses a named-range to get the range object.
Excel.run(function (ctx) { 
    var rangeName = 'MyRange';
    var range = ctx.workbook.names.getItem(rangeName).range;
    range.load('cellCount');
    return ctx.sync().then(function() {
        console.log(range.cellCount);
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});
// The example below sets number-format, values and formulas on a grid that contains 2x3 grid.
Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "F5:G7";
    var numberFormat = [[null, "d-mmm"], [null, "d-mmm"], [null, null]]
    var values = [["Today", 42147], ["Tomorrow", "5/24"], ["Difference in days", null]];
    var formulas = [[null,null], [null,null], [null,"=G6-G5"]];
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    range.numberFormat = numberFormat;
    range.values = values;
    range.formulas= formulas;
    range.load('text');
    return ctx.sync().then(function() {
        console.log(range.text);
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

load(propertyNamesAndPaths)

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

load(propertyNamesAndPaths?: { select?: string; expand?: string; }): Excel.Range;

Parameters

propertyNamesAndPaths
{ select?: string; expand?: string; }

Where propertyNamesAndPaths.select is a comma-delimited string that specifies the properties to load, and propertyNamesAndPaths.expand is a comma-delimited string that specifies the navigation properties to load.

Returns

merge(across)

Merge the range cells into one region in the worksheet.

[ API set: ExcelApi 1.2 ]

merge(across?: boolean): void;

Parameters

across
boolean

Optional. Set true to merge cells in each row of the specified range as separate merged cells. The default value is false.

Returns

void

Examples

Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "A1:C3";
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    range.merge(true);
    return ctx.sync(); 
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});
Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "A1:C3";
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    range.unmerge();
    return ctx.sync(); 
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

removeDuplicates(columns, includesHeader)

Removes duplicate values from the range specified by the columns.

[ API set: ExcelApi 1.9 ]

removeDuplicates(columns: number[], includesHeader: boolean): Excel.RemoveDuplicatesResult;

Parameters

columns
number[]

The columns inside the range that may contain duplicates. At least one column needs to be specified. Zero-indexed.

includesHeader
boolean

True if the input data contains header. Default is false.

Returns

The resulting object that contains the number of rows removed and the number of remaining unique rows.

Examples

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const range = sheet.getRange("B2:D11");

    const deleteResult = range.removeDuplicates([0],true);    
    deleteResult.load();    
    await context.sync();

    console.log(deleteResult.removed + " entries with duplicate names removed.");
    console.log(deleteResult.uniqueRemaining + " entries with unique names remain in the range.");
});

replaceAll(text, replacement, criteria)

Finds and replaces the given string based on the criteria specified within the current range.

[ API set: ExcelApi 1.9 ]

replaceAll(text: string, replacement: string, criteria: Excel.ReplaceCriteria): OfficeExtension.ClientResult<number>;

Parameters

text
string

String to find.

replacement
string

String to replace the original with.

criteria
Excel.ReplaceCriteria

Additional Replace Criteria.

Returns

OfficeExtension.ClientResult<number>

The number of replacements performed.

select()

Selects the specified range in the Excel UI.

[ API set: ExcelApi 1.1 ]

select(): void;

Returns

void

Examples

Excel.run(function (ctx) {
    var sheetName = "Sheet1";
    var rangeAddress = "F5:F10"; 
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    range.select();
    return ctx.sync(); 
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

set(properties, options)

Sets multiple properties of an object at the same time. You can pass either a plain object with the appropriate properties, or another API object of the same type.

set(properties: Interfaces.RangeUpdateData, options?: OfficeExtension.UpdateOptions): void;

Parameters

properties
Interfaces.RangeUpdateData

A JavaScript object with properties that are structured isomorphically to the properties of the object on which the method is called.

options
OfficeExtension.UpdateOptions

Provides an option to suppress errors if the properties object tries to set any read-only properties.

Returns

void

Remarks

This method has the following additional signature:

set(properties: Excel.Range): void

Examples

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");

    const range = sheet.getRange("B2:E2");
    range.set({
        format: {
            fill: {
                color: "#4472C4"
            },
            font: {
                name: "Verdana",
                color: "white"
            }
        }
    })
    range.format.autofitColumns();
    await context.sync();
});
await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");

    const sourceRange = sheet.getRange("B2:E2");
    sourceRange.load("format/fill/color, format/font/name, format/font/color");
    await context.sync();

    // Set properties based on the loaded and synced 
    // source range.
    const targetRange = sheet.getRange("B7:E7");
    targetRange.set(sourceRange); 
    targetRange.format.autofitColumns();
    await context.sync();
});

set(properties)

Sets multiple properties on the object at the same time, based on an existing loaded object.

set(properties: Excel.Range): void;

Parameters

properties
Excel.Range

Returns

void

setCellProperties(cellPropertiesData)

Updates the range based on a 2D array of cell properties , encapsulating things like font, fill, borders, alignment, and so forth.

[ API set: ExcelApi 1.9 ]

setCellProperties(cellPropertiesData: SettableCellProperties[][]): void;

Parameters

cellPropertiesData
SettableCellProperties[][]

A 2D array that represents which properties to set in each cell.

Returns

void

Examples

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getActiveWorksheet();

    // Creating the SettableCellProperties objects to use for the range.
    // In your add-in, these should be created once, outside the function.
    const topHeaderProps: Excel.SettableCellProperties = {
        // The style property takes a string matching the name of an Excel style.
        // Note that a style will overwrite any formatting,
        // so do not use the format property with the style property.
        style: "Heading1"
    };

    const headerProps: Excel.SettableCellProperties = {
        // Any subproperties of format that are not set will not be changed when these cell properties are set.
        format: {
            fill: {
                color: "Blue"
            },
            font: {
                color: "White",
                bold: true
            }
        }
    };

    const nonApplicableProps: Excel.SettableCellProperties = {
        format: {
            fill: {
                pattern: Excel.FillPattern.gray25
            },
            font: {
                color: "Gray",
                italic: true
            }
        }
    };

    const matchupScoreProps: Excel.SettableCellProperties = {
        format: {
            borders: {
                bottom: {
                    style: Excel.BorderLineStyle.continuous
                },
                left: {
                    style: Excel.BorderLineStyle.continuous
                },
                right: {
                    style: Excel.BorderLineStyle.continuous
                },
                top: {
                    style: Excel.BorderLineStyle.continuous
                }
            }
        }
    };

    const range = sheet.getRange("A1:E5");

    // You can use empty JSON objects to avoid changing a cell's properties.
    range.setCellProperties([
        [topHeaderProps, {}, {}, {}, {}],
        [{}, {}, headerProps, headerProps, headerProps],
        [{}, headerProps, nonApplicableProps, matchupScoreProps, matchupScoreProps],
        [{}, headerProps, matchupScoreProps, nonApplicableProps, matchupScoreProps],
        [{}, headerProps, matchupScoreProps, matchupScoreProps, nonApplicableProps]
    ]);

    sheet.getUsedRange().format.autofitColumns();
    await context.sync();
});

setColumnProperties(columnPropertiesData)

Updates the range based on a single-dimensional array of column properties, encapsulating things like font, fill, borders, alignment, and so forth.

[ API set: ExcelApi 1.9 ]

setColumnProperties(columnPropertiesData: SettableColumnProperties[]): void;

Parameters

columnPropertiesData
SettableColumnProperties[]

An array that represents which properties to set in each column.

Returns

void

setDirty()

Set a range to be recalculated when the next recalculation occurs.

[ API set: ExcelApi 1.9 ]

setDirty(): void;

Returns

void

setRowProperties(rowPropertiesData)

Updates the range based on a single-dimensional array of row properties, encapsulating things like font, fill, borders, alignment, and so forth.

[ API set: ExcelApi 1.9 ]

setRowProperties(rowPropertiesData: SettableRowProperties[]): void;

Parameters

rowPropertiesData
SettableRowProperties[]

An array that represents which properties to set in each row.

Returns

void

showCard()

Displays the card for an active cell if it has rich value content.

[ API set: ExcelApi 1.7 ]

showCard(): void;

Returns

void

showGroupDetails(groupOption)

Note

This API is provided as a preview for developers and may change based on feedback that we receive. Do not use this API in a production environment.

Show details of the row or column group.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

showGroupDetails(groupOption: Excel.GroupOption): void;

Parameters

groupOption
Excel.GroupOption

Specifies whether to show details of grouped rows or grouped columns.

Returns

void

showGroupDetails(groupOptionString)

Note

This API is provided as a preview for developers and may change based on feedback that we receive. Do not use this API in a production environment.

Show details of the row or column group.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

showGroupDetails(groupOptionString: "ByRows" | "ByColumns"): void;

Parameters

groupOptionString
"ByRows" | "ByColumns"

Specifies whether to show details of grouped rows or grouped columns.

Returns

void

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.Range object is an API object, the toJSON method returns a plain JavaScript object (typed as Excel.Interfaces.RangeData) that contains shallow copies of any loaded child properties from the original object.

toJSON(): Excel.Interfaces.RangeData;

Returns

track()

Track the object for automatic adjustment based on surrounding changes in the document. This call is a shorthand for context.trackedObjects.add(thisObject). If you are using this object across ".sync" calls and outside the sequential execution of a ".run" batch, and get an "InvalidObjectPath" error when setting a property or invoking a method on the object, you needed to have added the object to the tracked object collection when the object was first created.

track(): Excel.Range;

Returns

ungroup(groupOption)

Note

This API is provided as a preview for developers and may change based on feedback that we receive. Do not use this API in a production environment.

Ungroups columns and rows for an outline.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

ungroup(groupOption: Excel.GroupOption): void;

Parameters

groupOption
Excel.GroupOption

Specifies how the range can be ungrouped by rows or columns.

Returns

void

ungroup(groupOptionString)

Note

This API is provided as a preview for developers and may change based on feedback that we receive. Do not use this API in a production environment.

Ungroups columns and rows for an outline.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

ungroup(groupOptionString: "ByRows" | "ByColumns"): void;

Parameters

groupOptionString
"ByRows" | "ByColumns"

Specifies how the range can be ungrouped by rows or columns.

Returns

void

unmerge()

Unmerge the range cells into separate cells.

[ API set: ExcelApi 1.2 ]

unmerge(): void;

Returns

void

Examples

Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "A1:C3";
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    range.unmerge();
    return ctx.sync(); 
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

untrack()

Release the memory associated with this object, if it has previously been tracked. This call is shorthand for context.trackedObjects.remove(thisObject). Having many tracked objects slows down the host application, so please remember to free any objects you add, once you're done using them. You will need to call "context.sync()" before the memory release takes effect.

untrack(): Excel.Range;

Returns

Examples

Excel.run(async (context) => {
    const largeRange = context.workbook.getSelectedRange();
    largeRange.load(["rowCount", "columnCount"]);
    await context.sync();

    for (let i = 0; i < largeRange.rowCount; i++) {
        for (let j = 0; j < largeRange.columnCount; j++) {
            let cell = largeRange.getCell(i, j);
            cell.values = [[i *j]];

            // call untrack() to release the range from memory
            cell.untrack();
        }
    }

    await context.sync();
});