Learning to Create Forest Plots in Excel: A Step-by-Step Guide

A forest plot, sometimes informally referred to as a “blobbogram,” is an indispensable graphical tool primarily employed during meta-analysis. Its core purpose is to provide a coherent, visual summary of quantitative results derived from multiple independent scientific studies that all investigate the same research question. This visualization is essential for synthesizing evidence and communicating complex findings effectively.

This powerful graph is structured around two key axes: the x-axis, which quantifies the calculated measure of interest—such as an odds ratio, a standardized effect size, or the mean difference—and the y-axis, which systematically lists the findings from each individual contributing study. The visual representation allows researchers and readers to quickly grasp the magnitude and direction of the results.

The structure of the forest plot offers a highly efficient and intuitive methodology for synchronously evaluating both the consistency and the scale of research findings across a diverse collection of data sets. Although sophisticated statistical software packages are typically utilized for generating these visualizations, the following detailed, step-by-step guide demonstrates how to construct a professional-grade forest plot entirely within Microsoft Excel.

Step 1: Prepare and Structure the Data for Charting

The successful foundation of any complex data visualization relies on accurately structured input. Before commencing the chart creation process in Excel, it is crucial to ensure your data, derived from the various studies contributing to the meta-analysis, is organized systematically in the spreadsheet. Precision in data entry here will simplify all subsequent charting steps.

The spreadsheet must be structured to accommodate several key data points for each study. Specifically, you will need columns for: the study identifiers (names or numbers), the calculated point estimate (Effect Size), the lower boundary of the confidence interval (Lower CI), and the upper boundary of the confidence interval (Upper CI). Critically, we must also include a column, typically labeled ‘Points’, which is necessary for defining the vertical positioning of each study marker on the chart.

Enter the data for every contributing study using a format similar to the illustration provided below. It is essential to understand that the ‘Study’ names and the ‘Points’ column will form the initial basis for constructing the foundational horizontal bar chart, which we will later transform into the final forest plot layout.

Step 2: Initialize the Structural Framework Using a Bar Chart

The forest plot in Excel is constructed by first establishing a standard horizontal bar chart, which will serve as the necessary structural framework and coordinate system for the final visualization. This initial chart type provides the mechanism needed to align the study names vertically.

To begin, select the cell range that encompasses the study identifiers and the corresponding vertical positioning points (e.g., if your data starts at row 2, select the range A2:B21). With this range highlighted, navigate to the top Excel ribbon and click the Insert tab.

Within the Charts group, select the 2-D clustered bar option. Excel will automatically generate the starting visualization, which, at this preliminary stage, appears simply as a standard clustered horizontal bar chart. This chart will be manipulated in the following steps to achieve the desired forest plot appearance.

Step 3: Reposition the Vertical Axis Labels

For a chart to conform to the conventional forest plot design, the study names (the vertical axis labels) must be positioned on the left side of the data plotting area, adjacent to the effect size markers. By default, Excel places these labels on the right, so a crucial formatting adjustment is required.

To initiate this repositioning, double-click directly on the vertical axis labels (the list of study names). This action will immediately open the comprehensive Format Axis pane on the right side of your Excel workspace, providing access to detailed configuration options.

Within the Axis Options section, locate the Labels settings. Find the Label Position parameter and change its default value to Low. This specific setting instructs Excel to shift the labels to the side nearest the origin of the plot, which dramatically enhances the chart’s overall readability and structural alignment, moving the study names to the left.

As a result of this modification, the vertical axis labels are instantly organized correctly on the left side of the graphing area, laying the groundwork for the next major step: the integration of the data points.

Step 4: Incorporate Effect Size Data Using a Scatterplot

The effect size points and their associated confidence intervals will be represented by a new data series added as an XY Scatterplot, overlaid onto the existing bar chart structure. This is the core mechanism for displaying the primary meta-analytic results.

Begin by right-clicking anywhere within the chart boundary and choosing Select Data. In the ensuing dialog box, click Add to introduce a new series, which Excel will temporarily label Series2. Click OK to confirm the addition. At this point, the visualization might temporarily appear disjointed, perhaps showing an additional, unintended bar in the plot area.

