Spreadsheets are ideal for performing ad-hoc analyses and calculations with limited sets of data, but should never be used as a database.
In her article, “Excel is Not a Database”, author and administrative support expert, Jodith Allen, explains that many companies store critical data in Microsoft® Excel® spreadsheets, but simple human errors can skew formulas, equations, and figures resulting in inaccurate budgets, forecasts and other business processes.
Why businesses use spreadsheets instead of databases
Though it takes hours of coding and manual work to make spreadsheets behave like databases, many companies do just that. Here are the main reasons companies say they are sticking with spreadsheets:
We’ve always used Excel
Some companies argue that since they have always used spreadsheets to store data, there’s no reason to make the move to a database now. However, it’s easy to export cumulative data from databases to spreadsheets for one-off or special calculations, such as projections and planning.
We don’t have enough data to warrant the use of a database
Many companies believe their pool of data is relatively small, but Allen points out that as companies grow, so does the amount of data they must work with.
She explains that even if a company intends to use a spreadsheet to store a list of 50 employees and their contact information, details about those employees could grow exponentially in a short period of time, resulting a spreadsheet that contains thousands of rows and columns and takes several minutes to load.
Why your company should use a database, instead of spreadsheets, to store data
According to Allen, these are the six major reasons a database will serve your company better than spreadsheets when it comes to storing data:
1. More than one person can update a database at a time.
Only one person can use a spreadsheet at any given time. Allen notes that it’s common for someone to open a spreadsheet in “Read Only” mode, save changes to their hard drive, and then copy the spreadsheet back to the network. In this case, the employee has effectively erased the work of the person who worked on the spreadsheet before them.
2. Data can be audited in a database.
Usually, a single person is responsible for the creation and maintenance of a spreadsheet. If he or she is transferred to another department or leaves the company, all their knowledge goes along with them, and it may take months to train another employee to fill their role and get the spreadsheet back up and running.
However, databases that offer audit trails automatically record user activity, providing subsequent users with insight into how a spreadsheet was previously managed.
3. Databases support formal workflow.
When companies rely on spreadsheets to accomplish business processes, they must either email a single spreadsheet to multiple contributors, reviewers, and approvers or create and distribute multiple spreadsheets to dozens, if not hundreds, of users, and later consolidate these spreadsheets manually.
However, databases that support workflow enable managers to map, automate, track and manage processes specific to their business requirements onto a solution. As a result, each contributor effectively works with a centrally managed spreadsheet that includes instructions specific to their assigned tasks.
Workflow engines also notify both managers and contributors of upcoming or missed deadlines, eliminating the need for back and forth emails and phone calls or the reliance on the inefficient Outlook® and Excel pairing.
4. Databases can support modeling better than spreadsheets.
As spreadsheets and workbooks grow, they become very fragile and the smallest of user errors can have serious consequences. For example, copying existing equations to new locations can change the cell references, while accidentally inserting a number into a cell that already contains an equation converts the content of the cell into a constant.
5. It’s easy to create reports with databases.
Queries, and the reports based on those queries, are easier to write and run in a database. It’s easy to mix, match and re-sort data in a relational environment, as opposed to static spreadsheets.
6. Databases are secure and enforce control.
Since spreadsheets lack control and security features, their accuracy completely depends on the users’ skill level and ability to manually identify and correct mistakes. Mistakes usually go unnoticed, however, because spreadsheets are rarely checked or tested thoroughly before being rolled out.
Excel is not going away any time soon and the application is undoubtedly useful when it comes to data entry and review. Excel’s formatting, charting and graphing capabilities make it ideal for entering and accessing data and creating reports, but companies who use Excel also need a central database with a formal workflow, audit trail and business rules to secure their data and facilitate communication.
Excel users primarily use emails and phone calls to augment the application’s lack of communication tools. However, companies can stay in the familiar Excel environment without having to develop inefficient or costly workarounds to compensate for the important features that spreadsheets lack.
Companies would greatly benefit from a solution that combines Excel functionality with a secure, centrally managed application and database, allowing users to experience the power of an enterprise-scale solution without abandoning their extensive investments in spreadsheets or leaving the familiarity and flexibility of Excel behind.
Click here to read the full article.