Excel Function: TRIMRANGE

The TRIMRANGE function removes empty rows or columns around a data range.

Even more convenient, we’ll see below that this function can be simply replaced by one or two . when entering a cell range reference.

Usage:

=TRIMRANGE(range)

or

=TRIMRANGE(range, row_trim_mode, col_trim_mode)

Usage example

A sample range has been defined; the goal here is to remove empty rows and columns:

The arguments of the TRIMRANGE function are as follows:

• Range: the range to process (here, the highlighted range)
• Row_trim_mode: to define which rows to remove
  • 0: removes no row
  • 1: removes empty rows at the beginning
  • 2: removes empty rows at the end
  • 3: removes empty rows at the beginning and end (default)
• Col_trim_mode: to define which columns to remove
  • 0: removes no column
  • 1: removes empty columns at the beginning
  • 2: removes empty columns at the end
  • 3: removes empty columns at the beginning and end (default)

In this case, we’ll use the default behavior (i.e. remove empty rows and columns at the beginning and end), so there’s no need to specify optional arguments:

=TRIMRANGE(A1:D17)

Empty rows and columns have been successfully removed.

However, this function has an inconvenient behavior when the range contains empty cells, as it displays 0 in their place.

Removing the 0s

To remove these annoying 0s, there are several solutions. Choose the one that is most appropriate based on your use case if you need to get rid of the 0s.

Conditional formatting

The 0s are hidden here using conditional formatting (by coloring them white):

CLEAN function

The 0s are removed here using the CLEAN function (which does not remove 0s originally present):

=CLEAN(TRIMRANGE(A1:D17))

Other formulas

Other formulas allow you to remove 0s without modifying the data. Here are 2 examples:

=LET(a,TRIMRANGE(A1:D17),IF(ISBLANK(a),"",a))

=MAP(TRIMRANGE(A1:D17),LAMBDA(c,IF(ISBLANK(c),"",c)))

These formulas produce the following result:

Using dots

The TRIMRANGE function can be replaced by adding one or two . in the reference:

For example:

Dropdown list

If column A contains a variable number of data entries for a dropdown list, you can obtain the data range using this simple formula:

=A:.A

Then create your dropdown list by entering this same formula as the source:

To get: