Automatically updated risk matrix in Excel

For some time, I have wanted to make an automatically updated risk matrix in Excel. The matrix should automatically place the risk ID in the right cell of the matrix and show the current trend of the risk. After getting a risk responsibility in a new project I did a few web searches and came across this script that puts a series of values into a single cell (thanks Excel).

I did a few modifications to the script to fit it to my needs.

Public Function CCARRAY(rr As Variant, sep As String)
'rr is the range or array of values you want to concatenate. sep is the delimiter.
Dim rra() As Variant
Dim out As String
Dim i As Integer
On Error GoTo EH
rra = rr
out = ""
i = 1
Do While i <= UBound(rra, 1)
If ((rra(i, 1) <> False) And (Len(rra(i, 1)))) Then
out = out & rra(i, 1) & sep
End If
i = i + 1
out = Left(out, Len(out) - Len(sep))
Exit Function
rra = rr.Value
Resume Next
End Function

Then I created this Risk example as Excel spreadsheet and stored it as an Macro-Enabled Workbook. The cells in the risk matrix contains a formula like this:

=IFERROR(CCARRAY(IF($B3=RiskList!$C$2:$C$10;IF(C$2=RiskList!$D$2:$D$10;RiskList!$A$2:$A$10&" ("&RiskList!$F$2:$F$10&") ";"");""); ", "); "")

Remember to press “Shift+Ctrl+Enter” when exiting the cell to get the matrix functions working . The “beautiful” result may be viewed below.



Join the Conversation


  1. is it possible to add a new risk row in your excel matrix worksheet, or this is limited to nine risk rows?

  2. You may add as many rows as you please. However, you need to change $C$10 and the other references to the 10th row to for instance 100. Its been a while since I worked with this spreadsheet but let me know if you are unable to fix it and I’ll look into it if you want.

Leave a comment