How to Set Up an Automatic Error-Checking System in Excel Reports

Decades ago, I worked as a cost accountant for a large company. But because our department received terrible reports, I wrote my own reports…using punch cards!

Once I could query my data, I quickly developed this four-step error-trapping strategy:

1. When I discovered a new error in the data, I’d fix it.
2. I’d search my data for other errors like the one I discovered.
3. I’d correct those errors.
4. I’d add the report to a standard batch of weekly reports that alerted me to any new errors like the ones I’d found in the past.

In short, by using this four-step process I was able to ratchet down the errors in my department’s accounting data to zero…until a new type of error raised its ugly head.

In this fourth and last article about using Account Groups in Excel, I’ll explain a way to implement a similar error-trapping strategy in your own Excel reports.

Here are the links to all four articles in this series:

Part 1: How to Report GL Account Groups in Excel
Part 2: How to Define General Ledger Account Groups in Excel
Part 3: How to Use Array Formulas to Report Groups of Accounts
Part 4: How to Set Up an Automatic Error-Checking System in Excel Reports

And here’s where you can download a zip file with the workbooks used in the series.

Checking for errors is an important aspect of account grouping because much can go wrong:

• You could have thousands of accounts in your General Ledger, and if you put one of them in the wrong group, you could misstate the balances in two groups.
• In many companies, new accounts are added frequently, with no warning to the people who work with those accounts daily. So when transactions occur against new accounts, your reports might not have included them yet.
• Your Chart of Accounts data in the Meta sheet might have an incorrect sign for the natural balance of an account. This would cause numbers to be added or subtracted incorrectly.
• Your source data could be incorrect, incomplete, or garbled.
• Etc.

And therefore, it makes sense to set up an error-alerting system for your reports.

The General Strategy for Error-Alerting in Excel

The general strategy is simple:

First, you maintain an Error-Summary Table in your workbook, in a worksheet that I normally name Errors. This Table maintains a list of calculations that return TRUE if an error is detected and FALSE otherwise. Often those calculations reference other error checks, as you’ll see shortly.

Second, in a named cell you set up one formula that returns TRUE if any of those error checks return TRUE.

Third, you set up your reports to make it VERY obvious when that named error cell returns TRUE.

Fourth, as you discover additional ways that errors can occur, you add checks for them to your error-checking Table.

Setting Up the Error-Summary Table

The following Table—which could become quite large—checks for all likely errors in your report.

The first row of the Table performs two tasks. First, if you enter either TRUE or 1 in cell G3, you force it to report an error. And that allows you to check your error-alerting system, which we’ll get to in a few minutes.

Second, when you enter a formula into an Excel Table, Excel copies that formula into the rest of the column of the Table—but only when the original column was empty or had the same formula. Therefore, by entering a constant into cell G3, we stop that behavior in this Table. We want to do this because every formula in the Error column is intended to be different.

Here, the IsError value in cell B3 is the cell that returns TRUE if any of the values in the Error column are TRUE. Its formula is a simple one:

B3: =IFERROR( OR(ErrSmry[Error] ), TRUE )

The primary function here is OR, which returns TRUE if any value in the Error column is TRUE. However, if an Excel error value appeared in the Error column, the OR function would return that error value instead. That’s why we wrap the OR function with an IFERROR function, which returns TRUE if an Excel error value is returned anywhere in the Error column.

Checking for Missing and Misplaced Accounts

The first test in the Error Summary Table is…

G4: =IFERROR( Groups!IsError, TRUE )

…which references the IsError value in the Groups worksheet, shown here:

You might remember from Part 2, How to Define General Ledger Account Groups in Excel, that the formulas in column I return the actual number of accounts listed in each row of the table, starting with column M. And the formulas in column J count the number of accounts that should be listed, based on the GL account patterns you’ve listed in columns E-H. Therefore, if the two columns don’t match exactly, there’s an error. And that error usually means you must update your list of accounts.

The array formula in cell B7 is:

B7: =IF( NOT(ShowSpills), FALSE, IF( SUM(ABS( NumAccts – NumSpills ) ) = 0, FALSE, TRUE))

This formula begins by testing whether the ShowSpills value is TRUE. If it’s not TRUE, then the formula always returns FALSE. This is because when ShowSpills is FALSE, the formulas in columns I and J don’t calculate, which can speed calculation time considerably.

The second IF subtracts the NumSpills column from the NumAccts column, row by row. If the sum of the absolute values in the column of results doesn’t equal zero, we know that something has changed, which we need to fix.

The CSE version of the workbooks doesn’t include this test because it doesn’t have the power to calculate the list of accounts based on their GL Account Patterns.

Checking for Group Totals

Column C in the Report sheet also checks for errors, as shown here:

