Propensity Score Matching in Excel with BESH Stat NG

This tutorial shows how to run a propensity score matching (PSM) analysis in Microsoft Excel using BESH Stat NG. The example uses a RAND Health Insurance Experiment subset and compares a cost-sharing insurance plan group with a no-cost-sharing group on the number of outpatient visits to a medical doctor.

The goal is not simply to fit a good treatment-assignment model. The goal is to create a more credible treated-versus-control comparison by improving balance in measured pre-treatment covariates, then to inspect whether the adjusted comparison is believable enough to interpret.

What is propensity score matching?

Propensity score methods are used in observational data when treatment assignment was not randomized. The propensity score is the estimated probability of receiving treatment given measured baseline covariates. Subjects with similar propensity scores have similar measured covariate profiles, at least with respect to the variables included in the score model.

In a matching workflow, treated subjects are matched to control subjects with similar propensity scores or covariate profiles. The matched sample is then used to estimate a treatment effect, such as the average treatment effect among the treated (ATT).

Important limitation: propensity-score methods adjust only for measured pre-treatment covariates. They do not remove bias due to unmeasured confounding, post-treatment variables, poor timing, or lack of overlap.

Example data

download example workbook

The input data contain 320 observations: 160 treated and 160 controls. In this tutorial, treatment is defined as 1 = cost-sharing plan and 0 = no-cost-sharing plan. The outcome is outcome_mdvis, the number of outpatient visits to a medical doctor. The propensity score model uses seven pre-treatment covariates: lpi, idp, physlm, disea, hlthg, hlthf, and hlthp.

Open the PSM dialog

On the Excel ribbon, go to BESH Stat NG → Analyse → Causal Inference → Propensity Score Matching.

GUI workflow

1. Select the data

Use the Data tab to assign worksheet columns to analysis roles. Treatment, outcome, and covariates are required. ID is optional but recommended because it makes matched-pair and audit tables easier to read.

1. Set Active Worksheet to Input_Data.
2. Move treatment to Treatment (0/1).
3. outcome_mdvis to Outcome.
4. id to ID.
5. Leave Supplied score empty for the main logistic-score tutorial run.
6. Move lpi, idp, physlm, disea, hlthg, hlthf, and hlthp to Covariates.

2. Specify the propensity model

In the Propensity model tab, keep the tutorial model simple: main effects plus an intercept. This makes the example easy to reproduce and keeps the model auditable.

Do not include the outcome in the propensity-score model. Only include variables measured before treatment assignment that could affect both treatment and outcome.

3. Choose matching and scoring options

The attached result workbook was created with the default PSM options shown below: logistic-regression score estimation, ATT estimand, 1:1 nearest-neighbor matching, propensity-score distance, no replacement, no caliper, no common-support restriction, and standardized covariates for the propensity model.

4. Select diagnostics and outputs

For teaching and applied analysis, keep the diagnostic outputs enabled. The matched-pair table provides auditability; balance diagnostics show whether adjustment helped; overlap and weight diagnostics show whether the analysis depends on extreme extrapolation; and Love-plot output makes balance easier to inspect visually.

• Include doubly robust AIPW estimate: useful as a model-assisted sensitivity estimate.
• Overlap diagnostics: useful for detecting weak treated/control overlap.
• Weight diagnostics: important when interpreting weighting and AIPW results.
• Love-plot data: recommended for every PSM tutorial and report.
• Write matched-pair table: recommended for matching methods so users can audit each match.
• Write diagnostics tables: recommended for checking balance, overlap, weights, and sensitivity output.

Results from the tutorial run

Download full results excel workbook generate by BESHstatNG.

Run summary

Sample-size summary

Treatment-effect estimates

The matched-pair ATT estimate is negative, meaning that treated subjects had fewer MD visits on average than their matched controls. However, the matched-pair 95% confidence interval crosses zero. In this default run, the matched comparison alone does not provide strong evidence of a non-zero effect.

The weighting and AIPW estimates are more negative and their confidence intervals do not cross zero. These estimates should be interpreted cautiously because the diagnostics show residual imbalance and limited effective sample size among weighted controls.

