Analyzing risks is often best done visually when there is a great deal of data to consider. Excel allows the user to create a risk matrix that is a visual assessment of risk based on the data provided. Here is an example of creating a risk matrix in Excel.

## Risk Matrix Example in Excel

There he was, **JEFF**, a construction worker with LIMO LLC. A scaffold that wasn’t properly fitted had caused **JEFF** to become bedridden. The management ordered an immediate **RISK ASSESSMENT** which involves the use of a **RISK MATRIX** like the one in the example below. In this Matrix, the Y-axis tells us the **SEVERITY** of the risk involved and the X-axis tells us the **LIKELIHOOD **of occurrence of the **RISK.** Hence, the **RISK** of a hazard is calculated as: A Risk Matrix Example

**Risk** = **likelihood of occurrence (probability)** * **Severity of harm**

**Formula for a risk matrix**

A **RISK MATRIX** can be assessed by a formula based on the **INDEX** and **MATCH** functions. In the **Risk Matrix Example** below, the **GENERAL FORMULA** in **Cell D13** is given as:

`=INDEX(C5:G9,MATCH(Severity,B5:B9,0),MATCH(Likelihood,B5:B9,0))`

The Syntax for this formula is given as: =INDEX(array,row_mumber,(column_number))

**Explanation of formula**

To get the result based on the **RISK MATRIX** for **RARE * MODERATE **as seen in the **RISK MATRIX EXAMPLE** below, the formula is:

**=INDEX(C5:G9,MATCH(D12,B5:B9,0),MATCH(D11,C4:G4,0))**

To get the **Column value** for **SEVERITY**, the string below is used for the **RISK MATRIX EXAMPLE**

**=MATCH(D12,B5:B9,0)**

To get the **Row value** for **LIKELIHOOD**, the string below is used for the **RISK MATRIX EXAMPLE**

**=MATCH(D11,C4:G4,0)**

The **INDEX function** returns the number or value that is present in **ROW 3** and **COLUMN 2** based on the example below and this string: **=INDEX(C5:G9,E11,E12)**

The method used in the **EXAMPLE** is preferred to the **ALTERNATIVE METHOD** because you can vary the **SEVERITY** and **LIKELIHOOD** in **Cells D11** and** D12** respectively to **AUTOMATICALLY** get your result.

**Example **

**Step 1**

**Generate the RISK MATRIX table in the form below**

*Figure 1- Risk Matrix Example*

**Step 2**

- Click on
**Cell E11** - Type in this string:
**=MATCH(D11,C4:G4,0)** - Click on
**Enter** - Likewise, click on
**Cell E12** - Type in this string:
**=MATCH(D12,B5:B9,0)** - Click on
**Enter** - Your result must be similar to figure 2

*Figure 2- Risk Matrix Example*

**Step 3**

- Click on
**Cell D13** - Type in this string:
**=INDEX(C5:G9,E11,E12)** - Click on
**Enter** - Your result must be similar to figure 3

*Figure 3- Risk Matrix Example*

**Alternative method**

- Click on
**Cell D13** - Type the string below into the Cell

**=INDEX(C5:G9,MATCH(D12,B5:B9,0),MATCH(D11,C4:G4,0))**

**Click****Enter**- Your result must be similar to figure 4

*Figure 4- Risk Matrix Example*

Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free.

## Leave a Comment