This post was originally published on April 9, 2018 and was updated May 16, 2019.
With its universal use and affordability, it's no wonder Microsoft Excel is the basis of many firms' construction materials testing and reporting systems. Often, companies search for cost savings by purchasing standalone database software while leaning on Excel to fill in the gaps. After all, Excel's intuitive design does make it easy to manipulate report layouts and create detailed graphs.
And with the release of Excel 2019, standalone application users now have access to powerful functions that were previously reserved for Office 365 subscribers only. These functions include TEXTJOIN and CONCAT, which are useful when creating owner required paper work where things like project number and project name are combined into a single cell, and the new IFS function, which is a powerful way to determine compliance with a number of conditions simultaneously.
Microsoft continues to add functions that make it's applicability more universal. But what if there is more to consider? What if taking a closer look reveals that there are glaring inefficiencies costing companies more in resources than ever anticipated?
Let's take a closer look...
5 Hazards of Using Microsoft Excel for Construction Materials QC:
#1. Compromised Data Integrity
Excel has limited ability to protect built-in mathematics or prevent certain users from entering data into the spreadsheet. Since Excel uses passwords to grant or restrict access, the permissions are binary - meaning you either have access or you do not. It's impossible to build custom user-level access restrictions.
Good Excel programmers will lock certain cells to prevent modifying formulas. This works until specifications change and the spreadsheet needs to be modified, then the file's author can make the changes, which means all changes go through one person (we will talk about the risk of a single point of failure later). Alternatively, the password can be shared to help maintain the sheet. Once the password is used to open some of a spreadsheet, then the person can access the entire spreadsheet. That closely held secret password is no longer a secret.
When Excel is used to fill in the gaps of a commercial database system, data is gathered and exported into Excel. Since it is impossible to export the rules and permissions defined in the database system to Excel, once data is exported from a standalone database system into Excel, the integrity of the data can be compromised.
When Excel is being used for a material submittal, the user queries their database and export data. That data is then pasted into Excel. When the data is incomplete, the user can easily fill in the blanks with fake data to complete the form with no record of the transaction as would occur in a construction materials QC system.
Fake data cost these companies serious money and individuals faced jail time.
Testwell president, and vice president face felony charges
North Carolina Highway Contractor Agrees to $2.25 Million Civil Settlement for False Testing
When all the data is managed and reported by a materials quality control software, there are not only records of user activity, but also of report creation and modification dates. Tracking this information protects organizations from rogue employees by documenting their actions.
(I have personally seen how a rogue employee can adversely impact the morale of an organization and create a false perception of the entire company.)
#2. Disconnected Data
Larger organizations find themselves combining multiple Excel files to create more complex reporting and summaries. Since Excel is not a relational database, storing historical information or combining information is a burdensome task.
EXAMPLE 1: Aggregate records and asphalt production records are needed to develop asphalt mix designs. Typically, these records are not stored together in a single spreadsheet. When performing asphalt tests for projects, the records are usually kept in two spreadsheets: one for production records and another for project records. Keeping all these records straight is commonly done with some sort of file naming scheme and folder structure. The amount of time spent copying and pasting between spreadsheets alone drains time and money that could be better spent on an automated alternative.
EXAMPLE 2: Specifications change, you need to determine your anticipated performance against the specification and you need to asses what changes to your processes need to be made. Like in the previous example these records are not stored together in a single spreadsheet. They are in multiple spreadsheets likely managed by different groups. Someone has to compile the appropriate data, combine like data into a spreadsheet(s) and then perform the analysis. The amount of time spent performing this type of risk analysis or the cost of not being as thorough as possible could pay for an automated alternative.
#3. Multi-User Save Limitations
Only one person can be in an Excel spreadsheet at once. There are two options for entering data. Both individuals create and work in their own spreadsheet, or one person works in Excel while the other records data onto a slip of paper. In either case, there will be a double entry for the second user.
It is impractical to have a single Excel spreadsheet where ALL QC data is stored. Whether a company is using Excel exclusively or just to fill gaps, there will inevitably be multiple spreadsheets to manage. Developing an extremely efficient process to manage files, track versions and maintain permissions is difficult, if not impossible. The result is a cluttered file system with various versions of the same spreadsheet that become incredibly cumbersome to maintain. Simply knowing which spreadsheet contains the most representative data or which customer received which material submittal is a challenge.
There are all sorts of file naming conventions used to identify or find files in a folder structure. Folders and file names contain codes to identify plant, aggregate source, binder source, aggregate size, aggregate components used, specification, and the list goes on. These codes combine to create a string of characters that become part of an organization’s tribal knowledge. When the business needs change, getting the tribe to change their file and naming convention to match the newest business needs is nearly impossible.
#4. Manual Report Distribution
Excel lacks any sort of defined report distribution function, so distribution of reports must be done in a stand-alone email program, such as Microsoft Outlook. As there are often multiple distribution lists for projects, DOT’s, local government agencies, internal stakeholders, etc., maintenance of these lists is problematic and something as simple as sharing the lists turns out to be much easier said than done.
Large organizations will not allow a local QC or project team to create distribution lists on an email exchange server, for good reason. An email list can be made, but updates will need to be made manually by EACH user of the list. The most common solution is to create an Excel or MS Word file that contains the distribution lists, which quickly go out of date as the project evolves. People struggle to make time to send emails with results on the required schedule, let alone, maintain an external file for others to review when they need to send emails.
#5. Single-Operator Dependent Process with a Single Point of Failure
The biggest problem with Excel systems is that they are developed by a single person thus creating a single point of failure. This person spends hours developing spreadsheets, folder structure, data entry protocol, and managing Outlook distributions. Organizing the process this way makes their job easier in the short term, but results in more work for them over time, as maintenance usually falls back on this individual.
This person will not be allowed to dedicate more and more time to maintenance of their system, as will inevitably be required. They will be forced to choose between delivering work or updating their process. You can guess what will take precedence. Nobody has ever been told, “I am glad you didn't send that report to the DOT, because I preferred you update our Outlook distribution first.” They have to do both. Is this expectation reasonable?
What happens when they leave the organization? If yours is one of the very few organizations that thoroughly documents these processes, then you can fall back on your documentation, if necessary. If your organization doesn't have the processes documented in detail, the next person who comes in to manage the process will start again, spending precious time building a brand-new system that may or may not change the process for other users.
We are working with a customer who has spent 80 hours to update an Excel spreadsheet used for collecting and reporting nuclear density that was written by previous employees. The spreadsheet is filled with visual basic commands and outdated functions that make the spreadsheet bloated and run slowly. They only have time available to fix this sheet - not solve the problem of efficient data collection and reporting.
Thankfully, there are tools available in the materials industry that integrate and automate these processes, and those tools are maintained by professional organizations. It is worthwhile to compare these tools to Excel so you can know for yourself whether they can help you take your business to the next level.
The right tools will eliminate the above inefficiencies, satisfy your unique business requirements, and be flexible enough to integrate your tribal knowledge, all while fitting comfortably in your budget
Ready to explore an automated alternative to Excel?
Learn more about the powerful and easy-to-use testing and quality control platform LASTRADA™.
