Put Errors in Check

How do you know your workbook is accurate? This question is important because it’s our responsibility to ensure that data flows properly through the workbook, with numbers that tie out, and with reports that are internally consistent.

One way to help ensure workbook accuracy is by creating a worksheet within the workbook dedicated to this task, and one that will help you perform your review more quickly. In my workbooks, I call this error check worksheet ErrorCk.

Overview

The error check worksheet continuously monitors a variety of conditions throughout the workbook. These conditions, or tests, are designed using pass/fail logic. If all tests pass, then we feel confident in our workbook. If a test fails, we have an opportunity to address any issues.

What are some examples of tests? How about this one: Do debits equal credits? Or, do assets equal liabilities and equity? When we identify a test, we just put it on the error check sheet.

Here are some ideas on how to build a good error check sheet.

Walkthrough

To work along, please download the sample workbook. See Figure 1 for a screenshot of a basic ErrorCk sheet. There are several key things that make the ErrorCk operate well, and so we’ll walk through each of the following ingredients:

• Boolean Values
• Comparison Formulas
• The AND Function
• Conditional Formatting

Boolean Values for Test Result Values

Excel supports several different data types, and you’re no doubt familiar with numbers, dates and text strings. Additionally, Excel supports Boolean values, which are represented in cells as TRUE and FALSE. If you enter true into a cell, all lower case, Excel will convert it to uppercase to let you know that it has recognized and stored it as a Boolean value. Boolean values can be stored in cells, used as function arguments and returned as function results. For example, if you’re familiar with the VLOOKUP function, its fourth argument is a Boolean argument.

The way we use Boolean values in our error check sheet is to store the test results. If total assets equal total liabilities and equity, then the test passes and the result should be TRUE, as shown in B10 in Figure 1. If the difference in F14 does not equal zero, then the test fails and the test result should be FALSE.

Figure 1

We use Boolean values for a few different reasons. It’s easy to see when a test passes and when it fails, it ensures that all test results are displayed consistently and it facilitates an overview section as discussed more below.

Comparison Formulas to Compute Test Results

We use comparison formulas to compute the test results. Technically, we could calculate the test result using an IF function. For example, we could use the following formula in cell B10:

 =IF(F14=0,TRUE,FALSE)

That way, if the difference is zero, then the test result is TRUE; otherwise, it’s FALSE. However, there’s an easier approach: We can use a comparison formula
instead of the IF function since we just want to return TRUE or FALSE. This simplifies our test result formulas, making them easier to write, troubleshoot and
maintain. For example, we could use the following comparison formula in cell B10:

 =F14=0

When the difference is zero, the formula returns TRUE; when it’s not, it returns FALSE. As you can see, this simplifies our error check formulas.

Overview Section with the AND Function

Since a typical error check worksheet will have many tests, we can set up an overview section at the top to let us know if all of our tests passed, as illustrated by cell E6 in Figure 1.

The overview is achieved with the AND function, which returns TRUE when all arguments are true. If any argument is FALSE, then AND returns false. Rather than pass
each individual test result cell as a function argument, there’s an easier way. If we store all test results in column B, we can use that column as the sole AND function argument, such as the following formula used in E6:

 =AND(B:B)

When all tests in column B are TRUE, then the AND function returns TRUE. If any single test result is FALSE, then AND returns FALSE.

Conditional Formatting to Make it Sexy

Lastly, we’ll splash on some conditional formatting to make it easy to read. Conditional formatting formats a cell based on its value. So, when our test result cells are TRUE we’ll use green formatting, and when a test fails we’ll use red. We can easily apply conditional formatting by selecting the test result cells and then using the following Ribbon command:
Home > Conditional Formatting > Highlight Cell Rules > Equal To

When the cell value is equal to TRUE, apply green formatting. Then, set up an additional conditional formatting rule so that if the cell value is equal to FALSE, the software will apply red formatting.

Conclusion

The ErrorCk is an integral component of any recurring use workbook. It’s worth the time it takes to set up, because it helps improve workbook accuracy and efficiency. And, remember, Excel rules!