Life gets easier with new Excel features - Aptus Business Solutions
Aptus Business Solutions specialise in delivery and supporting solutions for Sage X3, Sage 300, Sage CRM and Sage Intacct. With over 30 years of knowledge, we have been supporting clients from around Australia and internationally. We cater to businesses of all sizes and business areas. Our services, support and business systems understanding enable clients to succeed and grow in their industry. We cover all aspects of system design, business processes, consultancy, hosting and IT, development and systems integration.
ERP, Consultants, Sage, Finance Management, Accounts Management, Sage 300, Sage X3, Sage Intacct, Sage Enterprise Manager, Sage CRM, Finance software, Accounts software, Business Solutions, Business Systems
16051
post-template-default,single,single-post,postid-16051,single-format-standard,ajax_fade,page_not_loaded,,side_area_uncovered_from_content,transparent_content,qode-child-theme-ver-1.0.0,qode-theme-ver-16.7,qode-theme-bridge,wpb-js-composer js-comp-ver-5.5.2,vc_responsive

Life gets easier with new Excel features

Life gets easier with new Excel features

Background

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.

Textjoin result

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

Results from switch function

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.

Transforming cells to data type

Each data type provides a nice info card when clicking on the cell data:

Showing geography info

The same can be had with stocks which are updated on a regular basis:

Showing results with stocks

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.