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 just a couple of formulas that are used for entire columns or rows, you are probably safe going this route.  Otherwise, move on to number 2.

2. Excel Automation

Another option is to automate the entry of data and formulas into the spreadsheet through External Data Source linking, Macros, and VBA code.  If I’ve just used terms you have never heard of, and the first option makes you want to throw a computer out the window, it’s probably time to move on to the third option.  Otherwise, happy coding!

3. IT Automation

Yes, I know this is a very broad and generic term.  We’re just going for the basic idea here.  If you want to discuss exactly what this means in terms of the problems you are encountering, send me an email and we can discuss it.  From a very generic standpoint, this option simply means building an automated process to consolidate your data sources (the information in the spreadsheet), prepare the data for reporting (aggregate, sort, calculate), and automatically generate reports.  The reports can still be delivered in Excel, but if a server application is creating them, you are guaranteed consistency.  You also are no longer dependent on that one individual who knows how it’s all put together.