Creating a risk register in MS List

Question

Creating a risk register in MS List

Stephen Laird 20

Hi everyone, I am building a risk register within MS List and have two choice columns for Risk Severity and Risk Likelihood, I also intend to use a calculated column, Risk Level, which would display text dependent on the other two columns. How do I assign a number to specific text in the first two columns, e.g. Risk Severity = Moderate; Risk Likelihood = Occasional, in accordance with the matrix attached the Risk rating which should appear in the calculated column is High. I have created the risk register in Excel using tables, lookups, etc. which works. My knowledge of MS List is not so good. Does anyone know how to achieve this, or am I better just using numbers in the columns. TIA.

User's image

0 comments

Answer accepted by question author

0 additional answers

Your answer

Answer 1

Hello @Stephen Laird

I tried to set this up based on your guidance, hope this aligns with what you were looking for.

To assign a numeric value to each text choice and calculate the final “Risk Level,” I used nested IF formulas. To keep things clean and easier to maintain, I created intermediate “Score” columns that convert the text selections into numbers first, and then used those scores to determine the final result.

First, create two choice columns with the following options:

Risk Severity (e.g., Catastrophic, Critical, Moderate, Marginal, Negligible)
Risk Likelihood (e.g., Frequent, Likely, Occasional, Seldom, Unlikely)

User's image

Next, go to List settings > Create column and create a calculated column named Severity Score, using this formula: =IF([Risk Severity]="Catastrophic",5,IF([Risk Severity]="Critical",4,IF([Risk Severity]="Moderate",3,IF([Risk Severity]="Marginal",2,IF([Risk Severity]="Negligible",1,0))))) Make sure the returned data type is set to Number.

User's image

Repeat the same process to create a Likelihood Score column using: =IF([Risk Likelihood]="Frequent",5,IF([Risk Likelihood]="Likely",4,IF([Risk Likelihood]="Occasional",3,IF([Risk Likelihood]="Seldom",2,IF([Risk Likelihood]="Unlikely",1,0))))) Again, set the returned data type to Number.
Finally, create the Risk Level calculated column. You can multiply the two score columns and map the result to a text value (High/Medium/Low). For example (assuming a score of 15+ is High, 8-14 is Medium, and <8 is Low): =IF(([Severity Score]*[Likelihood Score])>=15,"High",IF(([Severity Score]*[Likelihood Score])>=8,"Medium","Low")) Set this column to return Single line of text.

User's image

To keep the list view aligned with your original Excel layout, you can hide the Severity Score and Likelihood Score columns from the default view (Add column > Show/hide columns).

With this setup, when you click New and select values like “Moderate” and “Occasional,” the Risk Level will automatically calculate and display the appropriate result.

User's image

Hope this helps.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Stephen Laird 20 Reputation points

2026-06-30T14:50:52.4333333+00:00

Thank you @Anonymous your solution works a dream.
Anonymous

2026-06-30T14:57:24.2133333+00:00

I'm really happy to hear that the information I shared was useful to you @Stephen Laird If you ever need more help, feel free to post your questions in the forum. It's not just me, the whole community is here and always happy to support you.