How to Make a Forest Plot in Excel (2 Suitable Examples)

If you want to make a Forest plot in Excel, you have come to the right place. Although Excel does not have any built-in Forest plot, we will show you some easy techniques to make a Forest plot in Excel. Here, we will show you 2 easy examples to do the task smoothly.

What Is a Forest Plot?

A forest plot that is also familiar as a “blobbogram” is a graphical representation of the results of numerous studies in a single plot.

A Forest plot is mainly used in medical studies to represent the meta-analysis of the clinical trial results. Along with that, it is used in epidemiological studies.

In the following picture, you can see the overview of a Forest plot.

How to Make a Forest Plot in Excel: 2 Methods

The following dataset has Study, Effect Size, Lower Cl, and Upper Cl columns. Using this dataset, we will make a Forest plot in Excel.

Let’s explain the dataset to you so that you can have a better understanding.

Study Column – This column is showing several studies done for a meta-analysis. Generally, in forest plots, the study names are represented in chronological order.
Effect Size Column – The effect size shows the weight of the studies. Forest plot has different types of effect sizes. Among them, the odds ratio also known as the mean difference is used most often.
Lower Cl Column– The Lower Cl column represents the lower 95% confidence interval for every individual effect size.
Upper Cl Column – The Upper Cl column represents the upper 95% confidence interval for every individual effect size.

Next, in the following article, we will demonstrate to you 2 examples to make a Forest plot in Excel. Here, we used Microsoft Office 365. You can use any available Excel version.

1. Making Forest Plot with Effect Size

In this method, we will use the Effect Size to make a Forest plot in Excel.

Let’s go through the following steps to do the task.

Step-1: Inserting Bar Chart

In this step, we will insert a 2D Clustered Bar chart. This is the first step to make a Forest plot in Excel.

First of all, we will select both the Study and the Effect Size columns.
After that, we will go to the Insert tab.
Afterward, from Insert Column or Bar Chart group >> we will select 2D Clustered Bar Chart.

As a result, you can see the Bar chart.

Here, as the Effect Size has negative values in it, the bars with the negative values shift to the left side. Therefore, you can see the Vertical Axis in the middle of the bars.

Step-2: Moving Vertical Axis to Left Side

In this step, we will move the Vertical Axis to the leftmost side of the chart.

To do so, in the beginning, we will select the Vertical Axis >> right-click on it.
After that, we will select Format Axis from the Context Menu.

At this point, a Format Axis dialog box will appear on the right side of the worksheet.

Then, from the Axis Options >> click on Labels.
Next, click on the dropdown arrow of the Label Position box.
After that, several label positions will appear, and from them, we will select Low.

Therefore, you can see the Vertical Axis has shifted towards the left position of the chart.

Step-3: Adding an Orange Bar

In this step, we will add an Orange bar to the chart.

First of all, we will click on a bar and all the bars will be selected >> right-click on them.
Afterward, we will select the Select Data option from the Context Menu.

Then, a Select Data Source dialog box will appear.

After that, click on Add which is under the Legend Entries (Series).

Furthermore, an Edit Series dialog box will appear.

Next, do nothing on this dialog box and click OK.

Moreover, click OK in the Select Data Source dialog box.

Therefore, you can see an Orange bar in the chart.

Step-4: Replacing Orange Bar with Orange Scatter Point

In this step, we will replace the Orange bar with an Orange scatter point.

First, we will select the Orange bar >> right-click on it.
Then, from the Context Menu, select Change Series Chart Type.

At this moment, a Change Chart Type dialog box will appear.

Then, click on the drop-down arrow of the Clustered Bar box of Series 2.
Along with that, select Scatter chart.

Afterward, you can see Series 2 now shows Scatter.

Then, click OK.

As a result, you can see an Orange color scatter point in the chart.

Step-5: Adding Points to Chart

In this step, we will add a Points column to the dataset, and after that, we will add these points to our chart.

