Set up Excel worksheets to warn you when row and column totals don’t mesh

 

by Frank Kuhn

Application: Microsoft Excel 2000/2002/2003/2004/2007/2010/2013

Operating Systems: Microsoft Windows

Download: http://download.elijournals.com/excel/201810/IfError.zip

Maintaining accuracy is important to every business, and Excel spreadsheets go a long way to ensuring this accuracy. Unfortunately, Excel allows for only one formula in any given cell at a time. So it’s possible for your column and row totals not to match — and you’d never be the wiser if you didn’t double-check them! Instead of manually poring over your row and column totals, we’ll show you how a simple formula and an unmistakable warning message can do the job for you.

To automatically crosscheck column and row totals without manual calculations, we’ll:

  • Present an example of a spreadsheet whose cross-foot total doesn’t crosscheck due to an error in one of the functions that feeds into the cross-foot total.
  • Combine an IF function with an error message to alert us to similar crosscheck errors.
  • Provide tips for formatting our error message to maximize its visibility.
  • Test our IF function by correcting the worksheet’s incorrect formula

Crosschecking to make sure that column and row totals are equal is known in accounting circles as cross-footing, and it’s an important tool to any accountant in proofing the accuracy of spreadsheet and ledger data. In this article, we’ll show you how to use the IF function in conjunction with error messages to proof your data consistently and accurately. When you’re done, you’ll have yet another valuable Excel tool with which to verify the accuracy of your data.

Clue into cross-footing

Cross-footing is the process of validating that both row and column totals are equal. The cell in which the row and column totals intersect is known as the cross-foot.

Insert an IF function into your spreadsheet

To illustrate the need to compare row and column totals, we’ll use the workbook shown in Figure A. This worksheet is, no doubt, similar to many of your own; it sums values both across rows and down columns. Cells I2 through I4 contain SUM functions that total the values of each type of stock in rows 2, 3, and 4; cells D5, F5, and H5 contain SUM functions that sum the stock values for each client in columns D, F, and H. As the sum of the row totals should equal the sum of the column totals, you should be able to use either sum to find the grand total at the totals cross-foot in cell I5.

Download: If you’d like to follow along with our example, you can download and extract the sample workbook IfError.xls from the URL given at the beginning of this article.

IME18a01a_annotated

A: A cross-foot is the intersection where row and column totals intersect.

However, let’s suppose that you inadvertently make a mistake while entering one of the referenced functions. The formula in cell F5 is =SUM(F2:F3) as opposed to =SUM(F2:F4) as it should be. This means that one stock share is left out of the total in cell F5. Unless you specifically double-check for the accuracy of these results, you might never notice, especially if your worksheet is much longe[...]

 
Join NowClose
Return to the ExcelSkillsSociety's homepage