Excelling at the Rules of CBR

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.

Not only will these three tips help you spot errors, the final tip on Pivot Tables will improve your Excel productivity dramatically. In a real life example, somebody told me that my request to modify a spreadsheet would take three hours. Using Pivot Tables, it actually took 15 minutes. That’s Excelling!