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.

[ API set: ExcelApi 1.1 ]

Extends
OfficeExtension.ClientObject

Remarks

Our how-to guide on working with ranges has detailed walkthroughs, images, and code samples.

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 ]

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 ]

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 ]

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 as a string in the language 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.

[ 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 ]

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 ]

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 ]

worksheet

The worksheet containing the current range. Read-only.

[ API set: ExcelApi 1.1 ]

Methods

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 ]

delete(shift)

Deletes the cells associated with the range.

[ API set: ExcelApi 1.1 ]

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 ]

getColumn(column)

Gets a column contained in the range.

[ API set: ExcelApi 1.1 ]

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 ]

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 ]

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 ]

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 ]

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 ]

load(option)

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 ]

select()

Selects the specified range in the Excel UI.

[ API set: ExcelApi 1.1 ]

showCard()

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

[ API set: ExcelApi 1.7 ]

toJSON()
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.

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

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[][]

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();

    OfficeHelpers.UI.notify("Created a hyperlink to a URL for each of the products in the first table.");
});

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

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 as a string in the language 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.

[ 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

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[][]

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[][]

worksheet

The worksheet containing the current range. Read-only.

[ API set: ExcelApi 1.1 ]

readonly worksheet: Excel.Worksheet;
Property Value

Method Details

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));
    }
});

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

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));
    }
});

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));
    }
});

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));
    }
});

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));
    }
});

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

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

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) {
        OfficeHelpers.UI.notify("Need Data to Make Chart", "To create a meaningful chart, 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

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));
    }
});

load(option)

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

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

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

Returns
Remarks

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

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

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));
    }
});

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));
    }
});

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));
    }
});

showCard()

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

[ API set: ExcelApi 1.7 ]

showCard(): void;
Returns
void

toJSON()

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

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