Clean Up Data Effortlessly With Excel’s New TRIMRANGE Function

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.

Here is the syntax for the TRIMRANGE function:

        TRIMRANGE(range_to_trim, [rows_to_trim], [columns_to_trim])

    

The range_to_trim parameter takes the range you want to trim. The possible values for the rows_to_trim parameter are 0 for no rows to trim, 1 for trimming leading blank rows, 2 for trimming trailing blank rows, and 3 (default) for trimming both the leading and trailing blank rows. The cols_to_trim parameter takes the same values (they just apply to the columns instead).

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 make column A draw values from column E, which contains a list of names, by entering the formula below in A1:

        =E:E

    

Upon pressing Enter, column A will be filled with the names in column E. However, since we are referencing all of column E, including the empty cells, column A will have zeros where the blank cells are.

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

        =TRIMRANGE(E:E)

    

Since we have entered the range as the sole parameter in the function, it will trim both the leading and trailing zeros in column 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:

        =TRIMRANGE(E:E, , 2)

    

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 from column A, making the Excel sheet look much cleaner. And since it is a dynamic range, you can add and remove values in column 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, the XLOOKUP function in Excel may return #N/A if 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:

Currently, the XLOOKUP formula looks something like this:

        =XLOOKUP(A2:A12, H2:H11, G2:G11)

    

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

        =XLOOKUP(TRIMRANGE(A2:A12), H2:H11, G2:G11)

    

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

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 the Source field of the list.

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

Related

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:

        =E.:.E

    

This will trim the leading cells in the column:

        =E.:E

    

This will trim the trailing cells in the column:

        =E:.E

    

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

        =XLOOKUP(A2.:.A12, H2:H11, G2:G11)

    

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 to tidy up an Excel sheet, especially with the addition of Trim Refs.