Create a Risk Assessment Chart
Вставка
- Опубліковано 29 вер 2024
- This video covers how to create a colored (green-yellow-red) risk assessment chart. This is used in project management to compare risk to probability for various task or projects to help aid in decision making. See the link to the article (along with the Macro code) at support.micros....
If you have Excel 2013 or later, there is a function to add descriptive labels you can actually put labels without any VBA. See video at • How to Create and Cust...
If you have Excel 2010 or earlier and experiencing Error 91 this might help. To avoid 'error 91', you must have the object (chart) selected at the time you run the macro. Aslo see docs.microsoft...
Feel free to provide a comment or share it with a friend!
🔔 SUBSCRIBE to my channel ➜ goo.gl/wN3c3p
🏫 Excel Training ➜ www.exceltrain...
📚 Excel Books & Tech Gear ➜ www.amazon.com...
⚙️ Tools: Screencasting ➜ techsmith.z6rj...
⚙️ Tools: Microsoft Office ➜ microsoft.msaf...
⚙️ Tools: TubeBuddy ➜ www.tubebuddy....
📝 This description may contain affiliate links and we'll receive a small commission if a purchased is made using the links (but at no additional cost to you). It'll support the channel and so more videos like this can be made. Thanks for your support!
🎁 If you find these videos useful and want to support my channel go to www.buymeacoff...
#excel
#msexcel
#doughexcel
~-~~-~~~-~~-~
Please watch: "Convert Table in a PDF File to Excel"
• Convert Table in a PDF...
~-~~-~~~-~~-~
Very useful and well explained. To improve: try not to narrate continuously but pause after you make a point. Keep up the good work
Excel 2013 solved this problem I think . Thanks for the great video
You're Welcome!
wow, thanks man, you're amazing
You're welcome!
I will have an exam and thanks lol, it will be very useful for me
Best of luck!
Love this
Thanks!
Thanks for this, its really helpful! Keep posting!
Hi Mark Syed, thanks for the comment!
They seem to have fixed the label problem in 2016, so it saves messing with a macro.Good video!
Yes I noticed that but one thing I am having issues with is the Axis titles
Thanks - this is quite helpful
Nice, bro!!! It helps me a lot!!!
Super 👍👍
Thanks 🙏
Good video!
Thanks for the comment!
This is great, except the Macro didn't work. I don't know anything about VB, can someone help me? The debug highlights the following part of the script.
xVals = Mid(xVals, InStr(InStr(xVals, ","), xVals, _
Mid(Left(xVals, InStr(xVals, "!") - 1), 9)))
Same here
thanks!
You're welcome!
when i click the macro section (as you did in 6.00), I get an error which is run time error 91 object variable or with block variable not set. How can I fix it?
If you have Excel 2013 or later, there is a function to add descriptive labels you can actually put labels without any VBA. See video at ua-cam.com/video/NnoFXSFBtIg/v-deo.html
If you have Excel 2010 or earlier and experiencing Error 91 this might help. To avoid 'error 91', you must have the object (chart) selected at the time you run the macro. Aslo see docs.microsoft.com/en-us/previous-versions/visualstudio/visual-basic-6/aa264506(v=vs.60)?redirectedfrom=MSDN
@@DougHExcel thank you ^^
I'm getting a 404 missing page when searching for KB213750 or following the link. Can anyone provide a copy? Or another link?
try the updated link in the description of video
@@DougHExcel Thanks for the quick updates.
When I try to run the macro, I get the following error message: Run-time error '91': Object variable or with block variable not set. Can you tell me how I can fix it. I tried deleting the module and recopying the instructions, but I get the same message. (I am using Excel 2013) The debug is pointing to one line that says: xVals=ActiveChart.SeriesCollection(1).FormulaThank you!
this might help: msdn.microsoft.com/en-us/library/aa264506(v=vs.60).aspx
Hi Jean Dorsch, thanks for the comment!
For those who does not know how to create or run a macro. The solution to have the Risk factors R1 to R11 appear on the chart itself is to use the text box feature and put the text boxes near to the points on the graph and type in the required Risk factors. Thanks a lot for the video and detailed instructions
Hi Kebin Abraham, thanks for adding to the thread!
You can also choose to show data labels then right click any label and goto "Format Data Labels". Under "Label Options" tick "Values from cells" and use the "Select Range" button. This will select a whole column of labels. This might not be compatible with older versions of excel.
Hello ,please the first link you attached is not working
You can customize the macro labels in Excel 2013 by right clicking > format data labels > under label options select "value from cells"
Thank you
You are an absolute star! Thank you so much for this invaluable feedback!!!
Hi Syrine khoury, thanks for the kind words!
support.microsoft.com/en-gb/help/213750/how-to-use-a-macro-to-add-labels-to-data-points-in-an-xy-scatter-chart link to get the macro
Hi Karolina Rychlińska, thanks for adding to the thread!
Great Chart!!!! Nice VBA too!
Guys, error 91 it's easy to fix: you just have to run the macro with the selected graphic! Love it!
Totally! It does work.
Thanks for adding to the thread!
Your "Microsoft products:" tiny URL is give the following error: This webpage has a redirect loop ERR_TOO_MANY_REDIRECTS
Update: I was able to get the macro from the URL in your vid, thx.
Where? I am trying to find it and cannot locate it. Thanks!
sir can you send the code to me please.
Watching this in 2022. From the 3minute labelling mark, you can now select labela from a range. Just right click > data labels > label options > value from cells (select range)
Otherwise still a very helpful video!
Hi Arnee P, thanks for the comment!
I am not able get that RESOURCE from microsoft site
If you have Excel 2013 or later, there is a function to add descriptive labels you can actually put labels without any VBA. See video at ua-cam.com/video/NnoFXSFBtIg/v-deo.html
Hello what about error 5? Help pls
Need help in less than a day!! When I try to run macro, I get an error which's like: Run Time Error '91'. Object variable or with block variable not set.
If you have Excel 2013 or later, there is a function to add descriptive labels you can actually put labels without any VBA. See video at ua-cam.com/video/NnoFXSFBtIg/v-deo.html
If you have Excel 2010 or earlier and experiencing Error 91 this might help. To avoid 'error 91', you must have the object (chart) selected at the time you run the macro. Aslo see docs.microsoft.com/en-us/previous-versions/visualstudio/visual-basic-6/aa264506(v=vs.60)?redirectedfrom=MSDN
What do I do if I get an error "run-time error 91. Object variable or with block variable not set? Can you help me fix this?
If you have Excel 2013 or later, there is a function to add descriptive labels you can actually put labels without any VBA. See video at ua-cam.com/video/NnoFXSFBtIg/v-deo.html
If you have Excel 2010 or earlier and experiencing Error 91 this might help. To avoid 'error 91', you must have the object (chart) selected at the time you run the macro. Aslo see docs.microsoft.com/en-us/previous-versions/visualstudio/visual-basic-6/aa264506(v=vs.60)?redirectedfrom=MSDN
I can't get Macros to work I get the following
xVals = ActiveChart.SeriesCollection(1).Formula
Can anyone help?
If you have Excel 2013 or later, there is a function to add descriptive labels you can actually put labels without any VBA. See video at ua-cam.com/video/NnoFXSFBtIg/v-deo.html
Hi is there a more newer process to this?
Great vid
Hi forte, thanks for the comment! new vid/process? maybe I'll look into it 😁
Hi Doug, Quick question- if we change the "risk number" after we have imported the macro, will it still auto update it to the graph used? We had to remove a few risks and now it changed the numbering of the risks.
if you have later versions of excel, you don't need the macro...see the link in the description on how to use.
wow this video is amazing- i always try to follow these and they never seem to work. This one came out perfectly, i was very pleased!! thank you!!!
Glad it helped!
Love it, very well explained Doug H. I use this to create an Impact effort matrix.
Glad you liked!
I wanted to blow excel up but you saved the day, THANK YOU!!!
You’re welcome!
Hi, is it possible to get the excel risk assessment chart file directly ? That would be great… (not working to create it for me, i get VB bugs). Thanks a lot
If you have Excel 2013 or later, there is a function to add descriptive labels you can actually put labels without any VBA. See video at ua-cam.com/video/NnoFXSFBtIg/v-deo.html
Hi, I did exactly as you mention, but when I run the macro it says "Run-time Error 91. Object variable or with variable not set." Do you know what can be wrong? Thanks!
try to redo this from scratch...as i understand that error has something to do with something not properly entered. As an alternative, if you have Excel 2013 or greater the macro is not needed anymore for label creation. See ua-cam.com/video/NnoFXSFBtIg/v-deo.html
please tell me what is the formula in visual basic that you wrote so now I need to know about this how to put the risk reference number in the chart
I won't lie but this has been the best educative and well explained video I have ever watched. Thank you! I was able to a create a clear pretty risk assessment matrix
Thanks Shamim candy, glad it helped!
Is there any workaround to avoid overlapping labels or having to manually rearrange overlapping labels?
You'd have to manually move it around 😐
This is very helpful but I got stuck getting the macro. Can you publish the link?
Thanks for this video! Any tips on how to do this on Google sheets? Specifically regarding how to label your data points R1,R2 etc? And how to separate the plots as you have done here in excel? Thanks
Great suggestion; maybe a google sheets video in the future 😁
@@DougHExcel Hi! I have the same question! Made this chart based on your video, but when I export it to sheets it looses the macros function and there is no options to make a macro in sheets. Any suggestions how to get this working? And a big thanks for the tutorial!
Hi Doug, pls can you help me with what i can do if i want to change the first column (Risk Factor ) to alphabet such as Liquidity Risk, Credit Risk e.t.c. How will i do this to avoid Run time error 91
Not sure on the run time error. You might need to just create it again and run it. Excel 2013 now has the option of creating labels from a user define selection (ua-cam.com/video/NnoFXSFBtIg/v-deo.html) , so hopefully you have that option.
Hi Doug,
I am using a 3-D scatter chart with all my axis and bubble size with the right information. The issue is labeling the bubbles. I want to label it by combining two different elements. I saw you used VBA codes to label your graph. Is it possible using VBA to customize the labels as I want it?
Thank you.
You can combine the two elements together with the CONCANTENATE function (ua-cam.com/video/150vcUL6pvA/v-deo.html) or the ampersand (&) into one cell to use with the VBA.
If you have Excel 2013, it lets you add custom labels without the VBA. This video shows example ua-cam.com/video/NnoFXSFBtIg/v-deo.html
Hey can you share the VB code that you had written while creating the module
See the link to the article (along with the Macro code) at support.microsoft.com/kb/213750
Thank you very much that you tech this video thank you so much
great help...
added to my playlist for future help/reference... hope it's ok with you.
thanks for the help :-)
Thank you so much for this🙏🙏
You’re welcome!
Well done. I use GRC tool and it doesn't support Heat Map as i want it but after watching this video i was able to create heat map the way i want it. Thank you Doug. Really appreciate it.
Hi K Kamra, thanks for the comment!
It really works! Nice and helpful video
Glad it helped!
Hi Doug, Thanks for the informative video it's helped me a lot. Just one question when i have my list of columns with my information in them, if i choose to change the information in the first column why does this not update on my actual chart? Thanks
Thank you so much, 8 years later still helps!
Glad it helped!
Doug, Great help. Thank you for making the effort. Very helpful. I don't suppose you could tell us how to import this type of spreadsheet into a Power Point so it is still functional within the Power point.
+Doug H Thanks. Yes thats exactly what I want to do. For now I've just inserted a hypo link into the PP which throws me to the spreadsheet where I can demonstrate the changes required.
Sir do you have more videos related in how to conduct a complete Risk Assessment
Hi Raven, thanks for the comment! How to make a charts, but not the process of conducting...
thank you, this is very useful~!!
You’re welcome!
Thank you Dough... could you please let me know how do we arrive at impact & probability.
Thanks again.
Thank you
N13?
Sorry, don’t understand the comment/ question...
Wonderful information thanks tomorrow I will preapare
+Mishra Pawan glad u liked it!
Could i have the link please
thank you so much
You're welcome, sorry no link to files
Great help! Thank you, helped to create me a nice looking risk matrix
Thanks lenamadlene, glad it helped!
thank you, this is very useful~!! great~
Thank you so much for this tutorial. Helped me to fix some problems while creating a risk matrix.
You're Welcome!
Very informative, Great job.
thanks for the turotial.Really helpful!
Thanks Nurul Nadia, glad it helped!
worked perfectly thank you a lot
You’re welcome!
Great job. But how exactly can I get the macro ? Thanks.
The description field of the video page will have the link to Microsoft site for the macro.
Okay, thank you, Doug.
there is a runtime error 91
If you have Excel 2013 or later, there is a function to add descriptive labels you can actually put labels without any VBA. See video at ua-cam.com/video/NnoFXSFBtIg/v-deo.html
If you have Excel 2010 or earlier and experiencing Error 91 this might help docs.microsoft.com/en-us/previous-versions/visualstudio/visual-basic-6/aa264506(v=vs.60)?redirectedfrom=MSDN
thank you bro.. you helped me alot in doing my mid term test..
Thanks Rifky Adhlan Nomaden, glad it helped!
Great video, but the macro doesnt work. i get error 91
if you have excel 2013 or later, the macro is not needed....see ua-cam.com/video/NnoFXSFBtIg/v-deo.html
Thank you..
where can i get this macros
it's at Microsoft support.microsoft.com/kb/213750
where do i get the code
check in the video description
Thank you so much for this class Doug! Great job.
Thanks André Oliveira!
Thank you.
You're Welcome!
You are the man!
Thanks for the comment!
how can i correct this error Object variable not set (Error 91)
He did a great job. The macro is no longer needed because in 2016 the fix the ability to add a name. Use the left mouse button and use edit text command
could you tell me how i do that?
I can you must have office 2016 to make this work. Do you have office 2016? Use the scatter option in Excel.
stephicohu thanks
I got it
Could you explain this further? where is edit text command?
On a graph : + < Data Label < More Options < Histogram button < Label Options < Label Contains < Value From Cells !!!!
Superb. Thank you!
You're Welcome!
Thanks
You're Welcome!
Thanks a Lot!!
You're Welcome!