Microsoft Excel - Forest Plots (Odds Ratios and Confidence Intervals)

[@barbaragalland9387]

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!

[@tigerlitam9422]

Dang. 3 and a half years later and still saving people like me at work haha. Thank you Erica

[@gm-xx4fh]

most comprehensive, clear and instructional vid I have ever come across on youtube...thx

[@awong8253]

Erica, your job is marvellous! I just want to give you a hug!

[@ericengels2116]

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.

[@mayraysann7480]

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 :)

[@anjalyjohn5600]

Hi ! How do I do this ?

[@pauldahlke5618]

@@anjalyjohn5600 Before you delete the right hand axis (for the scatter plot) change its max bound to one higher than your amount of factors.

[@meenamaharjan3882]

@@pauldahlke5618 Thanks to you! Finally it worked.

[@bjfiero47]

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".

[@thembekileshato6243]

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.

[@onehanaya]

Thank you SO MUCH for this video! I was googling and searching for 2+ hours on forest plots. This was perfect. Helped me tremendously!

[@Ftrtts.]

Thank you so much!! I really didn't know how I was going to graph my odds ratio so this really helped!

[@VictorAntares]

Thank you. My committee loved the plot. And I guess the reviewers did too.

[@suprateekat5338]

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.

[@chukwuemekaogbu8037]

Yes. It does not for 8 factors too. Do you know any video where i can watch how to do this?

[@meritkullinger3226]

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.

[@uacwior]

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!

[@painless263]

wow. this was very helpful and easy to follow. My forrest plot looks great. Thank you!

[@tinlizzyification]

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.

[@user-nl8nt5kb4y]

Thank you for sharing a good tutorial

[@razanamoud1136]

I figured it out!!! when you are specifying the count for the odds do not highlight the space above the countdown

[@summoncaster]

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.

[@ibrahimchoucair]

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

[@nadiajalaludin7877]

@@ibrahimchoucair Thank you so much sir! This solved my problem! Huge lifesaver

[@ricardomorato2875]

Thank you! You have just helped me a lot! Made some forest plots for my master thesis. Thankssss.

[@nunodenoronha]

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?

[@ricardomorato2875]

@@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

[@nunodenoronha]

@@ricardomorato2875 Muito obrigado pela sua ajuda! Um abraço

[@nunodenoronha]

@@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.

[@ricardomorato2875]

@@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

[@shuktatube5333]

An excellent explanation for me. Thank you!

[@fikrewold]

Everything is perfect! Thank you SO MUCH for the great Tutorial. This was something which I needed - for my class presentation!

[@jjfitzpa]

Thanks Erica! This was causing me some trouble but fixed thanks to this!

[@diegochambergo-michilot1975]

BEST! JUST THE BEST FOLLOWING EACH STEP. CONGRATULATIONS! THANKS!!!!

[@mindycurran714]

This tutorial was so so so amazing and helpful even though I was not using the same version of Excel. Thank you so much!

[@olusegunadewaleadeyemi2499]

Thank you Erica, you are a life saver

[@chappahx]

Erica, you did a phenomenal job! Thanks!

[@lauriekincadema8487]

THANK YOU! Amazing tutorial!

[@AyanaApril-Sanders]

Brilliant! Just what I needed. Thanks for the tutorial.

[@cescherkoyo6165]

Excuse could i know the fonction you have used to make the countdown?

[@esawibowo5711]

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

[@traviswhitfill509]

This was SO helpful. Thank you so much!

[@maribres]

Thank you very much for this help! Wonderful video!

[@novel890]

Lot of thanks for this tutorial .This is really helpful for me

[@theflamekid]

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

[@giuliadiicorsica]

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.

[@markeid11]

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?

[@daviskibo1]

Im having the same problem

[@robertridde6914]

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

[@daviskibo1]

@@robertridde6914 Thanks for the reply. That what i did, reslts: same problem

[@flash2hridya]

I have the same issue, odds ratio scatter dots are not aligning with the bars

[@nycollassilva3558]

Gwml1jqgik

[@locpham6660]

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!

[@pragalathannaidoo7845]

Thank You. Explained well.

[@rominovriadi8387]

Thanks for sharing.....it was very helpful....awesome

[@silulekomkhize7839]

Very helpful, thank you so much. If you want to show the P values how would you go about?

[@enriquevalentin5245]

I'm so thankful!!!!! :))))

[@ZiaKhan-ox5ih]

Thank you so much Erica.........It was helpful :) Cheers

[@summoncaster]

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.

[@ericalee25]

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!

[@summoncaster]