Effect sensitivity summary

A useful teaching point is that matching, weighting, and AIPW do not have to produce identical estimates. Differences between them are often a signal to inspect overlap, balance, covariate specification, and target estimand rather than to choose the most favorable estimate.

Propensity-score model

Balance diagnostics and Love plot

Balance diagnostics are the most important part of a propensity-score analysis. The standardized mean difference (SMD) compares covariate means between treated and control groups on a standardized scale. A common rule of thumb is to review covariates with absolute SMD greater than 0.1.

In this tutorial run, lpi, idp, and disea remain above the 0.1 SMD threshold after matching. The reason is visible in the run summary: there are 160 treated rows and 160 control rows, and 1:1 matching without replacement uses all controls. Because all rows are retained, the matched sample has the same covariate means as the original sample. This is a useful diagnostic lesson: a completed match is not automatically a balanced match.

Weight and overlap diagnostics

The treated group has an effective sample size of 160, while the weighted control effective sample size is about 65.7. This indicates that the weighted comparison relies on unequal control weights. That does not invalidate the analysis, but it means the weighting results should be interpreted together with balance and overlap diagnostics.

The overlap summary flags observations outside support and extreme propensity scores in both groups. This supports a cautious interpretation and motivates sensitivity analyses with calipers, common-support restrictions, or overlap-focused estimands.

Matched pairs

The full BESH Stat NG output contains all 160 matched sets. The first 10 matched pairs are shown below as an audit excerpt.

Doubly robust AIPW estimate

The AIPW result is useful as a sensitivity estimate because it combines propensity-score adjustment with an outcome-model component. It should not be treated as a substitute for balance checking. If covariate balance or overlap is poor, the AIPW estimate can still depend heavily on modeling assumptions.

Rosenbaum-style matched-pair sensitivity

The Rosenbaum-style sensitivity section provides additional information for matched-pair analyses. It is most useful when the matched comparison is the primary estimand and the matched pairs show acceptable balance.

UDF workflow

The same analysis can be driven from worksheet formulas. This is useful for templates, reproducible teaching examples, and dashboards where users want to refresh outputs after changing inputs or options.

First fit the propensity-score analysis and store the returned handle in a cell, for example A1 on a worksheet named UDF_Workflow.

=BESH.PS.FIT(Input_Data!A2:A321, Input_Data!B2:B321, Input_Data!C2:C321,
             Input_Data!D2:J321, Input_Data!D1:J1,
             "matching", "ATT", "logit", , ,
             "lpi + idp + physlm + disea + hlthg + hlthf + hlthp",
             "ratio=1; replacement=false; distance=ps; order=descending; ridge=1e-7; maxIter=100; tol=1e-7",
             "smd=0.1; overlapBins=20; lovePlot=true")

Then use the handle to return output tables:

=BESH.PS.SUMMARY(A1)
=BESH.PSM.MATCHES(A1)
=BESH.PS.SCORES(A1)
=BESH.PS.WEIGHTS(A1)
=BESH.PS.BALANCE(A1)
=BESH.PS.EFFECT(A1)
=BESH.PS.LOVEPLOT_DATA(A1)
=BESH.PS.CLEANUP(A1)

For an exact benchmark using an already-computed propensity score column, use scoreMethod = supplied and pass the existing score column:

=BESH.PS.FIT(Input_Data!A2:A321, Input_Data!B2:B321, Input_Data!C2:C321,
             Input_Data!D2:J321, Input_Data!D1:J1,
             "matching", "ATT", "supplied", Input_Data!K2:K321, ,
             "",
             "ratio=1; replacement=false; distance=ps; order=descending",
             "smd=0.1; overlapBins=20; lovePlot=true")

When to use each PSM option

How to report the example

A compact report might read as follows:

