How to Easily Create a Forest Plot in Excel

Understanding the Forest Plot Structure

A forest plot, sometimes colloquially referred to as a “blobbogram,” serves as the cornerstone for communicating meta-analysis results. The visual design is highly standardized to facilitate immediate interpretation. It is imperative to understand the fundamental components before attempting construction in Excel, as these components dictate how the data must be formatted and plotted.

The structure is primarily defined by two axes. The horizontal axis (x-axis) quantifies the value of interest, which is typically a measure of treatment effect or association, such as an odds ratio, mean difference, or standardized effect size. Importantly, this axis also contains a vertical line—the line of no effect—which usually corresponds to a value of 1.0 (for ratio measures) or 0.0 (for difference measures). This line acts as a critical visual benchmark for determining the statistical significance of individual study outcomes.

Conversely, the vertical axis (y-axis) is reserved for identifying the study components. This axis lists the labels for each individual research paper or dataset included in the meta-analysis. Within the plot area itself, each study is represented by a specific symbol (often a square or circle) that indicates its calculated effect size. Attached to this symbol are horizontal lines, known as the confidence interval bars, which illustrate the precision of the estimate. Longer lines imply less precision and wider variance in the data.

This type of visual summary offers an exceptionally convenient and comprehensive way to compare the outcomes and variability of numerous studies simultaneously, providing instantaneous insight into heterogeneity and overall pooled results. The following step-by-step example shows precisely how to construct this visualization within Excel.

Step 1: Enter the Data and Define Variables

The foundational step for creating the forest plot involves meticulous data entry into an Excel spreadsheet. For this complex plot, we must calculate and enter several specific variables that dictate the placement of the markers and the length of the error bars. The essential variables include: a unique identifier for each study, the calculated Effect Size (the primary x-value), the upper and lower limits of the Confidence Interval, and a sequential variable (we will call this ‘Points’) used solely for correctly plotting the studies on the y-axis.

The ‘Points’ variable is crucial for the graphical structure. Since we will be utilizing a horizontal bar chart as the foundation, Excel plots data inversely based on the category order. If you have 20 studies, the first study (which should appear at the top of the chart) must be assigned the highest numerical point (e.g., 20) and the last study the lowest number (e.g., 1). This inverse counting ensures the studies appear in the correct sequential order from top to bottom on the final chart, aligning with standard meta-analysis conventions.

Furthermore, to calculate the required length for the custom Error Bars later on, you must derive two specific metrics: the absolute distance from the Effect Size to the lower confidence limit, and the absolute distance from the Effect Size to the upper confidence limit. These calculations are necessary because Excel requires positive values for error bar lengths when using the custom specification feature.

Ensure that your data is formatted as shown in the template below, where columns clearly define the Study Name, Effect Size, the numerical Points (Y-axis position), and the calculated upper and lower error bar distances. This careful preparation streamlines the subsequent chart creation process significantly.

Step 2: Initialize the Plot with a Horizontal Bar Chart

The construction begins by generating a base chart using a standard 2-D clustered bar graph. This initial choice is counter-intuitive for a scatter-based plot but is necessary because Excel’s bar charting functionality provides the cleanest way to automatically generate the study labels (from the ‘Points’ column) on the vertical axis, which will later be swapped out for the actual study names.

To initiate the chart, highlight the primary plotting ranges. Specifically, select the cells corresponding to the ‘Points’ variable and the ‘Effect Size’ variable (for the example data, this is typically a continuous range like A2:B21 if the header row is A1:B1). Navigate to the Insert tab on the top ribbon, locate the Charts section, and select the 2-D clustered bar chart option. This action will immediately render a horizontal bar chart.

The resulting chart, as displayed below, will initially look nothing like a forest plot. The lengths of the bars will represent the Effect Size values, and the vertical axis labels will show the numerical ‘Points’ (1 through 20). This initial chart serves only as the foundational framework for positioning the subsequent scatter points correctly. We must modify its graphical elements significantly in the following steps to achieve the desired output.

Step 3: Adjust Axis Positioning for Readability

In standard Excel bar charts, the category labels (the y-axis labels) usually sit on the right side of the plot area, which is not suitable for a professional statistical visualization like a forest plot where labels must frame the data on the left. To correct this, we need to format the vertical axis. Begin by double-clicking the vertical axis labels themselves to access the formatting options.

Double-clicking opens the Format Axis pane on the right-hand side of the Excel window. Within the options presented, locate the Labels section, which controls the positioning of the category identifiers. The key adjustment here is changing the Label Position setting. By default, this is likely set to ‘Next to Axis’ or ‘High’, which places the text near the right. Change this crucial setting to Low.

Setting the Label Position to Low forces the vertical category labels to migrate from the right side of the chart area to the left side, mirroring the standard layout of a published forest plot. This step drastically improves the chart’s aesthetic and functional readability, preparing it for the seamless addition of the individual study markers which will soon replace the bars.

Step 4: Incorporating Scatterplot Points

