The Top 5 Ways Spreadsheets Will Harm Your Business
Data integrity Spreadsheets make it very difficult to track who has changed what and if - even by accident - something changed that "shouldn't". Using dedicated software will help you avoid this mistake. This can be catastrophic when bidding on a job - for example - accidentally forgetting to change a column and missing an entire expense row or column costing you 100's or 1,000's of dollars.
Versioning If you have more than one person accessing a spreadsheet, how do you know who is using what version? One of the top issues with spreadsheets in the enterprise is versioning - you might be thinking - I don't have that problem I use a cloud spreadsheet and you could be right, on the other hand, how certain are you that someone didn't save a copy and accidentally select that copy from the short cut list in their cloud drive? Thus resulting in varying results from what should be a standard calculation.
Duplicate data entry Everyone has heard the saying "The more you touch data, the more wrong it becomes." Moving data from one sheet to another by copy-n-paste, hand-typing is just a problem waiting to happen. What is the difference between 1,776 and 177? It is not 1,599 - it is missing one character when copying. What is the difference between 10,110 and 11,010? It's not 900, it's being tired or in a rush and swapping 0 and 1. Do you think that a $900 error could be costly? You bet! Not just the money but the reputation.
Complexity curve Spreadsheets work great...until the don't. In 1956 psychologist George Miller published a paper "The Magical Number Seven, Plus or Minus Two: Some Limits on Our Capacity for Processing Information." In this paper, Miller discusses a human's ability to hold a number of items in their conscious mind at one time. Although the full spectrum is much wider 5-9 items, the general capacity of complexity in humans is 7 +/- 2 items. How does this relate to spreadsheets? Most business owners will solve a data problem with the tools they are familiar with and have access to. The challenge arises with scaling this solution over time and over an increasing data set. What happens when the system of spreadsheets needs to be updated to account for a new column or a new formula one more time than the system will successfully allow? Then you get Versioning (#2) and you lose integrity (#1) and then end up having to re-enter the data (#3).
Number ######## Invalid Value!# This is what happens when things change that a sheet creator didn't notice were connected. This is where all the accidental and unanticipated errors arise.
Spreadsheets do serve a few very useful functions
Analysis Spreadsheets are uniquely valuable when it comes to analyzing two-dimensional (2D) data. They can make it is to run dimensional calculations (SUM, AVG) on data sets. In these instances, the spreadsheet should be consuming data from an authority - or plainly put - the data you trust and want to analyze should come from a proper software package.
Charting Modern spreadsheet solutions have simple, drag-select, and click-to-chart functions. This is great for putting together a visual representation of two-dimensional (2D) data. Rows and Columns can translate nicely to a pie chart or a bar graph without too much of a hassle. Great for putting in presentations or communicating to team members and customers.
One-off Calculation If there is something you want to calculate and you don't know how to do it in your business software or your business software doesn't do it then spreadsheets can be a great way to set up and perform calculations on a data set.
I think there are 3 primary roles in the data-set world:
Collection This is where the data gets collected from a form, manual entry, download from another source. Somehow, data is collected.
Authority Data must then be stored and there should be a clear mechanism for determining data-authority. What source, storage, repository is the absolute authority of data in a given dataset? The data-authority is responsible for receiving data from the collector (#1) and providing data to calculator (#3)
Calculator Data becomes extremely interesting once calculations are performed. The calculations should make sense of the data and give it meaning. Calculations can be much more than math related number crunching; a calculation could be scaling an image, linking a contact record, or even analyzing the data in a spreadsheet.