Replicating the LP–IRF chart in Excel
Overview
In editing stage of research, there might be request of all plots in excel data and graphic forms. When you gotta do what you gotta do, I hope this guide would help you. Credits to our former intern at ECA — Tadele Muche Tefera — for building the original excel template.
The Stata do-file exports results to lp_to_plot.xlsx via export excel. This guide walks editors — or anyone without Stata access — through building the same IRF chart purely in Excel using a combo chart (Stacked Area + Line).
The finished chart reproduces the key visual elements of the Stata output: a blue semi-transparent confidence band, a red point-estimate line, and a green dashed zero-reference line.
Local Projection IRF
Response to fed funds shock — Stata reference output
Stata-generated reference · lp_ogap.png · replicate below in Excel
| File | Purpose |
|---|---|
| LP_demo_10Apr2026.do | Stata estimation + export |
| lp_to_plot.xlsx | Excel data + chart (this guide) |
| LP_template.xlsx | Blank template with chart pre-built |
Data layout in lp_to_plot.xlsx
Stata writes six columns to the IRF sheet (rows 1–14, header + 13 horizons). Column F (band) is computed by Stata, but if you ever rebuild from scratch use the formula =D2-E2.
| Col | Variable | Content | Chart role |
|---|---|---|---|
| A | h | Horizon index 0 – 12 | X-axis (category) |
| B | zero | Constant 0 — baseline reference | Line · green dashed |
| C | b | Point estimate βₕ from each LP regression | Line · red solid |
| D | ub | Upper 95 % CI bound (b + 1.96 × se) | — derived only |
| E | lb | Lower 95 % CI bound (b − 1.96 × se) | Stacked Area · no fill (spacer) |
| F | band | =D−E (ub − lb) — the CI width at each horizon | Stacked Area · blue fill |
lb the invisible base and band the visible layer is the standard workaround to position a floating CI band at the correct y-coordinates without using error bars (which can't be filled).Building the combo chart
Generate the underlying data
Run LP_demo_10Apr2026.do to obtainlp_to_plot.xlsx. Then paste it intoLP_template.xlsx. That should already update the plot by itself, continue reading to learn how to build the chart from scratch in Excel using the same data.
Select data and insert a Clustered Column chart
Highlight A1:F14 on the IRF sheet, then go to Insert → Charts → 2-D Column → Clustered Column. Don't worry about how it looks yet — the series types are changed in the next step.
Switch to a Combo chart and assign series types
Right-click the chart → Change Chart Type → All Charts → Combo. Set each series as follows:
| Series | Chart type | Secondary axis |
|---|---|---|
| zero | Line | No |
| b | Line | No |
| ub | — (remove) | — |
| lb | Stacked Area | No |
| band | Stacked Area | No |
Remove the ub series entirely — it was only needed to derive band. Set h as the Horizontal (Category) Axis Labels via Select Data.
Format the CI band (lb invisible · band blue)
lb series (spacer — must be invisible):
- Click the
lbarea → Format Data Series - Fill → No fill
- Border → No line
band series (the visible blue fill):
- Click the
bandarea → Format Data Series - Fill → Solid fill · Colour
#5B8DB8· Transparency 65 % - Border → No line
Format the beta and zero lines
b series (red solid point-estimate line):
- Colour
#C0392B· Width1.75 pt - Marker → None
zero series (olive-green dashed baseline):
- Colour
#5A7A1E· Width1 pt - Dash type → Square Dot (closest Excel equivalent to Stata's
lpattern(dash)) - Marker → None
Axes, labels, and titles
- Y-axis: set min
-1.1, max0.8to match Stata range - X-axis label interval: every 2 (shows 0, 2, 4 … 12)
- Chart title: "Local Projection IRF"
- X-axis title: "Horizon (quarters)"
- Y-axis title: "Change in output gap (percentage points)"
- Plot area fill: light blue-grey
#EBF0F5or plain white - Remove legend (series names add clutter — use a text-box if a legend is needed)
Notes & gotchas
Why not use Excel error bars?
Excel's built-in error bars don't support asymmetric fills and can't be shaded as a region. The lb (invisible base) + band (filled top) stacked-area trick is the standard workaround — it precisely replicates Stata's rarea ub lb h command.
ub column not plotted
The ub column is exported by Stata but is not used directly in the Excel chart — it exists solely so you can compute band = ub − lb. Remove it from the chart's data series if it appears as an extra column.
zero series vs a manual axis line
Using a zero data series (constant 0 across all horizons) instead of the built-in y=0 axis line gives you full control over colour, weight, and dash pattern — which Excel's axis formatting doesn't easily expose for dashed styling that matches Stata's lpattern(dash).
Y-axis bounds
Excel auto-ranges often clip the band. Always manually set the Y-axis minimum to -1.1 and maximum to 0.8 to match the Stata reference output exactly.
Updating after re-running Stata
Re-running the do-file overwrites lp_to_plot.xlsx with fresh estimates. If the Excel chart is embedded in the same file it will update automatically on open. If you copy data into a separate workbook, paste as Values only to avoid broken references.