Yesterday, I talked about how you don’t have to know how to code in order to highly leverage VBA. All you need to know is how to Google, Cut, and Paste. As discussed then, I ‘volunteered’ KV under pain of exposure to empty the contents of his secret satchel onto the virtual table, so that we can rummage through it. So without further ado, please put your hands together and give a warm Chandoo welcome to secret agent KV.
[Secret transmission starts…]
Hello, this is my first guest post on Chandoo.org (or any Excel website for that matter), and I will try to keep it simple, but useful for our readers.
I have been using spreadsheets since 1990, and Excel since 1995 – which sort of makes me a veteran in this sphere of business applications 🙂
One of my favorite topics in Excel is – “How can I make my day-to-day tasks in Excel easier and faster ?”. In fact, this is a topic that I think about in everything to do with computers.
There are many ways one can do this in Excel, but among the more effective and scalable ones, is storing commonly used macros in your Personal Macro Workbook.
This post is about some of the stuff that I have put in my Personal Macro Workbook over the years. You can read more about how to set up a Personal Macro Workbook, in this excellent tutorial on Ron de Bruin’s website. Like nuclear war, It’s a one-time exercise. And you can easily port it to any other computers that you use – or even share it with your friends and allied spooks.
This is the first bunch of macros which I use most frequently. Hopefully I will get a chance to post some more if this post is found to be good enough 🙂
So here goes.
1: Find the value of ActiveCell within selection, or in the whole sheet
This is a very useful macro which helps to search for the value in the ActiveCell within the selected range or the whole worksheet (if only ActiveCell is selected).
Sub SearchOnActiveCellContents()
' Keyboard Shortcut: Ctrl+Shift+G
On Error GoTo NotFound
If Selection.Cells.Count > 1 Then
Selection.Cells.Find _
(What:=ActiveCell.Value, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Else
Cells.Find _
(What:=ActiveCell.Value, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
End If
Exit Sub
NotFound:
MsgBox "No cells found with this cell's contents"
End Sub
As you will notice, the macro checks whether the selection is 1 cell or multiple cells, and accordingly executes the Cells.Find command.
2: Filter on value NOT equal to ActiveCell value
This is another handy macro, which filters the current column based on the value of the active cell, except that the filter is applied as “show records NOT equal to the value of the active cell”
The macro itself is a fairly simple one-line command :
Sub AutoFilterSelectionNOT()
' Keyboard Shortcut: Ctrl+Shift+K
Dim lField As Long
lField = ActiveCell.Column - ActiveCell.CurrentRegion.Column + 1
If TypeName(Selection) <> "Range" Then Exit Sub
Selection.AutoFilter Field:=lField, Criteria1:="<>" & ActiveCell.Value
End Sub
3. Show or Hide zeros in active sheet
This macro toggles the display of zero-value cells on the active sheet.
Sub Hide_Zeros()
' Keyboard Shortcut: Ctrl+Shift+Z
If TypeName(Selection) <> "Range" Then Exit Sub
ActiveWindow.DisplayZeros = Not ActiveWindow.DisplayZeros
End Sub
4: Show or Hide page-breaks in active sheet
This macro toggles the display of page-breaks on the active sheet.
Sub ShowHidePageBreaks()
' Keyboard Shortcut: Ctrl+Shift+J
If TypeName(Selection) <> "Range" Then Exit Sub
ActiveSheet.DisplayPageBreaks = Not
ActiveSheet.DisplayPageBreaks
End Sub
As the name suggests , this macro will show or hide the display of page breaks on the active sheet.
5: Display the 'GoTo special' xldialog
Quite often I find myself needing to use the GoTo Special command.
Of course, you can do it the way it was designed in Excel – press F5 to display the GoTo dialog box, and click on the Special… button. This takes one keystroke and a mouse-click; or 3 keystrokes (if you don’t use the mouse) 🙂
Or you can display the Goto > Special… dialog box (using a macro) with just 1 click of the mouse or 2 keystrokes (if you pin it on the QAT) !
Sub xlSelectSpecial()
On Error GoTo NotFound
If Selection.Cells.Count = 1 Then
MsgBox "Select more than 1 cell...", vbExclamation, "Select more cells..."
Exit Sub
End If
Application.Dialogs(xlDialogSelectSpecial).Show
Exit Sub
NotFound:
myMsgText = "No such cells found"
myTitle = "Not found"
myConfig = vbOKOnly + vbExclamation
myMessage = MsgBox(myMsgText, myConfig, myTitle)
End Sub
As you will notice, the macro has an error-checking line in case the type of ‘special cell’ you selected is not found. E.g. if you’re looking for blank cells in the selection, and all the cells in it are non-blank, the macro will display a message accordingly.
The macro also checks whether more than one cell is selected before executing the dialog. The reason for this is that if a single cell is selected, many of the options in the GoTo Special dialog box will execute on the entire ‘UsedRange’ of the spreadsheet, instead of the selected range.
If you wish, you can comment out the If … End If construct and test the macro to see what I mean.
6: Zoom-in / Zoom-out
These macros zoom in or zoom out on the worksheet, in increments of 5%.
Sub MyZoomIn()
' Keyboard Shortcut: Ctrl+E
Dim ZP As Integer
ZP = ActiveWindow.Zoom
If ZP >= 400 Then
ZP = 400
Else
ZP = ZP + 5
End If
ActiveWindow.Zoom = ZP
End Sub
Sub MyZoomOut()
' Keyboard Shortcut: Ctrl+Shift+E
Dim ZP As Integer
ZP = ActiveWindow.Zoom
If ZP <= 10 Then
ZP = 10
Else
ZP = ZP - 5
End If
ActiveWindow.Zoom = ZP
End Sub
As you will notice, will increase or decrease the zoom percentage by 5 points each time the macro is executed. The If… Then… Else… constructs are there to prevent an error if the current zoom percentage is already at the maximum or minimum level, when the macro is executed.
That’s all for this post from my side. I hope you will find it useful.
I welcome comments, suggestions for improvement & criticisms from readers on this topic, and the macros I have shared in this post.
[Secret transmission ended.]
Hey, thanks KV for sharing those shortcut-charged shortcuts. I look forward to torturing some more of that ill-gotten wisdom out of you. (While I don’t condone torture, I hate inefficient use of Excel even more. So while it’s going to hurt you more than me, it’s for the greater good.)
About the Author
KV is an undercover secret agent who spends his time rescuing the world from the crushing weight of evil, bloated spreadsheets.
His mild-mannered alter ego - Khushnood Viccaji - is a freelance professional and an expert in Management Information Systems and Business Applications with a focus on Data Management, Analytics, Transformation, Auditing, and Reporting.
Both these chaps have a flair for understanding and applying technology in business processes and an ability to present business information in many different ways. And one of them wears lycra.
24 Responses to “Free Excel Risk Map Template”
Why didn't you include the mitigation or risk IDs in the chart?
You can easily add such detail by modifying the TEXTJOIN function. Another way to use them is to add a slicer to highlight all risks that have a specific mitigation strategy or team member assigned to them. I left out those bits fto keep the article short.
I tried adding a slicer filter for the mitigation step but the TEXTJOIN is not affected by it. I added a helper column called "Visible" using the AGGREGATE function but I am unable to think of a method to pass that on to the map.
Could you please help, Chandoo?
Thanks
Never mind. I got it working. 🙂
Apologies, I didn't thank you for the file to begin with.
Great concept. thanks!
Awesome.. good to hear that Rajesh and of course you are welcome 🙂
Hello everyone,
Another amazing tutorial, great content and tips! My question is about slicers. How do you add slicers to this matrix? I've added 2 columns in my workbook table (Work Stream and Project Name) and I want to be able to filter (slice) the matrix on Project Name, but having some trouble with this. The slicer works fine in the data table, but how do I connect it to the risk matrix, so that only risk titles show up for the selected project?
Many thanks in advance for your guidance,
MyvJ
Can you create a sheet in live stock market data price change with profit and loss graph with time. which could indicate live profit and loss in each time frame 5minute, 10 minute, 15 minute, 30minute, hourly with some modifications
Hi
I've tried to get your formula to work, but likelihood / impact 1/1 does not seem to work.
Hi Chandoo
Awesome instructions! Thank you so much, this really helped me.
I was wondering if it would be possible to list the Risk ID number along with the Risk Title with a dash in between, rather than a bullet point? I have had a try at this but I keep getting a #VALUE error. I can see it's wrong but can't figure out what it should be instead. If you have time do you mind letting me know what I'm doing wrong?
{=" - " & TEXTJOIN(CHAR(10)&" - ",TRUE,
IF(RiskRegister[Likelihood]=$A17,IF(RiskRegister[Consequence]=F$3,CONCAT(RiskRegister[ID],RiskRegister[Risk Title]),""),""))}
Thank you!
Sally
Hey Sally, You are welcome.
I think the CONCAT inside TEXTJOIN is the culprit. Try this and hopefully you should see the ID too.
{=" - " & TEXTJOIN(CHAR(10)&" - ",TRUE,
IF(RiskRegister[Likelihood]=$A17,IF(RiskRegister[Consequence]=F$3,RiskRegister[ID]&RiskRegister[Risk Title],""),""))}
Hi Chandoo
You're a legend! Thank you so much! I had to make a minor tweak but otherwise it worked perfectly. Here is the tweaked version in case it helps anyone else:
=TEXTJOIN(CHAR(10),TRUE,
IF(RiskRegister[Likelihood]=$A8,IF(RiskRegister[Consequence]=C$3,RiskRegister[ID]&" - "&RiskRegister[Risk Title],""),""))
Thank you again!
Hi, Im not able to change the formula when trying to add risk Id instead of bullet point.
trying this: ="• "&TEXTJOIN(CHAR(10)&"• ";TRUE;IF(risks[Probability of Occurance *]=$C5;IF(risks[Severity of potential Impact *]=H$8;risks[Risk ID]&". "[Title *];"");""))
Cant see any solution on this.
thankful for help
Hi Chandoo,
This is perfect - One quick question, How can I add a hyperlink to the risks - So that I can click on the particular risk and it takes me to the actual row of that item.
Many thanks in advance.
HI Chandoo,
Is there a way to only display filtered item. Once the list gets big, it's hard to see all risk.
Kind regards,
SinYen
Hi Chandoo,
Quick question
1) Is there a way to remove duplicates within each risk block?
2) Is there a way to have the results in the chart update based on a filter or slicer?
Thanks a lot
Hi Chandoo,
The risk map is a brilliant tool, and I wanted to the risk map to only show Open risks. How can I do that?
Just found this today as I am making a risk matrix as well. I got the formula to work with this, where a risk score is above 30. Risk score = probability*impact*modifier.
So this works flawlessly, ="- "&TEXTJOIN(CHAR(10)&"- ",TRUE,IF('Risk tracker'!G4:G27>=30,IF(Table1[Urgency]="Now",'Risk tracker'!A4:A27,""),""))
I am trying to find a range now. Risk score in between 21-29. I tried using the AND function, but I couldnt get it to work. Is there anyway to get this formula to work with a range as mentioned above?
Thanks Eric.
You can't use AND() as it is not able to return arrays. You can try below formula.
="- "&TEXTJOIN(CHAR(10)&"- ",TRUE,IF(('Risk tracker'!G4:G27>=21)*('Risk tracker'!G4:G27<=29),IF(Table1[Urgency]="Now",'Risk tracker'!A4:A27,""),""))
Hello, this template is nice, thank you but im facing a problem when I need to find a range of impact. I cant figure out how..
My actual form is "="• "&TEXTJOIN(CHAR(10)&"• ";TRUE;IF(Table1[Impact]=A8;Table1[Title];"");"")"
Where A8 is number "1" so this formula finds everything with impact 1 and shows the titles.
What I need to get is a range so,
A8 is "1" and A9 is "2" and I need the formula to find all titles which impact is between 1 and 2.
I tried the AND function and so on, nothing worked..
Can you help me please?
i tried everything in your video in the end i only get the bullet... please guide me through
Sorted it... i was flash filling the other cells and it took other columns...
i do have another question though... how can i use slicers to filter the content of the matrix, so that it'll show only the departments i select?
slicer is working fine with the table, but the matrix still shows all the results
Just want to thank you for this.
It is awesome.
Hello everyone,
I think I accidentally nested my question in another thread. Apologies!
This is another amazing Excel tutorial, with great content and tips! My question is about slicers. How do you add slicers to this matrix? I've added 2 columns in my workbook table (Work Stream and Project Name) and I want to be able to filter (slice) the matrix on Project Name, but having some trouble with this. The slicer works fine for the data table, but how do I connect it to the risk matrix, so that only risk titles show up for the selected project?
Many thanks in advance for your guidance,
MyvJ
This is another amazing Excel tutorial! My question is about slicers. How do you add slicers to this matrix? Please advise