How to Make a Forest Plot in Excel (2 Methods)

What Is a Forest Plot?

A Forest plot, also known as a “blobbogram,” is a graphical representation that displays the results of multiple studies in a single plot. It is commonly used in medical research to represent the meta-analysis of clinical trial results and is also applicable in epidemiological studies.

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

How to Make a Forest Plot in Excel


Understanding the Dataset:

Before we proceed, let’s familiarize ourselves with the dataset we’ll be working with.

The dataset contains the following columns:

  1. Study Column: This column lists several studies conducted for a meta-analysis. Typically, in forest plots, study names are represented in chronological order.
  2. Effect Size Column: The effect size indicates the weight of each study. Forest plots use various effect size metrics, with the odds ratio (also known as the mean difference) being the most common.
  3. Lower Cl Column: The lower confidence interval (CI) represents the lower bound of the 95% confidence interval for each individual effect size.
  4. Upper Cl Column: The upper confidence interval (CI) represents the upper bound of the 95% confidence interval for each individual effect size.

Method 1 – Creating a Forest Plot with Effect Size:

Follow these steps to create a Forest plot using the effect size in Excel:

  • Inserting a Bar Chart:
    • Select both the Study and Effect Size columns.
    • Go to the Insert tab.
    • From the Column or Bar Chart group, choose 2D Clustered Bar Chart.

How to Make a Forest Plot in Excel

  • The resulting chart will display bars, with negative effect sizes shifting to the left side. The vertical axis will be positioned in the middle of the bars.

Step-2: Moving Vertical Axis to the Left Side

  • Right-click on the vertical axis.
  • Select Format Axis from the Context Menu.

How to Make a Forest Plot in Excel

  • In the Format Axis dialog box, navigate to Labels.
  • Choose the “Low” label position to shift the vertical axis to the left side of the chart.

The Vertical Axis has shifted towards the left position of the chart.

Step-3: Adding an Orange Bar

  • Click on any bar to select all bars.
  • Right-click and choose Select Data from the Context Menu.

How to Make a Forest Plot in Excel

  • In the Select Data Source dialog box, click Add under Legend Entries (Series).

  • An “Edit Series” dialog box will appear; click OK without making any changes.

How to Make a Forest Plot in Excel

  • Confirm by clicking OK in the Select Data Source dialog box.

  • An orange bar will now appear in the chart.

How to Make a Forest Plot in Excel


Step-4: Replacing Orange Bar with Orange Scatter Point

  • Right-click on the orange bar.
  • Select Change Series Chart Type from the Context Menu.

  • In the Change Chart Type dialog box, choose Scatter char” for Series 2.

How to Make a Forest Plot in Excel

  • Click OK.

  • The chart will now display an orange scatter point instead of the bar.

How to Make a Forest Plot in Excel


Step-5: Adding Points to the Chart

  • First, create a new column called Points in your dataset.
  • Assign a value of 0.5 to the Points column for Study 1 and set it to 1 for the other studies.

  • Right-click on the orange scatter point in your chart.
  • Choose Select Data from the Context Menu.

How to Make a Forest Plot in Excel

  • In the Select Data Source dialog box, choose Series 2 (under “Legend Entries (Series)”).
  • Click Edit.

  • In the Edit Series dialog box:
    • Set the X values to cells C5:C10 (from the Effect Size column).
    • Set the Y values to cells F5:F10 (from the Points column).
  • Click OK to confirm.

How to Make a Forest Plot in Excel

  • Click OK in the Select Data Source box.

  • You can see the points in the chart.

How to Make a Forest Plot in Excel


Step-6: Hiding Bars from the Chart

  • Select the bars in your chart.

  • A Format Data Series dialog box will appear on the right side of the worksheet.
  • From the Fill & Line group, click on Fill, and then select No Fill.

How to Make a Forest Plot in Excel

  • Now your chart will display only the orange scatter points.


Step-7: Adding Error Bars

  • Select the orange scatter points.
  • Click on the Chart Elements (the plus sign marked with a red color box).
  • From Chart Elements, click on the rightward arrow next to Error Bars, and choose More Options.

How to Make a Forest Plot in Excel

  • In the Format Error Bars dialog box:
    • Click on Custom under Error Bar Options.
    • Select Specify Value.

    • Set the positive error value to cells E5:E10 (from the Upper Cl column).
    • Set the negative error value to cells D5:D10 (from the Lower Cl column).
  • Click OK to apply the error bars.

How to Make a Forest Plot in Excel

