Spreadsheets in construction - The good, the bad and the ugly

In every construction project and organisation, you'll find tens of spreadsheets, for all kinds of things - heck some organisations even have a "spreadsheet of spreadsheets". They are cheap, easy to use, available for everyone and extremely flexible - so what is not to like? In fact, regardless of the function you work in, at least one of your processes will likely be managed using a spreadsheet.

But before making any conclusions about the state of spreadsheets today, we wanted to reflect on how people use spreadsheets.

• Information collection: Teams have to collect all kinds of information in construction projects. That ranges from timesheets and daily site reports to vendor questionnaires.
• Information storage and documentation: Projects rely on spreadsheets to store key information ranging from key contacts to responsibilities to projects and agreed prices.
• Analysis and reconciliation: Teams use spreadsheets to quickly compare data sets or perform analytical tasks using formulas. Some even use excel to carry out what-if scenarios to understand the impact that certain decisions can have on the project (eg. How would a delay in payment affect the project's cash flow?)
• Reporting: Reports and visuals are key in the business world to allow decision-makers to gain insight into data by presenting it in visual ways.

We appreciate the above may sound mundane but in our experience people focus on reporting - and while that is a key reason people chose excel it is important to remember it is not the only one.

Now that we've covered why people use spreadsheets, let's go back to the title of this post.

The Good

• Easy to use: Every professional has used spreadsheets in one capacity or the other. Apart from the excel whiz's who can do their magic with formulas, almost everyone finds recording information fairly intuitive. This means that businesses do not have to worry about training their users.
• Flexible: It is difficult to find a tool that is more flexible than excel. Not only can it be used across every function but it can also be used by organisations that have as few as one person and as many as thousands of people.
• Free and available: Every organisation has an account with Microsoft (reality is even we do!) to the point that Microsoft office is considered ubiquitous and free.
• VBA and Macros: For the excel gurus amongst you, you'll know that you can basically build applications that are extremely powerful using Macros.

The Bad

• Not built for collaboration: Many spreadsheets are built to be used by a large number of parties. We've seen workbooks that require input from 10s of individuals on projects (eg. Design review workbooks, Information Delivery Plans). Getting everyone's input into a workbook stored on a machine is impossible, so emails back and forth become a thing.
• They break: You delete or change the values in one cell. Suddenly the whole workbook stops working. Or the workbook becomes simply too big and it keeps crashing. Valuable work gets lost and you have to start over again.
• Hard to maintain: Legacy excel workbooks get handed over from one to the other. Figuring out what the previous owner of the excel workbook had designed is a nightmare. Debugging it if something goes wrong is near impossible.
• Difficult to find the information you need: Have you seen the spreadsheet of spreadsheet meme? Figuring out which spreadsheet or workbook has the information you need is really difficult.

The Ugly

• They do not connect to other systems: Spreadsheets were built in an age where the number of systems organisations used were minimal. However, as the number of data sources in projects increase, there is an increasing number of information sources across projects. While it is easy to create a spreadsheet, connecting it to the other information sources in a project is extremely difficult.
• Many versions and files: Companies often maintain various versions of any spreadsheet. Why? Reasons can range from needing to have different ones for different periods, organisations, or even because there are major changes to how it's run. Not only does this mean that individuals have to spend time bringing together the information but it also means that there is no clear audit trail of the information being produced.
• Data Quality is poor: 86% of spreadsheets have errors in them. This means you cannot rely on the information in the sheets without manual data cleansing.
• Critical data is vulnerable: Information can be shared with external parties without your knowledge. It is difficult to track who has access to any piece of information. If a file has been sent by email, you've fundamentally lost track and control of who has access to that information.

There are many well-documented cases where the use of spreadsheets have failed us spectacularly. One of the most recent resulted in nearly 16,000 coronavirus cases going unreported in England. In a project setting, the NHS Lothian's opening was delayed and cost-over ran by approximately 30% due to an error in the environmental matrix.

What is the alternative then?

Databases! These are programs that can be used to store, organise and retrieve data. Most of the applications you know of and use run on top of databases. Like spreadsheets, databases are organised as columns and rows. Unlike spreadsheets, databases tend to have more control of the information in them, and data can be easily linked and cross-referenced. While we do not propose that every spreadsheet should be a database, many of them should be. In our experience, databases are can be better for the following reasons.

• Data integrity and quality: column (or field) types can be defined and controlled. For example, in a timesheet, you'd have a column titled 'hours worked' that would have to be an integer. This would make it impossible for someone to mistakenly add text for instance. As such, databases are really good at preventing errors from happening in the first place.
• Data redundancy and re-use: it is easy to use re-use data available within your database. For example, many organisations maintain breakdown structures and classification codes that they have to use across systems. Referencing these data sets becomes really easy in a database.
• Data accessibility: filtering and sorting are mainstays of spreadsheets that we all use. However, databases provide the flexibility to query and access data with even the most complex logic, ranging from subsets of a table to combinations of multiple tables together. This also means that databases are great at acting as a central source of truth for the project.
• Data security: we've all had to lock columns or create many different spreadsheets to keep data secure. Databases are designed for multiple users and collaboration, ensuring that multiple people can access the same data easily.
• Data connectivity: (relational) databases especially are great at bringing datasets together, enabling you to have a holistic view of the project.

In our next post, we will be sharing examples of databases that can be used in projects. But until then, we will leave you with a thought: what if we could take the ease of use of spreadsheets and combine it with the power of databases? What possibilities would that unlock for the industry?