How to make forest plots in Microsoft Excel (meta analysis chart) 

Glad it was helpful!

Glad it was helpful!

Thanks Guilhermo! Hope it was useful.

Formulas are in this file: drive.google.com/uc?export=download&id=1YttEO1uXcSIDlYe_-pfYfsZIhcPIF5bD

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.

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.

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

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 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🤦‍♂

Thanks Joseph. These are 95% confidence intervals (1.96 x SE).

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.

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

If I understand correctly, you should calculate it based on the pooled dataset (all the data from both years combined).

@@mitch.henderson Thank you!

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

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.

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.

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 😊

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.

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

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/

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

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