If your Excel formulas keep spitting out zeros for empty cells, or your sorted lists drag hundreds of blank rows along with them, the problem is usually the range you pointed the formula at. The TRIMRANGE function fixes exactly that: it trims empty rows and columns off the edges of a range or array, so your formulas work with the data you actually have.
When to Use TRIMRANGE Instead of an Excel Table
For ordinary datasets, Excel tables are usually the better choice. They expand as you add rows, use structured references, and carry calculated-column formulas into new records automatically.
But tables are not the answer to everything. Dynamic array formulas such as SORT, UNIQUE and VSTACK need room to spill their results, and spilled arrays cannot live inside a table. You may also have ordinary ranges spread across multiple worksheets that you want to combine without carrying hundreds of empty cells into the result. That is where TRIMRANGE comes in.
How the TRIMRANGE Function Works
TRIMRANGE chops empty rows and columns off the outer edges of a range or array. It takes two optional arguments: trim_rows, which specifies whether to trim leading rows at the top, trailing rows at the bottom, both, or neither; and trim_cols, which does the same for leading columns on the left, trailing columns on the right, both, or neither.
This matters because when a dynamic array formula returns blank cells, Excel displays them as zeros in the worksheet. As you add entries within the referenced range, TRIMRANGE includes them automatically and adjusts the result to exclude blank rows and columns at the edges.
TRIMRANGE Example: Sorting a Growing Product List
Imagine you have a list of product names in column A, with a header in the first row and empty rows beneath the list for future entries. You want to sort the names alphabetically and leave room for more products later.
Sorting the entire column the plain way sorts the header too and returns zeros for every empty cell beneath your list. Wrapping the range in TRIMRANGE first trims those empty rows away, so the sorted result contains just your products — and when you add a new product inside the range, it is picked up automatically.
TRIMRANGE Limitations to Watch Out For
There are a few catches. TRIMRANGE only trims empty rows and columns at the outer edges — it will not remove blank rows sitting between populated records. A stray entry, or even a sneaky space, beyond your intended dataset will move where the range ends, so keep the area around your data clean.
And if the entire range is empty, you will get a #REF! error. Used with those limits in mind, TRIMRANGE is a tidy tool for anyone whose spreadsheets mix growing lists with dynamic array formulas: point it at a generous range, let it trim the blanks, and your SORT, UNIQUE and VSTACK results stay clean.
Source: How-To Geek — read the full story there.
