Summary: While harmless if used correctly, spreadsheets can create all types of business problems when misused. The bad news: Spreadsheets are often misused. Learn how businesses unknowingly misuse spreadsheets, and the risks they face as a result.

What if I told you that dangerous software has crept into your company? It’s infiltrated your employee’s computers. It drains productivity, leads to data errors, and could even put your entire business at risk. It’s almost impossible to control.

What is this dangerous software? Spreadsheets.

When misused, spreadsheets open businesses up to a host of problems. The bad news: Spreadsheets are regularly misused.

You see, spreadsheets were designed as single-user tools. They let business users easily alter and analyze small data sets. When used in this way, spreadsheets are harmless.

The problem is, this isn’t how spreadsheets are used. Companies turn them into multi-user and multi-department tools. Some even run their businesses off of spreadsheets. They use spreadsheets in ways they were never meant to be used.

What happens? These companies face problems. Big problems. So many problems, in fact, that we had to split this topic up into two articles. You can find the first 7 dangers of spreadsheet overuse in this article, and 5 more dangers listed below:

1. Spreadsheets don’t allow for data concurrency

When storing data in a database, you create separate tables for each type of data. For instance, you create a product database for your product information, and customer database for customer info.

Why is this so important? You don’t want the same piece of data stored in two separate tables. Otherwise, you run into all sorts of problems. For instance, what happens if you change one table, but not the other? How do you know which one is accurate?

Spreadsheets make these types of problems nearly unavoidable. There’s no way to know how many spreadsheets contain the same data, or where those spreadsheets live. As explained below, this creates inaccuracies, and even costly errors.

“In any business scenario there will probably be a case in which you need to access the same bit of information in two different places,” says Graham Cleven, Spreadsheet Expert. “Lets use a customer’s telephone number for example. This telephone number could exist in the normal customer spreadsheet and it could also exist in the customer telephone order database. Now, let’s say the customer changes their telephone number and it is updated in the customer database but the callback number in the customer telephone order database is not updated. Now let’s say the company needs to notify the customer of something. The most logical thing for the phone order handling department is to call back using the callback number on their spreadsheet which is now outdated so the information will never reach the customer.”

2. Spreadsheet data exists in a vacuum

Because spreadsheets often live in different locations, there’s no way to track related data. For instance, suppose different departments rely on separate spreadsheets. Each spreadsheet might contain a different aspect of the same type of data. When combined, those different views may create some valuable insight.

The problem: As explained below, spreadsheets make this type of analysis is nearly impossible.

“Often, data on spreadsheets exists in a vacuum,” says Shelly Collins, Professional Organizer at Clutter Contained. “Many of my clients have a variety of spreadsheets to track various individual data points about their business. The danger here is that there is no easy way to pull various types of data together to create a more comprehensive analysis. For example, a spreadsheet that tracks income by client useful in determining which clients generated the most revenue – but other important pieces of data such as the source of the client and the types of products or services that were purchased might not be accounted for, or might be kept in another spreadsheet. This makes this type of tracking useless, as you know who your current revenue generating clients are but you have no relevant data as to how to market to and obtain similar clients.”

3. Spreadsheets make collaboration difficult

“I did a study last year on marketing analytics, looking at several aspects of the process including the tools in use,” says Jerry Rackley Chief Analyst at Demand Metric Research Corporation. “For the marketing analytics process to work well, there must be collaboration around the process and sharing of results. The more of both that occur, the more likely it is that the marketing analytics process will deliver results. What I learned in the study is that of all the tools an organization could use to facilitate marketing analytics, there was one tool choice whose use actually lowered collaboration and sharing of data: spreadsheets. And yet, spreadsheets are the most widely used tool for marketing analytics. I counsel my clients to avoid using spreadsheets in favor of tools that help, not inhibit the process.”

I’d guess that you could apply those findings across all aspects of business. Proper collaboration can improve business results in any department.

The problem: Spreadsheets aren’t built for collaboration. They’re not easily distributed. Multiple users can’t edit the same spreadsheet at the same time. They’re single-user tools…yet too many businesses hurt their collaboration trying to turn spreadsheets into something they’re not.

4. Workflows are hard to define and enforce

Rather than complete each step manually, companies use workflow processes to automate business functions. For instance, purchase order forms often require separate approvals from managers across multiple departments. With a workflow application, that purchase order form will move from manager to manager for approval automatically.

The problem is, spreadsheets don’t offer workflow capabilities. When businesses rely on spreadsheets, they must complete every business process manually. They lose the productivity benefits of workflow automation. And as explained below, spreadsheets can stall a business process altogether.

“Workflows are hard to define and enforce,” says John Peebles, CEO of Administrate. “If something needs to happen after a bit of data is filled in, there’s no way to see if that’s happened or remind the person they still have some work to do!”

5. ‘Spreadsheet Accounting’ can put your business at risk

Throughout this article, and the previous article, we’ve explored ways that spreadsheets can harm your business. But, can they actually put your company in danger of going under? Yes. As explained below, when used for accounting purposes, spreadsheets can endanger companies.

“Have you ever been audited by the IRS?” asks Allan Madan, CA, CPA at Madan Chartered Accountant. “It’s a scary experience, which most small businesses are ill-equipped to handle, because of their reliance on ‘spreadsheet accounting’. Upon a tax audit, the IRS will demand to see financial reports and statements of a business, that can only be generated by an Accounting Software. So when a business owner presents a tax auditor with a spreadsheet of expenses and revenues, an ugly outcome is almost certain. I have seen businesses go ‘belly-up’ because tax auditors have disallowed thousands of dollars of expense deductions, resulting in a huge tax bill. This could have been prevented if a proper accounting system was used, and not simply a ‘collection of spreadsheets.’”

Solution

In the past two articles, we’ve explored 12 dangers of spreadsheet overuse. All of these problems can arise as a result of using spreadsheets as multi-user or multi-department tools.

The big question: What’s the solution? How can businesses break their spreadsheet dependence and insulate themselves from these problems? As explained below, the solution is simple: Move your data to a database.

“An alternate, much better solution to using many spreadsheet files is to use one database,” says Graham Cleven, Spreadsheet Expert. “A database uses multiple tables and data mappings to link data. Since all your information is now at one accessible location on a database server there are no issues with losing files and no issues with files being out of date with each other. Now also, when information needs to be shown in different ways the database can be used to generate dynamic reports that show information which will constantly update from the database itself. Also, because databases can have easy-to-use front ends interfaced with data validation unlike spreadsheets there is a much lower chance of either entering wrong data, entering data that is incorrectly formatted or accidentally changing / deleting data that doesn’t need to be changed. Also, because the database can export itself to one file backing it up is extremely easy and many database programs / servers have automatic backup systems built in. Spreadsheets can also be generated from databases for business meetings or especially detailed reports that need a large amount of aggregated data shown in an organized manner.”

So, what do you think? Is there anything you would add to this list? If so, please share your thoughts in the comments.