DocumentationFormulasFunctionsTrimrange

TRIMRANGE function

PRO

The TRIMRANGE function in Jspreadsheet Formulas Pro returns a range without its outer empty rows and columns. It is designed for references that are larger than the data they contain, such as a whole column, so that the rest of the formula only processes the cells that actually hold values. This keeps formulas short, avoids adjusting ranges every time new rows are added and prevents empty cells from being counted or spilled.

Documentation

Returns a range without its leading and trailing empty rows and columns.

Category

Lookup and reference

Syntax

TRIMRANGE(range, [row_trim_mode], [col_trim_mode])

Parameter Description
range The range or array to trim.
[row_trim_mode] Optional. Which empty rows to remove: 0 = none, 1 = leading rows, 2 = trailing rows, 3 = leading and trailing rows (default).
[col_trim_mode] Optional. Which empty columns to remove: 0 = none, 1 = leading columns, 2 = trailing columns, 3 = leading and trailing columns (default).

Behavior

The TRIMRANGE function inspects the outer rows and columns of the range and removes the ones that are completely empty, returning the remaining block as a dynamic array.

  • Only the outer edges are trimmed. Empty rows or columns surrounded by data are kept, so the shape of the data is preserved.
  • Only genuinely empty cells count as empty. A cell holding a formula that returns an empty string is treated as content.
  • With both modes set to 0 the range is returned unchanged.
  • A single cell reference returns the value of that cell.
  • Whole-column references such as A:A are supported. The result stops at the last row that contains data, which makes SUM(TRIMRANGE(A:A)) or ROWS(TRIMRANGE(A:A)) efficient.
  • The result can be used directly as the argument of another function or left to spill into the worksheet.

Common Errors

Error Description
#VALUE! Returned when row_trim_mode or col_trim_mode is not one of 0, 1, 2 or 3, including text values.
#REF! Returned when the range cannot be resolved.

Best practices

  • Wrap whole-column references with TRIMRANGE before passing them to functions such as SUM, COUNTA, SORT or UNIQUE, so new rows are included automatically without processing the empty tail of the column.
  • Use TRIMRANGE(range, 2, 2) when the data always starts at the first cell of the range and only the trailing part varies.
  • Combine it with ROWS or COLUMNS to measure how much of a range is actually filled.
  • Remember that the result is a dynamic array. Keep the cells that the result will occupy empty when the formula is not nested inside another function.

Usage

A few examples using the TRIMRANGE function.

// Data only in B3:C4 inside A1:C6
TRIMRANGE(A1:C6)
// Returns the 2x2 block B3:C4

TRIMRANGE(A1:C6, 2)
// Removes only the trailing empty rows; columns are still trimmed on both sides

TRIMRANGE(A1:C6, 0, 0)
// Returns the range unchanged

ROWS(TRIMRANGE(A1:C6))
// Returns 2, the number of rows that hold data

SUM(TRIMRANGE(A:A))
// Adds the whole column without processing its empty tail

Interactive Spreadsheet Demo

<html>
<script src="https://jsuites.net/v6/jsuites.js"></script>
<script src="https://cdn.jsdelivr.net/npm/jspreadsheet@13/dist/index.min.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v6/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/jspreadsheet@13/dist/jspreadsheet.min.css" type="text/css" />

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/formula-pro/dist/index.min.js"></script>

<div id="spreadsheet"></div>

