How to Create a Risk Matrix in Excel (With Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

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.


Watch Video – Create a Risk Matrix in Excel


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.
=$C6*D$5

Create a Risk Matrix in Excel


📌 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.

Create a Risk Matrix 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.

📌 Steps

  • 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.
=INDEX(C5:G9,MATCH(C12,B5:B9,0),MATCH(C11,C4:G4,0))

Create a Risk Matrix in Excel

🔎 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.

➣ MATCH(C12,B5:B9,0)

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.
Output: 4

➣ MATCH(C11,C4:G4,0)

The value returned by this MATCH function will be used as the column_num argument in the INDEX function.
Output: 3

➣ INDEX(C5:G9,4,3)

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.

Download Practice Workbook

You can download the practice workbook from the download button below.


Conclusion

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.


<< Go Back to | Matrix in Excel | Excel for Math | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Shamim Reza
Md. Shamim Reza

Md. Shamim Reza, a marine engineer with expertise in Excel and a fervent interest in VBA programming, sees programming as a time-saving tool for data manipulation, file handling, and internet interaction. His diverse skill set encompasses Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He holds a B.Sc in Naval Architecture & Marine Engineering from BUET and has transitioned into a content developer role, generating technical content focused on Excel and VBA. Beyond his professional pursuits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo