How to Create a Forest Plot in Excel?

Also known as the “blobbogram”, a Forest Plot is a graph showing a visual comparison of the scientific or clinical studies conducted on a particular case or study. It is often used in medical and epidemiological fields for the meta-analysis of clinical trial results. What’s great about it is that you can view the outcome of several studies on a particular subject concurrently.

Unfortunately, Forest Plot is not readily available in Excel, so you can’t simply insert one. There is, however, a workaround for this, and I’ll show you how to do just that. 

How to Create a Forest Plot in Excel?

STEP 1: Prepare the dataset.

A forest plot would require the following basic information:

  • Name of the Study
  • Effect Size – refers to the weight of the study. For this field, you can use the Odds Ratio, Hazard Ratio, or Mean Difference.
  • Lower CI – represents the lower 95% Confidence Interval (CI) of every Effect Size.
  • Upper CI – represents the upper 95% CI of every Effect Size.
Sample dataset containing the required fields for a Forest Plot.

Aside from these four fields, we will need to add three more columns to create the forest plot in Excel.

Next to your dataset, add the following column headers:

  1. Position
  2. CI Line Lowest
  3. CI Line Highest
Add these 3 additional headers to your dataset: Position, CI Line Lowest, and CI Line Highest.

Position  

We will use this field to specify where the dot should be on the y-axis. On the first cell, enter the number 0.5.

Next, add a formula to the remaining cells to add 1 to the cell that comes before it (see example below). 

Sample formula to use for the Position column.

CI Line Lowest

To draft the horizontal CI Line in our forest plots, we need to calculate the lowest and highest CI for each Effect Size.

In this column, add the following formula to subtract the Lower CI from the Effect Size (in my case, the Odd Ratio):

=[Effect Size] - [Lower CI]
Sample formula to add to the CL Line Lowest column.

CI Line Highest

In this column, use the following formula to subtract the Effect Size from the Upper CI.

=[Upper CI] - [Effect Size]
Sample formula to add to the CL Line Highest column.

STEP 2: Insert a 2-D Horizontal Bar Chart.

Highlight the first two columns in the dataset (Study and Effect Size).

Highlight the first 2 columns in the dataset.

Go to the Insert Tab. Click the top left button after the Recommend Charts.

Steps to insert a clustered bar.

From the list of 2-D Bar charts, select the Clustered Bar (the first one in the list).

You should now have your horizontal bar chart inserted on your sheet.

Sample horizontal bar chart in Excel.

STEP 3: Move the Vertical Axis Labels to the Left of the Chart.

If you have negative values for the Effect Size, the bars on your chart will likely overlap with the vertical axis labels. It will look something like this: 

Example of what happens when there's a negative value in a horizontal bar chart.

If this is the case, you need to adjust the vertical axis to position it towards the left of the bars.

NOTE: You can skip this step if you only have positive values for your Effect Size.

Right-click on the vertical axis labels and select Format Axis.

Right-click on the vertical axis labels and select "Format Axis".

The Format Axis menu will appear on the right side of the screen.

Find the Labels section and change the Label Position to “Low”.

Steps to change the Label Position of vertical axis labels to "Low".

After this update, you should now clearly see vertical axis labels (as shown below).

Example of what happens once the Label Position of vertical axis is changed to "Low".

STEP 4: Add the “Blobs” to the Chart Using Scatterplot Points.

To add the “blobs” or the dots to our Forest Plot, we need to add a new data series and change its chart type to “Scatter”.  

Right-click on the chart and click on Select Data.

Right-click on the chart and click "Select Data".

From the Select Data Source menu, click the Add button under the Legend Entries (Series) section.

Click the "Add" button under the Legend Entries (Series) section.

The Edit Series menu will appear. Leave the default values as is and click the OK button.

Click the OK button once the Edit Series menu appears. Leave the values as is for now.

Close the Select Data Source menu. You should now see the new data series added to your cart.

Example of what happens once a new data series is added.

Right-click on the new data series added (the orange one). From the menu, select “Change Series Chart Type”.

Right-click on the new data series added in the chart and select "Change Series Chart Type".

The Change Chart Type menu will appear. Change the Chart Type of “Series2”to Scatter and click OK.

Steps to change the Chart Type of the second series to "Scatter".

You should now see the new series converted into a dot in the chart.

Example of what happens once the new series is converted into Scatter chart type.

