5 Tips for Building a Spreadsheet-Based Environmental Monitoring Log

Tracking and trending environmental monitoring with pen and paper is doable, but a pain. Spreadsheets provide many of the benefits of expensive softwares, but without the hefty price tag. Benefits include:

  • Cost effective, less wasteful, no physical storage necessary.

  • Files can be backed up and protected

  • Years of historical data conveniently located in a single electronic file.

  • Reveal trends by filtering columns and creating pivot tables

  • Leverage pivot tables to generate reports, which you can then print and share with your boss, customers, inspectors and auditors.

  • Streamline inspections and audits by inserting hyperlinks to lab reports

5 Tips for Building a Spreadsheet-Based Environmental Monitoring Log

Tip #1: Use Different Colored Columns

The columns with blue headers (left) contain data collected during swabbing; the red columns (right) contain sampling results transcribed from lab reports

Screen Shot 2018-11-29 at 12.13.08 PM.png

Click here to see my tips on how to select a food laboratory

Tip #2: Include a Column to Indicate Composite vs. Individual Swabs

By selecting “composite” swabs under the column “Swab Type” (see above), the person filling out the spreadsheet can indicate when a single swab is used to sample multiple swab sites. The “Group” shows the number assigned to the composite swab on the particular swab date. Say, for example, 3 composite swabs were collected on April 1, you would record the samples in the PEM Log under 3 groups (1, 2 and 3). By keeping track of the group number and which swab sites were “grouped” together on a single swab, you can accurately trace lab results back to the swab sites that were sampled.

Looking for a food lab? Click here and scroll down to see Food Safety Guides’ Map of ISO 17025 accredited laboratories.

Tip #3: Insert Dropdowns

Screen Shot 2018-11-29 at 12.13.16 PM.png

Trending spreadsheet data requires inputing data in a consistent manner. For example, if you are trending a column of data where “apples” are identified as “apples” sometimes and “Gala” or “Granny Smith” other times, reports may not capture or trend all the data. Mis-spelling can also throw off data trending. Eliminate these potential hiccups by inserting dropdowns (or “data validation”, as it is called in Excel and Google Sheets). You can also protect the cells from mis-spellings or invalid data by selecting “Reject input” (see figure, right).

Tip #4: Use Hyperlinks

Upload lab report PDFs to the cloud and insert hyperlinks to the reports in the spreadsheet. By doing so, the log becomes a one-stop jump off to all of your lab reports, making it quick and easy to locate lab reports when you need them.

Tip #5: Use Formulas to Automate Reporting

For reporting purposes, you will want to also have columns indicating the zone and surface name, but how can you or your employees remember the codes for dozens of surfaces? And even it you could remember the surfaces, it would be a pain to punch them in everytime you fill out the PEM log or select them from a dropdown, not to mention the risk of mis-spelling and other human errors. Avoid these pitfalls with formulas (or more specifically, a specific type of formula called an “array”). Stay tuned for my next post for instructions!



Create Your Own Custom PEM Log

Want to explore what a spreadsheet can do for your PEM program? Contact us at info@foodsafetyguides.com. We have templates including reports designed to conform with major retailers’ audit criteria (e.g. Trader Joe’s) and GFSI benchmark scheme requirements. Below is an example of what a PEM log might look like.