Software

Avoid costly errors by verifying cross-foot results in Excel

Cross-footing can help keep your worksheet healthy, but comparing subtotals can only go so far. See how a simple IF function can alert you to problems with your data.

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 definitions

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

=SUM(B6:E6)

sums the column subtotals. When all is right, the totals are the same.

Figure A

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.

Figure B

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.

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:

  1. Select the cell that contains the verifying formula. In the case of the example spreadsheet, you'd select cell F6.
  2. Choose Conditional Formatting from the Format menu.
  3. Choose Cell Value Is from the first drop-down list. (This is the default, so you shouldn't have to do anything.)
  4. Choose Equal To from the second drop-down list.
  5. 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.
  6. 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.
  7. Click OK twice.

Figure D

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.

Figure E

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.

About

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.

9 comments
judi_hu
judi_hu

Thanks for the extra protection, and just to let you know I think there is an error in the instructions The formula in cell E7 =SUM(B6:E6) sums the column subtotals. When all is right, the totals are the same. I think it should be 'The formula in Cell F7', if I'm not mistaken.

mcoleman1969
mcoleman1969

I just set the conditional formatting on the check cell to change the background color (usually red or yellow) when the two cells are not equal to each other. My spreadsheets have always been technical data so being able to see the actual value of the error is a clue to the location of the error.

IainMSB
IainMSB

Thankyou - this is a useful post. As an accountant who qualified in the '70s, I have, over the years, repeatedly passed on my training on this subject, when coaching non-accountants who may be involved in developing spreadsheets. The advice given was that to "cross-total" their work was an absolute must. Though I have never come across the terms "footing" or "cross-footing" until reading this article, I am well aware that "totalling" and "cross-totalling" have been part of professional accounting best practice since the double-entry theory of accounting was invented by Luca Pacioli in the 15th or 16th century. Wikipedia: Fra Luca Bartolomeo de Pacioli (sometimes Paciolo) (1446/7?1517) was an Italian mathematician and Franciscan friar, collaborator with Leonardo da Vinci, and seminal contributor to the field now known as accounting. He was also called Luca di Borgo after his birthplace, Borgo Santo Sepolcro, Tuscany.

ssharkins
ssharkins

This technique is easy to customize. Thanks for sharing your use.

mitchtownsend
mitchtownsend

It may be a US term of art. I got my CPA certificate in the 1980's and have always heard and used the term. For some horror stories about real-life spreadsheet errors, check the European Spreadsheet Risks Interest Group. Footing and other formula errors are one danger; another is hidden rows or columns. Cross-footing within Excel may not catch this, since it considers the values in the range whether you see them or not. Consider checking it the old-fashioned way, with a calculator tape. Here is one example of a very expensive spreadsheet error based on hidden rows.

byu1980
byu1980

My accounting degree dates from the mid-60's, and we learned to foot and cross-foot always. Think what a pain it was to develop 13- or 20- or 27-column spreadsheets by hand and ensure that they footed and cross-footed. Thank heaven for the spreadsheet technology of today, and yes I used the original Visicalc!

ssharkins
ssharkins

Thanks for the history lesson -- always glad to learn something new. I am not an accountant by training or trade! ;) I'm glad you liked the article!

IainMSB
IainMSB

I was an accountant by training and trade. Then I changed lanes, learned about computers, and became an assembler programmer. I was blown away by the introduction of the first spreadsheet - Visicalc on the Apple II computer in 1979. (http://en.wikipedia.org/wiki/VisiCalc) It was a mystery to me as to why people kept creating such error-filled spreadsheets since then. Then I realised - because, no cross-casting(cross-totalling)! The reason was, they were never taught it. So many spreadsheets. So many errors. An auditor's nightmare. We should all try to learn from history, otherwise we will be certain to repeat the errors of our forefathers. How boring would that be? More boring than history, I suspect.

dhays
dhays

I said what in the wrold is cross footing? So I read the article, I just call it check summing. After all we are using a computer aren't we? I am not an accountant, have never taken an accounting class, Physicist by education, engineer by title. Since my spreadsheet is small, I won't go to the trouble of posting an error message, I can see it is not the same. The sums are gathered from information on other sheets in the workbook anyway. So if my sums are off, it probably is bad information sent to the summary page or retrieved from the wrong spot by the summary page.

Editor's Picks