The TRIMRANGE function looks like this:

=TRIMRANGE(range,[trim_rows],[trim_cols])

You can find more detail on the syntax from Microsoft

While TRIMRANGE can be useful when working with dynamic arrays and can also improve the efficiency and performance of LAMBDA functions, there are simpler use cases for this function. However, this isn’t just a new function, as alongside TRIMRANGE Microsoft have introduced a whole new element to formula syntax.

Before we dive into an example, please note that as of March 2025, Microsoft has announced that TRIMRANGE has now been rolled out to Windows Current Channel and Monthly Enterprise Channel users, so most Excel users should see this function the next time they receive Microsoft 365 and Office Updates.

A simple use case

To demonstrate how you can get started with using TRIMRANGE, let’s take a simple example.

I’ve got some sales data across multiple years and months in my spreadsheet. and I update this table every month with new data. I want to sum the last 12 months of data, so I know what the running total of sales over the last 12 months is. While I could use a simple SUM formula, I want this to be dynamic so that every time I update the spreadsheet with more sales data the formula updates itself.

A starting point in this example would be to use the SUM function in combination with TAKE to select the last 12 values from the array. This formula would look like this:

=SUM(TAKE(C:C,-12)

While this formula does SUM the last 12 values from the array, it also includes the blank cells in the value count. So, my total only truly covers the last 3 months of sales.

This is where TRIMRANGE comes in! I can use TRIMRANGE in my formula to exclude the trailing blanks. In this formula, I’ve selected the range, which is column C with the sales figures, but I’ve left the rows argument blank as TRIMRANGE will exclude trailing and leading blank rows (or columns) by default. As a result, this formula will look something like this:

=SUM(TAKE(TRIMRANGE(C:C) ,-12))

Now my formula only counts the last 12 months of sales values excluding any blank values.

The best part of this is that when I add new sales data to the table, my total value will continue to update.

Trim references

Along with the new function, Microsoft have also introduced trim references – essentially TRIMRANGE shortcuts that can be used to simplify your formula.

In the full function, the ‘trim_rows’ and ‘trim_cols’ arguments determine which rows/columns should be trimmed:

0. None
1. Trims leading blanks
2. Trims trailing blanks
3. Trims both leading and trailing blanks (the default behaviour if the argument is excluded)

Instead of using the full function with these arguments, you can now use ‘.’ on either side of the range’s colon in any function. Including the period ‘.’ designates both the application of TRIMRANGE to the function, and determines the rows and columns to be trimmed:

.: - Trims leading blanks from both rows and columns in the range
:. – Trims trailing blanks
.:. – Trims both leading and trailing blanks

If I use these references in my formula, it will still work in the same way but will look like this instead:

SUM(TAKE(C:.C),-12))

You can take a look at the function in action in the attached workbook.