Top Mistakes When Using Excel to Manage Personal Protective Equipment

When our customers join Risk Memo, we offer to upload their PPE data to their accounts. From our experience, over 50% of adventure activity builders and operators use Excel to manage their Personal Protective Equipment (PPE) inventory and inspections. In this article, we’ll share the most common mistakes we see so you can avoid them to ensure your PPE management is efficient and error-free.

Duplicate or Incorrect Serial Numbers

By far and away, the most common issue we encounter is duplicate or incorrect serial numbers. This is very easily done due to the lack of data validation in Excel, at least without writing advanced Excel algorithms, leading to multiple entries with the same serial number or typos creeping in. This can cause significant confusion, making it difficult to track the history and condition of each piece of equipment.

Another cause of duplicate serial numbers is cases where no product serial numbers can be used. The most common solution is assigning a number, such as H1 (Helmet 1), H2, etc. However, when H1 is retired or moved to another location, the temptation is to assign it to another item, creating a duplicate.

Ultimately, without a unique identifier, there is no way to confidently say that digital record X corresponds to physical item X. Should the item be involved in an accident, discrepancies could leave you liable. It could be argued that the inspection system is unreliable and, therefore, the item's history is unknown.

Excel solution:

Typos are difficult to eliminate in any system, but you can ensure that they only have to be entered once.

To avoid duplicates, implement a system so that each piece of PPE has a unique identifier. In most cases, you can use the item's serial number, but failing that, create a code using the item name, location, date logged, and sequential number, for example, Helmet (H), Risk Memo (RM), June 2024 (0624), item 1 (001): HRM0624001.

Next, check for duplicates. Checking for existing duplicates when adding a new item is simple; click command (Mac) or control + F to open the find in document window and search for the identifier you want to enter. If the identifier doesn't exist in the document, you can enter it.

Checking for duplicates in an existing data set is a little trickier. Select the identifier column, then Format > Conditional Formatting. Select the Classic style and "Use a formula to determine which cells to format". In this example case, the identifiers are in column A, and the formula is as follows:

=COUNTIF($A$1:$A$1000, A1) > 1

Copy and paste the formula into the Formatting Rule window.

If there are any duplicates, the cell will be highlighted in red.

Risk Memo solution:

We've intentionally been very strict about equipment identifiers. Our database does not allow duplicates or deletions, making entering the same ID twice impossible. We also have a built-in QR code scanner, making it simple to search for identifiers if a QR code is available.

Data Structure

The structure of your Excel data plays a crucial role in how effectively you can manage and analyse your PPE inventory. Poor data structure, such as mixing different data types in a single column or using merged cells, can lead to analysis errors and complicate data management tasks.

Excel solution:

Adopt a clear and consistent data structure. Each column should represent a specific attribute of the PPE (e.g., Serial Number, Purchase Date, Inspection Date, Condition). Avoid using merged cells and ensure that each row corresponds to a single piece of equipment. Excel tables can help manage data more effectively by providing built-in sorting, filtering, and validation options.

Here's an example table with PPE data structured incorrectly

There's a lot to cover here, so we're going to break it down individually.

Naming conventions

Consistent item names are crucial for consistency and to make it easier to search and analyse your PPE data. A simple way to do this is to use the manufacturer's technical name, for example, “Panga,” Petzl’s Adventure Park helmet.

Make sure you stick to “Panga” and not “Petzl Panga,” as we’ll move on to other data points next. Using just the technical name will allow Excel to autocomplete, and it is a simple rule for your team to follow.

Naming conventions can be applied to the item's name and all other data points, making them all easier to search and analyse.

Separate data types

Adding multiple data points to one cell is a very common mistake. Not only does it make it impossible to follow a naming convention, but it also does not specify what data needs to be collected. 

The search and filter Excel tool is the most useful for quickly analysing your PPE data. For example, if you want to know how many Petzl items you have in your inventory, select the name column, then select Data > Filter and search “Petzl.”

The screenshot above shows the issue: three Petzl items are missing from the search, the third Panga and two Joko 30s.

This highlights the issue you will encounter when searching your data, but adding multiple data points to a single row will make every other data management process difficult as it’s impossible to re-order, rename, count, etc.

Below is an example of the same data structured correctly.

We can instantly see how this is easier to manage and analyse. Additionally, when the user enters data, it explicitly states what data is required. Note that we’ve changed the size naming convention to “S” instead of “Small” and included a column for the serial number as well as an identifier.

Dates

In the first example, you may have noticed that some of the dates are on the left of the cell and others on the right. This is because the dates on the left of the cell are not dates, at least not according to Excel. To Excel, they are strings of text. 

You may wonder why this is a problem as long as the reader can understand the date. Excel cannot perform date functions on text if the cell is formatted as text. For example, let’s say we want to make it clear that “June 26” means the month of June 2026 rather than the 26th of June. If the cell is formatted as a date, we can format the date via Format > Cells:

Selecting this will format all dates the same way, including the four-digit year. Consistent date formatting allows us to perform functions as well. For example, we can now filter the table by retirement date before any particular date with just a few clicks.

Risk Memo solution:

Our solution is simple: We manage the data structure for you. We also save all the names and tags you enter to populate a dropdown menu for you to select from rather than typing out names and tags multiple times, reducing the chances of mistakes.

All you have to consider is your naming conventions, and we will do the rest.

Conclusion

While Excel is a powerful tool for many tasks and in the right hands, it has its pitfalls when managing PPE for adventure activities.

Addressing these common mistakes—duplicate or incorrect serial numbers, spelling mistakes and inconsistencies, and poor data structure—can improve the accuracy and efficiency of your PPE management.

At Risk Memo, we provide specialised tools and support to help you manage your PPE data effortlessly. If you're ready to streamline your PPE management process and eliminate these common mistakes, consider switching to a dedicated PPE management system.

---

For more information and to see how Risk Memo can help you manage your PPE data effectively, click the chat widget to book a demo today!