End-users love them, yet their over-use can cause nightmares for IT.
Julia Vowler looks at ways to keep spreadsheets under control
More than any other individual piece of software, spreadsheets seem to demonstrate the eternal tension between IT and end-users. Simple to use and extraordinarily convenient, spreadsheets are an established favourite with business users.
However, to the corporate IT function, the popularity of spreadsheets has a downside. "Eventually, all spreadsheet applications fail because they are so easy to do that they are used for things they should not be used for," warns business intelligence expert Nigel Pendse.
"It's very easy to start an application with a spreadsheet - accountants are usually dab hands with them - but the applications are very unstructured and there is no documentation," he adds.
"They are great for quick and dirty applications, but then users add new features and that can lead to mistakes - and wrong results can go a long away [around the organisation]."
It isn't, says Pendse, the spreadsheet software that gets it wrong, but users who get out of their depths, or applications that become over-complex.
A useful spreadsheet application can be the victim of its own success. "Even if the original author did a good job, it can be very hard for the next guy to continue," says Pendse. "Spreadsheets are not designed for large applications, so users build linked spreadsheets with tenuous connections. Then they run very slowly and it's much harder to find where the problems are. But so much user time and effort has been put in that the application can't be chucked.
"Finally, the whole wobbly card tower collapses, and IT is called in to sort things out."
Ironically, IT staff may not be the best people to sort things out. The expert skills in spreadsheets are usually found in the end-user community. "But it has now become an IT problem," says Pendse, "even though asking IT to sort out a messed-up spreadsheet application is like asking a bricklayer to fix a house made of wood."
Inevitably, IT will fix the problem in great detail with exhaustive specification requirement exercises and taking so long that end-users lose patience, however good the industrial-strength spreadsheet substitute is.
If IT bans spreadsheets because they are inadequate for performing heavy-duty business intelligence, then end-users will simply rebel in droves. "You can't ban spreadsheets. Users need them for their speed and flexibility," says Pendse.
Once a ban is enforced, business users simply run their spreadsheets at home.
The only solution may be a compromise. Through using separate spreadsheet add-in software that will store the data in a proper database where IT can look after the data properly, end-users can still use spreadsheets to display that information and manipulate it on their desktops for their own use.
You keep the good things about a spreadsheet, he says, but the database ensures good data management, maintainability, security and large-volume data handling.
Build times should not be too grim, Pendse says, depending on the state of the spreadsheet applications. "It should take one or two weeks to extract the data - the less structured it is the harder it is to do - but finding out what the end-users need is the largest problem," he says.
Co-operation is required, however. "End-users need to tell IT: 'These are the kind of calculations we need to do: now fix it up for us.' That saves IT a lot of time, as half the effort is done by end-users," says Pendse.
Business users can then take the data they need into their spreadsheets, add their own data if they want but leave the server database to be looked after by the IT department.
But the IT department should not relax completely, warns Pendse. However happy end-users are filling up their spreadsheets from the server, they will still, inevitably, go off and build their own spreadsheet applications again, keying in their own data. "The total amount of bad spreadsheets is continuously increasing," Pendse warns. Corralling them is, he says, a Forth Bridge experience.
Spreadsheets: to use or not to use?
- Overreliance on spreadsheets as a business intelligence tool is dangerous - they are not designed for large, complex applications, especially where data has to be shared and stored reliably
- There's no point in the IT department trying to ban spreadsheets. Users love them too much. They will ignore bans and the software IT tries to replace spreadsheets with.
Case study: Allied Mills lets users keep Excel but retains central control
Allied Mills is implementing new business intelligence systems to provide end-users with a central data repository based on the transaction systems in its nine flour mills. Although the business intelligence strategy did not originally encompass spreadsheets, end-users were unwilling to relinquish them completely.
IT provided spreadsheet add-in software, Intelligent Apps, so spreadsheets could be filled from the central repository. "The corporate data sources can be used by Excel, which has got us user buy-in," says Steve Butcher, systems development manager at Allied Mills. "We couldn't have taken Excel away - we would just have got total resistance. Besides, it takes time to change platforms, and a lot of our Excel users are accountants who have tight deadlines."
Direct feeding of spreadsheets from the central data repository replaces "a lot of the rekeying of the transaction data, which not only introduced errors but also was a complete waste of end-users' time, and was very difficult to validate and check, especially if there were more copies of the data which led to different versions", says Butcher. He reckons that accountants can now save about two man-days when preparing their reports.
Hanging Excel off the new repository does not mean that the heavy-duty business intelligence data delivery software is ignored by end-users, however, says Butcher. "The role for Excel is as a powerful 'what if' analytical tool, but it supplements other tools such as Pro-clarity which is visually superb, and which users do like," he says.
Once in their copy of Excel, end-users can change the data, but, says Butcher, "they can't write it back to the server", which therefore stays under IT control. "We could use Excel as a data source for the repository, but we would need to treat it with great care. We've talked with users and we could see a situation where we would do it in the future, under controlled circumstances."
Butcher sees little likelihood of end-users launching breakaway spreadsheet applications. "They are getting more data from the central repository, and the speed and efficiency of that far outweighs any alternatives. They can still use Excel independently to play with the numbers or manipulate, say, external data."
This was first published in August 2002