There are lots of circumstances that don’t fall under abuse but do account for inappropriate spreadsheet usage.

Part of the problem has to do with the universal appeal of spreadsheets: after all, they have been around for decades and the user interface of the grid of empty cells is so seductive. You just have to type in a number or a formula and you are good to go, right?

Well, not exactly. There are lots of warning signs that it is time to retire your Excel spreadsheet and move to something more sophisticated and capable to build your mathematical models. As a public service, let’s look at a few questions to get to the bottom of things.

Is your data poorly structured? Just like poorly structured programs, a spreadsheet that has input cells all over the place (instead of at the top or organized into a single section) can quickly become a nightmare to debug and understand. Say one of those input cells is accidentally deleted. Or what if you have a spreadsheet that doesn’t clearly label its input fields at all (with a contrasting color or some other graphic device)? When you get to this level of complexity, it is time to consider using something else.

Do you have a very sparse matrix? When you have a spreadsheet that has hundreds of rows and columns with just a few numbers or formulae sprinkled throughout, it becomes harder to find those numbers across all that empty space. Even on the largest of monitors, there is a whole lot of scrolling around to see the active data cells, and easy to miss something that is hiding in an open field. Databases are better for this sort of situation.

Are you mixing spreadsheets with other data sources? You have heard the expression oil and water don’t mix, right? The more complex your data model, the more difficult it is to see how it works and what is the ultimate goal. This is especially true when it comes to using SQL and other data sources as part of your spreadsheet. Ideally, your spreadsheet shouldn’t depend on any external data file to do its work, but that isn’t always possible. If you are building a more complex model like this, think about using a different product or entering the world of serious programming. You probably should start by using an online tool that can make the data connections easier. One way to go would be to look carefully at what Microsoft is doing in its support for online OData sources, and while certainly you can add this to an existing spreadsheet, you probably don’t want to.

Can you find your mistakes quickly? Most spreadsheets suffer from this problem, because they have been built over time with lots of additions and changes to their models. This means that obvious arithmetic mistakes are often hidden or obscured. Or worse yet, you don’t have a feel for your numbers. You may not be able to test your assumptions and making some common sense inputs to see if your spreadsheet produces results that are within your expectations. If your spreadsheet has grown like topsy then maybe it is time to look at some other way to model your calculations.

Do you have more than one author of your spreadsheet? This can be a real issue. Having two or more authors writing formulae can be a problem: how do you track changes and who has the most current version of your file? Chances are, you have experienced this when two people are working on different portions of the file concurrently, and then it is up to someone else to be the final judge of which changes get put into production where. When your spreadsheet is in the cloud, this becomes a non-problem.