Your mission-critical data may not be where you think it is

, Nov 17, 2005

“Think of your centralized database applications as a set of large rocks. Their great strength is their solidity. Their great weakness is their lack of flexibility. Think of spreadsheets as the water that flows over and around the rocks. Their great strength is their flexibility. Their great weakness is their lack of solidity. The easiest route to the far side of a rock is to be like water and flow over or around it, rather than to change the nature of the rock.”

Master Foo, as reported by the brilliant Sean McGrath.

On November 17, 2005 6:00 PM, Doug said:

Potential loss of critical enterprise data isn’t the only risk with spreadsheets. They seem to have an accuracy problem, too:

Spreadsheets aren’t going away, but I’m not convinced that the corporate world is doing much to control the risks.

On November 23, 2005 4:58 AM, Richard Tallent said:

I’ve taken a novel approach on the corporate web applications I develop: we have invested heavily in making sure that every application has fully integrated, robust, roundtrip Excel import/export capabilities.

Most of our data are non-numeric, but the users are engineers who are nonetheless much more comfy within Excel. Also, our teams generally consist of 5-30 people spread out throughout the US, so simultaneous web access is a must.

The average user’s workflow is: (1) search, (2) download to Excel, (3) edit in Excel, (4) upload. Data “unit tests” solve the validation problem on the import side while also allowing users to upload incomplete data.

Office 2003’s SpreadsheetML has been a godsend for us—before, we were using a combination of Excel HTML on export and Excel ODBC (users had to “Save As” when opening the file for the first time off the server). Now our spreadsheets download complete with conditional formatting, validation, etc. and upload without any of the weird limits of Excel ODBC.

Response over the past few years has been great: users can work offline, use copy/paste/formulas/conditional formatting without issue, pass around for review, combine and split data in ways they could never manage on a web form. I also teach Advanced Excel classes internally to make sure users know how to make the most of the tool and avoid the pitfalls.