First of all, we add a Point column in the dataset.

Here, for Study 1, the point is 0.5 and after that, we have to 1 for other studies.

Next, we will right-click on the Orange point of the chart >> select the Select Data option from the Context Menu.

Then, a Select Data Source dialog box will appear.

After that, click on Series 2 which is under the Legend Entries (Series).
Along with that, click on Edit.

At this point, an Edit Series dialog box will appear.

After that, in the Series X values box, select cells C5:C10 from the Effect Size column.
In addition, in the Series Y values box, select cells F5:F10 from the Points column.
Afterward, click OK.

Moreover, click OK in the Select Data Source box.

Then, you can see the points in the chart.

Step-6: Hiding Bars from Chart

In this step, we will hide the bars from the chart.

In the beginning, we will select the bars.

Next, a Format Data Series dialog box will appear at the right end of the worksheet.

After that, from the Fill & Line group >> click on Fill >> select No Fill.

Hence, you can see there is no bar showing in the chart, and the chart is showing orange color scatter points only.

Step-7: Adding Error Bars

In this step, we will add Error bars to the chart.

First, we will select the orange color scatter points >> click on the Chart Elements, which is a plus sign marked with a red color box.
Then, from Chart Elements >> click on the rightward arrow of the Error Bars >> select More Options.

Next, a Format Error Bars dialog box will appear at the right end of the worksheet.

After that, from the Error Bar Options >> click on Custom >> select Specify Value.

At this point, a Custom Error Bars dialog box will appear.

Then, in the Positive Error Value box, we will select cells E5:E10 from the Upper Cl column.
Along with that, in the Negative Error Value box, we will select cells D5:D10 from the Lower Cl column.
After that, click OK.

As a result, you can see Error bars in the chart.

Afterward, we will delete the Vertical Error bars.

To do so, we will select the Vertical Error bars >> press the DELETE button.

Therefore, you can see the chart looks like a forest plot.

After that, we will delete the Y axis of the chart.
To do so, we will select the Y axis >> press the DELETE button.

Therefore, the chart looks more presentable now.

Step-8: Adding Chart Axis and Chart Title

In this step, we will add Chart Axis and Chart Title to the chart.

First of all, click on this chart >> from Chart Elements >> mark Axis Titles and Chart Title.

After that, we edited the Chart Title as Effect Size by Study.
Along with that, we edit the Horizontal Axis Title as Effect Size.
In addition, we edit the Vertical Axis Title as Study.

As a result, you can see the forest plot with a chart and axis title.

Step-9: Formatting Forest Plot

In this step, we will format the Forest plot to make it more eye-catching. This is the final step in making a Forest plot in Excel.

First of all, we will select the scatter points of the chart.

Then, a Format Data Series dialog box will appear at the right end of the worksheet.

After that, from the Fill & Line group >> click on Marker.

Along with that, from the Marker group, select Border >> set the Width to 3 pt.

Here, you can set the Width to any size according to your preference.

As a result, the scatter points of the Forest plot is looking more visible.

Next, we will format the Error bars of the Forest Plot.

To do so, select the Error bars.

Then, a Format Error Bars dialog box will appear at the right end of the worksheet.

After that, from the Fill & Line group >> set Width to 1 pt.

Here, you can set the Width to any size according to your preference.

Along with that, we select a Black color for the Error bars.

Here, you can select any color by clicking on the drop-down arrow of the Color box and selecting a color according to your preference.

Therefore, you can see the Forest plot made in Excel.

2. Using Odds Ratio to Make a Forest Plot in Excel

In this method, we will use the Odds Ratio as Effect Size to make a Forest plot in Excel. To do so, we will use the following dataset.

Let’s go through the following steps to do the task.

Step-1: Making Chart with Scatter Point

In this method, we will insert a 2D Bar chart, after that, we will add an Orange color bar to the chart. Afterward, we will replace the Orange color bar with a Scatter point. Along with that, we will add scatter points to the chart. Then, we will hide the bars, as a result, the chart will only contain scatter points.