Next, update the chart to plot the dots correctly.

Right-click on the chart again and click “Select Data”.

The Select Data Source menu will appear. Select the second series (“Series2”) and click the Edit button on top of it.

Select the second series and click the "Edit" button.

Edit the x- and y-axes.

  • Set the Series X Values so that they point to the Effect Size data rows.
  • Set the Series Y Values so that they point to the Position column.
Steps to edit the x- and y-axes of the second series.

Once done, click OK and close the Select Data Source menu.

You should now see your chart look something like this:

Example of what happens to the chart once the x- and y-axes of the second series is updated.

STEP 5: Hide the Bars from the Chart.

Next, we need to hide the blue bars in the chart.

You might be asking, why do we need to hide these bars? Can’t we just delete them?

We need these bars to keep the Study labels intact, so we can’t simply delete them.

To hide them from the chart, right-click one of these bars and select Format Data Series.

Right-click on the first data series in the chart and select "Format Data Series".

Click the paint bucket button from the Format Data Series menu and set Fill to “No Fill”.

Steps to hide the selected data series in a chart.

After that, the bars will disappear from the chart.

Example of what happens once the first data series is hidden.

STEP 6: Add the CI Lines to the Chart Using Error Bars.

Next, we’ll add Error Bars to our chart to show the CI Lines.

Click the plus (+) sign next to the chart. Click the > button next to Error Bars and select More Options.

Steps to add the Error Bars into the chart.

The Add Error Bars menu will appear to ask you what series to add the error bars to.

Select the second series from the "Add Error Bars" menu and click OK.

Select the second series (“Series2”) and click OK.

We will not need the vertical error bars, so right-click on them and select Delete.

Steps to delete the vertical error bars.

Your chart should now slowly look like a Blobbogram or a Forest Plot.

Example of what happens once the error bars are added to the chart.

Next, we’ll need to adjust these error bars to display the correct confidence intervals.

Double-click on one of these bars. The Format Error Bars menu will appear on the right side of the screen.

Scroll down until you find the “Custom” option under the Horizontal Error Bar section.

Steps to specify the value of the Error Bars.

Then, click the Specify Value button. The Custom Error Bars will appear.

  • Set the Positive Error Value so that it points to the cells under the CI Line Highest column.
  • Set the Negative Error Value so that it points to the cells under the CI Line Lowest column.
Screenshot of where to assign the Positive and Negative Error Values of the error bars.

And that’s it! Your chart should now reflect the correct values from your dataset.

Example of what happens to the chart once the Error Bars are pointed to the values from the dataset.

STEP 7: Customize the Chart.

We are now at the last step – chart customization. This step is crucial for cleaning up the chart and making it more like a real Forest Plot.

1. Remove the second y-axis from the chart (the one on the right) since it is not necessary. To do this, right-click on it and select Delete.

Steps to remove the second y-axis on the chart (the one on the right).

2. Next, let’s configure the dot on our chart to change its color to black (since this is what we usually see in Forest Plots).

Double-click on one of the dots. The Format Data Series menu will appear.

Steps to change the color of the dots in the chart to black.

Click the paint bucket button on top and click on Marker.

Go to the Fill section and select Solid Fill. Then, change the color to black.

Next, we need to change the border’s color to black. From the Border section (just below Fill), select “Solid Line” and change the color to black.

Steps to change the border color of the dots in the chart to black.

The chart’s now starting to look like a legit Forest Plot. Just a few more tweaks and we’re all done.

Example of what happens once the dot in the chart is configured to have a black color and border.

3. Add a horizontal line on top of the x-axis by double-clicking on the said axis.

In the Format Axis menu, click the paint bucket button. Go to the Line section and select “Solid Line“.

Steps to add a horizontal in the y-axis of the chart.

4. Now, we’ll remove the vertical line next to the y-axis.

Double-click the vertical axis labels and set the Line to “No line”.

Steps to remove the line in the x-axis of the chart.

5. As a final step, we will remove the gridlines from the chart.

Click the plus (+) button next to the chart and untick the Gridline checkbox.

Steps to remove the gridlines from the chart.

And that is it! We have successfully added the Forest Plot to our sheet.

Example of a Forest Plot added in Excel.

Conclusion

Forest Plots may not be readily available in Excel, but as you can see, there’s still a way for us to add one in Excel. I hope this article helps you do just that.

Leave a Comment