Ok, that sounds great--will do. I appreciate your help, Erica.

[@amyfeatherstone781]

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.

[@ericalee25]

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.

[@MWLippitt]

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?

[@pooloperation]

Fantastic guide through this

[@attilatordai4658]

Thanx, it was extremely creative and HELPFUL :)

[@hameedksn6116]

Thank you very much, great, nice and informative video , good luck

[@aakashrahman7652]

Excellent work. Thanks

[@ashstatbhu1]

How you are calculating countdown column. Is it simply the total number of factors or something else?

[@laaaaarrnaaatt]

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?

[@HuyNguyen-rv9ge]

Erica, can you let me know where I should click to get countdown? as you did so fast and can not see it.

[@abbyscurfield2035]

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?

[@nmcand01]

so helpful! thanks a bunch, Erica

[@nicolasvanderlinden3668]

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:-(.

[@patrickmcdonald1913]

same problem

[@achintamallick7302]

thank you for the excellent tutorial. really helpful

[@petraschultz384]

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.

[@diegoramonfaur7837]

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.

[@kristianj.salasespinoza3582]

Thanks Erica, I was wondering how to show the p values in the graph.

[@ferhasyed1698]

thanks. really clear video! :)

[@jaipism]

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.

[@haihuang7529]

thank you very much

[@drlalatendumoharana]

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

[@philippgild4376]

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!

[@ericalee25]

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.

[@mathildebourgneuf7579]

Hello, I have the same problem. Do you know how to solve it? Many thanks for the video

[@nobaksa]

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!

[@castorhuang1881]

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.

[@soccerbabez45]

sorry where did you find this tab though?

[@leahdodds9242]

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

[@majoramirez7767]

You are amazing!!! thanks!

[@mamadouadamasow9477]

Very good, thanks

[@jerryli2436]

Excellent work!

[@weikewang8108]

Hi Erica, great video. Is there any way to bold just 1 gridline, say the 1 at OR=1. Thanks!

[@angan2selalu]

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!

[@ahshanulstatistician6644]

Many Many Thanks

[@rauf208]

Thanks!, It was very helpful

[@davidrestrepo2927]

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

[@toyvousamaki]

Very helpful thank you

[@jordaneke423]

awesome video helped me so much! However, my subjects have plotted themselves over the table rather than on the side ?

[@bobrydzewski9195]

Thank you! Very helpful.

[@ayokunleolagoke5541]

This is a pretty helpful tutorial

[@cassioveludo8703]

Awesome! great job

[@statisticstime4734]

Excellent!

[@osupermono]

Hi Erica, you don't know how many times I said "I love you"

[@ongonz]

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.

[@katiebeckerwa]

THANK YOU THANK YOU THANK YOU!!!!

[@williamlouis5883]

Thanks a lot, But my countdown is always wrong.

[@akanshabatra4958]

Great Tutorial !!!

[@wanis44]

This is great! Thanks!

[@geethas.maniam1943]

I cannot use more than five variables, the bar chart becomes horizontal.

[@gregrhee9481]

Can you create the same video using an Excel 2016 version? It would be very helpful. Thanks!

[@boudebabaabdelghafour3000]

very helpful thank you so much

[@UrvishPatel]

Erica Lee: I am getting wrong Confidence Interval (custom selection) why?

[@edwardchau5818]

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?

[@anthonybavry2642]

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.

[@andreib.8565]

i have the same problem with changing the red bar to a scatter plot. Did you solve the problem?

[@sherinhanem]

The same here

[@jazandriz]

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?

[@kellykang8421]

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.

[@elisabethlangelier2764]

What equation !?!?!

[@MissHurleyWallace]

Thank you, this was so helpful!

[@athirahnordin6822]

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

[@khalodygaming3904]

No you cannot use lower values already existing ones. Because we are building error bars and we just need the difference for this.

[@carolynhall7923]

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

[@moisesdiaz1982]

Thank u, i apresiate your way

[@jenniferangel9803]

Thank you so much Mam :)

[@mitochondria2829]

One question professor, from where you got the initial values, I mean lower, upper, and Odds?

[@LeonAruba]

That would be data extracted from literature or from your own investigations.

[@endreszigethy5704]

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.

[@marias4137]

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

[@LeonAruba]

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.

[@mariasanz8643]

@@LeonAruba Thank you so much for your help!

[@LeonAruba]

@@mariasanz8643 Did that fix it?

[@mariasanz8643]

@@LeonAruba yes it did! thanks so much

[@samkab677]

How to interpret results?

[@top10trendingitems88]

Hi Is it possible to share your working excel sheet to work on it as a template.