The actual study markers, representing the centralized effect size for each study, must be plotted using a separate data series formatted specifically as an XY Scatter chart. This is a critical technique in Excel visualization: overlaying a scatterplot onto the underlying bar chart framework. Start by right-clicking anywhere on the existing plot and selecting Select Data to manage the data series.

In the Select Data Source dialogue box, click Add to introduce a new series (Series2). You can initially leave the Series Name blank. Since this series is not yet defined, it will momentarily appear on the chart as a single, unassigned bar. Right-click this newly added bar (it is often a different color, like orange) and select Change Series Chart Type from the context menu. This action opens the comprehensive Change Chart Type dialogue where the chart types for all existing series are managed.

Within the Change Chart Type window, scroll down to Series2 and change its chart type designation from ‘Clustered Bar’ to Scatter. After clicking OK, the singular bar will transform into a single scatterplot marker on the chart. This conversion is vital as only scatterplots allow the addition of precise, customizable Error Bars, which we will use shortly to represent the confidence interval.

Now, we must assign the correct coordinates to this new scatter series. Right-click the single orange point and choose Select Data again. Highlight Series2 and click Edit. For the X-values, select the range containing the Effect Size data. For the Y-values, select the range containing the numerical Points data. This binds the scatter markers to the precise (X, Y) location corresponding to each study’s effect size and its intended vertical position, thereby adding the full set of study markers to the plot.

Step 5: Refinement – Hiding Bar Elements and Secondary Axes

With the scatter points successfully plotted, the original underlying bar chart is now redundant and aesthetically disruptive. The next crucial phase is to remove these bars without disrupting the underlying axis structure they created. Right-click on any of the bars in the plot. In the formatting options (usually under the Paint Bucket icon), navigate to Fill and select No Fill. Simultaneously, select No Line for the border option. This renders the bars completely invisible, leaving only the scatter points and the axes visible.

Next, focus on the right-hand side Y-axis. This secondary axis was automatically generated by Excel when we overlaid the scatterplot series. Before deleting it, double-click the axis and ensure that its bounds (Minimum and Maximum) are set to appropriately match the range of your ‘Points’ data (e.g., Min 0, Max 20). Setting these bounds ensures the primary left axis scaling is locked in place, preventing unexpected shifts when the secondary axis is removed.

Once the bounds are fixed, simply select the right-hand Y-axis and press the Delete key. This cleaning process leaves a minimalist plot structure: the X-axis (Effect Size), the left Y-axis (Study Labels, currently numerical points), and the scatter points representing the individual study estimates. This resulting graph structure is now prepared for the final addition of the confidence interval markers.

Step 6: Implementing Custom Confidence Interval Error Bars

The horizontal lines representing the confidence interval are arguably the most distinctive and important feature of a forest plot. These are added using Excel’s Error Bars feature. Click on the chart area, then select the tiny green plus sign (+) located in the top-right corner. From the dropdown menu, check the box next to Error Bars. Excel will initially add both vertical and horizontal error bars, which is inappropriate for this type of statistical plot.

Immediately select one of the unwanted vertical error bars and press Delete; this action removes the vertical error bars from all points simultaneously. Next, select the remaining horizontal error bars. Click the small arrow next to Error Bars in the chart elements menu, and select More Options. This opens the detailed formatting pane for the error bars.

In the formatting pane, ensure the direction is set to Minus (to ensure bars extend both left and right) and the End Style is set to Cap (to create the standard vertical ticks at the end of the interval). Most critically, select the Custom option under Error Amount and click Specify Value. This is where the pre-calculated distances from the Effect Size to the upper and lower confidence limits are utilized.

Use the column containing the distance to the Upper Bound for the Positive Error Value field, and the column containing the distance to the Lower Bound for the Negative Error Value field. This mapping correctly visualizes the confidence intervals around the central effect estimates. Upon clicking OK, the precise horizontal confidence interval bars will appear for every study, completing the core statistical representation of the meta-analysis results.

Step 7: Aesthetic Finalization and Labeling

The final steps involve enhancing the chart’s professional appearance and ensuring all explanatory labels are present. Use the chart elements menu (the green plus sign) to add a descriptive Chart Title and clear Axis Labels for the horizontal axis (e.g., “Odds Ratio” or “Mean Difference”). It is also essential to correctly draw the line of no effect (usually X=1 or X=0), often achieved by formatting the X-axis major gridlines or by adding a separate data series representing a vertical line at the null value.

Lastly, customize the visual elements for maximal impact. Change the color and size of the scatter markers and the error bars for better contrast and visibility. Crucially, ensure the y-axis labels are replaced with the actual study names instead of the temporary numerical points; this is often done by right-clicking the axis, selecting data, editing the original series, and changing the categorical labels to reference the column containing the Study Names.

Modifying colors, fonts, and marker styles ensures the final product is not only statistically sound but also aesthetically pleasing and ready for publication or presentation, providing clear insight into the combined findings of the meta-analysis.

You can find more Excel visualization tutorials on relevant statistical platforms.