Building an Error Check Worksheet with ExcelJeff Lenning
October 21, 2013 — 1,188 views
Let me ask you a question. How do you know your Excel workbook is accurate? This question is of utmost importance. It is critical that we prepare workbooks that are internally consistent, with numbers that tie out and data that flows properly throughout. As Excel users, this is our responsibility. This article summarizes Chapter 25 of Excel University Volume 2 and explores the idea of a dedicated Error Check worksheet.
The Error Check worksheet, ErrorCk, continuously monitors a variety of conditions throughout the workbook. These conditions, or tests, are designed using pass-or-fail logic. If all tests pass, then we have confidence in our workbook. If a test fails, we identify the error and correct it. For a sample ErrorCk sheet, please download the sample file referenced below.
Let’s think about different test ideas for a moment. How about this one: do debits equal credits? That condition must be true for our workbook to be accurate. Here is another example: is the balance sheet in balance? Assets must equal Liabilities and Equity, and if they do not, there is a problem.
Let’s review the ingredients of the ErrorCk worksheet.
The Excel ingredients used in the sample ErrorCk sheet follow:
- Boolean Values
- Comparison Formulas
- The Logical AND Function
- Conditional Formatting
Let’s walk through each of these in a bit of detail.
Excel accommodates several different types of stored values. For example, it can store a text value, a number, or a date in a cell. In addition to these data types, Excel also supports Boolean values. A Boolean value is displayed in a cell as TRUE or FALSE. It is convenient to use Boolean values for the test result cells, since they make it easy to tell if a test passed or failed.
To compute the test results, we can use a comparison formula. For example, if debits are stored in cell A1, and credits are stored in B1, we could simply use a comparison formula such as:
If debits (A1) equal credits (B1) then the formula returns TRUE, and we are happy because the test passed.
The Logical AND Function
Since we’ll likely have many tests, we want a single cell that monitors the results of all tests. If all tests pass, then we want our overview cell to be TRUE. If any single test fails, we want our overview cell to be FALSE. We can use the AND function to help out. If we stored all test results in column B then we could use the following formula:
If all test results are TRUE, the function returns TRUE. If any test fails, it returns FALSE. The overview would go at the top of the worksheet, making it easy to see.
To make it easy to tell which tests passed and which failed, we apply conditional formatting to the test result cells. We use green when the cell value is equal to TRUE because the test passed. We use red when the cell value is equal to FALSE because the test failed.
We can improve efficiency and reduce errors in our workbooks when we ask Excel to help review our work. Setting up an ErrorCk sheet is convenient since it allows us to quickly confirm if our tests have passed or failed. This approach has helped in countless workbooks, and I hope it can help your workbooks as well.
To download the sample file, please visit excel-university.com/lorman
To view a Lorman webinar that goes into more detail about this subject, please visit http://www.lorman.com/live-webinar/392742?discount_code=N9507789&p=13389