Excel does exactly what you tell it to do.  It’s just that sometimes there seems to be a language barrier…
Don’t get me wrong.  I’m a fan of Excel.  The capabilities in terms of automation are extensive, and the ease of use is great.  However…  there’s also the fact that many users of the application don’t have a firm grasp of how formulas can backfire if you cut and paste data or move columns around.  As we discussed in The High Cost of Low Productivity, these types of errors could be costing you more than you realize.

In her article “Damn you, Excel spreadsheets, JP Morgan Chase edition”, Heather Timmons points out a real-world example of a problem you could be experiencing in your own organization and not even know it:
“Due to an error in an Excel spreadsheet used to model risk, JP Morgan seriously underestimated the downside of its synthetic credit portfolio, which ultimately led to the bank to declare $6 billion in losses and could lead to another $600 million in fines. As James Kwak explains on Baseline Scenario, the errors stemmed from a combination of copy-paste mistakes and a faulty equation created to crunch the numbers.”
So do I stop using Excel?
The short answer is “No”.   If you’re utilizing Excel for large amounts of information and formulas, and manually copying data into the spreadsheets, you basically have 3 options to guarantee you aren’t creating more problems than you’re solving.
1.Validate, Test, Validate, Test, etc…
The first option is to manually go through and validate all of the formulas.  Copy and paste will distribute the formula to all rows, but now you’re involving people in the process and that introduces risk.  If you’re dealing with small amounts of data and […]