This video is very helpful. I just wish it was not so difficult to make these graphs in Excel. Really appreciate your time and effort in explaining this clearly.
Excellent tutorial. In the end, I would like to add that if your CI's are very wide using the format axis option (for the axis with numbers) and choose the option "logarithmic scale base 10" with axis bounds set to auto. Then format axis for the axis with factors at set label position to "low".
Just what I needed! Great tutorial. Only note I would add is depending on the version of Excel you have you may need to adjust your y-axis range if the scatterplot points don't align with your countdown/factor labels :)
Thank you Erica!! For those who may struggle with the very last part i.e. aligning bars to odds ratios, make sure to check out @Castor Huang and @pgbarnett's solutions down below in the comments section.
I had the same problem as other here that the ORs didn't align when using less factors than eight. Using four factors, I changed the countdown values to: 2,4 1,8 1,2 and 0,6 and the Ors aligned perfectly. So just try different values, starting from the highest number in the series and look how that changes the position of the marker in your graph. Using Excel for Mac.
Thank you! I had the same problem with 4 variables and your solution worked (Win10 here). I didn't think about using decimals, was experimenting with total valuies. Many thanks!
Your explanation is perfect (clear and concise), thanks! I am using Excel 2016, and for some reason, my odds ratio values do not position themselves on the tick marks: the values a dispersed and barely match with the labels on the left corner, they are lower on the graph than they should be. Been playing with the data for a while, and nothing happens--I will try again tomorrow to see if I can figure out a way around this.
Go to chart element "the + sign on the right of your graph" then axis then clic on secondary vertical axis, go to format axis and make sure your axis are from 0 to number factors number +1. now you can delete the axis so they wont show on your graph
It turns out that there is a problem when using 12 variables ... I found a solution by adding 1 more empty column above it (in the explanation only 1 empty column above) ... hope it helps
Hi Erica, thank you for the video, but how do you select one gridline (value 1) to fill it/make it bold as seen in forest plots in published journal articles?
Thank you, this was extremely helpful. Can you individually change the color of individual circles? EVery time I try to chanche the color every circle changes color as well.
Hey Erica. Thanks for this great tutorial. Can you tell me, how to calculate change in odd ratio if we increase 10 ug/3 pollutant concentration? If you have any video regarding this.
when I try to add in the odds ratios, the values seem to add in an a different y axis- and I cannot get the values to match with the original bar countdown values. I am using excel 2016 on a Mac.. Any suggestions?
This was super helpful. Anyone know how to show two sets of data in the same plot area? For instance outcomes for two different groups, for instance different doses of medications. I'm looking to show OR for complications associated with two dose levels of same medication.
Hi Erica, thank you so much for this helpful video. I'm having an issue with my datapoint/label alignment, though. First of all, I'm seeing no tick marks at all on my Y axis, and second, when I go to change the "odds" data to scatter, it moves the datapoints further above the axis labels, and "positioning on tick marks" doesn't change it. Any advice for this issue?
Thanks Erica. That was really useful. I managed to do a forest plot excel. However, how do I make it neater by grouping the factors? For example, your 8 factors are actually based on 4 categories (gender, age group, income quintile and rural-urban residence). Can you make your plot such that there is an extra space between the 4 categories? I tried to do that and the plots are not in line with the y-axis categories. thank you!
Hey Erica, thanks for this great tutorial! However, I encountered the following problem: When I add more than 8 factors and 8 odds ratios, I am not able to align factor bars and odds ratios (see video at 7:31). Do you have a solution for that problem? many thanks!
Hello Philipp, thank you for your comment. How many factors in total do you have? I will play around with it on my end and get back to you. Thanks so much.
You need to adjust the format secondary vertical axis given the number of factors you have, since excel 2016 will adjust the minimum and maximum of your axis automatically. For example, if I had 20 factors, I have to put 0 as my minimum and 21 (20+1) as my maximum axis. After doing so, my labels are all aligned. Hope this is helpful.
Thanks for the video- unfortunately if your 95% CI's go negative they overlap the labels... what can one do to align the labels to the right so they don't overlap?
hey erica, the vid is helping me a lot. but, i hve a doubt.. why OR needs to be minus with lower bound, since the lower values were there ady?? can we just use the ady existed lower values?? tq
There are many unnecessary steps there. You don't have to start with a bar chart, just add a new scatter chart and edit the X Y values. The vertical axis parameters should be adjusted and ready.
Your explanation is perfect (clear and concise), thanks! I am using Excel 2016, and for some reason, my odds ratio values do not position themselves on the tick marks: the values a dispersed and barely match with the labels on the left corner, they are lower on the graph than they should be. Been playing with the data for a while, and nothing happens--I will try again tomorrow to see if I can figure out a way around this. Regards, C.
When you hover your mouse on one of your odds ratios, does the odds ratio value align with the countdown column value? For example, if you have an odds ratio of 5 and the countdown value is 8, the value should say (5, 8).-When adding labels to the y-axis, make sure labels (factors) are aligned with the bars -Align the labels with the tick marks after you have plotted your odds ratios. -Re-check your countdown column make sure it is counting down correctly. If none of these work, keep playing around with the empty cells. Leave it out when creating the y axis, leave one in and one out, or include the empty cells in when adding in the odds ratios. Also, try different major unit values. For me, it took a lot of trial and error getting everything to align perfectly. Let me know how it goes. Feel free to e-mail me your results if it’s still not working. Good luck!
Hi I'm having the same problem, please can you help? The odds ratio scatter dots do not align with the factors bars, the labels are the same from 1-12 on both bars and dots but one does not sit over the other.
Hi Amy. For the "Factors" and "Countdown" column, when you select it's values, do you select the empty cell above and below the values? You also may want to check your X "Axis Options". Make sure the major unit is .5. Is it your scatter dots that's aligned with your tick marks on the Y axis? Or is the factor labels aligned on the tick marks. Let me know how those go.
Hi - I'm having the same problem. When I make the graph with 8 factors, the OR dots and CI align properly to the factor labels on the Y axis. However, when I try making the graph with more than 8 (I need 10) factors, the numbers are all misaligned. I followed these instructions about when to select the empty cells, but it doesn't help. Any ideas?
Thank you Erica Lee for your detailed instruction. However, I cannot find the option "On tick marks" in term of position of axis - I'm using Excel 2011 on Macbook. I have researched on the internet but I cannot not find any solutions about it. Could you help give me your suggestion? Thanks in advance!
Hello, I have some trouble, I have 28 factors and while the bottem labels of the factors are in line with the graphs (the dot and error bar) the graphs are everytime a bit to higher when you go one variable up so the first labels are totally out of place. Edit: the bars are in place but the "dot and line" not in line with the bars
I think I had the same problem you did, and I realized I had to readjust the secondary vertical axis so that the maximum number was 1 above our highest count while the minimum was 0, and then the issue was fixed. Gave me a grey hair though.
Thank you so much for this video!! It is very clear with every step and so helpful, saved me so much time from having to search endlessly for other videos Unfortunately this does not work with 10 or more factors.
I had the same problem. In the end the problem for me was that the language of Excel was in Dutch in my case. So if your excel program is in another language you should change the term ROWS into the language that your Excel is set up in.
How do you add in the labels for that factor (e.g. Age, sex, income, rural residence) on the y-axis? . The way you have it right now is unclear. e.g. Age >86 66-75 76-85 Similarly, how do you add the legend in that lists the actual values of the hazard ratios and the confidence intervals?
How do we compare two groups with the same factor names (e.g. comparing groups A and B and both have factors from Women to rural vs Urban). I am trying to make a combined forest plot but the points for the odds ratio appear in line for both groups. I would like if group A was slightly higher than group B but both should be in line with women to rural vs urban.
this plot isn't correct. for an odds ratio to be plotted correctly, the space between 0.5 and 1 and the space between 1 and 2 should be equal. all other ticks would be spaced according to the graphic representation section on this link: en.wikipedia.org/wiki/Logarithmic_scale
The error bars are not adjusting accurately using excel 16. Is there a way to fix this? It seems like they don't adjust even after specifying the value
It has taken me a long time to figure out the misalignmnet (using Excel for Mac 2011)- but my problems were solved when I unticked "Horizontal axis crosses between categories" in the primary vertical axis. None of the other suggestions on here worked for me, but this one did. Great video Erica!
Hey guys, I am busy with my thesis that is due next week. My research is on systematic literature review. I need someone to assist me create a forest plot.
most comprehensive, clear and instructional vid I have ever come across on youtube...thx
This video is very helpful. I just wish it was not so difficult to make these graphs in Excel. Really appreciate your time and effort in explaining this clearly.
Excellent tutorial. In the end, I would like to add that if your CI's are very wide using the format axis option (for the axis with numbers) and choose the option "logarithmic scale base 10" with axis bounds set to auto. Then format axis for the axis with factors at set label position to "low".
Just what I needed! Great tutorial.
Only note I would add is depending on the version of Excel you have you may need to adjust your y-axis range if the scatterplot points don't align with your countdown/factor labels :)
Hi ! How do I do this ?
@@anjalyjohn5600 Before you delete the right hand axis (for the scatter plot) change its max bound to one higher than your amount of factors.
@@pauldahlke5618 Thanks to you! Finally it worked.
Thank you Erica!!
For those who may struggle with the very last part i.e. aligning bars to odds ratios, make sure to check out @Castor Huang and @pgbarnett's solutions down below in the comments section.
I had the same problem as other here that the ORs didn't align when using less factors than eight. Using four factors, I changed the countdown values to: 2,4 1,8 1,2 and 0,6 and the Ors aligned perfectly. So just try different values, starting from the highest number in the series and look how that changes the position of the marker in your graph. Using Excel for Mac.
Thank you! I had the same problem with 4 variables and your solution worked (Win10 here). I didn't think about using decimals, was experimenting with total valuies. Many thanks!
An excellent explanation for me. Thank you!
BEST! JUST THE BEST FOLLOWING EACH STEP. CONGRATULATIONS! THANKS!!!!
Wow - THANK YOU for this! Your explanations are perfect - even using a newer version of Excel I was able to suss out what to do.
This was SO helpful. Thank you so much!
THANK YOU! Amazing tutorial!
Thank you very much for this help! Wonderful video!
Lot of thanks for this tutorial .This is really helpful for me
Your explanation is perfect (clear and concise), thanks!
I am using Excel 2016, and for some reason, my odds ratio values do not position themselves on the tick marks: the values a dispersed and barely match with the labels on the left corner, they are lower on the graph than they should be. Been playing with the data for a while, and nothing happens--I will try again tomorrow to see if I can figure out a way around this.
Go to chart element "the + sign on the right of your graph" then axis then clic on secondary vertical axis,
go to format axis and make sure your axis are from 0 to number factors number +1.
now you can delete the axis so they wont show on your graph
@@ibrahimchoucair Thank you so much sir! This solved my problem! Huge lifesaver
@@ibrahimchoucair 6 years later and this comment SAVED me
Excuse could i know the fonction you have used to make the countdown?
I figured it out!!! when you are specifying the count for the odds do not highlight the space above the countdown
Thanks for sharing.....it was very helpful....awesome
Thank you very much, great, nice and informative video , good luck
Thank you so much Erica.........It was helpful :) Cheers
Thanx, it was extremely creative and HELPFUL :)
thank you for the excellent tutorial. really helpful
It turns out that there is a problem when using 12 variables ... I found a solution by adding 1 more empty column above it (in the explanation only 1 empty column above) ... hope it helps
Erica, can you let me know where I should click to get countdown? as you did so fast and can not see it.
How you are calculating countdown column. Is it simply the total number of factors or something else?
Thanks!, It was very helpful
thank you very much
Hi Erica, thank you for the video, but how do you select one gridline (value 1) to fill it/make it bold as seen in forest plots in published journal articles?
Thank you, this was extremely helpful. Can you individually change the color of individual circles? EVery time I try to chanche the color every circle changes color as well.
Hey Erica. Thanks for this great tutorial.
Can you tell me, how to calculate change in odd ratio if we increase 10 ug/3 pollutant concentration? If you have any video regarding this.
when I try to add in the odds ratios, the values seem to add in an a different y axis- and I cannot get the values to match with the original bar countdown values. I am using excel 2016 on a Mac.. Any suggestions?
Im having the same problem
For OR polts , you need to select your OR values column for X-axis data series, and the Countdown column for Y-axis data series. I'm using excel 2016
@@robertridde6914 Thanks for the reply. That what i did, reslts: same problem
I have the same issue, odds ratio scatter dots are not aligning with the bars
Gwml1jqgik
This was super helpful. Anyone know how to show two sets of data in the same plot area? For instance outcomes for two different groups, for instance different doses of medications. I'm looking to show OR for complications associated with two dose levels of same medication.
Hi Erica, thank you so much for this helpful video. I'm having an issue with my datapoint/label alignment, though. First of all, I'm seeing no tick marks at all on my Y axis, and second, when I go to change the "odds" data to scatter, it moves the datapoints further above the axis labels, and "positioning on tick marks" doesn't change it. Any advice for this issue?
awesome video helped me so much! However, my subjects have plotted themselves over the table rather than on the side ?
Thank you! Very helpful.
Very good, thanks
Thanks Erica. That was really useful. I managed to do a forest plot excel.
However, how do I make it neater by grouping the factors? For example, your 8 factors are actually based on 4 categories (gender, age group, income quintile and rural-urban residence). Can you make your plot such that there is an extra space between the 4 categories? I tried to do that and the plots are not in line with the y-axis categories. thank you!
You are amazing!!! thanks!
Hey Erica,
thanks for this great tutorial! However, I encountered the following problem:
When I add more than 8 factors and 8 odds ratios, I am not able to align factor bars and odds ratios (see video at 7:31). Do you have a solution for that problem?
many thanks!
Hello Philipp, thank you for your comment. How many factors in total do you have? I will play around with it on my end and get back to you. Thanks so much.
Hello, I have the same problem. Do you know how to solve it? Many thanks for the video
Hello, I also have the same problem as above. I have total 4 factors, and could you give me an advise, thanks for the video tutorial anyway!
You need to adjust the format secondary vertical axis given the number of factors you have, since excel 2016 will adjust the minimum and maximum of your axis automatically. For example, if I had 20 factors, I have to put 0 as my minimum and 21 (20+1) as my maximum axis. After doing so, my labels are all aligned. Hope this is helpful.
sorry where did you find this tab though?
How to interpret results?
Can you create the same video using an Excel 2016 version? It would be very helpful. Thanks!
Awesome! great job
Excellent!
Thanks a lot, But my countdown is always wrong.
I cannot use more than five variables, the bar chart becomes horizontal.
Thanks for the video- unfortunately if your 95% CI's go negative they overlap the labels... what can one do to align the labels to the right so they don't overlap?
Right click on the labels => Format Axis => Axis Options: Select "low" to align the labels to the left or "high" to align the labels to the right.
My countdown column counts up instead of down. Also, when I try to change the red bar to a scatter plot, my figure falls apart.
i have the same problem with changing the red bar to a scatter plot. Did you solve the problem?
The same here
Thank you so much Mam :)
Erica Lee: I am getting wrong Confidence Interval (custom selection) why?
hey erica, the vid is helping me a lot. but, i hve a doubt..
why OR needs to be minus with lower bound, since the lower values were there ady?? can we just use the ady existed lower values?? tq
No you cannot use lower values already existing ones. Because we are building error bars and we just need the difference for this.
Anyone know how to get a bold vertical line on the odds ratio of 1 so that the reference is well seen?
The only way I know how to do it is to manually add a line using the shapes tool.
You rock !
nice
There are many unnecessary steps there. You don't have to start with a bar chart, just add a new scatter chart and edit the X Y values. The vertical axis parameters should be adjusted and ready.
한국인 이신가여??
Thank you very much for this video super helpful. I would lover to follow you on twitter if you have one ;)
Your explanation is perfect (clear and concise), thanks!
I am using Excel 2016, and for some reason, my odds ratio values do not position themselves on the tick marks: the values a dispersed and barely match with the labels on the left corner, they are lower on the graph than they should be. Been playing with the data for a while, and nothing happens--I will try again tomorrow to see if I can figure out a way around this.
Regards,
C.
When you hover your mouse on one of your odds ratios, does the odds ratio value align with the countdown column value? For example, if you have an odds ratio of 5 and the countdown value is 8, the value should say (5, 8).-When adding labels to the y-axis, make sure labels (factors) are aligned with the bars
-Align the labels with the tick marks after you have plotted your odds ratios.
-Re-check your countdown column make sure it is counting down correctly. If none of these work, keep playing around with the empty cells. Leave it out when creating the y axis, leave one in and one out, or include the empty cells in when adding in the odds ratios. Also, try different major unit values. For me, it took a lot of trial and error getting everything to align perfectly. Let me know how it goes. Feel free to e-mail me your results if it’s still not working. Good luck!
Ok, that sounds great--will do. I appreciate your help, Erica.
Hi I'm having the same problem, please can you help? The odds ratio scatter dots do not align with the factors bars, the labels are the same from 1-12 on both bars and dots but one does not sit over the other.
Hi Amy. For the "Factors" and "Countdown" column, when you select it's values, do you select the empty cell above and below the values? You also may want to check your X "Axis Options". Make sure the major unit is .5. Is it your scatter dots that's aligned with your tick marks on the Y axis? Or is the factor labels aligned on the tick marks. Let me know how those go.
Hi - I'm having the same problem. When I make the graph with 8 factors, the OR dots and CI align properly to the factor labels on the Y axis. However, when I try making the graph with more than 8 (I need 10) factors, the numbers are all misaligned. I followed these instructions about when to select the empty cells, but it doesn't help. Any ideas?
Erica, your job is marvellous!
I just want to give you a hug!
Thank you Erica Lee for your detailed instruction.
However, I cannot find the option "On tick marks" in term of position of axis - I'm using Excel 2011 on Macbook.
I have researched on the internet but I cannot not find any solutions about it. Could you help give me your suggestion?
Thanks in advance!
Very helpful, thank you so much. If you want to show the P values how would you go about?
What equation !?!?!
Hello, I have some trouble, I have 28 factors and while the bottem labels of the factors are in line with the graphs (the dot and error bar) the graphs are everytime a bit to higher when you go one variable up so the first labels are totally out of place.
Edit: the bars are in place but the "dot and line" not in line with the bars
I think I had the same problem you did, and I realized I had to readjust the secondary vertical axis so that the maximum number was 1 above our highest count while the minimum was 0, and then the issue was fixed. Gave me a grey hair though.
Thank you so much!! I really didn't know how I was going to graph my odds ratio so this really helped!
Thank you so much for this video!! It is very clear with every step and so helpful, saved me so much time from having to search endlessly for other videos
Unfortunately this does not work with 10 or more factors.
Yes. It does not for 8 factors too. Do you know any video where i can watch how to do this?
thanks. really clear video! :)
Hi! The formula =(ROWS($A$4:$A$11)-ROW()+ROW($A$4:$A$11)) gives me #NAME error, could anybody help me? Thank you so much
I had the same problem. In the end the problem for me was that the language of Excel was in Dutch in my case. So if your excel program is in another language you should change the term ROWS into the language that your Excel is set up in.
@@LeonAruba Thank you so much for your help!
@@mariasanz8643 Did that fix it?
@@LeonAruba yes it did! thanks so much
I'm so thankful!!!!! :))))
How do you add in the labels for that factor (e.g. Age, sex, income, rural residence) on the y-axis? . The way you have it right now is unclear. e.g.
Age
>86
66-75
76-85
Similarly, how do you add the legend in that lists the actual values of the hazard ratios and the confidence intervals?
How do we compare two groups with the same factor names (e.g. comparing groups A and B and both have factors from Women to rural vs Urban). I am trying to make a combined forest plot but the points for the odds ratio appear in line for both groups. I would like if group A was slightly higher than group B but both should be in line with women to rural vs urban.
Thank you ma'am for the video,, it was very helpful. ma'am I am unable to align the x axis lebels to their respective odds ratios. kindly help
Many Many Thanks
this plot isn't correct. for an odds ratio to be plotted correctly, the space between 0.5 and 1 and the space between 1 and 2 should be equal. all other ticks would be spaced according to the graphic representation section on this link: en.wikipedia.org/wiki/Logarithmic_scale
The error bars are not adjusting accurately using excel 16. Is there a way to fix this? It seems like they don't adjust even after specifying the value
It has taken me a long time to figure out the misalignmnet (using Excel for Mac 2011)- but my problems were solved when I unticked "Horizontal axis crosses between categories" in the primary vertical axis. None of the other suggestions on here worked for me, but this one did. Great video Erica!
Hi Erica, great video. Is there any way to bold just 1 gridline, say the 1 at OR=1. Thanks!
Hey guys, I am busy with my thesis that is due next week. My research is on systematic literature review. I need someone to assist me create a forest plot.
Thank you. My committee loved the plot. And I guess the reviewers did too.
wow. this was very helpful and easy to follow. My forrest plot looks great. Thank you!
Hi Erica, you don't know how many times I said "I love you"
Hi
Is it possible to share your working excel sheet to work on it as a template.
Hey Erica, thank you so much for the tutorial. Is it possible to get rid of the grid lines and leave only the line that is on the #1? Thanks
Does not work under the Office'2013.
Dang. 3 and a half years later and still saving people like me at work haha. Thank you Erica
Thank you for sharing a good tutorial
Thanks Erica! This was causing me some trouble but fixed thanks to this!
Thanks Erica, I was wondering how to show the p values in the graph.
Thank you SO MUCH for this video! I was googling and searching for 2+ hours on forest plots. This was perfect. Helped me tremendously!
Thank You. Explained well.
Thank you Erica, you are a life saver
Everything is perfect!
Thank you SO MUCH for the great Tutorial. This was something which I needed - for my class presentation!
Excellent work. Thanks
THANK YOU THANK YOU THANK YOU!!!!
how to draw diamond estimate?
Erica, you did a phenomenal job! Thanks!
Very helpful thank you
very helpful thank you so much
Thank u, i apresiate your way
One question professor, from where you got the initial values, I mean lower, upper, and Odds?
That would be data extracted from literature or from your own investigations.
so helpful! thanks a bunch, Erica
Brilliant! Just what I needed. Thanks for the tutorial.
Fantastic guide through this