We fitted a logistic propensity-score model for cost-sharing plan assignment using lpi, idp, physlm, disea, and health-status indicators. The default 1:1 nearest-neighbor ATT analysis matched all 160 treated observations to 160 controls. The matched mean difference in MD visits was -0.588 (95% CI -1.638 to 0.463), so the matched-pair estimate was not statistically distinguishable from zero. However, balance diagnostics showed residual imbalance for lpi, idp, and disea, and overlap diagnostics flagged observations outside support. Weighted and AIPW sensitivity estimates were more negative, but they should be interpreted cautiously because residual imbalance and control effective sample size indicate that the adjusted comparison remains model-dependent.

Sensitivity analyses with alternative matching options

Download results workbooks: psm_results2.xlsx, psm_results3.xlsx

The default run is useful, but it also shows a common pitfall: a completed 1:1 match is not automatically a well-balanced matched design. Because the default run matches all 160 treated observations to all 160 controls, the after-matching covariate means are the same as the original treated/control comparison. For that reason, is good to include at least one sensitivity analysis that changes the design.

The two additional runs below use the same RAND HIE input data and the same logistic propensity-score model, but they change the matching rules. The goal is not to select the run with the smallest p-value. The goal is to show how sample size, balance, overlap, and effect estimates change when the matching design is made stricter or more flexible.

The caliper run keeps the same nearest-neighbor ATT design but adds a 0.2 caliper on the standardized logit propensity score. This prevents many distant matches, so the matched sample drops from 160 pairs to 87 pairs. The Mahalanobis/common-support run keeps almost all eligible treated observations, but it allows replacement and therefore uses only 47 unique controls for 156 matched treated observations.

Treatment-effect estimates across runs

The default matched estimate is -0.588 MD visits and its confidence interval crosses zero. The caliper run gives a more negative matched estimate (-1.414), but the smaller matched sample makes the interval wider and it still crosses zero. The Mahalanobis/common-support run gives a negative estimate with a confidence interval below zero, but it achieves this design by reusing controls: only 47 unique controls are used for 156 matched treated observations.

The weighting and AIPW estimates are unchanged across these three runs because the same propensity-score model and weighting definitions are used. They are useful as sensitivity estimates, but they do not replace balance and overlap diagnostics for the matched design.

Balance comparison

The default match does not improve balance because all treated and all control observations are used. The caliper run improves balance for idp and disea, but lpi, physlm, and hlthg still exceed the 0.1 threshold. The Mahalanobis/common-support run gives the best after-matching balance in this example, leaving only lpi above 0.1, but the repeated use of controls should be reported as part of the design.

How to interpret the sensitivity runs

• Default run: It is deliberately diagnostic: matching all rows leaves the main imbalances unchanged.
• Caliper run: best for showing the cost of stricter matching. Balance improves for some covariates, but 73 treated rows are left unmatched and the matched estimate becomes less precise.
• Mahalanobis/common-support run: best for showing a more balanced matched design. It improves most SMDs, but it relies on matching with replacement and therefore reuses controls.
• Weighted and AIPW estimates: useful sensitivity estimates, especially when matching and weighting point in the same direction, but they remain model-dependent and should be read together with overlap and effective-sample-size diagnostics.

Recommended wording for the tutorial interpretation

In this RAND HIE example, the default nearest-neighbor ATT run matched all 160 treated observations to all 160 controls, but covariate balance did not improve because all controls were used. Adding a standardized-logit caliper produced a smaller matched sample of 87 pairs and improved balance for several covariates, although the matched ATT confidence interval still crossed zero. A Mahalanobis-distance run with replacement and common-support restriction produced the best balance among the three runs and a negative matched ATT estimate with a confidence interval below zero, but the design reused controls heavily, with 47 unique controls matched to 156 treated observations. The practical conclusion is that PSM should be reported as a design-and-diagnostics workflow: treatment-effect estimates should be interpreted only after balance, overlap, matched sample size, and control reuse have been reviewed.

References and source material

• RAND HIE / statsmodels dataset
• MatchIt R package
• MatchIt nearest-neighbor documentation
• cobalt balance diagnostics package
• BESH Stat NG documentation

See also

• Tutorials hub
• Validation
• UDF Cookbook
• Binary classifier reporting in Excel
• ROC analysis in Excel
• Formula-driven workbook templates with UDFs
• GLM help
• Regression-model UDF help
• Plot-data UDF help