July 12, 2005
On the Ubiquity of Excel
As a matter of course I don't use it at all at home, as I don't have any licenses for office. I do use it at work though, but as with any tool I make sure to use it only where it's applicable.
Like all good software, it gets used in situations where it really shouldn't. The main reason for this in Excel's case is that it's a great way to store semi-structured information. Lists of things are hard to maintain in Word (most people's other weapon of choice) and with Excel you get sorting and filtering thrown in for free. Of course this is the tip of the iceberg. Because Excel doesn't scale well or easily adapt to changing requirements.
There are a couple of cases in point on my current project.
The system test bug (issue/opportunity) list is being maintained in an Excel spreadsheet by the test manager. This is bad in a couple of ways. Only one person can practically look at or amend it at once so there is little or no access control, meaning that anyone can be the 'one' person that edits the spreadsheet at any time. Because it's in Excel there is no version history.
Luckily they have "solved" the last two problems by mandating that anyone who changes the bug list in any way has to save a new copy with a filename that includes today's date and their initials. Can anyone else see the drawbacks in this approach? I did ask, just to be awkward you understand, what date a certain issue was last updated. Surprisingly enough it took an awfully long time to find out.
The other problem this raises is when the requirements change. Initially the test manager just thought he would like to know which issues were open and which closed. It very quickly became apparent that it would be useful to know which problems were being tackled by a developer, which were waiting for re-test and which weren't bugs so much as change requests. So another column was added to the worksheet, but because it was just another Excel column there was no rhyme or reason to the values that ended up in it and it manifestly failed to serve it's purpose of being able to group issues in a like state.
The other major failing of this particular spreadsheet, and it's in the implementation not the tool used, is that they don't link issues to the particular versions of source code that have been changed to resolve them.
Needless to say I didn't want to include my team in this use of inappropriate technology. We're using Subversion for version control and I convinced a colleague to install Trac on the same machine. I'm using it's ticketing feature to log and manage all of our system testing issues. It's not perfect, although the features coming in versions 0.9 and 1.0 will get it very, very close, but it's a chauffeur driven limousine compared to the unicycle that is the Excel solution.
Our other major use of spreadsheets on the project is in the data entry system. We have some obstensibly straight forward data entry requirements, which involve quite large volumes of data. So we've produced some rather nice spreadsheets which download basic information from Oracle, allow the user to add pages and pages of detail and then upload it back into the database.
At first glance this requirement just screams spreadsheet. Until you start adding some very stringent data integrity and validation rules. Then things start to get a bit scary. Because we end up replicating large parts of the (quite complex) data entry screens provided as part of the packaged application.
The problem here is that Excel doesn't lend itself to tied down data entry. It's strength is that worksheets are free form and allow you to specify your own formulas and manipulate information as necessary. In an ideal world we would provide a template and then convert the worksheet to a comma seperated file which would be extensively validated as it's uploaded.
Sadly the other requirement which mandates the use of Excel in this circumstance is that the data (including all of it's complex validation rules) need to be available to users who aren't connected to the network. So we're stuck with developing a standalone .EXE or giving them a spreadsheet.
So whilst we've produced an enormous amount of code that replicates standard functionality with all of the risks that entails. But given the skill set we have in the team I don't see a viable alternative. Any suggestions?
Posted by Andy Todd at July 12, 2005 10:14 PM