TEXTJOIN – Derek Harlan

For those of us who review accounting workbooks it’s helpful to have a few tools to make the review easier. In today’s blog post I’m going to list 3 of my favorite Excel formulas or shortcuts that improve the quality of my reviews.

View Formulas helps you remove plugs or audit formulas

Pressing control and the tilde key (i.e. CTRL + ~) will change Excel to show the formulas in each cell rather than the values. This is very helpful when reviewing workbooks to ensure there are no plug values and that the formulas are consistent have the right ranges, etc. Press CTRL + ~ to put the view back to normal

The shortcut key CTRL + ~ changes the cells to show their formula rather than the value

As you can see in the screenshot, with view formulas it’s very easy to spot formula inconsistencies.

TEXTJOIN for making lists

The TEXTJOIN formula allows you to create lists easily with a seperator character. This is particulatly useful in my trade for combining lists of entites to put into a filter in the accounting system. The syntax of TEXTJOIN is = TEXTJOIN (delimiter, Ignore Empty, cell range)

The delimiter is the character you want to separate the items with, it can be anything but I’ll use the carrot ^ character for this example.

Ignore Empty parameter is either TRUE of FALSE, normally you’ll use TRUE so that it doesn’t conisder any empty cells in the range.

In the screenshot below, you can see how I used the formula to easily create the list (i.e. propert1^property2^property3^property4)

SUMIFS for totaling by category

The SUMIFS formula is very handy to get a quick total. It’s syntax is

=SUMIFS(sum range, category range 1, category, [more categories])

The sum range is the values you want to add.

The category range is the labels for each value

The category is which label you want to add

Consider this example. We have two projects TI Project #1 and #2. If we want to add TI Project #1 interdependently we can do this very easily using the SUMIFS like this:

SUMIFS can easily add up categories for your review

Although we tend to look to excel functions for preparation, even reviewers can harness the power of Excel shortcuts and formulas to make them better reviewers and help reduce or eliminate errors.

Do you want to learn how to automate your financial workpapers? Do you want to get started with VBA?

My book, Beginning Microsoft Excel VBA Programming for Accountants has many examples like this to teach you to use Excel to maximize your productivity! It’s available on Amazon, Apple iBooks and other eBook retailers!

Resources:

TEXTJOIN https://support.microsoft.com/en-us/office/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c

SUMIFS https://support.microsoft.com/en-us/office/sumifs-function-c9e748f5-7ea7-455d-9406-611cebce642b

Technology: Office 365 as of 2020

Microsoft introduced a new function which makes combining the contents of cells even easier than before. In addition to concatenating, or joining, the contents of cells the TEXTJOIN function also allows you to insert a delimiter between each item. This makes it more powerful than the old concatenate formula.

The ability to add a delimiter is what makes this function very useful. In the past we had the CONCAT and CONCATENATE functions which would join together text however, depending on the application, you usually need some character between your values.

Here’s a common example. Pasting a list of email addresses from Excel into an email in Outlook. The application expects a semicolon or comma between each email address or name, however you may not have included these characters in the list in Excel as it wouldn’t make sense to store this with the data.

If you require functionality similar to the old CONCATENATE function you can set the delimiter to “” which joins the text together without any character between each item. There is also the ability to include or exclude blank cells in the cell range from the results. If you need to do this depends on your use case.

The syntax is =TEXTJOIN([delimiter], [ignore blank cells], [cell range])

Where:

Delimiter is any character you want between your list. The character need to be enclosed with quotes, i.e. “;” This is required.

Ignore blank cells is either True or False depending if you want blanks included in your list. This is required

And the cell range is your list items you’re wanting to join together. You can also have multiple ranges in this field if you need them.This is required.

Let’s look at some examples

This first example presents a case where we want to email some individuals, to put their email addresses in the To field of our email client we need them separated by a semi-colon.

This next example provides a result similar to the old concatenate function. In this example the list of numbers is joined together without any delimiter.

The TEXTJOIN function saves a lot of time for those of us who use Excel to work with data needed in other applications. The power of adding a delimiter means you can quickly transform a list stored in a cell range to suit the needs of your application. It’s a simple & powerful formula to add into your repertoire.

Do you want to get started with VBA? Want to learn how to automate your financial workpapers?

Learn More:

Example Workbook:View Workbook on Microsoft OneDrive

TextJoin Function: TEXTJOIN at Microsoft Office Support

Tag: TEXTJOIN

Excel Functions for Financial Reviewers

TEXTJOIN function makes list building easy