SMS: Master Hazard Registry

Tips for using the Master Hazard Registry spreadsheet

Tab: Index of Sheets

Instead of having a sheet tab for each category, they are all now on one tab “Hazards Data”. The extra sheet tabs are now for additional hazard info eg: storing images,charts. 

The first tab is an index of categories and, if applicable, their active sheets. If there is data on the sheet, then the index entry is hyperlinked to that sheet tab.

Tab: Risk Matrix

The Risk Matrix sheet tab is linked to the formulas on the adjoining sheet tab “Hazards Data”column L-N & S-U. 


Any changes to cells K5 to O9 affect the data index in the Vlookup Table starting in row 19.


The colours are controlled by conditional formatting which follow rules to look for the number ranges 1-3, 4-9, 12-15, 16-25 and fill with coordinating colour.

Tab: Hazards Data

The Hazards Data sheet tab is where all the hazards are stored. This Excel Table is designed to be filtered by any column. 

Data in columns L, M and S, T are controlled by drop-down data selection. The data in these trigger automatic vlookup formulas (built in the previous sheet tab) in columns N & U. The colours in N & U are again conditional formatting rules based on the calculated numbers. 

Filters can be applied in one or more columns, by selecting the checkbox next to the data desired. Filters can also be applied searching for exact words in the column or by colours.

All filters across the table can be cleared by selecting on the toolbar “Data”, suboption “Clear”.

Sorts also can be applied to the columns, whether filtered or not; sort A to Z, Z to A, sort smallest to largest, sort largest to smallest…