Excel is a great tool and I use it a lot. It's available on almost every business user's desktop and it's highly extensible (with some sensible design) through add-ins and programming but it can't do everything; push it too far and the results can be nasty.
Here are my nominations for "The Worst Use of Excel ever" awards.
- Entire applications built in Excel/VBA. I'll admit it, I have done this: it's expedient and for prototyping it can work effectively, but the more you try to lock down Excel to behave as an application (rather than a general purpose spreadsheet) the more problems you encounter. At some point you need to crank up a real programming environment with purpose built components, even if it's only to build an Excel add-in (like XLReportGrids)
- Surveys conducted through Excel/Email. Build a survey template, email it out to 200 folks and get back...junk you can't use unless you manually sift through each response. (Yes, I know you can try to lock down the survey spreadsheet, but you can't stop stupidity. People will copy it, change it, enter incomplete records and it will never be a good substitute for direct entry to a database through a form that handles proper validation.)
- Trying to join multiple "tables" by extensive use of VLOOKUP functions. Judicious use of VLOOKUPs actually extends your capability substantially and can help maintain data integrity rather than duplicate data... but, Excel is not a database. VLOOKUP is very slow compared to a database join and do you really want to check that the right function is defined in every row? What happens when I need to add a few records? Can you make sure that the calculation copies down correctly?
- Using Excel to edit database tables. Pull some data from your database into Excel, let someone "edit" it and then try to upload the changes. It's always particularly (un)helpful if they color-code what changed, added new records or added/deleted a few fields.
- Excel as a project management tool. I may get some flack from this one as I know it's really popular but it seems to me that Excel is used just as a grid to layout tasks and timelines. I can do that with a whiteboard. Typically there is no calculation at all and if you want to tie tasks to resources or visualize slippage in tasks across time, this is not the place to start.
- Using Excel's "analytic" capabilities when you need something industrial strength. I'm not a purist, you can use Excel's Solver and Data-Analysis tools quite effectively for smaller/simpler problems. As size and complexity increase you may be able to use more sophisticated add-ins but at some point you will need to upgrade to a purpose-built tool to work effectively..
- Repeating the same "analysis" or "reporting" once per tab for 40 different brands (or factories or products or managers,...). Seriously, there is no way you can stop errors creeping in. You need a reporting or analytic tool that will generate these for you.
- Of course there are also the folks that use Excel as a word-processor, a presentation tool or even a grid to hold the numbers they produced on a calculator but that's really not a fault of the system is it?
My own personal favorite for the top spot is #2 at least until I see another example for one of the others :-)
Which ones resonate for you? Any other nominations?
Coming soon, a new series of posts around using the "Right Tool for the Job".
This may be a good example of using the wrong tool for the job. Running VaR analytics for a bank from a tool that probably should have been used purely for prototyping.
ReplyDeletehttp://baselinescenario.com/2013/02/09/the-importance-of-excel/
Number 1 - drives me crazy!
ReplyDeleteI feel your pain :-). To be fair I have, very occasionally, seen it done well, but typically it is not. Even when it's been done in a professional manner the limitations imposed by Excel and the effort involved in trying to lock it down are rarely worth the end result.
Delete