Quickly clean data with Excel’s TRIMRANGE and trim references

Q. How do the TRIMRANGE function and trim references in Excel work?

A. Excel’s TRIMRANGE function and trim references help users quickly tidy up datasets. This makes for a cleaner, easier-to-follow, and more efficient spreadsheet. Let’s look at how these tools work.

TRIMRANGE function

The TRIMRANGE function eliminates blank rows or columns from the boundaries of a specified range. This can be especially beneficial when creating dynamic array formulas (see my February 2022 Technology Q&A article, “How to Spill Formulas in Excel“). This is also helpful when importing data from external sources, which can result in inconsistent formatting.

The syntax for the TRIMRANGE function is =TRIMRANGE(range, [row_trim_mode], [col_trim_mode]).

The required argument is the Range argument. This defines the range or array of cells to trim.

The last two arguments are optional.

• Row_trim_mode determines which rows to trim.
  • 0 = None.
  • 1 = Trim leading blank rows.
  • 2 = Trim trailing blank rows.
  • 3 or omitted = Trim leading and trailing blank rows.
• Col_trim_mode determines which columns to trim.
  • 0 = None.
  • 1 = Trim leading blank columns.
  • 2 = Trim trailing blank columns.
  • 3 or omitted = Trim leading and trailing blank columns.

Trim references

Trim references (also known as “trim refs”) provide a more concise method to refer to the typical variations of TRIMRANGE. They act as a modifier for the well-known colon range operator (e.g., A1:D10). By adding a period before or after the colon, you can specify that you want to remove blank spaces from the beginning, the end, or from both sides of a cell range. Specifically,

• Add a period before the colon to trim leading blanks;
• Add a period after the colon to trim trailing blanks; and
• Add a period before and after the colon to trim leading and trailing blanks.

These functions significantly reduce manual data-cleaning tasks, reducing the risk of human error compromising data integrity and accuracy. Accountants rely heavily on precise data for reporting, auditing, and compliance purposes. Any unwanted spaces could lead to calculation errors, mismatches, and inaccurate reports. Unwanted space is also distracting, making it harder for the user to process the information.

Currently, the TRIMRANGE function and the trim references are available only in Microsoft Excel 365.

If you would like to follow along in using this function, you can download this Excel workbook and view the video at the end of this item.

Let’s look at a simple example to illustrate why you would use these functions and how to use them. See below for a small snippet of the spreadsheet we will use. The full spreadsheet contains 25 employees, along with their actual sales (column C) and budgeted sales (column D).

I would like to simply calculate the difference between the actual sales amount and the budgeted sales amount for each employee (column E). See the screenshot below.

Easy enough! We are done, right? But what happens if I need to add more employees over time? The formula doesn’t automatically adjust for this. See the screenshot below when I add a 26th employee.

What we could do, instead of using the formula =D1-C1 and dragging it down to our current range of employees, would be to reference the entire column for actual and budgeted sales. Then Excel would add a new difference total each time a new employee is added. See the screenshot below for how this method looks.

This solved the problem of the difference not being calculated for new employees being added, but it also introduced a new problem. Now we have a calculated value for thousands of rows. The zero value shows as a dash in the screenshot above. Besides looking messy, this also increases the file size, which slows things down in our file. TRIMRANGE can help us have the best of both worlds. Add the TRIMRANGE function in front of each column reference as shown in the screenshot below.

This still allows us to have a dynamic formula that automatically adjusts, growing or shrinking as the size of the data changes, but TRIMRANGE has eliminated all the unwanted cells.

This was pretty easy, but it can be even easier by using the trim reference. We actually don’t have any leading blanks, but I generally put a period before and after the colon to cover all my bases as the spreadsheet evolves. See the screenshot below where I used the trim ref instead of TRIMRANGE to accomplish the same task.

Both TRIMRANGE and trim refs can be used in formulas that combine other functions, allowing those formulas to be even more useful. Each of these is a powerful tool in Excel to help build cleaner, more reliable, and more efficient datasets.

About the author

Kelly L. Williams, CPA, Ph.D., MBA, is an associate professor of accounting at the Jones College of Business at Middle Tennessee State University.

Submit a question

Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org.