Difference-in-differences in Excel: the 2×2 table and the regression

Why two simple comparisons both mislead

A fictional grocery chain introduced free delivery in its four East stores in April. The four West stores kept the old service. The question is what free delivery did to weekly sales, and the answer depends on what East would have sold without it, the counterfactual nobody can observe.

• Before and after in East: +6.5 thousand a week. But spring sales rise anyway, so part of that is the season.
• East against West after April: East is still 2.0 below West. But East was already 6.0 below before, because its stores are smaller.

Difference-in-differences combines both comparisons. The West change (+2.5) estimates the season and the market trend, the East change (+6.5) contains the trend plus the effect, and the difference of the two changes, 4.0, is the effect of free delivery.

Worked example: free delivery in the East stores

Rows 2 to 17 are average weekly sales in thousand dollars for four West and four East stores, before (January to March) and after (April to June) free delivery started in East. Rows 19 to 22 are the 2×2 table, rows 24 to 31 the regression. The chain and its numbers are fictional.

Worked example: free delivery in the East stores

	A	B	C	D	E
1	Region	Treated	Post	Treated × Post	Sales ($k/wk)
2	West	0	0	0	50.0
3	West	0	0	0	49.0
4	West	0	0	0	51.0
5	West	0	0	0	48.0
6	West	0	1	0	53.0
7	West	0	1	0	51.0
8	West	0	1	0	54.0
9	West	0	1	0	50.0
10	East	1	0	0	44.0
11	East	1	0	0	43.0
12	East	1	0	0	45.0
13	East	1	0	0	42.0
14	East	1	1	1	51.0
15	East	1	1	1	49.0
16	East	1	1	1	51.0
17	East	1	1	1	49.0
18
19	Mean sales	East (treated)	West (control)	East − West
20	Before (Jan–Mar)	43.50	49.50	-6.00
21	After (Apr–Jun)	50.00	52.00	-2.00
22	Change	6.50	2.50	4.00
23
24	LINEST: Treated × Post (b3 = DiD)				4.00
25	LINEST: Post (b2, common trend)				2.50
26	LINEST: Treated (b1, gap before)				-6.00
27	LINEST: Intercept (b0, West before)				49.50
28	Standard error of b3				1.41
29	t statistic of b3				2.83
30	Two-sided p-value (df = 12)				0.015
31	Effect vs counterfactual (B20 + C22)				8.7%

The standard error assumes independent rows. With real panel data, cluster the standard errors by unit in a statistics package.

Step 1: the 2×2 table with AVERAGEIFS

Keep the data in long format: one row per store and period, with a 0/1 column Treated (1 for East) and a 0/1 column Post (1 for April to June). Each cell of the 2×2 table is then one AVERAGEIFS, for example the East stores before April: =AVERAGEIFS(E2:E17,B2:B17,1,C2:C17,0) = 43.5.

Read the table in both directions. Down the columns you get each region's change (6.5 and 2.5), across the rows the gap between the regions (−6.0 before, −2.0 after). The DiD is the difference of the changes or, equally, the change in the gap: −2.0 − (−6.0) = 4.0. Both ways must give the same number, which is a good check of the formulas.

Step 2: the DiD regression with an interaction term

The same estimate comes out of a regression: sales = b0 + b1·Treated + b2·Post + b3·(Treated × Post). The interaction column is simply =B2*C2, which is 1 only for East stores after April. Each coefficient has a meaning:

• b0 = 49.5: West before, the baseline.
• b1 = −6.0: the gap between East and West before the change.
• b2 = 2.5: the common trend from the first to the second quarter.
• b3 = 4.0: the difference-in-differences, the effect of free delivery.

LINEST returns the coefficients in reverse order of the x columns, so with Treated, Post and Treated × Post in columns B to D, =INDEX(LINEST(E2:E17,B2:D17),1,1) is b3 and column 4 is the intercept. The same model can be run with the Analysis ToolPak (Data, Data Analysis, Regression) if you prefer a printed table. For more on reading LINEST output, see the LINEST guide.

Is the effect significant?

With LINEST(…,TRUE,TRUE) the second row holds the standard errors. The standard error of b3 is 1.41, so t = 4.0 ÷ 1.41 = 2.83 with 16 − 4 = 12 degrees of freedom, and =T.DIST.2T(2.83,12) gives a p-value of 0.015. The effect is unlikely to be chance.

Be careful with that standard error on real data. Plain regression treats all 16 rows as independent, but the same eight stores appear twice, and stores in one region often move together. Proper analyses cluster the standard errors by store or region, which Excel cannot do directly, and with only a few clusters any p-value is fragile. Treat the Excel p-value as a first check, not as proof.

The parallel trends assumption

DiD only works if, without the change, the treated and control groups would have moved in parallel. The West trend is used as East's counterfactual, so if East was already growing faster before April, part of that head start is counted as the effect.

You cannot prove the assumption, but you can test it with data from before the change:

• Plot both groups' monthly sales for several months before the start and check that the lines run side by side.
• Compare the slopes of the pre-period gap: a gap that is already shrinking is a warning.
• Run a placebo DiD with a fake start date inside the pre-period. An "effect" before the real change means the trends differ.

Also check that nothing else changed in East in April (a competitor closing, a new store opening), that customers did not move between regions, and that the treated group was not chosen because it was having a bad quarter.

DiD, A/B tests and other methods

If you can choose who gets the change at random, a randomised experiment is cleaner than DiD, because the groups are alike by design (the A/B test sample size guide shows how many units you need). DiD is the method for changes you cannot randomise: a policy in one region, a price change in some stores, a feature launched in one country. When there is no good control group, matching the treated units to similar untreated ones, or a regression discontinuity at a cutoff, are the usual alternatives.

Common mistakes

• Comparing only before and after. The change in the treated group alone mixes the effect with the season and the market trend. Always subtract the control group's change.
• Reading the wrong LINEST column. LINEST returns coefficients in reverse order of the x columns. With Treated, Post and the interaction in that order, the DiD is column 1, not column 3.
• Forgetting the interaction term. A regression with only Treated and Post has no DiD coefficient. The interaction column (Treated × Post) is the estimate.
• Skipping the parallel trends check. If the treated group was already growing faster, DiD credits that head start to the change. Plot the pre-period and run a placebo test.
• Trusting the plain standard error. Repeated observations of the same units are not independent. The Excel standard error is usually too small for real panel data.
• A control group that was also affected. If West customers started ordering from East stores, the control group changed too and the estimate is biased.