Signs You Need a Database Instead of an Excel Workbook

Like many engineers and environmental professionals, I love Excel workbooks. Excel shines when it is used for an intended purpose, such as self-contained calculations. Over the course of my career I have seen many cases where Excel workbooks are used because they are convenient rather than because they are the right tool. In some cases, a better tool for the job would be a database.

What are some signs that you need a database instead of an Excel workbook?

• The Excel workbook keeps slowing down or even freezing, or you must periodically archive old data to keep using the workbook.
  • For example, when I was a junior consultant, one of my tasks was to remove years of data from a workbook so that it would not freeze or crash when opened. It was a very detailed and error-prone process, and a database could have avoided this effort.
• You need to create new reports for internal or external customers, which is taking significant time or resources you wish you could spend on other tasks.
  • With a database, reports or queries can be written and reused or tweaked as needed. Instead of formatting data each year, time can be spent checking the accuracy of the reported data.
• You want to be able to store relationships between pieces of data in ways that are more sophisticated than Excel allows.
  • Databases often allow “one to many” relationships; this means one timestamped record can be associated with multiple activities. The common way to address this in Excel is to have a row with many columns, one column per possible activity associated with every date and work shift. This can be inefficient and prone to errors.
• You want a consistent data set – not inconsistent copies on different people’s computers. Excel does not have versioning controls.
  • A database can have controls that allow some users to only view data, and other users to edit.
  • Databases can also have change tracking so that you can see who made specific changes to the data.

If several of the signs above resonate with you, then what are your options?

1. Select and use a commercial tool that fits your needs with the user interface and database backend already built.
   1. Advantage: Lower development and maintenance needs, usually supported by the software vendor.
   2. Disadvantage: May have to fit a commercial product, usually designed for enterprise usage (cost prohibitive for smaller organizations)
2. Build a database in SQL or a similar database platform and build a user interface.
   1. Advantage: Can build exactly what is needed.
   2. Disadvantage: More effort to build and maintain a tool, may require in-house programing skills.
3. In the short term, keep using workbooks but build in additional safeguards.
   1. Advantage: Lower short-term effort and probably minor increases in usability.
   2. Disadvantage: Does not address underlying issue of using Excel outside of its intended purpose.

The ALL4 Digital Solutions team understands both the challenges of providing adequate environmental compliance services and digital tools, and balancing demands from internal and external stakeholders. If you would like to discuss options in more detail, or a specific instance of a workbook that might be a good candidate for a database, please contact our team or Julie Taccino at jtaccino@all4inc.com or 281-201-1247.