A forest plot is an efficient figure for presenting several effect sizes and their confidence intervals (and when used in the context of a meta-analysis, the overall effect size) (.pdf). They can be created in a variety of tools, including R and meta-analytic software. Here I will describe how to create these plots using Excel.* *Note*: It is very possible (if not likely) that this entire task is easier with R or with other meta-analytic software. The purpose here is to show how this can be done with a tool that many of us are familiar with (if fact, I will assume that you have working knowledge about Excel).

Some people offer special Excel meta-analysis plugins to create forest plots, but these are not necessary. Instead, I will build on a short peer-reviewed paper that describes how to make forest plots in Excel (see their template). In short, I borrow their method and expand it to show how it can be very customizable. I used Excel 2007 and I know that Excel 2010 is very similar. I don’t know how either newer or older versions of Excel will work. [Update: These instructions seem to work well enough with Excel 2013 too.]

The key insight for making forest plots in Excel is that scatter plots do not need to be used to make scatter plots. Instead, they can be treated as a blank canvas where you place things using X-Y coordinates. I will make a forest plot of the association between religious fundamentalism and the feeling thermometers for 20+ groups from the 2012 ANES (higher numbers = more fundamentalism and more positive feelings).

You can find the Excel file to use as a template here.

1. Setup your Excel spreadsheet like the figure below. The first column is the list of the target groups I looked at in the ANES. The second column is the order that I want them in for the forest plot. They are ordered so that in the forest plot they range from most positive at the top to the most negative at the bottom. The next two columns are the low and high values of the 95% confidence intervals of the correlation.** And the last two columns are the CIs that are used when making the figure (distance between the CI and the r).

2. Create a scatter plot using the second column as your Y-axis values and the third column (with the correlations) as your X-axis values. Make several adjustments: (a) Adjust the y-axis to range from 0 to 24 (in the case of the current data) and remove all tick marks and labels. (b) Get rid of the gridlines. (c) Adjust the x-axis to a range that makes sense (for this example, -.80 to +.80 works well) with a major unit that makes sense (for this example, .2). (d) Make the data points look good. I prefer black squares, adjust the size as it makes sense of your application. This is the start.

3. Now add in custom horizontal confidence intervals using the last two columns. (more info on adding custom error bars in Excel) This, more or less, gets you to the place where previous examples will get you, typically with an added suggestion to use text boxes to add labels. Don’t use text boxes. There is a better way…

4. Now you need to add a new series to the scatterplot that you will use to place the labels (in this case, the names of the groups). I added a new column with the entry -.58 for all of the groups (this number can be modified based on aesthetics). Then create the new series with this new column as the x-values and the same y-values as before. This will make a vertical line of markers on the left side of the figure and these new data points will be used to anchor your labels. Now, make those invisible by removing their fill and their border.

5. Right click on the now invisible markers and click “add data labels”. Right click again and click “format data labels”. Select “left” for label position.

6. Now select the “1” label, put an “=” in the formula box, and then click on the label for the 1 position (Atheists). Do this for each of the labels and soon you’ll have labels for each of the groups. (here is another explanation for how to add custom labels with clearer step-by-step instructions for this step)

7. Now, you can finish things up. Most concretely: Add a label at the bottom.

But you can also go further if you’d like. You can add vertical lines to indicate heuristic cutoffs for small, medium, and large effects. You can text information about the size of the effects. You can change the color or shape of the labels to highlight different comparisons (e.g., effects that are p < .05 different from zero). See below for an example with some of these additions.

You can find the Excel file for both of these forest plots here.

*Note that Cumming’s Excel files for his book can also help with forest plots, but they are not always as customizable and flexible as I’d like.

**The sample size here is >5000, so these CIs are preeeeetttttty small.

You are my hero! Thanks so much, this was exactly what I needed… works perfectly!

Best from the Netherlands!

I have to echo that- you are awesome! This makes my work easy.

Is it possible to make a similar plot with two lines? I want to compare genders.

