This article illustrates how to create a risk matrix in excel. You can easily determine the risk that an event possesses to disrupt the production of your company using a risk matrix. The risk is determined based on the likelihood of the event and the severity of its impact. Therefore, a risk matrix is made up of the products of the likelihood of an event happening and the severity of its impact on another event.
How to Create a Risk Matrix in Excel: Step-by-Step Guide
Follow the steps below to be able to create a risk matrix for your company on your own.
📌 Step 1: Set Probability Criteria of an Event
- First, you need to list the possibilities or likelihoods, or certainties of an event happening. For example, it may be very unlikely, rare, possible, likely, or almost certain for an event to be happening. Now, list these possibilities on cells C4:G4. You can list them in a column instead if needed.
📌 Step 2: Set Severity Criteria of an Event
- Then, you need to create a list of the magnitudes that events can cause. For example, an event may have severe disastrous consequences on the business market. It may have a catastrophic impact on the economy. You can describe the severity of the impact of the event on the market as insignificant, minor, medium, major or catastrophic. Now, list these impact labels on cells B5:B9.
📌 Step 3: Assigning Values to Probability
- Next, assign the numbers 1 to 5 to the possibilities of the event, i.e. from Unlikely to Certain respectively on cells C5:G5.
📌 Step 4: Assigning Values to Severity
- Similarly, assign the numbers 1 to 5 to the severity labels of the event, i.e. from Insignificant to Catastrophic respectively on cells C5:C9.
📌 Step 5: Entering Formula
- After that, enter the following formula in cell D6. Notice that the formula contains mixed references.
📌 Step 6: Filling the Risk Matrix
- Now, drag the Fill Handle icon to the right and then below to fill the matrix elements. Alternatively, you can drag it down first and then to the right.
Finally, the risk matrix is ready as shown below. You can apply conditional formatting to color the matrix elements to make them easily understandable.
- How to Calculate Covariance Matrix in Excel
- Multiply 3 Matrices in Excel
- How to Create Traceability Matrix in Excel
- Make an Eisenhower Matrix Template in Excel
How to Retrieve Value from a Risk Matrix in Excel
Hopefully, you can create a risk matrix on your own now. But, how will you retrieve values from the matrix for a particular likelihood and impact level? Follow the steps below for that.
- First, select cell C11 to create a dropdown list for the likelihood of the event. Then, select Data >> Data Validation or press ALT+A+V+V to open the Data Validation window.
- Next, select List for the first Validation criteria using the dropdown list. Also, check the Ignore blank and In-cell dropdown After that, enter C4:G4 as the Source using the upward arrow. Then, select OK.
- Next, create another dropdown list using the impact labels in cell C12. The Source range this time is B5:B9.
- Now, select any likelihood or impact labels using the dropdown lists in cells C11 and C12. Then enter the following formula in cell C13. Finally, you will get the following result.
🔎 How Does the Formula Work?
➣ INDEX(array, row_num, [column_num]
The INDEX function returns a value or the reference of the cell at the intersection of a particular row and column, in a given range.
➣ Here the range C5:G9 is used for the ‘array’ argument.
The MATCH function returns the relative position of an item in an array that matches a specified value in a specified order. The value returned by this formula will be used as the row_num argument in the INDEX function.
The value returned by this MATCH function will be used as the column_num argument in the INDEX function.
Output: 12 [from cell E8]
Things to Remember
- The formula with mixed references must be entered correctly. Otherwise, copying the formula to the adjacent cells will return erroneous results.
- You can use conditional formatting to highlight matrix elements based on their risks. This will make the matrix more presentable and easily understandable.
You can download the practice workbook from the download button below.
Now you know how to create a risk matrix in excel. Did it serve your purpose? Do you have any further queries or suggestions? Please let us know in the comment section below. Stay with us and keep learning.