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.
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:
- Position
- CI Line Lowest
- 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).
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]
CI Line Highest
In this column, use the following formula to subtract the Effect Size from the Upper CI.
=[Upper CI] - [Effect Size]
STEP 2: Insert a 2-D Horizontal Bar Chart.
Highlight the first two columns in the dataset (Study and Effect Size).
Go to the Insert Tab. Click the top left button after the Recommend Charts.
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.
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:
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.
The Format Axis menu will appear on the right side of the screen.
Find the Labels section and change the Label Position to “Low”.
After this update, you should now clearly see vertical axis labels (as shown below).
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.
From the Select Data Source menu, 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.
Close the Select Data Source menu. You should now see the new data series added to your cart.
Right-click on the new data series added (the orange one). From the menu, select “Change Series Chart Type”.
The Change Chart Type menu will appear. Change the Chart Type of “Series2”to Scatter and click OK.
You should now see the new series converted into a dot in the chart.
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.
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.
Once done, click OK and close the Select Data Source menu.
You should now see your chart look something like this:
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.
Click the paint bucket button from the Format Data Series menu and set Fill to “No Fill”.
After that, the bars will disappear from the chart.
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.
The Add Error Bars menu will appear to ask you what series to add the error bars to.
Select the second series (“Series2”) and click OK.
We will not need the vertical error bars, so right-click on them and select Delete.
Your chart should now slowly look like a Blobbogram or a Forest Plot.
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.
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.
And that’s it! Your chart should now reflect the correct values from your 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.
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.
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.
The chart’s now starting to look like a legit Forest Plot. Just a few more tweaks and we’re all done.
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“.
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”.
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.
And that is it! We have successfully added the Forest Plot to our sheet.
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.