Next, we must transform this new series type. Right-click on the newly added bar (typically colored orange or another contrasting color) and select Change Series Chart Type. Locate Series2 in the menu and explicitly change its chart type from Clustered Bar to Scatter (XY Scatter). Ensure that the original Series1 remains a Clustered Bar, as it maintains the structural alignment.

After clicking OK, the chart will display a single, often misplaced, placeholder scatterplot point, confirming the successful transformation of the series type. Now, we connect the scatterplot to the actual data. Right-click the single orange point and select Select Data once more. Highlight Series2 and click Edit. This is the most critical mapping step: for the X-values, use the cell range containing the Effect Size values, and for the Y-values, use the cell range containing the Points (the vertical position column). Click OK to finalize the data mapping.

The chart will now accurately display the scatterplot points, positioned according to their respective effect sizes on the horizontal axis and aligned vertically with the correct study names:

Step 5: Aesthetic Clean-up and Axis Refinement

With the effect size markers successfully plotted, the initial horizontal bars (Series1) have served their structural purpose and must now be hidden to achieve a clean forest plot aesthetic. These bars are essential for setting the Y-axis scale but are not intended to be visible in the final output.

Right-click on any of the blue bars in the plot. In the formatting options, navigate to the Fill settings. Change the fill type to No Fill. This action effectively renders the bars transparent, eliminating the visual clutter without disrupting the underlying coordinate system defined by the original Series1 data.

Next, address the redundant vertical axis on the right side. Double-click this axis and modify its bounds to match the range of your ‘Points’ column, typically setting the minimum value to 0 and the maximum value to 20. This ensures the axis encompasses all plotted points. Finally, select the right y-axis and press the Delete key to remove it entirely. This leaves a visually clean plot containing only the essential study labels on the left and the effect size markers in the central plotting area.

Step 6: Define and Apply Custom Confidence Interval Error Bars

The hallmark of a forest plot is the visualization of uncertainty, typically represented by confidence intervals (CIs) around each point estimate. In Excel, these are integrated using horizontal error bars linked directly to your data.

Click the small green plus sign icon located in the top right corner of the chart area. In the dropdown menu that appears, check the box labeled Error Bars. Excel will initially add both default horizontal and vertical error bars to all scatterplot points. Since vertical error bars are extraneous to the forest plot design, click on any of the vertical error bars and press the Delete key to remove them from all points simultaneously.

The remaining horizontal error bars must now be customized to reflect the specific lower and upper bounds calculated in your meta-analysis data (Step 1). Click the horizontal error bars and select the More Options sub-menu next to Error Bars. In the formatting pane that opens, navigate to the Error Bar Options and select the option to specify Custom error bars. In the Custom dialog box, you will define the positive error values (Upper CI) and the negative error values (Lower CI) using the cell ranges containing your calculated confidence interval bounds.

By applying these custom data ranges, you ensure that the error bars accurately stretch across the plot, completing the statistically valid visualization for the uncertainty associated with each individual study finding:

Step 7: Final Polish and Professional Presentation

The technical construction of the forest plot is now complete, but the final stage involves applying essential labels and aesthetic enhancements necessary for professional presentation and intuitive interpretation. A well-designed plot communicates results clearly.

Ensure you add a descriptive chart title that clearly communicates the meta-analysis topic. Furthermore, apply appropriate labels for the horizontal axis, such as “Odds Ratio,” “Relative Risk,” or “Standardized Mean Difference,” depending on the metric used in your study. Remember to also include a reference line at the point of “No Effect” (e.g., 1.0 for ratios or 0.0 for mean differences).

Finally, leverage Excel’s formatting tools to refine the visual impact: modify marker shapes, sizes, and colors, and adjust line thicknesses for the error bars. These aesthetic adjustments ensure adherence to publication standards and dramatically improve the ease with which the results can be understood. The resulting visualization is a complete, statistically valid forest plot, constructed entirely using standard Microsoft Excel functionality.

For additional tutorials focusing on advanced data visualization techniques using Excel and other specialized statistical platforms, we encourage you to consult our comprehensive resource library.