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!
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.
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 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.
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".
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!
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.
Hey, Ricardo! Não estou a conseguir fazer. Dá-me erro coma fórmula mesmo mudando as células - (ROWS($A$4:$A$11)-ROW()+ROW($A$4:$A$11)) A ti não te deu erro?
@@nunodenoronha se estiver usando o Excel em português, substitua o ROW/ ROWS por LIN/LINS. Eu também não consegui nessa parte, até perceber que era o idioma
@@ricardomorato2875 Só mais uma dúvida: Depois de você escrever LIN/LINS e alterar o número das células, a série de números que lhe apareceu estava correta? A minha série de números começa em 4 e acaba em 7, não em 1.
@@nunodenoronha se vc começou sua série de fatores na linha 4, é só alterar o segundo valor para o número da última linha que contém fator. Pelo que vc falou, na sua tabela seria de 4 a 7, enquanto no vídeo foi de 4 a 11
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!
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?
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.
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?
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?
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.
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.
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!
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?
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
Thanks for the video. Unfortunately, I'm experiencing the same problem as most users who commented your video. I'm working with Word for Mac and, although I followed carefully your guidelines, I'm unable to align the bars with the thick marks, let alone the odd ratios:-(.
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.
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?
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?
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
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
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.
Erica, your job is marvellous!
I just want to give you a hug!
most comprehensive, clear and instructional vid I have ever come across on youtube...thx
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!
Dang. 3 and a half years later and still saving people like me at work haha. Thank you Erica
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.
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 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?
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".
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! I was googling and searching for 2+ hours on forest plots. This was perfect. Helped me tremendously!
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!
wow. this was very helpful and easy to follow. My forrest plot looks great. Thank you!
Thank you. My committee loved the plot. And I guess the reviewers did too.
An excellent explanation for me. Thank you!
Thank you for sharing a good tutorial
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.
Thank you! You have just helped me a lot! Made some forest plots for my master thesis. Thankssss.
Hey, Ricardo! Não estou a conseguir fazer. Dá-me erro coma fórmula mesmo mudando as células - (ROWS($A$4:$A$11)-ROW()+ROW($A$4:$A$11)) A ti não te deu erro?
@@nunodenoronha se estiver usando o Excel em português, substitua o ROW/ ROWS por LIN/LINS. Eu também não consegui nessa parte, até perceber que era o idioma
@@ricardomorato2875 Muito obrigado pela sua ajuda! Um abraço
@@ricardomorato2875 Só mais uma dúvida: Depois de você escrever LIN/LINS e alterar o número das células, a série de números que lhe apareceu estava correta? A minha série de números começa em 4 e acaba em 7, não em 1.
@@nunodenoronha se vc começou sua série de fatores na linha 4, é só alterar o segundo valor para o número da última linha que contém fator. Pelo que vc falou, na sua tabela seria de 4 a 7, enquanto no vídeo foi de 4 a 11
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!
Excuse could i know the fonction you have used to make the countdown?
Very helpful, thank you so much. If you want to show the P values how would you go about?
Everything is perfect!
Thank you SO MUCH for the great Tutorial. This was something which I needed - for my class presentation!
Thank you very much for this help! Wonderful video!
Erica, you did a phenomenal job! Thanks!
Thank you Erica, you are a life saver
I figured it out!!! when you are specifying the count for the odds do not highlight the space above the countdown
THANK YOU! Amazing tutorial!
Thanks Erica! This was causing me some trouble but fixed thanks to this!
Lot of thanks for this tutorial .This is really helpful for me
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!
Brilliant! Just what I needed. Thanks for the tutorial.
BEST! JUST THE BEST FOLLOWING EACH STEP. CONGRATULATIONS! THANKS!!!!
Thank You. Explained well.
Thanks for sharing.....it was very helpful....awesome
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 tutorial was so so so amazing and helpful even though I was not using the same version of Excel. Thank you so much!
Excellent work. Thanks
Thanx, it was extremely creative and HELPFUL :)
Thank you very much, great, nice and informative video , good luck
Thank you so much Erica.........It was helpful :) Cheers
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.
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 for the excellent tutorial. really helpful
I'm so thankful!!!!! :))))
Fantastic guide through this
Erica, can you let me know where I should click to get countdown? as you did so fast and can not see it.
so helpful! thanks a bunch, Erica
Hi Erica, great video. Is there any way to bold just 1 gridline, say the 1 at OR=1. Thanks!
How you are calculating countdown column. Is it simply the total number of factors or something else?
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?
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?
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.
Thanks Erica, I was wondering how to show the p values in the graph.
You are amazing!!! thanks!
Very good, thanks
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
awesome video helped me so much! However, my subjects have plotted themselves over the table rather than on the side ?
Very helpful thank you
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!
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?
Excellent work!
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
thanks. really clear video! :)
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
This is a pretty helpful tutorial
Hi Erica, you don't know how many times I said "I love you"
thank you very much
Thanks for the video. Unfortunately, I'm experiencing the same problem as most users who commented your video. I'm working with Word for Mac and, although I followed carefully your guidelines, I'm unable to align the bars with the thick marks, let alone the odd ratios:-(.
same problem
Thanks!, It was very helpful
Thank you! Very helpful.
Awesome! great job
Can you create the same video using an Excel 2016 version? It would be very helpful. Thanks!
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.
Excellent!
Hi
Is it possible to share your working excel sheet to work on it as a template.
Great Tutorial !!!
very helpful thank you so much
THANK YOU THANK YOU THANK YOU!!!!
This is great! Thanks!
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.
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 to draw diamond estimate?
Many Many Thanks
How to interpret results?
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.
Thank you so much Mam :)
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
Thank you, this was so helpful!
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
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.
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.
Erica Lee: I am getting wrong Confidence Interval (custom selection) why?
Thanks a lot, But my countdown is always wrong.
Thank u, i apresiate your way
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.
@@Leon1Das-MJPZ Thank you so much for your help!
@@mariasanz8643 Did that fix it?
@@Leon1Das-MJPZ yes it did! thanks so much
You rock !
I cannot use more than five variables, the bar chart becomes horizontal.
What equation !?!?!