Six Pitfalls to Watch for When Starting that Excel Project
Posted by Sean MacKenzie on Mon, Feb 15, 2010
Spreadsheets are one of the most useful tools that businesses have today. Some people have become so proficient at using them that, as a result they use them all the time, for all kinds of things. This often leads into people using spreadsheets for purposes they weren't designed for. You would not use a spreadsheet when:
It needs to be shared concurrently.
When many others need to have access to view and update it, you need another tool. Excel allows one user at a time. This often leads to many people taking a copy of their own, which leads to a real mess when it all has to come back together.
You are continuously collecting data.
An engineer who has 50,000 rows and growing in an Excel sheet will soon hit the worksheet row limit of 65,536 rows. At that point she would have to figure out a way to continue.
The data needs to be secure.
Excel does not have data security in the same way an enterprise application does, and so can only protect data using some simple mechanisms.
The data needs to validated on entry.
Though it is possible to set up some data entry validation in excel, in practice, most people just pull up a sheet and start punching data into it. This can lead to errors in data-entry, making the data less accurate, and harder to run statistics on.
There is obviously another tool that would do it better.
There are examples of users creating letter templates, trying to maintain multi-user mission critical manufacturing schedules, trying to create graphics files with it, and more.
You're building a "database".
Many IT support staff find this to be one of the biggest misuses of Excel. Usually users spend a lot of time designing and entering data before they find their solution does not work. At this point the sheet is mission critical, so they ask their IT support staff to help them. It takes more time to analyze it, so time is wasted on both sides.
Ask for Help
If you find yourself in a position where you're considering using Excel for a project, consider some of the points above. If you fall into any of these categories, you may want to have a chat with your IT support staff or an IT specialist before continuing. You might find that a small amount of time invested with them up front will save you a large amount of grievance in the long run.
For example, they might be able to set up a simple SQL Server/MS Access database that everyone can use, has unlimited data entry, is more secure, and validates data on entry. As an added bonus, an MS Access front-end could spit out reports in Excel for analysis, a purpose Excel is more suited for.
Many thanks to people who contributed to a discussion on this topic at http://www.linkedin.com/answers/technology/information-technology/computers-software/TCH_ITS_CMP/625070-2600475