It’s been some time, but here’s another in the ongoing series of “The Rules of Can’t Be Right” pieces. Consider these tips to improve your ability to spot potential spreadsheet errors.
- Approximate numbers so you can do the math in your head as a double check before you begin, i.e., an 8% increase in 745 replies can be estimated at 10% of 750, or 75 minus 15 (15 being 2% of 75) or 60. The precise answer? 59.6.
- Use the Data Filter and Sort capabilities in Excel – filters allow you to get a quick look at all the values in a table, which is great for spotting outliers or eliminating small variations in test fields (i.e., standardizing on either “management” or “mgmt.” as a descriptor). The sort feature allows you to rearrange tables based on various dimensions to highlight potential problems.
- Learn Pivot Tables in Excel – beyond their traditional analytical value, Pivot Tables are great for quickly looking at combinations of data to highlight problems. Recently, I was given a table in Excel that looked fine. By putting it into a pivot table and checking some relationships, it became apparent that 4 lines were missing from a SUM equation and 50% of the costs that should have been represented in the table weren’t accounted for properly.