This is a guest post by Vijay, our in-house VBA Expert.
Hello Everyone,
This post shows how to make project risk maps using VBA. If you have modern versions of Excel (365, 2019 or 2016 with TEXTJOIN() function), see the Excel Risk Map Template page.
We all have some projects to manage every now and then and there are needs of various trackers that help us in gauging the progress of the same. One of the most important things are heat maps that quickly help us in visually displaying the names of the projects that need special attention and resolve issues that are impacting them.
So go ahead and grab a cup of coffee and read this article that would help you in creating a Risk Heat Map in excel (will use some double shot espresso in the form of VBA code) to help us to the target.
Before we begin – Data for Project Risk Maps
First of all we will understand what we are trying to create here by looking at the image below.
You would have seen a picture like this while managing project risks.
So today we will be learn how to create this in Excel to become more awesome in managing projects.
What is important here is how your data for the projects/entities being tracked laid out. We will use the Excel data tables [structured references] to help us here.
There is a Setup sheet in the excel file where we can add the names of the projects that we will use on the data table, as well as the Probability and Impact have been defined as data tables. This helps us in using their contents as drop down options in the data table.
Adding Named Ranges
We need to use the Name Manager to create named ranges to be able to use the data table columns as drop down items, this is show below.
- 1.Type this in a blank cell and then copy “=tblProject[Project]”.
- 2.Bring up the Name Manager by pressing CTRL + F3, or by going to the Formula’s Tab and clicking on Name Manager.
- 3.Click on New
- 4.Type the name lstProject in the Name box
- 5.Paste “=tblProject[Project]” in the Refers To box and the click on OK.
Repeat this process for “=tblProbability[Probability]” and “tblImpact[Impact]”
Now you can go the actual risk data table and select the Project columns first blank cell and put in Data Validation List here, choose List and put the Source as lstProject. Repeat this for Probability and Impact cells. You will only need to this one time for the first row, new rows when added to the table will automatically contain these settings.
After we have created the above data table, we need to add 3 columns to the right side where we will setup the calculation that will be used to update the risk map.
a) First Column is named as “ProbabilityScore” Since probability has been marked as “A, B, C or D”, we would need to convert this into a number. This is done by using the below formula.
=IFERROR(CHOOSE(MATCH([@Probability],lstProbability,0),4,3,2,1),””)
b) Second Column is named as “SearchString”
=IF([@Status]=”Open”,CONCATENATE(“x”,[@ProbabilityScore]^4+[@Impact]),””)
c) Third column is named as “DisplayName”
=CONCATENATE([@ID],” “,LEFT([@Project],20),IF(LEN([@Project])>20,”…”,””))
Understanding the SearchString Table
When creating the SearchString we are raising the probability score to the power of 4, this is what I have chosen you may select any number that you need. Below is the resulting matrix of numbers that we obtain by doing this.
The last columns in only used for trimming the name of the project to 20 characters of there is a big name, else the actual name of the project is used to display in the Risk Map.
Understanding the Code
So now we are ready to look into the VBA code that helps us in creating the Risk Map.
Public Function showRiskMap(inputRange As Range, searchString As String, dataRange As Range, separator As String)
Dim cntr As Long
Dim tempArray() As Variant
Dim tempDataArray() As Variant
Dim tempString As String
tempArray = inputRange.Value
tempDataArray = dataRange.Value
For cntr = LBound(tempArray) To UBound(tempArray)
If tempArray(cntr, 1) = searchString Then
tempString = tempString & tempDataArray(cntr, 1) & separator
End If
Next
showRiskMap = tempString
End Function
We are sending 4 parameters to this function which are
- inputRange – this is the SearchString columns data
- SearchString – this is a manual enrty such as “x257”
- dataRange – this is the Display Name column from where we will pick the name of the project to display
- separator – this is CHAR(10) which is a line break in case we have multiple projects falling in the same category
We are making use of Array’s here to pass the data from the Table column into the array and then a simple For loop to parse them and show us the results.
I hope you will enjoy this article and this assist in managing your projects in a much efficient way.
Download Excel Risk Map File
Click here to download the file & use it to understand this technique.
Click here for Risk Map Template for new versions of Excel.
Do you use Excel for creating Risk Maps?
Do you also user Excel for creating Risk Maps? If yes please put in the comment below how do you use the same and what has been your experience. Leave a comment.
More on VBA & Macros
If you are new to VBA, Excel macros, go thru these links to learn more.
Join our VBA Classes
If you want to learn how to develop applications like these and more, please consider joining our VBA Classes. It is a step-by-step program designed to teach you all concepts of VBA so that you can automate & simplify your work.
Click here to learn more about VBA Classes & join us.
About Vijay
Vijay (many of you know him from VBA Classes), joined chandoo.org full-time this February. He will be writing more often on using VBA, data analysis on our blog. Also, Vijay will be helping us with consulting & training programs. You can email Vijay at sharma.vijay1 @ gmail.com. If you like this post, say thanks to Vijay.
29 Responses to “Excel Project Risk Map Generator – using VBA”
Hi, Chandoo, Vijay!
I think there's a major mistake or missing data in the design and survey phases. Projects Coffee, Latte, ...? No way! There's only one valid attribute for those: Project Carlsberg.
Regards!
If b(ut)ob(ut)hc had read this...
Chandoo, As always, great stuff - thanks!
As a new student to Project Management, I really like the use of Excel for tools, and the creation of this Risk Register is an appealing idea. However, when I glances at the chart, I thought, what does "2 - Project Strong Coffee" mean? What is "3 - Project Strong Coffee" or "8 Project Big" and "9 Project Big"? How are they different?
One suggestion that I would make here is to use the risk register to portray risks for a SINGLE PROJECT. Specifically, instead of consolidating project risks and listing projects multiple times, the register could be reserved for a specific project, and the specific risk is put in place of the project name (where you have it in the table - this requires removal of the Project Name validation). By making this change, the chart then will display the RISK in the applicable quadrant, so viewers can see at a glance what the risks are, and their respective impact and likelihood. For example: "System Overload" or "Labor Strike", etc.
If the project manager must portray multiple projects and their risks in a single chart, I would suggest abbreviating, lettering, or numbering projects, and concatenating project letter with an abbreviation of the risk, such as "A100 Labor Strike" or "A100 Mechanical Failure", etc., so readers would know that the risks are and to which project they relate.
GOOD WORK
Hi Vijay, Chandoo,
Once again a very helpful and useful post!
I work in the area of risk management and frequently make use of risk maps like these. This post is great by the way!
In addition to the 2 dimensions of impact and probability I sometimes also want to monitor a 3rd dimension, lets call it the Velocity of a risk, i.e. how much/little time do you have to act before it is too late. This can also be measured for simplicity on a scale of 1 to 5 with 1 being "lots of time" and 5 being "very little time". This is usually displayed by clever risk tools as a bubble of which the size depends on the rating (1-5). So instead of simply displaying a risk name in the riskmap the risk names are also accompanied by bubbles of varying size depicting the Velocity of a risk.
Can this be done in excel? I believe so, but I think some clever tricks with charts is required. Or can this be done in a cell on the worksheet?
Has anyone done this before?
Regards
Thanks Vijay!!
Am a tad confused -
Whilst I like the 4 by 4 grid, since it stops someone fence sitting, I do not understand the Likelihood scale. Suggest swap the top two status, since 'Almost Certain' must be less than 'Certain' [or 'Is Certain'].
In fact something that is Certain, should instead be considered an 'Issue', this thus suggests that perhaps that 'Certain' should be changed to 'More than Likely' [or 60% to 80%] and 'Almost Certain' changed to 'Very likely' as in 80% to 95%];
I wonder if the very top right hand cell should have a Black background, as in BRAG [Black, Red, Amber, Green] or GRAB [Green, Amber, Red, Black];
Whilst it may be useful to have the Project Name shown, I am not sure of its value other than to the Programme Manager, since the Project Manager is more than likely to be interested in how many BRAG risks their project has.
Also I assume that the actual complete version would contain columns in which to enter the risk definition, plus the risk type another source of mappable data?
interesting
This was a great primer to get me thinking about a project I need to accomplish in Excel. I've stumbled upon Chandoo.org a few times in the past when looking for Excel/VBA solutions but I'll be back more often now.
Thanks Vijay and Chandoo for the great insights.
Hi Chandoo,
I have quick question, can we highlight a particular word in an excel cell. for example "Sachin is a Batsman". i need to highlight only Batsman with a different background. I am not talking about the font color. This is one of our requirement can this be achieved in excel with in a single cell.
Hi Vijay!
Great excel file for risk analysis, but ...
Could you - or other excel expert - create this excel without VBA code?
This is a good tool, but for example at our company, we do not use VBA code for security reason. (this regulation comes from IT HQ, do not ask me why)
Best regards,
N.
Thank You.
Regards,
Megha Nagaraja....
Nice maps! Useful code, too!
Reference a text :"The failure of risk management, and how to fix it" and, by the same author, "How to measure almost anything".
The problem with any risk maps which have probabilities of 'low', 'medium', high', or consequences which are qualitative, is that they are not calibrated and the same cell can mean different things to different people.
Always best to have distinct probabilities in percent ranges, and consequences in a common value with a range - say dollars, or incidences, or whatever - but something countable and quantifiable. As indicated in the texts.
So be careful, all, in developing a tool for risk which may be misleading.
For your consideration,
T.
Tony
Agree with what you say, however the consequences could be time [which delays the Project or Programme], cost [more resources, equipment, skills, delay in benefits / profits], reputation etc.
Yes, it might mean different things to different people, but the value of a risk register is it allows the Programme and/ or Project Management team to quantify the risks and then present the risks using a number of maps, images etc.
What is vital is having the risks recorded initially, then for the organisation to learn what value it can extract.
Exactly: "...quantify the risks ..."
Need numbers which can be compared - high, medium and low are too fluffy!
Tony
I have used in the past a combination of % for likelihood and numbers to indicate the severity, plus both % and numbers for likelihood, as in 5 = 80%to 95%. Anything that increases the audience's understanding of what is being presented must be good.
Hi Chandoo
I love this template.
I have been tinkering changing names and stuff only to find when I saved it and then re-opened it i got the following error - "Error in loading DLL".
Any thoughts on what may have caused it and how I can fix it??
Hi Vijay!!
It's a great post. Thanks so much! As soon I read it, I began to study about Arrays and Function in VBA. I would like to learn more about both this topics!
This is great but there are a couple of changes I am struggling to incorporate. Firstly the grid needs to be 5 x 5 to support likelyhood values of:
Almost certain
Likely
Possible
Unlikeley
Rare
And Impacts of:
Insignificant
Minor
Moderate
Major
Severe
I have added these to the Setup tab and altered column F in Risk Register Tab to:
=IFERROR(CHOOSE(MATCH([@Probability],lstProbability,0),5,4,3,2,1),"")
It's not clear to me if I need to alter the formula for column G and I have almost no knowledge of VDA to know it that need adjusting at all.
The other aspect I would like to see changed is to make this project centric, not programme, to highlight different tasks with risks as opposed to whole projects, which would then see make this spreadsheet awesome.
Mike,
Could you post your version of the matrix please?
I have cross posted to the form and attached the spreadsheet. See this thread. http://chandoo.org/forum/threads/modified-risk-map.15689/
Some interesting scale measures. I wonder if it would help if each risk could be in addition typed or flagged as in Finance, Time, Resources, Benefit, Reputation, Objective. I also wonder if useful to provide the option of recording a risk, but being able to set the likelihood and / or impact to say TBC, so that the risk is not forgotten plus there would be something to remind the Project Manager to complete the risk's assessment.
Being able to present a Programme perspective would be useful as well.
@Tim
It is the concept that is important here.
You can customise the axis to suit your businesses/industries needs
I have seen many variations including having the Important numbers at the Top Left or having the important numbers with the lowest score not highest.
A small suggested correction to the terminology in the Risk Map.
In the Risk Map there is a label applied to the B Likelihood value, namely, "Certain". This should be changed to a different label. A risk even that has a "Certainty" has a 100% probability and is no longer a risk but an Issue.
WJL
20 years aligning project results to corporate strategy
THANK you, guys... although not a coffee person but you've made my day with this great article.
I would like to have to only risk numbers separated with Comma. I can do it with inserting "," but unfortunately the formula inserts a comma after only one risk number also. Any suggestions?
=CONCATENATE([@ID],” “,LEFT([@Project],20),IF(LEN([@Project])>20,”…”,”"))
Thank you,
Mattiu
There are too many risks in a cell and the row is already at its maximum height. What modifications must be made to tackle this problem?
Hi was trying to do the above stuffs. However, I get stuck at the add Named manager itself. Get an error when I do the first step
1.1.Type this in a blank cell and then copy “=tblProject[Project]”.
Error message: The formula you typed contains an error.
Please advise
Hi Vijay and Chandoo,
with the introduction of the new ISO9001 in September 2015 Risk Management became a focus item for certification. Your solution is exactly what we need to identify the risk in our procedures. I try to make this work for us.
However, I am struggling with the "List" part of your instructions. Can you be a bit more specific here? What menu, etc?
And: Is the set up sheet really neccessary as a separate sheet or can one combine it with the risk data sheet?
Can you kindly advise?
Hello Vijay!
This looks wonderful!
would it possible if we can show single project in low, moderate or high section if there are same risks repeated multiple time.
I tried this in my risk register which worked perfectly fine. The only concern i have now is that there are 180 risks i have in the risk register and each risk repeats 5 to 10 times and same appears in the heat maps as well which pretty confusing and does not look good. could you please help me out with this so that we can show only unique name in the map.
thank you!
Waseem