How to make forest plots using Microsoft Excel 2007. Thank you Jon Peltier for sharing your method. Countdown Column Equation: =(ROWS($A$4:$A$11)-ROW()+ROW($A$4:$A$11))
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.
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!
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 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 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?
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, 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.
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.
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!
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!
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?
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.
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!
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 :)
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.
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
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?
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
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
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?
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?
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?
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.
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
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:-(.
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.
