Trim ranges with the dot in Excel
How do you reference a range in a formula?
For decades, the answer has been simple: select multiple cells to create a reference with the starting cell on the left, a colon in the middle, and the ending cell on the right.
However, these references are static, meaning they don’t adapt to the source data if it grows or shrinks.
But there’s been a recent twist — the dot operator (.). This adds trimming behaviour directly into the reference, so your ranges automatically adjust to exclude unwanted blanks.
The three options are:
- Trim leading blanks (.:)
- Trim trailing blanks (:.)
- Trim both ends (.:.)
For example, if you have data in A4:B19, you could safely future-proof any reference to this by including more rows than needed. The reference might be A4:B30.
Without the dot operator, though, all the empty rows below the last non-empty one (A19:B19) would be part of the spilled range, resulting in a tail of 0s in both columns.
This is unsightly, inefficient, and bad practice. Plus, it has the potential to slow down formulas if you’re dealing with a lot of data.
But using A4.:.B30, for instance, will ensure the spilled range is only as long as it needs to be. This is ideal when you add or remove entries, as it’ll adapt accordingly.
