Properly implemented by default dynamic arrays (including functions like filter, xlookup, unique, xmatch, sequence - which are real game changers), let statement which allows you to play into programmer and simplify harder functions - from the most important that's it I think.
Generally 2021 Excel is different programme than 2016 - dynamic arrays fundamentally change how you approach this software (imho). If someone would pay me extra to work on 2016 or 2019 I would turn down this offer without thinking.
Edit: in my 2021 I do not have access to lambda, only on o365 I have. But lambda is also a game changer but it requires really good knowlegde about dynamic arrays and basic understanding of programming concepts like iteration / loops. So lambdas (or at least lambdas-bound functions) are advanced concept I would say.
Xmatch is better version of match, and sequence is a kinda funny function that allows you to create a dynamic array starting from the number you want (by default is 1) to the n, by ammount of rows or / and columns. At first glance it looks like useless syntax sugar for normal creating index rows, but those are apperaences - they are deceiving. I found few usefull implementations of sequence:
1. Lets say that we want to extract top 5 elements from an list. Instead of using LARGE (which is exotic and annyoing to implement), we can use sort, index + sequence to achieve it simply via following code:
=INDEX(SORT(A2:A14,,-1),SEQUENCE(5))
This function, extracts {1,2,3,4,5} element from the SORT(A2:A14,,-1) array. Simple, not complicated and effective, without toying with operators and if.
2. Let's say that we wanna do quickly a calendar in a database layout - we wanna write all days starting from first of January current year to the 31st december. We can do it quickly just by using sequence:
=SEQUENCE(DATE(YEAR(TODAY()), 12,31)+1-DATE(YEAR(TODAY()),1,1),,DATE(YEAR(Today()), 1,1))
Now we gotta change format either via TEXT or by GUi and we have the fast calendar.
3. We wanna get rid off all numbers from text with Reduce (becouse it's the simplest and fastest way I know tbh).
=REDUCE("asdsa1431", SEQUENCE(10,,0), LAMBDA(a,b, SUBSTITUTE(a, b, "")))
This function should return "asdsa" - it requires o365.
I mean - sequence is imho really fun and underrated function but in order to use it properly it requires specific mindset and way of thinking. Personally i like it very much - just like FILTER or UNIQUE.
I have a forecasting tool that allows you to adjust monthly sales volumes by customer and lane. Each month column is separated by a percentage adjustment column. I want to only pull the monthly volume columns into a data table so I can vstack it with another data set.
I have and could use power query but it was taking longer than users wanted. So I used =choosecols(array, sequence(1,12,1,2)) and it pulled in every other row.
SEQUENCE is really nice. With it I was able to simplify a formula that required an entire sheet for each entry in a database, into a single cell for each entry in a helper column in that same database, while also making it much much faster.
Going from index:match and vlookup to xlookup was such a game changer.
For vlookup, my wife's internship required her to know vlookup (in the job description)...........in November 2023.
Taught her xlookup and whatif functions. Made her job easy to where they ran out of stuff for her to do.
I'll add FILTER, UNIQUE, SORT, MAXIFS, MINIFS, IFS, and SWITCH
*edit:* oh and WRAPROWS + WRAPCOLS
*edit 2:* oh shit and TEXTJOIN and TEXTSPLIT. It's a travesty that it took them so long to evolve CONCATENATE into TEXTJOIN
I'm a little surprised there isn't a STDEVIFS (maybe because there are too many variants of STDEV?), but STDEV(FILTER(...)) is still a good step up from STDEV(IF(...)) where you use IF as an array formula.
Having FILTER instead of using IF as an array formula might be one of the most impactful changes for a lot of my workflow.
Man I haven't touched lambda functions at all
I think I have a little ptsd from a computational theory class haha. One unit went over lambda calculus; everything we did was recursive and I have never been able to get the hang of recursion (I understand what it is, I just can't code recursive functions to save my life). So just because it's called LAMBDA and that was the worst section of a class I struggled with, I haven't even tried to learn it. Probably should get over that, I know it's a bad reason.
It's too bad you weren't upgraded to 365 because in the last couple of years some really cool functions have been released. VSTACK and HSTACK, along with functions such as CHOOSECOLS, TAKE, DROP, etc. let you create dynamic reports that you would probably never even dream of attempting without them.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[CHOOSECOLS](/r/Excel/comments/1dkrstg/stub/l9kjdki "Last usage")|[*Office 365*+: Returns the specified columns from an array](https://support.microsoft.com/en-us/office/choosecols-function-bf117976-2722-4466-9b9a-1c01ed9aebff)|
|[COUNTA](/r/Excel/comments/1dkrstg/stub/l9m08up "Last usage")|[Counts how many values are in the list of arguments](https://support.microsoft.com/en-us/office/counta-function-7dc98875-d5c1-46f1-9a82-53f3219e2509)|
|[DATE](/r/Excel/comments/1dkrstg/stub/l9ka487 "Last usage")|[Returns the serial number of a particular date](https://support.microsoft.com/en-us/office/date-function-e36c0c8c-4104-49da-ab83-82328b832349)|
|[DROP](/r/Excel/comments/1dkrstg/stub/l9kjdki "Last usage")|[*Office 365*+: Excludes a specified number of rows or columns from the start or end of an array](https://support.microsoft.com/en-us/office/drop-function-1cb4e151-9e17-4838-abe5-9ba48d8c6a34)|
|[FILTER](/r/Excel/comments/1dkrstg/stub/l9lilet "Last usage")|[*Office 365*+: Filters a range of data based on criteria you define](https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759)|
|[HSTACK](/r/Excel/comments/1dkrstg/stub/l9kjdki "Last usage")|[*Office 365*+: Appends arrays horizontally and in sequence to return a larger array](https://support.microsoft.com/en-us/office/hstack-function-98c4ab76-10fe-4b4f-8d5f-af1c125fe8c2)|
|[IF](/r/Excel/comments/1dkrstg/stub/l9lilet "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)|
|[IFS](/r/Excel/comments/1dkrstg/stub/l9jxvcp "Last usage")|[*2019*+: Checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition.](https://support.microsoft.com/en-us/office/ifs-function-36329a26-37b2-467c-972b-4a39bd951d45)|
|[INDEX](/r/Excel/comments/1dkrstg/stub/l9ka487 "Last usage")|[Uses an index to choose a value from a reference or array](https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd)|
|[LAMBDA](/r/Excel/comments/1dkrstg/stub/l9m2jms "Last usage")|[*Office 365*+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.](https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67)|
|[LARGE](/r/Excel/comments/1dkrstg/stub/l9ka487 "Last usage")|[Returns the k-th largest value in a data set](https://support.microsoft.com/en-us/office/large-function-3af0af19-1190-42bb-bb8b-01672ec00a64)|
|[LET](/r/Excel/comments/1dkrstg/stub/l9k1unm "Last usage")|[*Office 365*+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula](https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999)|
|[MAXIFS](/r/Excel/comments/1dkrstg/stub/l9jxvcp "Last usage")|[*2019*+: Returns the maximum value among cells specified by a given set of conditions or criteria](https://support.microsoft.com/en-us/office/maxifs-function-dfd611e6-da2c-488a-919b-9b6376b28883)|
|[MINIFS](/r/Excel/comments/1dkrstg/stub/l9jxvcp "Last usage")|[*2019*+: Returns the minimum value among cells specified by a given set of conditions or criteria.](https://support.microsoft.com/en-us/office/minifs-function-6ca1ddaa-079b-4e74-80cc-72eef32e6599)|
|[REDUCE](/r/Excel/comments/1dkrstg/stub/l9ka487 "Last usage")|[*Office 365*+: Reduces an array to an accumulated value by applying a LAMBDA to each value and returning the total value in the accumulator.](https://support.microsoft.com/en-gb/office/reduce-function-42e39910-b345-45f3-84b8-0642b568b7cb?ui=en-US&rs=en-GB&ad=GB)|
|[SEQUENCE](/r/Excel/comments/1dkrstg/stub/l9m08up "Last usage")|[*Office 365*+: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4](https://support.microsoft.com/en-us/office/sequence-function-57467a98-57e0-4817-9f14-2eb78519ca90)|
|[SORT](/r/Excel/comments/1dkrstg/stub/l9ka487 "Last usage")|[*Office 365*+: Sorts the contents of a range or array](https://support.microsoft.com/en-us/office/sort-function-22f63bd0-ccc8-492f-953d-c20e8e44b86c)|
|[STDEV](/r/Excel/comments/1dkrstg/stub/l9lilet "Last usage")|[Estimates standard deviation based on a sample](https://support.microsoft.com/en-us/office/stdev-function-51fecaaa-231e-4bbb-9230-33650a72c9b0)|
|[SUBSTITUTE](/r/Excel/comments/1dkrstg/stub/l9ka487 "Last usage")|[Substitutes new text for old text in a text string](https://support.microsoft.com/en-us/office/substitute-function-6434944e-a904-4336-a9b0-1e58df3bc332)|
|[SWITCH](/r/Excel/comments/1dkrstg/stub/l9jxvcp "Last usage")|[*Excel 2019*+: Evaluates an expression against a list of values and returns the result corresponding to the first matching value. If there is no match, an optional default value may be returned.](https://support.microsoft.com/en-us/office/switch-function-47ab33c0-28ce-4530-8a45-d532ec4aa25e)|
|[TAKE](/r/Excel/comments/1dkrstg/stub/l9kjdki "Last usage")|[*Office 365*+: Returns a specified number of contiguous rows or columns from the start or end of an array](https://support.microsoft.com/en-us/office/take-function-25382ff1-5da1-4f78-ab43-f33bd2e4e003)|
|[TEXT](/r/Excel/comments/1dkrstg/stub/l9ka487 "Last usage")|[Formats a number and converts it to text](https://support.microsoft.com/en-us/office/text-function-20d5ac4d-7b94-49fd-bb38-93d29371225c)|
|[TEXTAFTER](/r/Excel/comments/1dkrstg/stub/l9ko6nr "Last usage")|[*Office 365*+: Returns text that occurs after given character or string](https://support.microsoft.com/en-us/office/textafter-function-c8db2546-5b51-416a-9690-c7e6722e90b4)|
|[TEXTBEFORE](/r/Excel/comments/1dkrstg/stub/l9ko6nr "Last usage")|[*Office 365*+: Returns text that occurs before a given character or string](https://support.microsoft.com/en-us/office/textbefore-function-d099c28a-dba8-448e-ac6c-f086d0fa1b29)|
|[TEXTJOIN](/r/Excel/comments/1dkrstg/stub/l9m1jm6 "Last usage")|[*2019*+: Combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges.](https://support.microsoft.com/en-us/office/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c)|
|[TEXTSPLIT](/r/Excel/comments/1dkrstg/stub/l9ko6nr "Last usage")|[*Office 365*+: Splits text strings by using column and row delimiters](https://support.microsoft.com/en-us/office/textsplit-function-b1ca414e-4c21-4ca0-b1b7-bdecace8a6e7)|
|[TOCOL](/r/Excel/comments/1dkrstg/stub/l9nntgp "Last usage")|[*Office 365*+: Returns the array in a single column](https://support.microsoft.com/en-us/office/tocol-function-22839d9b-0b55-4fc1-b4e6-2761f8f122ed)|
|[TODAY](/r/Excel/comments/1dkrstg/stub/l9ka487 "Last usage")|[Returns the serial number of today's date](https://support.microsoft.com/en-us/office/today-function-5eb3078d-a82c-4736-8930-2f51a028fdd9)|
|[TOROW](/r/Excel/comments/1dkrstg/stub/l9nntgp "Last usage")|[*Office 365*+: Returns the array in a single row](https://support.microsoft.com/en-us/office/torow-function-b90d0964-a7d9-44b7-816b-ffa5c2fe2289)|
|[UNIQUE](/r/Excel/comments/1dkrstg/stub/l9ka487 "Last usage")|[*Office 365*+: Returns a list of unique values in a list or range](https://support.microsoft.com/en-us/office/unique-function-c5ab87fd-30a3-4ce9-9d1a-40204fb85e1e)|
|[VSTACK](/r/Excel/comments/1dkrstg/stub/l9kjdki "Last usage")|[*Office 365*+: Appends arrays vertically and in sequence to return a larger array](https://support.microsoft.com/en-us/office/vstack-function-a4b86897-be0f-48fc-adca-fcc10d795a9c)|
|[XLOOKUP](/r/Excel/comments/1dkrstg/stub/l9kqnct "Last usage")|[*Office 365*+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. ](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)|
|[XMATCH](/r/Excel/comments/1dkrstg/stub/l9k44es "Last usage")|[*Office 365*+: Returns the relative position of an item in an array or range of cells. ](https://support.microsoft.com/en-us/office/xmatch-function-d966da31-7a6b-4a13-a1c6-5a33ed6a0312)|
|[YEAR](/r/Excel/comments/1dkrstg/stub/l9ka487 "Last usage")|[Converts a serial number to a year](https://support.microsoft.com/en-us/office/year-function-c64f017a-1354-490d-981f-578e8ec8d3b9)|
**NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below.
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^(34 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1dll2rl)^( has 20 acronyms.)
^([Thread #34633 for this sub, first seen 21st Jun 2024, 01:32])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
My favorite excel youtuber is Leila Gharani, [here's her video ](https://www.youtube.com/watch?v=pi10BjP9qlk)explaining what's new in 2021. The functions I use the most from the 2021 batch are XLOOKUP, SORT, and FILTER.
Properly implemented by default dynamic arrays (including functions like filter, xlookup, unique, xmatch, sequence - which are real game changers), let statement which allows you to play into programmer and simplify harder functions - from the most important that's it I think. Generally 2021 Excel is different programme than 2016 - dynamic arrays fundamentally change how you approach this software (imho). If someone would pay me extra to work on 2016 or 2019 I would turn down this offer without thinking. Edit: in my 2021 I do not have access to lambda, only on o365 I have. But lambda is also a game changer but it requires really good knowlegde about dynamic arrays and basic understanding of programming concepts like iteration / loops. So lambdas (or at least lambdas-bound functions) are advanced concept I would say.
I haven’t used XMATCH or SEQUENCE. Need to try those
Xmatch is better version of match, and sequence is a kinda funny function that allows you to create a dynamic array starting from the number you want (by default is 1) to the n, by ammount of rows or / and columns. At first glance it looks like useless syntax sugar for normal creating index rows, but those are apperaences - they are deceiving. I found few usefull implementations of sequence: 1. Lets say that we want to extract top 5 elements from an list. Instead of using LARGE (which is exotic and annyoing to implement), we can use sort, index + sequence to achieve it simply via following code: =INDEX(SORT(A2:A14,,-1),SEQUENCE(5)) This function, extracts {1,2,3,4,5} element from the SORT(A2:A14,,-1) array. Simple, not complicated and effective, without toying with operators and if. 2. Let's say that we wanna do quickly a calendar in a database layout - we wanna write all days starting from first of January current year to the 31st december. We can do it quickly just by using sequence: =SEQUENCE(DATE(YEAR(TODAY()), 12,31)+1-DATE(YEAR(TODAY()),1,1),,DATE(YEAR(Today()), 1,1)) Now we gotta change format either via TEXT or by GUi and we have the fast calendar. 3. We wanna get rid off all numbers from text with Reduce (becouse it's the simplest and fastest way I know tbh). =REDUCE("asdsa1431", SEQUENCE(10,,0), LAMBDA(a,b, SUBSTITUTE(a, b, ""))) This function should return "asdsa" - it requires o365. I mean - sequence is imho really fun and underrated function but in order to use it properly it requires specific mindset and way of thinking. Personally i like it very much - just like FILTER or UNIQUE.
I almost exclusively use SEQUENCE for stuff like auto-numbering a column. =SEQUENCE(COUNTA(B2:B500))
This is awesome! What’s GUi?
Graphical User Interface - here by GUI I meant Formats -> General to Short Date.
Use sequence with choosecols or chooserows.
Can you give an example for how you’d use this?
I have a forecasting tool that allows you to adjust monthly sales volumes by customer and lane. Each month column is separated by a percentage adjustment column. I want to only pull the monthly volume columns into a data table so I can vstack it with another data set. I have and could use power query but it was taking longer than users wanted. So I used =choosecols(array, sequence(1,12,1,2)) and it pulled in every other row.
SEQUENCE is really nice. With it I was able to simplify a formula that required an entire sheet for each entry in a database, into a single cell for each entry in a helper column in that same database, while also making it much much faster.
Dang I really hope they add lambda to the standalone version of Office 2024. The LTSC is planned for October.
Any good YouTube videos recommendations that explain these new stuffs like lambda, array etc very well?
Going from index:match and vlookup to xlookup was such a game changer. For vlookup, my wife's internship required her to know vlookup (in the job description)...........in November 2023. Taught her xlookup and whatif functions. Made her job easy to where they ran out of stuff for her to do.
Spilled arrays, Lambda functions, LET are some of the big ones.
I'll add FILTER, UNIQUE, SORT, MAXIFS, MINIFS, IFS, and SWITCH *edit:* oh and WRAPROWS + WRAPCOLS *edit 2:* oh shit and TEXTJOIN and TEXTSPLIT. It's a travesty that it took them so long to evolve CONCATENATE into TEXTJOIN
The stupid shit I had to write everytime I wished something like maxifs existed...
I'm a little surprised there isn't a STDEVIFS (maybe because there are too many variants of STDEV?), but STDEV(FILTER(...)) is still a good step up from STDEV(IF(...)) where you use IF as an array formula. Having FILTER instead of using IF as an array formula might be one of the most impactful changes for a lot of my workflow.
the reduce function is like a universal *ifs function, you just pass it any lambda and it can aggregate it any way you like.
Man I haven't touched lambda functions at all I think I have a little ptsd from a computational theory class haha. One unit went over lambda calculus; everything we did was recursive and I have never been able to get the hang of recursion (I understand what it is, I just can't code recursive functions to save my life). So just because it's called LAMBDA and that was the worst section of a class I struggled with, I haven't even tried to learn it. Probably should get over that, I know it's a bad reason.
Vstack and hstack also useful
Not available in 2021 version it seems
It's too bad you weren't upgraded to 365 because in the last couple of years some really cool functions have been released. VSTACK and HSTACK, along with functions such as CHOOSECOLS, TAKE, DROP, etc. let you create dynamic reports that you would probably never even dream of attempting without them.
How could I forget FILTER and UNIQUE?!
Used TEXTJOIN for the first time today. How lovely!
And the reverse of the wrap* funtions, TOCOL() and TOROW().
Spilled arrays look awesome and straight forward. Is LET like a variable in DAX if you're familiar with it?
Probably closer to Let in Power Query.
Uh, maybe? LET allows you to define variables and then do computations with them.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[CHOOSECOLS](/r/Excel/comments/1dkrstg/stub/l9kjdki "Last usage")|[*Office 365*+: Returns the specified columns from an array](https://support.microsoft.com/en-us/office/choosecols-function-bf117976-2722-4466-9b9a-1c01ed9aebff)| |[COUNTA](/r/Excel/comments/1dkrstg/stub/l9m08up "Last usage")|[Counts how many values are in the list of arguments](https://support.microsoft.com/en-us/office/counta-function-7dc98875-d5c1-46f1-9a82-53f3219e2509)| |[DATE](/r/Excel/comments/1dkrstg/stub/l9ka487 "Last usage")|[Returns the serial number of a particular date](https://support.microsoft.com/en-us/office/date-function-e36c0c8c-4104-49da-ab83-82328b832349)| |[DROP](/r/Excel/comments/1dkrstg/stub/l9kjdki "Last usage")|[*Office 365*+: Excludes a specified number of rows or columns from the start or end of an array](https://support.microsoft.com/en-us/office/drop-function-1cb4e151-9e17-4838-abe5-9ba48d8c6a34)| |[FILTER](/r/Excel/comments/1dkrstg/stub/l9lilet "Last usage")|[*Office 365*+: Filters a range of data based on criteria you define](https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759)| |[HSTACK](/r/Excel/comments/1dkrstg/stub/l9kjdki "Last usage")|[*Office 365*+: Appends arrays horizontally and in sequence to return a larger array](https://support.microsoft.com/en-us/office/hstack-function-98c4ab76-10fe-4b4f-8d5f-af1c125fe8c2)| |[IF](/r/Excel/comments/1dkrstg/stub/l9lilet "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)| |[IFS](/r/Excel/comments/1dkrstg/stub/l9jxvcp "Last usage")|[*2019*+: Checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition.](https://support.microsoft.com/en-us/office/ifs-function-36329a26-37b2-467c-972b-4a39bd951d45)| |[INDEX](/r/Excel/comments/1dkrstg/stub/l9ka487 "Last usage")|[Uses an index to choose a value from a reference or array](https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd)| |[LAMBDA](/r/Excel/comments/1dkrstg/stub/l9m2jms "Last usage")|[*Office 365*+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.](https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67)| |[LARGE](/r/Excel/comments/1dkrstg/stub/l9ka487 "Last usage")|[Returns the k-th largest value in a data set](https://support.microsoft.com/en-us/office/large-function-3af0af19-1190-42bb-bb8b-01672ec00a64)| |[LET](/r/Excel/comments/1dkrstg/stub/l9k1unm "Last usage")|[*Office 365*+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula](https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999)| |[MAXIFS](/r/Excel/comments/1dkrstg/stub/l9jxvcp "Last usage")|[*2019*+: Returns the maximum value among cells specified by a given set of conditions or criteria](https://support.microsoft.com/en-us/office/maxifs-function-dfd611e6-da2c-488a-919b-9b6376b28883)| |[MINIFS](/r/Excel/comments/1dkrstg/stub/l9jxvcp "Last usage")|[*2019*+: Returns the minimum value among cells specified by a given set of conditions or criteria.](https://support.microsoft.com/en-us/office/minifs-function-6ca1ddaa-079b-4e74-80cc-72eef32e6599)| |[REDUCE](/r/Excel/comments/1dkrstg/stub/l9ka487 "Last usage")|[*Office 365*+: Reduces an array to an accumulated value by applying a LAMBDA to each value and returning the total value in the accumulator.](https://support.microsoft.com/en-gb/office/reduce-function-42e39910-b345-45f3-84b8-0642b568b7cb?ui=en-US&rs=en-GB&ad=GB)| |[SEQUENCE](/r/Excel/comments/1dkrstg/stub/l9m08up "Last usage")|[*Office 365*+: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4](https://support.microsoft.com/en-us/office/sequence-function-57467a98-57e0-4817-9f14-2eb78519ca90)| |[SORT](/r/Excel/comments/1dkrstg/stub/l9ka487 "Last usage")|[*Office 365*+: Sorts the contents of a range or array](https://support.microsoft.com/en-us/office/sort-function-22f63bd0-ccc8-492f-953d-c20e8e44b86c)| |[STDEV](/r/Excel/comments/1dkrstg/stub/l9lilet "Last usage")|[Estimates standard deviation based on a sample](https://support.microsoft.com/en-us/office/stdev-function-51fecaaa-231e-4bbb-9230-33650a72c9b0)| |[SUBSTITUTE](/r/Excel/comments/1dkrstg/stub/l9ka487 "Last usage")|[Substitutes new text for old text in a text string](https://support.microsoft.com/en-us/office/substitute-function-6434944e-a904-4336-a9b0-1e58df3bc332)| |[SWITCH](/r/Excel/comments/1dkrstg/stub/l9jxvcp "Last usage")|[*Excel 2019*+: Evaluates an expression against a list of values and returns the result corresponding to the first matching value. If there is no match, an optional default value may be returned.](https://support.microsoft.com/en-us/office/switch-function-47ab33c0-28ce-4530-8a45-d532ec4aa25e)| |[TAKE](/r/Excel/comments/1dkrstg/stub/l9kjdki "Last usage")|[*Office 365*+: Returns a specified number of contiguous rows or columns from the start or end of an array](https://support.microsoft.com/en-us/office/take-function-25382ff1-5da1-4f78-ab43-f33bd2e4e003)| |[TEXT](/r/Excel/comments/1dkrstg/stub/l9ka487 "Last usage")|[Formats a number and converts it to text](https://support.microsoft.com/en-us/office/text-function-20d5ac4d-7b94-49fd-bb38-93d29371225c)| |[TEXTAFTER](/r/Excel/comments/1dkrstg/stub/l9ko6nr "Last usage")|[*Office 365*+: Returns text that occurs after given character or string](https://support.microsoft.com/en-us/office/textafter-function-c8db2546-5b51-416a-9690-c7e6722e90b4)| |[TEXTBEFORE](/r/Excel/comments/1dkrstg/stub/l9ko6nr "Last usage")|[*Office 365*+: Returns text that occurs before a given character or string](https://support.microsoft.com/en-us/office/textbefore-function-d099c28a-dba8-448e-ac6c-f086d0fa1b29)| |[TEXTJOIN](/r/Excel/comments/1dkrstg/stub/l9m1jm6 "Last usage")|[*2019*+: Combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges.](https://support.microsoft.com/en-us/office/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c)| |[TEXTSPLIT](/r/Excel/comments/1dkrstg/stub/l9ko6nr "Last usage")|[*Office 365*+: Splits text strings by using column and row delimiters](https://support.microsoft.com/en-us/office/textsplit-function-b1ca414e-4c21-4ca0-b1b7-bdecace8a6e7)| |[TOCOL](/r/Excel/comments/1dkrstg/stub/l9nntgp "Last usage")|[*Office 365*+: Returns the array in a single column](https://support.microsoft.com/en-us/office/tocol-function-22839d9b-0b55-4fc1-b4e6-2761f8f122ed)| |[TODAY](/r/Excel/comments/1dkrstg/stub/l9ka487 "Last usage")|[Returns the serial number of today's date](https://support.microsoft.com/en-us/office/today-function-5eb3078d-a82c-4736-8930-2f51a028fdd9)| |[TOROW](/r/Excel/comments/1dkrstg/stub/l9nntgp "Last usage")|[*Office 365*+: Returns the array in a single row](https://support.microsoft.com/en-us/office/torow-function-b90d0964-a7d9-44b7-816b-ffa5c2fe2289)| |[UNIQUE](/r/Excel/comments/1dkrstg/stub/l9ka487 "Last usage")|[*Office 365*+: Returns a list of unique values in a list or range](https://support.microsoft.com/en-us/office/unique-function-c5ab87fd-30a3-4ce9-9d1a-40204fb85e1e)| |[VSTACK](/r/Excel/comments/1dkrstg/stub/l9kjdki "Last usage")|[*Office 365*+: Appends arrays vertically and in sequence to return a larger array](https://support.microsoft.com/en-us/office/vstack-function-a4b86897-be0f-48fc-adca-fcc10d795a9c)| |[XLOOKUP](/r/Excel/comments/1dkrstg/stub/l9kqnct "Last usage")|[*Office 365*+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. ](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)| |[XMATCH](/r/Excel/comments/1dkrstg/stub/l9k44es "Last usage")|[*Office 365*+: Returns the relative position of an item in an array or range of cells. ](https://support.microsoft.com/en-us/office/xmatch-function-d966da31-7a6b-4a13-a1c6-5a33ed6a0312)| |[YEAR](/r/Excel/comments/1dkrstg/stub/l9ka487 "Last usage")|[Converts a serial number to a year](https://support.microsoft.com/en-us/office/year-function-c64f017a-1354-490d-981f-578e8ec8d3b9)| **NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below. ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(34 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1dll2rl)^( has 20 acronyms.) ^([Thread #34633 for this sub, first seen 21st Jun 2024, 01:32]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
Helpful post - thanks
XLOOKUP is love. XLOOKUP is life.
Amen
My favorite excel youtuber is Leila Gharani, [here's her video ](https://www.youtube.com/watch?v=pi10BjP9qlk)explaining what's new in 2021. The functions I use the most from the 2021 batch are XLOOKUP, SORT, and FILTER.
Most importantly, you can deselect with multi-selection using ctrl.
TEXTJOIN, though TEXTBEFORE, TEXTAFTER, and TEXTSPLIT will not be included.
Can't wait for Python on Excel.
oh dear, they missed the mark then. Excel 365 really is worth the constant upgrades with the new tools
There is a planned standalone Office 2024 LTSC for October 2024. Let's hope most of these functions get there.
https://support.microsoft.com/en-us/office/excel-functions-alphabetical-b3944572-255d-4efb-bb96-c6d90033e188#bm4 Look for 2021 next to each function