<script>
// You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox.
// The license is valid for one day, after which the spreadsheet will become read-only.
// For a longer trial period, you can create a free account and generate a demo license with an extended expiration date.
jspreadsheet.setLicense('OWMzYjdhZjQzZmEzMGYwYmE1Mzk5MjlhNmEwZWJlZjBjODk4YzY5ZmFkYmUyZjExMThiMjlhOGE4ODBlODlmN2FiMzhmN2U2MThmODhhYmFkMmMwYmIxZDJkZWNlZGFlZjk5ZDdlYzc3OWY1YzYyMTI2NGM1ODUzNmUwOTAzMzcsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpreE1ESTRNalE0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW5ZeE15SXNJbU5vWVhKMGN5SXNJbVp2Y20xeklpd2labTl5YlhWc1lTSXNJbkJoY25ObGNpSXNJbkpsYm1SbGNpSXNJbU52YlcxbGJuUnpJaXdpYVcxd2IzSjBaWElpTENKaVlYSWlMQ0oyWVd4cFpHRjBhVzl1Y3lJc0luTmxZWEpqYUNJc0luQnlhVzUwSWl3aWMyaGxaWFJ6SWl3aVkyeHBaVzUwSWl3aWMyVnlkbVZ5SWl3aWMyaGhjR1Z6SWl3aVptOXliV0YwSWl3aWNHbDJiM1FpWFN3aVpHVnRieUk2ZEhKMVpYMD0=');

// Set the extensions
jspreadsheet.setExtensions({ formula });

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "",
        "",
        "",
        "",
        "=TRIMRANGE(A1:C6)",
        "",
        "=ROWS(TRIMRANGE(A1:C6))"
    ],
    [
        "",
        "",
        ""
    ],
    [
        "",
        "Q1",
        "Q2"
    ],
    [
        "",
        120,
        80
    ],
    [
        "",
        "",
        ""
    ],
    [
        "",
        "",
        ""
    ]
]
  }]
});
</script>
</html>

import React, { useRef } from "react";
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/react";
import formula from "@jspreadsheet/formula-pro";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// Set license
jspreadsheet.setLicense('OWMzYjdhZjQzZmEzMGYwYmE1Mzk5MjlhNmEwZWJlZjBjODk4YzY5ZmFkYmUyZjExMThiMjlhOGE4ODBlODlmN2FiMzhmN2U2MThmODhhYmFkMmMwYmIxZDJkZWNlZGFlZjk5ZDdlYzc3OWY1YzYyMTI2NGM1ODUzNmUwOTAzMzcsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpreE1ESTRNalE0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW5ZeE15SXNJbU5vWVhKMGN5SXNJbVp2Y20xeklpd2labTl5YlhWc1lTSXNJbkJoY25ObGNpSXNJbkpsYm1SbGNpSXNJbU52YlcxbGJuUnpJaXdpYVcxd2IzSjBaWElpTENKaVlYSWlMQ0oyWVd4cFpHRjBhVzl1Y3lJc0luTmxZWEpqYUNJc0luQnlhVzUwSWl3aWMyaGxaWFJ6SWl3aVkyeHBaVzUwSWl3aWMyVnlkbVZ5SWl3aWMyaGhjR1Z6SWl3aVptOXliV0YwSWl3aWNHbDJiM1FpWFN3aVpHVnRieUk2ZEhKMVpYMD0=');

// Set the extensions
jspreadsheet.setExtensions({ formula });

export default function App() {
    // Spreadsheet array of worksheets
    const spreadsheet = useRef();

    // Worksheet data
    const data = [
    [
        "",
        "",
        "",
        "",
        "=TRIMRANGE(A1:C6)",
        "",
        "=ROWS(TRIMRANGE(A1:C6))"
    ],
    [
        "",
        "",
        ""
    ],
    [
        "",
        "Q1",
        "Q2"
    ],
    [
        "",
        120,
        80
    ],
    [
        "",
        "",
        ""
    ],
    [
        "",
        "",
        ""
    ]
];

    // Render component
    return (
        <Spreadsheet ref={spreadsheet}>
            <Worksheet data={data} />
        </Spreadsheet>
    );
}

<template>
    <Spreadsheet ref="spreadsheet">
        <Worksheet :data="data" />
    </Spreadsheet>
</template>

<script>
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/vue";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
import formula from "@jspreadsheet/formula-pro";

