RISK & COMPLIANCE

How to build a risk register in SharePoint Lists that people actually use

Most risk registers fail for the same reason: one person owns the spreadsheet, and everybody else is a spectator. Here is the column design, rating logic and review cycle I use to rebuild them in tools you already licence.

Why the spreadsheet register stops working

A risk register in Excel starts fine. One person builds it, the board sees it once, and it looks like control. Eighteen months later the same file has four versions with similar names, a "Last reviewed" column full of dates from the year it was created, and a treatment owner who left in March.

The problem is not Excel. The problem is that a spreadsheet has no concept of an owner, a due date that means anything, or a person who needs to be told. Every one of those has to be supplied by a human remembering to do it. Registers do not decay because people stop caring about risk. They decay because nothing in the file ever asks anyone for anything.

A list-based register in Microsoft 365 fixes that, not because SharePoint is a better grid than Excel, but because a list row can trigger a flow, and a flow can chase a person.

What the register has to do before you design a single column

Write these down before you touch the tooling. Every column you add should serve one of them:

• Tell a specific named person what they owe, and when.
• Show the board a defensible view without anyone preparing a paper.
• Prove, at audit, that a review actually happened on the date claimed.
• Survive the departure of whoever built it.

If a column does not feed one of those four, it is decoration. Registers get abandoned because they became data-entry exercises, and data-entry exercises lose to real work every time.

The column schema

This is the smallest set I have found that still holds up in front of an auditor. Build it in a single SharePoint List, not a library of documents.

Calculating the rating

Store your choice labels with the number first ("4 - Major") and the calculated column can pull it out. A simple product of likelihood and consequence, banded into a rating, is enough:

=IF(VALUE(LEFT([Residual likelihood],1))*VALUE(LEFT([Residual consequence],1))>=15,"Extreme",
 IF(VALUE(LEFT([Residual likelihood],1))*VALUE(LEFT([Residual consequence],1))>=10,"High",
 IF(VALUE(LEFT([Residual likelihood],1))*VALUE(LEFT([Residual consequence],1))>=5,"Medium","Low")))

Then use SharePoint's list formatting to colour the column. The point is not prettiness. It is that nobody can quietly downgrade a risk by editing a rating directly, because the rating is derived. If you want the rating to change, you have to change the assessment, and the version history records who did it.

Next review, the column that does the work

=DATE(YEAR([Last reviewed]),MONTH([Last reviewed])+
 IF([Review frequency]="Monthly",1,
 IF([Review frequency]="Quarterly",3,
 IF([Review frequency]="Six-monthly",6,12))),DAY([Last reviewed]))

Every automation downstream keys off this one field. Get it right and the register maintains its own tempo.

Views instead of reports

The reason nobody reads a register is that everyone gets the same 60-row grid. Build four views and send people to their view, not to the list:

• My risks - filtered to Risk owner = [Me]. This is the only view most staff should ever see.
• Overdue treatments - Treatment due < [Today] and Status ≠ Closed. This is your management meeting agenda.
• Due for review - Next review < [Today]+30. The compliance officer's working list.
• Board view - grouped by Domain, filtered to Residual rating = High or Extreme, with description, owner, treatment and due date only. Point the board paper at this view and stop rebuilding a table every quarter.

The review cycle in Power Automate

Two flows. Resist the urge to build more.

Flow one - the review nudge. A scheduled flow, weekly. Get items where Next review falls inside the next 14 days. Group by risk owner so one person gets one email listing their risks, not six separate emails. Include a direct link to each item and an Adaptive Card with a "Confirm reviewed" action that writes today's date into Last reviewed. The moment a reviewer can close the loop from inside Outlook or Teams, compliance rates change dramatically.

Flow two - treatment escalation. Scheduled daily. Where Treatment due has passed and status is not Closed: day 1, notify the treatment owner. Day 7, notify their manager. Day 21, add the item to a digest that goes to the GM. The escalation ladder is the entire point - a reminder that only ever goes to the person already ignoring it is not a control.

Both flows should write to a simple audit list on every action. When an auditor asks how you know a review happened, you want a timestamped record that no one hand-typed.

What to leave out

I have watched more registers die from over-building than under-building. Things I now refuse to include in version one:

• A risk appetite matrix embedded in the list. Put it in the policy and link to it. It changes yearly and it does not belong in a row.
• Attachments on every row. Evidence belongs in a document library with a lookup, not stapled to a risk.
• A custom Power App front end. Not in version one. Get the list working and adopted first. Most organisations never need the app.
• Free-text owner fields. The single most common thing I have to undo. If the owner is text, nothing can be automated, ever.

Migrating the register you already have

Do not import the spreadsheet as-is. Two thirds of the rows in a neglected register are either closed, duplicated, or were never really risks - they were issues. Sit down with the file, close what is closed, merge what is duplicated, and move the issues to an issues log. You will typically land somewhere between 25 and 40 live risks for a small organisation, which is a number people can actually work with.

Then import the survivors, set Last reviewed to the true date of the last genuine review even if it is embarrassing, and let the flows start chasing. The first month is noisy. That noise is the backlog you already had, finally becoming visible.

The part that has nothing to do with SharePoint

A register works when the executive meeting opens with the overdue-treatments view on screen. It fails when it does not, regardless of how well it is built. The tooling removes the excuses - no version confusion, no manual chasing, no report to prepare. It does not supply the attention. That still has to come from somebody senior deciding this is a standing agenda item and not a document.

Running a register that has stopped being read? Tell me what you have now and I will tell you whether it is worth rebuilding or just worth pruning.