You can see Error bars in the chart.

  • Select the Vertical Error bars >> press the DELETE button.

You can see the chart looks like a forest plot.

  • Select the Y axis >> press the DELETE button.

How to Make a Forest Plot in Excel

The chart now looks more presentable.


Step-8: Final Touches – Adding Chart Axis and Title

  • Click on the chart.
  • From Chart Elements, add both Axis Titles and Chart Title.

How to Make a Forest Plot in Excel

  • Edit the titles as follows:
    • Chart Title: Effect Size by Study
    • Horizontal Axis Title: Effect Size
    • Vertical Axis Title: Study


Step-9: Formatting the Forest Plot

  • Select the scatter points in the chart.

How to Make a Forest Plot in Excel

  • In the Format Data Series dialog box:
    • From the Fill & Line group, click on Marker.

  • In the Marker group, select Border, and set the width to 3 pt (adjust as desired).

How to Make a Forest Plot in Excel

The scatter points of the Forest plot are looking more visible.

  • Next, format the error bars:
    • Select the error bars.

How to Make a Forest Plot in Excel

    • Set the width to 1 pt (customize as needed).
    • Choose a black color for the error bars.

You can see the Forest plot made in Excel.

How to Make a Forest Plot in Excel

Read More: Create Clustered Stacked Column Combo Chart with Lines in Excel


Method 2 – Creating a Forest Plot with Odds Ratio:

Step-1: Inserting a Scatter Point Chart:

  • Start by inserting a 2D Clustered Bar chart using the Study and Odds Ratio columns from your dataset.
  • Follow the same steps as in Method 1 to create the bar chart.

  • Next, add an orange bar to the chart (Step 3 from Method 1).

How to Make a Forest Plot in Excel

  • Replace the orange bar with a scatter point (Step 4 from Method 1).

  • Add a new column called Points to your dataset.
  • Assign a value of 0.5 to the Points column for Study 1 and set it to 1 for the other studies.

  • Add these points to the chart (Step 5 from Method 1). Note that in the Edit Series dialog box, use the Odds Ratio values as the X values and the Points as the Y values.

How to Make a Forest Plot in Excel

The chart looks like the following.

  • Hide the Bars from the chart by following Step-6 of Method 1.

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

How to Make a Forest Plot in Excel


Step-2: Modifying Dataset

  • Create two new columns: Graph Lower 95% Cl and Graph Upper 95% Cl.

  • In cell G5, subtract the Lower 95% Cl from the Odds Ratio:
=C5-D5

How to Make a Forest Plot in Excel

  • Press ENTER.
  • Drag down the formula with the Fill Handle tool to fill the entire Graph Lower 95% Cl column.

  • In cell H5, subtract the Odds Ratio from the Upper 95% Cl:
=E5-C5

How to Make a Forest Plot in Excel

  • Press ENTER.
  • Drag down the formula with the Fill Handle tool to fill the entire Graph Upper 95% Cl column.

How to Make a Forest Plot in Excel


Step-3: Adding Error Bars to the Chart

  • Follow Step 7 from Method 1 to add error bars to the chart.
  • In the Custom Error Bars dialog box, input the following:
    • Positive Error Value: Select cells H5:H10 from the Graph Upper 95% Cl column.
    • Negative Error Value: Select cells G5:G10 from the Graph Lower 95% Cl column.
  • Click OK to apply the error bars.

You can see Error bars in the chart.

  • Delete the vertical error bars (press DELETE).

How to Make a Forest Plot in Excel

The chart looks like a Forest plot.

How to Make a Forest Plot in Excel

  • Delete the Y axis from the chart (Step 8 from Method 1).
  • Add chart titles and axis titles (Step 9 from Method 1).
  • Format the Forest plot as desired.

Read More: How to Shade Area Between Two Lines in a Chart in Excel


Practice Section

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

How to Make a Forest Plot in Excel


Download Practice Workbook

You can download the practice workbook from here:


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
Afia Kona
Afia Kona

Afia Aziz Kona, a graduate of Civil Engineering from Khulna University of Engineering & Technology, Bangladesh, serves as a technical content creator in the ExcelDemy project. Possessing a passion for innovation and critical thinking, she actively embraces challenges. Beyond her engineering background, Afia exhibits a keen interest in Excel, having authored numerous articles on Excel & VBA-related issues to simplify the experience for users facing obstacles in Excel. Apart from creating Excel tutorials, she is also... Read Full Bio

2 Comments
  1. Reply
    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.

    • 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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo