There was a time when spreadsheets were the workhorses for many business processes. Before special purpose apps and cloud-based project management software, spreadsheets were easier and more accurate than paper ledgers and calculators, so people took right to them. Problem is, while better than their predecessors, they quickly outgrew their strengths and started showing their liabilities.
If you are still using spreadsheets for more than what they were intended, you’re probably using these following excuses:
“I never said I knew how to write code.”
It’s a wondrous experience to create a spreadsheet formula and see it in action. After you do a few, you feel you might have missed your calling to be a programmer. Then, you see other ways you can use spreadsheets, and before long, you’ve got one for each different project need.
When you study the sources citing spreadsheet dangers, it turns out the biggest dangers usually come from the people who create and use spreadsheets. Few people get training in developing and using spreadsheets. In fact, most people learn through trial and error, by watching tutorials, or by having a coworker show them.
Therein lies the problem. If they didn’t learn the correct lessons from their errors, skipped important parts of the tutorials, or the coworker wasn’t the spreadsheet genius everybody took them for, then the sky’s the limit on how many mistakes might creep into their work.
In fact, every one of the top four vulnerability factors for spreadsheets are human related:
— Incorrect formulas,
— Wrong numbers input,
— Mistakes in logic,
— Admin errors (no password protection, no setting of a single source of truth).
When you consider that people also accidentally change formulas and misuse built-in functions, you might conclude that nobody should use spreadsheets without formal training and get tested to ensure they know what they’re doing.
“Huh? I don’t know why that estimate is so low.”
Spreadsheets reign in estimating. Many early estimating solutions were completely spreadsheet based; many still survive today. People have estimated thousands of projects using spreadsheets and have come out okay.
The trouble starts as estimates get complex. Spreadsheet work savers like dynamic ranges and macros behave unpredictably as the estimate grows, and they get copied and added to new sheets.
Formulas have absolute and relative cell references that change the calculations. A professionally prepared software solution will have confronted and solved all these variables. However, when the solution ends up in the hands of the untrained, the spreadsheet estimate becomes less and less reliable. They also don’t make it easy to grab data from other sources.
If you want to pull in historical data for your estimate, you’ve got to do some tricky acrobatics with formulas and linking to pull it off. Even then, a single change may yield either no data or the wrong data.
“I thought everybody was looking at the same numbers.”
Spreadsheet collaboration often amounts to confusion—at any moment, you can have multiple copies of a spreadsheet in use. With no single source of truth, people are left to work with outdated data.
You can’t quickly see who introduced what changes. If you don’t buy and use a third-party spreadsheet auditing solution, you face a manual, multi-step process to catch errors, flaws, and inappropriate changes. By the time you trace dependents, reveal and evaluate formulas, and validate the data, there are new inputs and changes, and so you have to start over. Things become more obtuse when you want to know when a formula was changed or who set the links to external documents.
Since spreadsheets in construction often span multiple sheets and include a lot of data and formulas, people save them often. They also back them up often. With all the saves and backups, bad formulas, incorrect links, and error-prone macros are also getting saved until it is impossible to find a reliable and correct copy.
“I couldn’t figure out why the spreadsheet author used a formula like that.”
Spreadsheets tend to be highly personalized. Each person finds the spreadsheet practices that work for them. When others review or interact with those sheets, there are multiple logic decisions they’ll never see. Worse still, there are no breadcrumbs pointing to the reasons the creator did what they did. If your estimator retires or moves to another line of work, you’re left with their personally designed system that probably won’t be user friendly to you.
“There was no way I could have known about that security issue.”
You build spreadsheets manually. Every number, cell description, formula, and formatting originates from users inputting them. That’s a lot of opportunity for transposed numbers, incorrect numbers, and missing numbers. You need to have a robust system for tracking and validating spreadsheets. And that system has to be usable by multiple people.
While you can set up sheets to require a password, that’s not widely practiced. In construction, the practice is practically unusable as multiple people rely on the spreadsheet. You might create the password, but then everybody has to remember it. When someone forgets, somebody else sends it to them in an email. And so your secure spreadsheet just went out the window.
Once you protect cells, you reduce the spreadsheet’s usefulness to others. On the other hand, formulas in unprotected cells are vulnerable to intentional or unintentional changes by third parties. Spreadsheet security is nothing to take lightly, especially when the data is sensitive for your business, your client, or your partners.
Today, there are various software and app solutions custom fit for construction. They can do just about everything you might do with a spreadsheet and do it better with less risk.
When these solutions integrate with the Procore platform, you get an enterprise-grade technology stack that breaks down information silos and frees data for use where it’s needed, when it’s needed. Use spreadsheets for the one-off tasks they excel at and give the collaborative work to Procore and its Marketplace Partners.
Leave a Reply