Accuracy is vital. If accuracy didn't matter, you'd rely on your fingers instead of on Excel to sum your transactions. Unfortunately, Excel is just as vulnerable to mistakes as your calculating fingers. To verify subtotals and totals, auditors use a technique called cross-footing, which works great until someone erroneously deletes or overwrites a dependent formula. If something like that happens, you have to visually catch the discrepancy. To ensure that you don't miss such a mistake, use a cross-footing formula that verifies itself.
Note: This article is also available as a PDF download.
Some definitionsCross-footing is a modern term that has evolved to mean more than it originally did. Footing is the act of totaling columns. Cross-footing originally meant to total values in a row. However, today, the term refers to the overall process of double-checking totals by comparing columnar and row subtotals. Figure A shows cross-footing at work. The formula in cell F6 totals the row subtotals. The formula in cell E7
sums the column subtotals. When all is right, the totals are the same.
Compare subtotals to verify accuracy.Figure B shows what happens when someone deletes (or overwrites) one of the subtotals. The cross-foot totals don't agree. With a quick glance, you can see that something's wrong.
When subtotals don't match, you know something's wrong.
The obvious but impractical solution
Since verifying cross-foot results involves dependent formulas, protecting those cells that contain dependent formulas seems like the most obvious way to ensure the accuracy of your spreadsheet. It works well, but it's impractical if more than one person can update the spreadsheet. When that's the case, those people have the password to unprotect the file and while modifying the spreadsheet, anything can happen. By all means, apply protection, but don't rely on it solely.
Verifying cross-foot formulas
Although the two formulas alert you to a problem, the responsibility is on you to notice that the values differ. That's a lot of responsibility, but a simple IF function can help by alerting you in a more obvious way. Let's return to the sample worksheet in Figure A to employ a cross-foot formula that verifies itself. In this case, you'd use a formula that compares both sets of subtotals using the following syntax:
=IF(SUM(rowsubtotal)=SUM(columnsubtotal), SUM(eithersubtotal), "warningtext")If a user accidentally overwrites or even deletes a subtotal, the verifying formula will warn you, as shown in Figure C.
Use text to warn you when subtotals don't match.
Now, the warning text goes a long way toward getting your attention, but you can improve the effect by applying a conditional format as follows:
- Select the cell that contains the verifying formula. In the case of the example spreadsheet, you'd select cell F6.
- Choose Conditional Formatting from the Format menu.
- Choose Cell Value Is from the first drop-down list. (This is the default, so you shouldn't have to do anything.)
- Choose Equal To from the second drop-down list.
- In the third control, type warningtext exactly as it appears in the formula. In this case, you'd enter "SUMS DO NOT BALANCE," as shown in Figure D.
- Click the Format button and set the formatting you want when the verifying formula returns warningtext. In this case, click the Font tab and choose Bold from the Font Style list boox nd Red from the Color drop-down list.
- Click OK twice.
Enter the exact warningtext, as used in the verifying formula.As you can see in Figure E, the warning text stands out and gets the message across. You can apply more conditional formats. Just let your spreadsheet's existing formats dictate the right effect and don't hesitate to mix things up a bit.
The red warning text is hard to miss.
Don't be at cross purposes: Verify!
Cross-footing is a good way to keep your spreadsheets healthy, but comparing subtotals can only go so far. Use the simple IF function reviewed in this article to display an obvious and meaningful warning when something's wrong.
Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.