It might be time for me to upgrade to a newer Excel version because of some new notation and functions like TAKE and TRIMRANGE, or does it?
Posted by jpluimers on 2026/03/23
Over the last decades, I hardly needed to upgrade Excel. For a very long time I stayed at Excel 2003, as the ribbon interface introduced with Office 2007 (version 12) was horrible (it still is, especially since 19:10 monitors are gone and the ribbon takes too much vertical screen estate).
After that, I needed newer features so I upgraded to Excel 2013 (version 15) mainly because it ditched Multiple-document interface (MDI) and I like SDI over MDI a lot, and Office 2013 was largely compatible with Office for Mac 2011 (version 14).
Mostly recovered from my cancer treatments, I noticed that MacOS ditched 32-bit support in MacOS 11 Big Sur, which meant I could not use Office 2011 any more (it was 32-bit x86 only) so in 2022 I upgraded all my office installations to Office 2021 (up in the version 16.* range as starting with Office 2016 the major version number stayed 16.minor).
I might actually upgrade to Office 2024 (version 16.many) soon despite the major version 16, finally Excel has started sped up new development of new functions and features, of which the ones below are very interesting: they will make my largest spreadsheets a lot simpler and therefore easier to maintain:
- [Wayback/Archive] TRIMRANGE Announcement: Announcing TRIMRANGE and accompanying trim references
- [Wayback/Archive] New Excel Functions: Announcing New Text and Array Functions
- [Wayback/Archive] TRIMRANGE function – Microsoft Support
The TRIMRANGE function scans in from the edges of a range or array until it finds a non-blank cell (or value), it then excludes those blank rows or columns.=TRIMRANGE(range,[trim_rows],[trim_cols])…
A Trim Ref can be used to achieve the same functionality as TRIMRANGE more succinctly by replacing the range’s colon “:” with one of the three Trim Ref types described below:TypeExampleEquivalent TRIMRANGEDescriptionTrim All (.:.)A1.:.E10TRIMRANGE(A1:E10,3,3)Trim leading and trailing blanksTrim Trailing (:.)A1:.E10TRIMRANGE(A1:E10,2,2)Trim trailing blanksTrim Leading (.:)A1.:Z10TRIMRANGE(A1:E10,1,1)Trim leading blanks - [Wayback/Archive] TAKE function – Microsoft Support
=TAKE(array, rows,[columns])The TAKE function syntax has the following arguments:-
array The array from which to take rows or columns.
-
rows The number of rows to take. A negative value takes from the end of the array.
-
columns The number of columns to take. A negative value takes from the end of the array.
-
Inspired by [Wayback/Archive] You’re ONE DOT Away from Cleaner Excel Reports | Before vs. After TRIMRANGE – YouTube
Do I really need to upgrade?
A practical use of this was in this formula which remarkably worked with Excel version 16.95 (25030928) which was part of my Office 2021 for MacOS license: =UNIQUE('Percussie-per-werk'!C2:.C1048576)
This worked, so I might not really need to upgrade at all.
I came to the above function as I had a table of percussion instruments per music work, and wanted to know the unique percussion instruments for logistic purposes. The formula immediately gave me that list using both the UNIQUE function. The formula uses the C2:.C1048576 trick to select all cells in a row except the title row: 1048576 is the maximum number of rows in Excel 2007 and up; for Excel 2003 and lower you would use 65536.
This brings me to another few links with more modern Excel features:
- [Wayback/Archive] Filter for unique values or remove duplicate values – Microsoft Support shows the in-situ filtering that Excel has had for a very long time (I think before version 2003)
- [Wayback/Archive] How to filter ONLY unique values in a column? : excel marks UNIQUE as an Office 365+ feature, but it does work in Office 2021 and up.
- [Wayback/Archive] UNIQUE function – Microsoft Support: The UNIQUE function returns a list of unique values in a list or range. You have to fold down the Syntax section which only works if you have JavaScript enabled (I hate sites that hide information when you have that enabled), so I include the first part of that section here:
=UNIQUE(array,[by_col],[exactly_once])
The UNIQUE function has the following arguments:
Argument Description array Required
The range or array from which to return unique rows or columns [by_col] Optional
The by_col argument is a logical value indicating how to compare. TRUE will compare columns against each other and return the unique columns
FALSE (or omitted) will compare rows against each other and return the unique rows
[exactly_once] Optional
The exactly_once argument is a logical value that will return rows or columns that occur exactly once in the range or array. This is the database concept of unique. TRUE will return all distinct rows or columns that occur exactly once from the range or array
FALSE (or omitted) will return all distinct rows or columns from the range or array
- [Wayback/Archive] text selection – Selecting whole column except first X (header) cells in Excel – Super User (thanks [Wayback/Archive] Robert Koritnik, [Wayback/Archive] dkusleika and [Wayback/Archive] MikeTeeVee)
Query: [Wayback/Archive] excel filter list unique values – Google-søk
--jeroen
You’re ONE DOT Away from Cleaner Excel Reports | Before vs. After TRIMRANGE – YouTube
The Wiert Corner - irregular stream of stuff 
Leave a comment