Put errors in check: building a better errorCk.

• Author: Lenning, Jeff
• Position: Worksheet tips

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 ErrorCk 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 workbook at www.excel-university.com/ errorck. 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 FI4 does not equal zero, then the test fails and the test result should be FALSE.

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