Excel Top Tip #11 - Do you use Excel's new functions?

Here is the email sent to all subscribers on 22 April 2021. To receive Excel top tips just like this, you can subscribe by clicking here.

Hello

One of last month's tips was to use IFERROR to hide #N/A, #VALUE and #DIV/0 so they don't make your Excel workbooks look ugly, and instead to change the cell to offer a useful prompt to guide the user to enter the desired input information. The function IFERROR was introduced in Excel 2007.

Here's some other functions that were added with Excel's more recent updates that you may have missed:

Excel 2016

CONCAT replaces CONCATENATE() to combine the text from multiple ranges and/or strings, but it doesn't provide delimiter or IgnoreEmpty arguments.

=CONCAT(text1, [text2],…)

TEXTJOIN builds on CONCAT() to combine the text from multiple ranges and/or strings, AND includes a delimiter you specify between each text value that will be combined.

=TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

IFS checks whether one or more conditions are met, and returns a value that corresponds to the first TRUE condition. IFS can take the place of multiple nested IF statements, and is much easier to read with multiple conditions.

=IFS([Something is True1, Value if True1,Something is True2,Value if True2,Something is True3,Value if True3)

SWITCH also replaces the need for nested IF statements; it returns a defined value, such as the day of the week, depending on the value of a cell or formula, like here:

=SWITCH(WEEKDAY(A2),1,"Sunday",2,"Monday",3,"Tuesday","No match")

MAXIFS/MINIFS returns the maximum value among cells specified that match a given set of conditions.

=MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Excel 2019

FILTER filters a range of data based on supplied criteria, and extracts matching records.

=FILTER (array, include, [if_empty])

SORT, well, sorts the contents of a range or array. Values can be sorted by one or more columns. SORT returns a dynamic array of results.

=SORT (array, [sort_index], [sort_order], [by_col])

UNIQUE returns a list of unique values in a list or range.

=UNIQUE(array,[by_col],[exactly_once])

SEQUENCE generates a list of sequential numbers in an array. The array can be one dimensional, or two-dimensional. Start and step values are arguments.

=SEQUENCE (rows, [columns], [start], [step])

Excel 365

XLOOKUP searches a range or an array, and then returns the item corresponding to the first match it finds. If no match exists, then XLOOKUP can return the closest (approximate) match.

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

LET assigns names to calculation results. This allows storing intermediate calculations, values, or defining names inside a formula. These names only apply within the scope of the LET function. Similar to variables in programming, LET is accomplished through Excel’s native formula syntax. To use the LET function in Excel, you define pairs of names and associated values, and a calculation that uses them all. You must define at least one name/value pair (a variable), and LET supports up to 126.

=LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])

And with that, go help your colleagues impress your clients and colleagues in Excel. For any other questions, get in touch; why not book a call to talk about how we can help you?

David - The StatementReader Team