It’s 3:12 PM on a Tuesday. You’re reviewing the latest meta-analysis draft for the oncology team’s submission to The Lancet Oncology. Your colleague just Slack’d you a Word doc with odds ratios and confidence intervals — but the journal requires a forest plot as a high-res PNG. You open Excel. You’ve never made one before. And you have 87 minutes until the deadline.
The Setup
You’re working with data from six randomized controlled trials comparing immunotherapy vs. standard care in non-small cell lung cancer (NSCLC). Each study reports an odds ratio (OR) for progression-free survival, plus its 95% CI. No built-in chart type handles this right out of the box — and that’s where most people stall.
| Study | OR | Lower CI | Upper CI | N (total) |
|---|---|---|---|---|
| KEYNOTE-189 | 0.58 | 0.47 | 0.71 | 1,206 |
| CheckMate-227 | 0.74 | 0.62 | 0.89 | 1,735 |
| IMpower150 | 0.78 | 0.65 | 0.94 | 1,202 |
| JAVELIN Lung 200 | 0.92 | 0.76 | 1.11 | 792 |
| OAK | 0.74 | 0.63 | 0.87 | 852 |
| POPLAR | 0.82 | 0.66 | 1.01 | 425 |
| PACIFIC | 0.52 | 0.42 | 0.64 | 713 |
| CASPIAN | 0.72 | 0.60 | 0.87 | 805 |
This is your raw data — copied straight from Table 2 of the protocol appendix. It lives in A1:E9 (including headers). Note: ORs are not symmetrical around 1 — so your x-axis must be logarithmic, or your plot will mislead. That’s what most people miss.
The Challenge
A forest plot isn’t a bar chart. It’s a scatter plot with custom error bars, reversed y-axis order, manually labeled studies, and a vertical reference line at OR = 1. Excel doesn’t label points by row text automatically — and if you try to use a regular bar chart, your CIs stretch left/right unevenly because the scale isn’t log-based.
The biggest trap? Forgetting to sort your studies before building the chart. If you don’t sort them top-to-bottom in the order you want them displayed (KEYNOTE-189 at the top, CASPIAN at the bottom), Excel will assign y-values alphabetically — not by your intended visual hierarchy. Also, error bars default to plus/minus values — but here, Lower CI and Upper CI are absolute numbers, not offsets. So you can’t just feed them in raw.
And yes — you need two separate series: one for the OR points, another for the CI lines. Trying to do it in one series breaks the formatting. This isn’t optional. It’s required.
Walking Through It
We’ll build this in four deliberate steps — no macros, no add-ins. Just native Excel (tested in Excel 365 and Excel 2021).
Step 1: Prepare helper columns
In column F (starting F1), type Y Position. In F2, enter 1. In F3, enter 2, and drag down to F9. This gives each study a numeric rank — we’ll use it as the y-axis coordinate.
In column G: CI Lower Offset. In G2, enter =B2-C2 (OR minus Lower CI). In H2: CI Upper Offset, formula =D2-B2. These are the distances from the OR point to each CI bound — needed for error bars.
Step 2: Insert the base scatter plot
Select F2:G9 (Y Position and OR values only). Hold Ctrl and also select B2:B9 (the OR column). Now go to Insert → Charts → Scatter with Only Markers. Excel will auto-detect two series — ignore Series 1 for now. Right-click the chart → Select Data. Remove any series except the one plotting B2:B9 on Y and F2:F9 on X. Wait — no. Actually, reverse that: you want OR on X-axis, Y Position on Y-axis. So select B2:B9 (OR) for X, and F2:F9 (Y Position) for Y.
Keyboard shortcut: Alt → N → C → S (Insert → Chart → Scatter). Then Alt → J → L → E to open Select Data.
| Step | Action | Result | Shortcut |
|---|---|---|---|
| 1 | Add Y Position (1–8) in F2:F9; CI offsets in G2:H9 | Now you have numeric y-coords + symmetric error bar inputs | Ctrl+D to fill down |
| 2 | Select B2:B9 (OR) and F2:F9 (Y Position); insert Scatter | Points appear left-to-right — but y-axis reads bottom-up (we’ll flip it) | Alt+N+C+S |
| 3 | Right-click y-axis → Format Axis → check 'Categories in reverse order' | Studies now flow KEYNOTE-189 (top) → CASPIAN (bottom) | Alt+J+Y+R |
| 4 | Add error bars: Chart Design → Add Chart Element → Error Bars → More Options → Custom → specify G2:G9 (minus) and H2:H9 (plus) | Horizontal lines now extend correctly from each point | Alt+J+U+E |
Surprising tip: Don’t format the error bars first. Excel applies formatting to *all* error bars at once — and if one is missing a value (e.g., G5 is blank), the whole set fails silently. Always verify G2:H9 has numbers before adding error bars.
Step 3: Add the reference line at OR = 1
Right-click the x-axis → Format Axis. Under Axis Options, set Minimum to 0.3, Maximum to 1.5, and crucially — check Logarithmic scale. Then add a vertical line: click Chart Design → Add Chart Element → Lines → Vertical Line. Click the new line → Format Shape → set Width to 1.5 pt, Dash Type to Solid, Color to #0f766e.
Step 4: Label studies manually (yes, really)
Excel won’t auto-label scatter points with text from column A. So: right-click any data point → Add Data Labels. They’ll show numbers (the ORs). Double-click a label → in Formula Bar, type =A2 (then press Enter). Repeat for each point — tedious, but precise. Pro move: copy A2:A9, then paste into labels using Paste Special → Text — but that only works if you’ve selected all labels first (Ctrl+click each one).
The Result
Here’s what your final forest plot table looks like — ready for copy-paste into PowerPoint or export as PNG:
| Study | OR | 95% CI | Y Position | CI Lower Offset | CI Upper Offset |
|---|---|---|---|---|---|
| KEYNOTE-189 | 0.58 | 0.47 – 0.71 | 1 | 0.11 | 0.13 |
| CheckMate-227 | 0.74 | 0.62 – 0.89 | 2 | 0.12 | 0.15 |
| IMpower150 | 0.78 | 0.65 – 0.94 | 3 | 0.13 | 0.16 |
| JAVELIN Lung 200 | 0.92 | 0.76 – 1.11 | 4 | 0.16 | 0.19 |
| OAK | 0.74 | 0.63 – 0.87 | 5 | 0.11 | 0.13 |
| POPLAR | 0.82 | 0.66 – 1.01 | 6 | 0.16 | 0.19 |
| PACIFIC | 0.52 | 0.42 – 0.64 | 7 | 0.10 | 0.12 |
| CASPIAN | 0.72 | 0.60 – 0.87 | 8 | 0.12 | 0.15 |
Your chart now shows: a clean y-axis with study names (manually pasted), horizontal error bars sized correctly, a bold green line at OR = 1, and a log-scaled x-axis where distances reflect multiplicative change — not additive.
What Could Go Wrong
Three mistakes I saw colleagues repeat last week — each cost 20+ minutes:
- Mistake #1: Using a bar chart instead of scatter — Bar charts force categories onto the x-axis and treat OR as a category label. You’ll get vertical bars centered at arbitrary positions, no control over CI width, and zero ability to set log scale. The result looks like a grocery list, not evidence.
- Mistake #2: Skipping the logarithmic axis — Without it, OR = 0.5 and OR = 2.0 appear equally distant from 1 — but they’re not. 0.5 means “half the odds”, 2.0 means “double the odds”. Log scale preserves proportional meaning. Excel defaults to linear — and most people don’t notice until the reviewer asks, “Why does PACIFIC look closer to 1 than KEYNOTE?”
- Mistake #3: Forgetting to reverse y-axis order *after* adding data labels — If you reverse the axis after labeling, Excel keeps labels anchored to old positions — so KEYNOTE-189’s label ends up near CASPIAN’s dot. Fix: delete all labels, reverse axis, then re-add.
Next step: Open your dataset. Go to A1. Type Y Position in F1. Fill 1–8 down F2:F9. Then hit Alt+N+C+S. You’re already halfway there.