// Set license
jspreadsheet.setLicense('OWMzYjdhZjQzZmEzMGYwYmE1Mzk5MjlhNmEwZWJlZjBjODk4YzY5ZmFkYmUyZjExMThiMjlhOGE4ODBlODlmN2FiMzhmN2U2MThmODhhYmFkMmMwYmIxZDJkZWNlZGFlZjk5ZDdlYzc3OWY1YzYyMTI2NGM1ODUzNmUwOTAzMzcsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpreE1ESTRNalE0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW5ZeE15SXNJbU5vWVhKMGN5SXNJbVp2Y20xeklpd2labTl5YlhWc1lTSXNJbkJoY25ObGNpSXNJbkpsYm1SbGNpSXNJbU52YlcxbGJuUnpJaXdpYVcxd2IzSjBaWElpTENKaVlYSWlMQ0oyWVd4cFpHRjBhVzl1Y3lJc0luTmxZWEpqYUNJc0luQnlhVzUwSWl3aWMyaGxaWFJ6SWl3aVkyeHBaVzUwSWl3aWMyVnlkbVZ5SWl3aWMyaGhjR1Z6SWl3aVptOXliV0YwSWl3aWNHbDJiM1FpWFN3aVpHVnRieUk2ZEhKMVpYMD0=');

// Set the extensions
jspreadsheet.setExtensions({ formula });

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "",
        "",
        "",
        "",
        "=TRIMRANGE(A1:C6)",
        "",
        "=ROWS(TRIMRANGE(A1:C6))"
    ],
    [
        "",
        "",
        ""
    ],
    [
        "",
        "Q1",
        "Q2"
    ],
    [
        "",
        120,
        80
    ],
    [
        "",
        "",
        ""
    ],
    [
        "",
        "",
        ""
    ]
]

        return {
            data
        };
    }
}
</script>

import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import formula from "@jspreadsheet/formula-pro";

// You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox.
// The license is valid for one day, after which the spreadsheet will become read-only.
// For a longer trial period, you can create a free account and generate a demo license with an extended expiration date.
jspreadsheet.setLicense('OWMzYjdhZjQzZmEzMGYwYmE1Mzk5MjlhNmEwZWJlZjBjODk4YzY5ZmFkYmUyZjExMThiMjlhOGE4ODBlODlmN2FiMzhmN2U2MThmODhhYmFkMmMwYmIxZDJkZWNlZGFlZjk5ZDdlYzc3OWY1YzYyMTI2NGM1ODUzNmUwOTAzMzcsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpreE1ESTRNalE0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW5ZeE15SXNJbU5vWVhKMGN5SXNJbVp2Y20xeklpd2labTl5YlhWc1lTSXNJbkJoY25ObGNpSXNJbkpsYm1SbGNpSXNJbU52YlcxbGJuUnpJaXdpYVcxd2IzSjBaWElpTENKaVlYSWlMQ0oyWVd4cFpHRjBhVzl1Y3lJc0luTmxZWEpqYUNJc0luQnlhVzUwSWl3aWMyaGxaWFJ6SWl3aVkyeHBaVzUwSWl3aWMyVnlkbVZ5SWl3aWMyaGhjR1Z6SWl3aVptOXliV0YwSWl3aWNHbDJiM1FpWFN3aVpHVnRieUk2ZEhKMVpYMD0=');

// Set the extensions
jspreadsheet.setExtensions({ formula });

@Component({
    standalone: true,
    selector: "app-root",
    template: `<div #spreadsheet></div>`
})
export class AppComponent {
    @ViewChild("spreadsheet") spreadsheet: ElementRef;
    // Worksheets
    worksheets: jspreadsheet.worksheetInstance[];

    // Create a new data grid
    ngAfterViewInit() {
        // Create spreadsheet
        this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
            worksheets: [{
                data: [
    [
        "",
        "",
        "",
        "",
        "=TRIMRANGE(A1:C6)",
        "",
        "=ROWS(TRIMRANGE(A1:C6))"
    ],
    [
        "",
        "",
        ""
    ],
    [
        "",
        "Q1",
        "Q2"
    ],
    [
        "",
        120,
        80
    ],
    [
        "",
        "",
        ""
    ],
    [
        "",
        "",
        ""
    ]
]
            }]
        });
    }
}