I can’t quite visualize what you have in mind. Could you link to an example?

Thanks for doing this – it’s very helpful. Just wondering how to add more vertical dotted lines?

You just need to add another data series to the figure, like the series already used for the vertical lines (see the files that you can download in the post). You can adjust the location of the lines by adjusting the x-axis values.

Thanks Mark. Got it sorted now.

Is there any way to manipulate the size of the data points to reflect the effect size?

You can’t do it in a smooth and easy way, but you can do it.

If you know what the size differences need to be, you first select an individual data point and then you change the size of that individual data point. You do this for each data point. It isn’t ideal because you’ll need to calculate the sizing of each point yourself, but it should do what you want.

The nice thing about this variation on forest plots is that you can edit it the same ways you can edit any scatter plot in Excel.

Thanks MB, the information in te post really helped!

For reference, re: effect sizes, what I did was use the standard error to rank all the studies into deciles. The deciles could then be used as a marker for data point size (e.g. 1st decile = point size 1, 9th decile = point size 9, etc).

Can you tell me how did you get numbers in summary D12 and E12? Anyway thanks for great work!

Sure. D12 is just a simple correlation and E12 is the lower bounds of the 95% confidence interval. I calculated the former with SPSS and the latter with this online calculator: http://vassarstats.net/rho.html

And which excel boxes you used for D12 and which for E12? Sorry for bothering you!

Hi Dani — I’m sorry, but I don’t think I quite understand your question? Can you try asking it in a different way?

I do not understand which numbers in the Table you used for the calculation of D12 and E12. Thanks for replies!

D3 to D25 are correlations calculated from a different data set. E3 to E25 are the lower bounds of confidence intervals for those same correlations. These are not calculated from any table in the spreadsheet. Perhaps we are looking at different spreadsheets. This is the one I’m looking at: https://drive.google.com/file/d/0BwzeLDhDo7seY2lfT21zU0N2STA/edit?usp=sharing

Alternatively, you could also use Bias-corrected accelerated CI (calculated in SPSS via the bootstrap function)

Many thanks for providing this

Hi, how can we create these plots for Odds Ratios?

You can just replace the correlation coefficients with the OR. And then the upper and lower bounds of the OR in the 95% CI columns.

First, this is amazing. Thank you! I have a question – I want to add a lot more rows to chart than your starter file, and for the life of me, I cannot figure out what I’m doing wrong. I’ve edited all the series references, the error bar references – everything. But none of my new rows are showing up. What am I missing? HELP!

I totally figured it. DUH. I needed to review the instructions again when I wasn’t tired. Y axis adjusted!

I’m glad it worked out!

Thanks for the great template. I have used it several times. However, I can’t figure out how to add rows into the template. Do you have any suggestion? Thanks

You should be able to add rows just like you would any other Excel file. You will also need to make sure the additional info needed to place the labels etc are also input into the new row.

thank you sir

Thank you so much. Well explained. I have a doubt: why are we not directly using the lower and upper limits? why are we calculating them again for figures (lower limit= odds ratio-lower limit and upper limit = upper limit-odds ration). can’t we use the original upper and lower limit values directly?

Hi Dolly. As far as I understand it, the CI function in Excel uses the “length” of the CI. So, you need the distance between the upper/lower values and the estimate. But perhaps there is another way to do it that I’m not aware of?

Hi MB, do you have any idea on how to calculate Effect size. Thanks in advance

There are many different ways depending on the goals of your project and the types of analyses that you are doing. There are a lot of helpful, online resources for this. E.g., this might get you started: https://www.theanalysisfactor.com/calculate-effect-size/

Thank you for this very useful guide!

Is there any way to set limits for the x-axis so that it only ranges within the plot space and doesn’t overlap with the Target Groups-column and the r-column?

For example, could you set the limit for the x-axis to -0.45 without cutting out the Target Groups-column from the plot?

Yes, that is possible in principle. You need to adjust at what points the target groups are linked with. E.g., you could move them in to be at -.44.