Cell F5 can be calculated in two ways. First, it can use a simple formula to subtract cell F4 from F3. Second, because we’ve set up a Group Account code for Gross Profit, we can calculate it directly.

Therefore, the formula in cell C5 tests the accuracy of both sets of calculations, like this:

C5: =OR( F3-F4<>F5, G3-G4<>G5 )

(Note: If you have many accounts, you might need to modify these formulas as explained in How to Foot and Cross-Foot Excel Reports in a Floating-Point World.)

And similarly, cell C7 tests the other total value in this report:

C7: =OR( F5-F6<>F7, G5-G6<>G7 )

And finally, cell F5 in the Error Summary…

…returns the summary of the report’s error values like this:

G5: =IFERROR( OR(Report!Error), TRUE )

Notice that when the OR function tests a range that contains empty cells, it ignores those empty cells in its calculation. The same is true for the AND function.

Checking that Debit and Credit Sign Values Equal 1 or -1

In the Meta sheet’s Chart of Accounts Table, the Sign column formats positive numbers as Dr, and negative numbers as Cr. But we need those numbers to equal 1 or -1, and nothing else.

Therefore, we need an easy way to check those values.

This formula in the Errors sheet shows the first way that occurred to me:

G6: =IFERROR( ISNA( SUM(
MATCH(Meta[Sign], {1, -1}, 0) ) ), TRUE )

The key function here is MATCH, which has this syntax:

=MATCH( lookup_value, lookup_array, match_type )

What we want to do is to check each value in the Sign range to see if any of them has a value that’s not equal to 1 or -1. And therefore, in this formula…

• the lookup_value—where Excel expects to find a single value—instead contains the entire Sign column. This will cause the MATCH calculation to be performed for each value in that column.
• the lookup_array is the array constant {1, -1}. By using only those two values, it means that if there’s an empty row in the Chart of Accounts Table, the test will raise an error. If you don’t want that to happen, you could change your array constant to:
  {1, 0, -1}
• the match_type is zero, which tells MATCH to return #N/A if the Sign column contains any value other than the two (or three) values in the array constant.

In the formula, the only purpose of the SUM function is to return some value or #N/A. And therefore, if ISNA(value) equals TRUE, one of the values in the Sign column has a value other than 1, -1, or the optional zero.

And finally, the IFERROR formula returns TRUE if SUM returns an error other than #N/A.

Later, however, I thought of a simpler way to find the same result:

G6:  =SUM(ABS(Meta[Sign]))<>COUNT(Meta[Sign])

If the Sign column contains only -1 and 1 values, the sum of their absolute values will equal the number of values in the column. And therefore, we have an error if the two calculations do not equal.

The second version of this formula will calculate more quickly, but the first version is more fun. You can use whichever one you like.

Displaying Error Alerts

When the IsError cell in the Errors sheet returns TRUE, we should make the error obvious.

Here, for example, the Error Found label is returned by this formula:

E1:  =IF(IsError, “ERROR FOUND”, “Income Statement”)

And what really makes the error obvious is that we use conditional formatting to turn the body of the report bright red.

To do so in this example…

1. Select the range E2:G7.
2. In the Home, Styles group, choose Conditional Formatting, New Rule.
3. In the dialog, choose Use a formula to determine which cells to format.
4. In the New Formatting Rule dialog, in the  Format values where this formula is true edit box, enter: =IsError
5. In the dialog, choose Format.
6. In the Format Cells dialog, in the Fill tab, assign the bright red background color.
7. OK each dialog to return to the Ready Mode.
8. To test your results, enter 1 in cell G3 of the Errors sheet:

And then, when you’re satisfied with your error alerts, enter zero in cell G3.

Wrapping Up

As I wrote in Part 1, you can use Account Groups for purposes that have nothing to do with Debits and Credits. But I’ve concentrated on accounting reports because this is where I’ve seen the greatest need for Account Groups.

If you just want a quick-and-dirty way to set up Account Groups, Tests 8 and 9 in Part 3 will show you the method. But if you want to follow a system for creating them, along with a system for trapping errors, you should find all parts of this series useful.

Again, you can download a zip file with all three example workbooks at this link.

And if you haven’t already done so, please support my Excel.UserVoice requests about the INDIRECT function and the array of arrays—as I described in Part 2, How to Define General Ledger Account Groups in Excel.

And here are the links to all four articles in this series:

Part 1: How to Report GL Account Groups in Excel
Part 2: How to Define General Ledger Account Groups in Excel
Part 3: How to Use Array Formulas to Report Groups of Accounts
Part 4: How to Set Up an Automatic Error-Checking System in Excel Reports

Do you know of other Excel users who could find this article useful? Now would be a great time to send them a link to it or to post it to a social group.