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
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:
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