First of all, we will insert a 2D Clustered Bar chart using the Study and Odds Ratio columns of the dataset.
Here, we followed Step-1 of Example-1 to insert the Bar chart.

As a result, you can see the Bar Chart.

Next, we followed Step-3 of Example-1 to add an Orange bar to the chart.

Therefore, the chart looks like the following.

Then, we followed Step-4 of Example-1 to replace the Orange bar with a scatter point.

As a result, the chart looks like the following.

Afterward, we add a Point column to the dataset.

Here, for Study 1, the point is 0.5 and after that, we have to 1 for other studies.

Therefore, you can see the dataset with the Point column.

Then, we followed Step-5 of Example-1 to add points to the chart.

Here, one thing must be noted, in the Edit Series dialog box, we have to add values of the Odds Ratio in the Series X values.

Here, in the Series X values box, we select cells C5:C10 from the Odds Ratio column.
In addition, in the Series Y values box, select cells F5:F10 from the Points column.
Afterward, click OK.

Therefore, the chart looks like the following.

Next, we hide the Bars from the chart by following Step-6 of Example-1.

As a result, the chart now has Scatter points in it.

Step-2: Modifying Dataset

In this step, we will add two new columns to the dataset. These are the Graph Lower 95% Cl, and Graph Upper 95% Cl columns.

First of all, we will type the following formula in cell G5.

=C5-D5

This simply subtracts Lower 95% Cl from the Odds Ratio.

After that, press ENTER.

As a result, you can see the result in cell G5.

Then, we will drag down the formula with the Fill Handle tool.

Therefore, you can see the complete Graph Lower 95% Cl column.

Afterward, we will type the following formula in cell H5.

=E5-C5

This simply subtracts Odds Ratio from the Upper 95% Cl.

After that, press ENTER.

As a result, you can see the result in cell H5.

Then, we will drag down the formula with the Fill Handle tool.

Hence, you can see the complete Graph Upper 95% Cl column.

Step-3: Adding Error Values to Chart

In this step, we will add Error bars to the chart.

To do so, we followed Step-7 of Example-1.

However, in the Custom Error Bars dialog box, we have to give the following input.

Here, in the Positive Error Value box, we will select cells H5:H10 from the Graph Upper 95% Cl column.
Along with that, in the Negative Error Value box, we will select cells G5:G10 from the Graph Lower 95% Cl column.
After that, click OK.

As a result, you can see Error bars in the chart.

Then, we will select the Vertical Error bars >> press the DELETE button.

As a result, the chart looks like a Forest plot.

After that, we followed Step-8 of Method-1 to delete the Y axis from the chart, and add Chart Titles and Axis Titles to the Forest plot.
Along with that, we followed Step-9 of Example-1 to format the Forest plot.

Therefore, you can see the complete Forest plot made in Excel.

Practice Section

You can download the above Excel file to practice the explained methods.

Download Practice Workbook

You can download the Excel file and practice while you are reading this article.

Make a Forest Plot.xlsx

Conclusion

Here, we tried to show you 2 examples to make a Forest plot in Excel. Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, please let us know in the comment section below.

<< Go Back to Excel Combo Chart | Excel Charts | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

2 Comments

ConcernedExcelUser Apr 10, 2023 at 2:03 PM

Your error bars aren’t showing up at the value for Lower/Upper CI. Just look at study 6. The upper error bar is 1.88… it should be right next to the plotted point (OR = 1.84), but it isn’t.
- Reply
  Afia Kona Apr 10, 2023 at 10:46 PM
  
  Dear CONCERNEDEXCELUSER,
  Thank you for your comment.
  Please have a look at the graph, the error bar for Study 6 is at the topmost position. And point 1.88 is right next to point 1.84.
  Thank you.
  
  Regard,
  Afia Aziz Kona
  Content Developer
  Exceldemy