How to calculate the margin of error in Excel

The margin of error formula

Margin of error = z × √(p × (1 − p) ÷ n)

• p: the share you measured, as a decimal (52% = 0.52).
• n: the number of completed interviews behind that share.
• z: the value from the normal distribution for your confidence level: 1.645 for 90%, 1.96 for 95%, 2.576 for 99%.

In the example: 1.96 × √(0.52 × 0.48 ÷ 1,000) = 1.96 × 0.0158 = 0.031, or ±3.1 percentage points. The confidence interval is the share plus and minus the margin: 52% ± 3.1 points = 48.9% to 55.1%. Note that the margin is in percentage points, not percent of the share.

Worked example: margins of error for a referendum poll

The poll and the city are fictional: 1,000 likely voters, 52% Yes and 41% No. Rows 7 to 10 give the margin and the 95% interval for the whole poll and three age groups. Rows 12 to 19 cover the headline margin, the lead and the sample size for the next poll.

Worked example: margins of error for a referendum poll

	A	B	C	D	E	F
1	Input		Value
2	Confidence level		95%
3	z value		1.960
4	No share (all voters)		41.0%
5
6	Group	Interviews	Yes share	Margin ±	Low	High
7	All voters	1,000	52.0%	3.1%	48.9%	55.1%
8	Age 18–34	220	61.0%	6.4%	54.6%	67.4%
9	Age 35–64	520	50.0%	4.3%	45.7%	54.3%
10	Age 65+	260	47.0%	6.1%	40.9%	53.1%
11
12	Headline margin (p = 50%, all interviews)			3.1%
13	Same with CONFIDENCE.NORM			3.1%
14	Lead: Yes − No			11.0%
15	Margin of the lead (same poll)			5.9%
16	Target margin for the next poll			2.0%
17	Interviews needed (p = 50%)			2,401
18	Voters in a small ward			6,000
19	Interviews needed there (finite population correction)			1,715

Fictional poll for practice only. The margins assume a simple random sample.

The z value with NORM.S.INV

Instead of typing 1.96, let Excel calculate z from the confidence level in a cell: =NORM.S.INV(1-(1-C2)/2). With 95% in C2 this is NORM.S.INV(0.975) = 1.960. The margin is two-sided, so half of the 5% goes into each tail of the normal curve. =NORM.S.INV(0.95) would give 1.645, the one-sided value, and a margin that is too narrow.

Excel also has CONFIDENCE.NORM(alpha, standard_dev, size), which returns the same margin directly. For a share, the standard deviation is √(p(1 − p)): =CONFIDENCE.NORM(0.05,SQRT(0.52*0.48),1000). In row 13 of the example, with p = 50%, it reproduces the headline margin.

The headline margin and subgroups

Pollsters print one margin under the headline. It is calculated at p = 50%, where p(1 − p) is largest, so it is the worst case for every share in the poll: 1.96 × √(0.25 ÷ 1,000) = ±3.1 points. It is the margin of the full sample only.

Every subgroup has its own, smaller n, and so a wider margin. The 220 voters aged 18 to 34 give ±6.4 points, the 520 aged 35 to 64 ±4.3 and the 260 aged 65 and over ±6.1. A difference between two age groups that looks large can still be within the noise. The same logic applies to customer surveys, see NPS calculation in Excel.

Is a lead bigger than the margin of error?

The lead of Yes over No in the same poll is 52% − 41% = 11 points. Its margin is not simply twice ±3.1, because the two shares come from the same people and move against each other. The margin of the difference of two shares in one poll is:

=z*SQRT((p1+p2-(p1-p2)^2)/n)

Here that is ±5.9 points. The 11-point lead is clearly larger, so Yes is ahead beyond the random sampling error. A rule of thumb many journalists use is that a lead needs to be about twice the headline margin to be clear.

Sample size for a target margin

Turn the formula around to plan the next poll: n = z² × p(1 − p) ÷ E², with E the target margin. For ±2 points at 95% and p = 50%: 1.96² × 0.25 ÷ 0.02² = 2,401 interviews, rounded up with ROUNDUP. Halving the margin needs four times the interviews, which is why few polls go below ±2 or ±3 points.

When the sample is a large part of a small population, apply the finite population correction: n = n₀ ÷ (1 + (n₀ − 1) ÷ N). In a ward of 6,000 voters the same ±2 points need only 1,715 interviews. For tests between two groups rather than one share, see A/B test sample size.

What the margin of error does not cover

The margin measures random sampling error only, under the assumption of a simple random sample. It does not include people who refuse to answer, a list that misses part of the population, question wording or opinions that change after the fieldwork. Weighted polls are also less precise than their raw n suggests: the design effect reduces the effective sample size and widens the margin. A poll can be precise and still wrong.

Common mistakes

• Using the one-sided z. NORM.S.INV(0.95) gives 1.645 and a margin that is too narrow. A ± interval at 95% needs NORM.S.INV(0.975) = 1.96.
• Applying the full-sample margin to a subgroup. A group of 220 has a margin of about ±6 points, not ±3. Use the subgroup's own n.
• Doubling the margin for a lead. The lead within one poll has its own formula. Doubling the headline margin is only a rough rule.
• Forgetting the square root. z × p(1 − p) ÷ n gives a tiny number. The standard error is the square root of p(1 − p) ÷ n.
• Reading the margin as percent of the share. ±3.1 means percentage points: 52% can be 48.9% to 55.1%, not 52% ± 3.1% of 52.
• Ignoring weighting and non-response. The formula assumes a simple random sample. Weighted and online panel polls have a larger real error.