How to make forest plots in Microsoft Excel (meta analysis chart)
ฝัง
- เผยแพร่เมื่อ 29 มิ.ย. 2024
- Forest plots are great for showing lots of information in one chart (multiple effect sizes, odds ratios, confidence intervals, etc). Microsoft Excel doesn't naturally have a feature to easily make them, but using the method I show in this video, you're able to make great looking and flexible forest plots.
NOTE: The 'Value from cells' option is only available on Excel for Windows (thanks to Paola Rodriguez Giustiniani). Mac users can follow this link to see how you can set custom data labels in a chart: support.office.com/en-us/arti... - วิทยาศาสตร์และเทคโนโลยี
Thank you so much! Saved my academic life. I'll shout you out to whoever listens !!
Thanks, this was SUPER helpful. I’ve watched several YT videos on making forest plots in Excel, and your is by far the best. No unnecessary steps. Thank you!
Mitch, thank you! Great tutorial! Tried several other videos, but they didn't help. Your step by step advices are really helpful!
Thanks for the tutorial - you helped me a lot today! Lots of good tips & tricks.
A great video really! Solved my many weeks' lasting problem! Thank you so much! :)
Perfect, Many thanks, you made it so easy to imagine
Great video Mitch! Best excel video Inhave seen so far. Less cumbersome.
Wonderful, thanks so much for sharing. This is great!
Very helpful. Made a similar plot of diagnostic odds ratio + CI. Thank you!
This was a simple method. It was also easy to follow. It saved me so much time and effort. I am glad I did not use the recommended complex tools and chose to see this video instead.
Glad it was helpful!
Hi Mitch, congrats! Excelent tutorial video!
Thanks Guilhermo! Hope it was useful.
Thank you so much for a great video!
Perfect. Thanks from Vietnam.
This helped me tremendously. Thank you very much! :)
Glad it was helpful!
This is incredibly useful
Very helpful. My supervisor will be pleased.
This video helped me so much!
Extremely helpful
Very helpful!
Very neat tutorial, have a like. :-)
Nice explanation and video! Thanks Mitch.
Would you mind sharing how to calculate (the formula in excel) effect size and 95% CI?
Thank you. I appreciate your help
Formulas are in this file: drive.google.com/uc?export=download&id=1YttEO1uXcSIDlYe_-pfYfsZIhcPIF5bD
Hi Mitch and thank you so much, for the best tutorial in this subject! I was wondering if you have solutions to a few problems I have - I would like the numbers on my X axis to appear only below the bars and not to extend to where the labels are. I would also like to create an empty space beneath and above the y axis labels and to add a few labels beneath and above the chart. Thank you again,
very much!
Hi, I'm not sure the first part is possible in Excel. If a static image is all you need, then you could try pasting an image of the chart into image editing software like Inkscape or Illustrator. The second part might be possible by including blank cells in the ranges of the y axis values and labels but it's not something I've tried.
Thank you very much, this is a great tutorial! How did you align the effect sizes so neatly? It looks like point decimal alignment. Please tell me your sorcery :)
Haha thanks but no sorcery here. Do you mean where the points are placed along the x-axis (i.e. left to right alignment)? The x-axis values are very detailed (at least 9 decimal places), you can see them in the B column of the data in the video.
If you'd like to play around with the same data that I used in the video, you can download my .csv file here: drive.google.com/uc?export=download&id=1sToIO7gU60CccMUPaRW7iLYaF3jdp60m
Mitch,
Great video!
Can I ask:
“Are these truly 95% confidence intervals, or are they some other measure of variation (e.g., standard errors or standard deviations)?”
Thanks Joseph. These are 95% confidence intervals (1.96 x SE).
This is wonderful, thanks. I could reproduce it. Is there a way the x-axis values would not extend to the names of the sport teams and other extra information on the plot. Also, can we add labels to each of the data on the plot, like the point estimate +_ confidence interval?
No problem.
You can choose whatever x-axis values you want so the y-axis labels will fit. This is just done by manual trial and error until you find a value that fits.
Yes, you can add data labels to the points on the plot by clicking on one (thus selecting them all), selecting 'Add Chart Element' and then 'Data Labels'. Custom labels can be added using the same method I showed in the video.
how do you get the error bar to go to the actual value on the graph, not +/- of it?
Not exactly sure what you mean, but it sounds like you're describing a bar chart? If the error bars only went to the actual value, they wouldn't be error bars.
Thank you very much for your video. After I failed miserably with some customizations in R, I am back to Excel as well. I just wonder how you managed to adjust the colors differently for the different teams and their respective dot?
Best regards,
Robert
I just did this manually (clicked on an individual dot so it was selected and then changed the colour). I think this is the only way sadly.
@@mitch.henderson How did you change the color of the text individually without changing to the same color for all the text? :)
Best regards,
Dane Dane
@@danedane6613Clicking on a bit of text selects all the common bits of text, and if you click on a specific bit of text again, it'll select only that part. You can then format it individually.
@@mitch.henderson Thanks :) I used another youtube video for the initial forest plot, so I had to go the the beginning of your video to better understand it :) Only problem is, that Excel reverts the colors everytime I zoom in. Dough. And Ctrl + Z doesn't work🤦♂
Hi Mitch. Thank you so much for the tutorial. May I know how to add name labels on top of the chart? Is it the same steps like adding right and left labels?
Hi, for the title and subtitle in my example I just created extra space above the plot area by clicking within the plot and dragging the upper edge down. Then I placed a text box in the top corner and formatted the text how I wanted. Hope that helps!
I have upper and lower 95% CI that are not quite as symmetric as your values. How do I add those?
Hi Mitch,
Excellent tutorial video. When I try to label my data "value from cells" does not appear so I cannot add my y axis labels? Any idea on why this might be? I'm using the latest Excel.
Hi Alex,
Are you using Excel for Mac? If so, the 'Value from cells' is not available. You can achieve the same outcome using the method described here: support.office.com/en-us/article/set-custom-data-labels-in-a-chart-272a4658-0dab-4fe1-9248-6463207fe595?ui=en-US&rs=en-US&ad=US
It is a little more complex unfortunately.
@@mitch.henderson Thanks Mitch,
I'm using the latest Office on Windows 10 so not sure why this isn't there. I'll look into the alternative version you have provided. Thanks again
Hi Mitch,
When making a graph using this method for a dataset with data from two years, which confidence interval should be graphed, the one from year one or from year two?
Thanks
If I understand correctly, you should calculate it based on the pooled dataset (all the data from both years combined).
@@mitch.henderson Thank you!
Wonderful tutorial, but what about the overall effect size "the diamond"?
Yeah I haven't come across ways to achieve this in Excel. There may be some hacky ways to do it in other YT videos, but I'd recommend using more dedicated statistical software like Jamovi (free, open source, point and click interface) or R (free, open source, requires programming knowledge).
Hello, I am attempting to make one of these but I just don't understand how to set up the 95% confidence intervals .Do I need to have a mean? I am confused. I viewed your formulas in Excel but I am unable to follow. Could you please give me some more information? I would really appreciate it.
Hi @ChynaTropic19, I've not done a meta analysis before personally so I can't help sorry. I believe confidence interval calculation for meta analysis is different to what I've done here. The purpose of my video is simply for the visualisation once you have the statistics calculated.
Excellent Mitch, thank you so much. Do you perhaps have R code for this?
This was all done in Excel, so no R code unfortunately.
Is it the analysis or the visualisation that you're interested in writing code for? I could point you in the direction that I would go to code this.
@@mitch.henderson Hi Mitch mainly the visualisation.
For excel i tried, but i have values with very wide confidence interval so they overlap with the right legend. I am not sure how to clip/limit them with an arrow of some sort.
@@tr-zd8nv You can adjust where the labels sit on the x axis by changing the values in the E (left labels) and F (right labels) columns in the video. I just used 1.4 and -1.4 because that fit my data, but you could make this number bigger so it doesn't cut off your confidence intervals.
Dear Mitch Henderson
Thank you pretty much for this excellent and informative video on forest plots. I would like to kindly ask you to provide me with the excel spreadsheet that you used in this video. Thanks in advance for your kind cooperation and looking forward to hearing from you. with my best regards
Hi Dr El-Nahhal.
I hope you found the video valuable.
You can download the .csv file that I used here:
drive.google.com/uc?export=download&id=1sToIO7gU60CccMUPaRW7iLYaF3jdp60m
@@mitch.henderson Thanks so much for your fast reply and for sending me the .csv file. I appreciate it highly and of course that I found your video valuable.
best regards
Hi @Mitch Henderson. I would like to ask you to answer some questions and provide me with some explanations about the following issues :
1. Please add full name to PTS
2. how you calculate games/ team =82
3. columns G and H why did you choose the numbers -1.4 and 1.4 and similarly columns J and K -0.2, 0.2
4. It is also very helpful to add names to the columns F-K, SHEET 4.
Thanks in advance for your kind cooperation and looking forward to hearing from you.
Best regards
lastly, how can we cite this work.
Hi @@dribrahimel-nahhal2477 , my apologies. I've linked the wrong file!
The correct file that I used (in .csv format) is here: drive.google.com/uc?export=download&id=1sToIO7gU60CccMUPaRW7iLYaF3jdp60m. I've also updated the link above so it is correct now also.
To answer your questions:
1. The PTS column in the incorrect file was only used to calculate the effect sizes and confidence intervals
2. Each team in the NBA plays 82 games in a season
3. Those columns show those numbers because that is the position on the x axis that I want for the text labels (-1.4 , 1.4) and the dotted vertical line (-0.2 - 0.2). I discuss this in the video at 4:28 (for the text labels). If you look at the finished figure, notice the team names are aligned to the -1.4 x axis value, and the effect size ± 95%CI are aligned to the +1.4 x axis value, that's what I'm specifying in these columns. It sometimes takes a little trial and error to find the position that works best.
4. Yes I completely agree, the correct file (now linked above) has these labels (as shown in the video).
No need to cite this work, simply pass this video on to anyone who you think will benefit from it 😊
Do we need to include the weights as well?
Hi Ng Wei Kei, I'm not sure what you mean by weights? Can you please clarify for me and I'll try to help.
is that effect size means expected frequency?
Hi!
No, effect size is a simple way of quantifying the difference between groups. I've used Cohen's d in this circumstance. You can learn more about effect sizes here: www.simplypsychology.org/effect-size.html
I am not able add the label as you have selected label from the column or call
Please explain it any other convenient way
Thanks
Hi Dr. Wagay,
Are you using a Mac? If so, the 'Value from cells' is not available.
You can achieve the same outcome using the method described here: support.office.com/en-us/article/set-custom-data-labels-in-a-chart-272a4658-0dab-4fe1-9248-6463207fe595?ui=en-US&rs=en-US&ad=US
It is a little more complex, unfortunately.
I have tried all these steps and I failed on this particular step because I could not add the author names in place of Y-axis, actually it adds the numbers but not particular column which I need.
Thanks for your quick response
How do you work out the confidence level?
It is a little complex, but this article explains it nicely. Just remember that in this case, I've calculated a confidence interval around the effect size (not raw points).
www.dummies.com/education/math/statistics/how-to-calculate-a-confidence-interval-for-a-population-mean-when-you-know-its-standard-deviation/
How do you change colors of an individual error bar?
The error bars in my video are all black, I've only changed the colour of the points. I'm not sure if changing individual error bars is possible?
@@mitch.henderson I’d also be interested in how to change the color of individual dots.
@@esperanzazagal7241 you need to select each point individually by clicking on them twice (once selects all the points, twice selects only the point you are clicking on) and changing the fill colour.
@@mitch.henderson somehow I can’t select an individual dot, not sure why