Trim ranges and references in Excel
It's now possible to trim ranges and references, thanks to a new Excel update for Microsoft 365 Insiders on the Beta Channel.
The TRIMRANGE function removes the excess from a result — either the leading empty rows (at the top), the trailing empty rows (at the bottom), or both.
By doing so, you can keep everything tidy and potentially benefit from increased performance.
In the video example, a list of names are present in A2:A11. Adjacent to this, a formula is constructed that concatenates each name to '@email.com':
=LOWER(A2:A11&"@email.com")
e.g. [email protected], [email protected], [email protected]
This works fine, but if you wanted to futureproof the reference to reflect the fact that more names will be added, extending it now to A2:A21 means you can avoid doing it later on.
However, the problem with this is the '@email.com' repeats itself for as many empty cells as there are.
The solution is to wrap the reference in TRIMRANGE so the unnecessary values do not appear in the final result:
=LOWER(TRIMRANGE(A2:A21)&"@email.com")
By default, TRIMRANGE cuts off leading and trailing empty rows. However, its optional arguments [row_trim_mode] and [col_trim_mode] allow you to be specific about this.
Alternatively, instead of TRIMRANGE, use the new range operator for a more succinct way to trim references:
➤ Full trim ref (.:.)
➤ Leading trim ref (.:)
➤ Trailing trim ref (:.)
For example, =LOWER(A2.:.A21&"@email.com") does the same as what TRIMRANGE defaults to.
Although official Excel tables are often the best way to store data, they do have certain limitations, such as not being compatible with dynamic arrays. Therefore, this new function and operator gives us greater flexibility we can tap into. It'll be interesting to see how they’re used going forward.
✄---
By @andrewcharlesmoss.
#exceleration #excel #globalexcelsummit
---
The Global Excel Summit is the world's largest gathering of Microsoft Excel users and experts.
Find us on:
🌐 Website: https://globalexcelsummit.com
👔 LinkedIn: / global-excel-summit
🆇 X: https://x.com/ExcelSummit
🤳 TikTok: / globalexcelsummit
📸 Instagram: / globalexcelsummit
🟦 Facebook: / globalexcelsummit
🧵 Threads: https://www.threads.net/@globalexcels...