22 Apr Life gets easier with new Excel features
I’ve spent a portion of my time helping colleagues and clients transform and manipulate data in Excel. Coming from a software development background I’m quick to open the VBA editor and start coding to solve the problem.
Thankfully each release of Excel Microsoft adds new features making my task easier and empowers the end user to perform these tasks themselves. I’m going to highlight the ones that we use frequently but you can go to Microsoft Excel 2019 updates for the full list of features.
I pick you and you and you
Want to join a range of cells to form a new text, TEXTJOIN will do that for you. While you could use the new CONCAT function (basically a shortened form of the standard CONCATENATE function) I prefer the new TEXTJOIN. The thing that makes this function powerful is the ability to ignore blank cells and to provide a delimiter between text.
Let’s say you create your Sage 300 item SKU using Excel based on four segments that you specify over columns A to D
- segment one is supplier
- two is unit of measure
- three is year
- fourth is a random number
Within column E you want to display the final item SKU ready to import into Sage 300, delimiters and all.
Using TEXTJOIN allows you to join all these columns together including the delimiter. You can see the results of my combinations below.
Note that empty cells are skipped but you can easily include them by changing the second input from TRUE to FALSE.
My formula in E4 is:
=TEXTJOIN("-",TRUE, A1, B1, C1, D1)
It’s a powerful and easy to use function that you can use for all your joining functionality.
The full syntax is below:
TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
The old switcheroo
Here is a handy function that eliminates long IF statements. The syntax for SWITCH is based on an input followed by a list of if/then statements.
Let’s say you’re doing your yearly GL budget in Excel and you have your fiscal periods as a number from columns B to M but you would like to display the month above them i.e. April. In the example below I have specified to evaluate cell B2, and if the value is 1 then the text will be “Jan”; if the value is 2 then the text is “Feb”; all the way until May. The last input is for all other values which I want to display as “Future”.
Formula in cell B1 is
=SWITCH(B2, 1, "Jan", 2, "Feb", 3, "March", 4, "April", 5, "May", "Future")
With results as follows
I recommend you use the SWITCH statement in place of long IF/THEN/ELSE statements which can be error prone and hard to read and maintain.
Full syntax is below:
=SWITCH(Value_to_switch, Value_to_match, Value_to_return_on_match, default_value)
It’s all in the details
Feel like enhancing your Excel sheet by referencing geography and stocks, then data types are for you. Available in the Data column this functionality allows you to tag cells with references. It’s a sure-fire way to provide more information into your Excel reports without cluttering up columns and cells.
In the example below I’m going to add geography and stock references to the cities in column A and currencies in column E respectively. Simply highlight the cells and select the appropriate data type.
Each data type provides a nice info card when clicking on the cell data:
The same can be had with stocks which are updated on a regular basis:
There are currently only two data types available but keep an eye out as Microsoft adds new data types on updates. Bank feeds and currency rates would be great!
One to rule them all
If you’ve only been using VLOOKUP and HLOOKUP, then XLOOKUP is for you.
It works the same way you would expect a LOOKUP to function where it returns values from an associated row or column, except it also include IFERROR, and/or INDEX/MATCH functionality. The basic syntax is below, but you can also add what happens when text is not found, search conditions, and match mode.
=XLOOKUP(lookup_value, lookup_from, return_from)
I’m going to leave examples for the next installment, however, I do recommend you learn it as it makes lookups in Excel so much easier.