Spreadsheet Secrets: Underrated Spreadsheet Functions Pt.2 - Sheets and Excel
Very useful new functions that are both in Google Sheets and in Excel. Including XLOOKUP; SPLIT; UNIQUE / FILTER / SORT; TOCOL; TEXTJOIN; and more
Continuation of the previous piece: “Spreadsheet Secrets: Underrated Spreadsheet Functions Pt.1“ (October 1, 2023, which was about useful functions that are in Google Sheets and not in Excel, including tools and methods for processing ChatGPT4 outputs; regex Functions; Currency Conversion; Convert to chip; Open multiple URLs). This is the second part of a planned four-part series on my recommended tech hacks. They are (what I believe to be) relatively simple, accessible tech hacks that I personally find useful. I believe many of these are highly underrated. The upcoming parts will be: “Optimizing Desktop: Chrome, Docs, Windows”; Optimizing Android Smartphone”. See also my previous piece on my mobile setup: “Mobile Minimalism: Streamlining My Smartphone Layout” (July 5, 2023)
Both Excel and Sheets have a number of relatively new functions that are signnificant improvements on previous functions.
(This is In addition to Regex functions, see on those in previous post)
XLOOKUP, vs. older VLOOKUP
Both Sheets and Excel now have this. XLOOKUP is a significant upgrade over the traditional VLOOKUP.
Detailed comparison:
Direction of Lookup: VLOOKUP can only search for values on the leftmost column of the table array, moving rightwards to retrieve the related data. XLOOKUP can move righwards or leftwards.
Reference Style: For VLOOKUP, users must specify the column index number to retrieve data, which can be cumbersome and prone to errors, especially in large datasets. XLOOKUP uses a reference style, allowing users to directly select the return range. This eliminates errors associated with column index numbers and enhances the accuracy of data retrieval.
Handling Errors: VLOOKUP has limited error handling capabilities. If the function doesn’t find a match, it either returns an error (or the closest match, if 0 isn’t specified in the 4th argument, which can be quite misleading). XLOOKUP offers improved error handling. Users can specify a custom error message in the 3rd argument, to be displayed if no match is found
Return Array: VLOOKUP can only return a single value from the specified index column. XLOOKUP allows returning an entire array (multiple columns or rows), enabling users to retrieve a broader set of related data with a single function.
SPLIT (Sheets - SPLIT ; Excel - TEXTSPLIT), vs. older “text to columns” in Excel
SPLIT is a function that divides text into columns or rows. With just a formula, users can separate text strings into distinct cells. Users define custom delimiters, from commas and spaces to more complex characters. Being a formula, SPLIT automatically updates the split data if the original text string is modified.1
SPLIT offers dynamic data segmentation, that automatically updates, when new values are added. As opposed to "Text to Columns" wizard, which is a static, one-time operation. (Or previously, To split string in Excel, you generally had to use the LEFT, RIGHT or MID function in combination with either FIND or SEARCH.)
Array formulas: UNIQUE / FILTER / SORT, vs. older static, one-time operations
Dynamic - refreshes automatically
Instead of needing to do it manually, or with clunky macro, can copy paste the formula, and it refreshes immediately. Especially helpful for a workflow that repeats itself consistently.
UNIQUE, like a Pivot Table, extracts distinct values from a specified range, eliminating duplicates and presenting a clean, unique dataset. It’s much cleaner and simpler than using a Pivot Table.
FILTER, like a manual filter, returns rows or columns that meet the conditions specified, offering a filtered view of the dataset.
SORT, like sorting with a filter, arranges data sets in ascending or descending order, based on specified criteria.
See further details here.
These three functions can be combined. I’ve successfully worked with ChatGPT4 to build very helpful formulas using these new functions.
TOCOL function
The TOCOL function transforms an array into a single column. This is often quite helpful. Google Sheets has an older function called FLATTEN, which is no longer needed, and has been superseded.
It’s beneficial when dealing with functions or analyses that require a one-dimensional data array. If uou have a dataset spread across multiple columns and rows and need to consolidate it for analysis or presentation, use TOCOL to transform the data into a single column for easy handling.
Other recent, very helpful functions
Other recent, very helpful functions, with links to discussions on the great website ‘Exceljet’:
TEXTJOIN - joins multiple values together with a chosen delimiter. Vs. previously needing to repeat delimiter multiple times.
CHOOSECOLS - returns chosen columns from a range. Very powerful, there is no previous equivalent that’s even close.
LET - “lets you define named variables in a formula. There are two primary reasons you might want to do this: (1) to improve performance by eliminating redundant calculations and (2) to make more complex formulas easier to read and write.” Vs. previously needing to repeat variables multiple times.
IFS - “Use the IFS function to evaluate multiple conditions without multiple nested IF statements. IFS allows shorter, easier to read formulas.” Vs. previously needing to nest IF statements, which can be quite complex.