You shouldn’t need five steps to clean a column. Or a script. Or a headache. Excel’s new TRIMRANGE function quietly solves a huge pain point. And if you work with imported or user-entered data, this one’s a lifesaver.

What Is the TRIMRANGE Function?

Imagine you just imported a dataset into Excel and noticed that there are several empty rows and columns in the range. They’re making the sheet messy, and you want to get rid of them in one go instead of doing it manually. That’s where the TRIMRANGE function comes in, allowing you to remove blank leading and trailing rows and columns, leaving you with only the relevant data.

How to Use the TRIM Function in Excel

With Excel’s TRIM function, you can trim text in your cells or remove blank spaces from it. Here’s how to use it!

Here is the syntax for the TRIMRANGE function:

Therange_to_trimparameter takes the range you want to trim. The possible values for therows_to_trimparameter are0for no rows to trim,1for trimming leading blank rows,2for trimming trailing blank rows, and3(default) for trimming both the leading and trailing blank rows. Thecols_to_trimparameter takes the same values (they just apply to the columns instead).

The TRIMRANGE function does not remove blank rows and columns inside the range, just the trailing and leading ones.

The TRIM function in Excel

How to Use the TRIMRANGE Function

Now that you know what the TRIMRANGE function is and how it works, let’s examine some use cases.

Basic Usage of the TRIMRANGE function

For this example, I will makecolumn Adraw values fromcolumn E, which contains a list of names, by entering the formula below inA1:

Upon pressingEnter,column Awill be filled with the names incolumn E. However, since we are referencing all ofcolumn E, including the empty cells,column Awill have zeros where the blank cells are.

A dynamic range with extra zeros at the end.

We can easily clean those zeros up by wrapping the range in the TRIMRANGE function. Here is what that looks like:

Since we have entered the range as the sole parameter in the function, it will trim both the leading and trailing zeros incolumn A. If we want to specify that we only want to get rid of the trailing blank cells in the column, we can enter the following formula:

The dynamic range with extra zeros at the end removed with the TRIMRANGE function.

I left the parameter in the middle blank because we have no rows (remember that it is optional). Now the trailing empty cells will be gone fromcolumn A, making the Excel sheet look much cleaner. And since it is a dynamic range, you can add and remove values incolumn E, and it will expand or shrink without any zeros.

Use TRIMRANGE With Other Excel Functions

You can combine the TRIMRANGE function with others for interesting results and analyses. You can even use it to avoid errors. For instance, theXLOOKUP function in Excelmay return#N/Aif it doesn’t find a value where one is expected.

Here’s an example of how bad that sheet would look when XLOOKUP returns errors:

The XLOOKUP function with errors.

Currently, the XLOOKUP formula looks something like this:

To fix this, let’s wrap the lookup range (the first parameter) in the TRIMRANGE function. Here’s what it would look like:

As you can see, this will instantly get rid of those errors, and you can even continue expanding the range.

The XLOOKUP function with the errors removed using the TRIMRANGE function.

Use TRIMRANGE in Data Validation

Since the TRIMRANGE function is a great way to work with clean data, it can come in handy in other places as well, such as data validation.

Suppose you’re creating a dropdown list that you want to expand later on, you would create a dynamic range by including extra rows for adding future values. But this would make your dropdown list look a little awkward since it will have an extra blank space.

You can get rid of that blank space by including the TRIMRANGE function in theSourcefield of the list.

Your dropdown list will look cleaner and still expand as needed.

Make Your Excel Spreadsheets Smarter With Dropdown Lists

The simple trick to making your data more reliable and your life easier.

Simplify the TRIMRANGE Function With the Dot Operator

As you can see, the TRIMRANGE function is already easy to use, but you can make it simpler with Trim References (Trim Refs). Using them is as simple as adding a dot operator on either or both sides of a range’s colon.

This will trim both leading and trailing cells in the column:

This will trim the leading cells in the column:

This will trim the trailing cells in the column:

Even the example I gave earlier with XLOOKUP can look cleaner, like so:

Without the TRIMRANGE function, people often resorted to using workarounds like the TOCOL and OFFSET functions. Since these are not intended for removing empty rows, you need to use them carefully to avoid errors. The TRIMRANGE function offers an easier and more flexible way totidy up an Excel sheet, especially with the addition of Trim Refs.