Shoelace formula in Excel: the area of a polygon from its coordinates

The shoelace formula

Area = ½ × |Σ (xᵢ · yᵢ₊₁ − xᵢ₊₁ · yᵢ)|

The vertices (x₁, y₁) … (xₙ, yₙ) must be listed in order around the shape, and the last one connects back to the first, so after the last vertex the "next" point is vertex 1 again. Writing the products crosswise between two columns looks like lacing a shoe, hence the name. Mathematicians also call it Gauss's area formula or the surveyor's formula. It works for any polygon whose edges do not cross, convex or not, with any number of corners.

Row by row in Excel

Put the vertices in B2:C7 and repeat the first vertex in row 8 (=B2 and =C2), so the last edge has a "next" row too. In D2, the cross term of the first edge is =B2*C3-B3*C2: this row's x times the next row's y, minus the next row's x times this row's y. Fill it down to D7. The cross terms of the plot are −30, 846, 1,035, 711, 162 and −36, and their sum is 2,688. Half of the absolute value is the area: 1,344 m². The helper column makes it easy to check each edge, which is useful when a surveyor's list has a typo.

Worked example: the area of a six-corner building plot

Corner coordinates in metres from a site plan, listed anticlockwise. Row 8 repeats the first corner to close the shape. Column D holds the cross terms and column E the length of each edge.

Worked example: the area of a six-corner building plot

	A	B	C	D	E
1	Vertex	x (m)	y (m)	Cross term	Edge length (m)
2	P1	2	1	-30	36.12
3	P2	38	4	846	24.04
4	P3	45	27	1,035	20.52
5	P4	30	41	711	21.59
6	P5	9	36	162	20.12
7	P6	0	18	-36	17.12
8	P1 again (closes the shape)	2	1
9
10	Sum of the cross terms (signed, ×2 area)			2,688
11	Area row by row: |sum| ÷ 2 (m²)			1,344
12	Area in one formula with SUMPRODUCT (m²)			1,344
13	Area without the repeated row: LET + VSTACK (m²)			1,344
14	Perimeter (m)			139.51
15	Area in hectares			0.1344
16	Check: bounding rectangle 45 m × 40 m (m²)			1,800

A fictional plot. Coordinates on a flat grid, for example from a site plan or a projected map in metres.

In one cell with SUMPRODUCT

The sum of the cross terms is the same as Σxᵢyᵢ₊₁ − Σxᵢ₊₁yᵢ, and each of those is a SUMPRODUCT of two ranges shifted by one row against each other:

=ABS(SUMPRODUCT(B2:B7,C3:C8)-SUMPRODUCT(B3:B8,C2:C7))/2

B2:B7 with C3:C8 pairs every x with the next y, and B3:B8 with C2:C7 every next x with this y. Both ranges in each SUMPRODUCT must have the same number of rows, six here, and the repeated vertex in row 8 closes the shape.

Without the repeated vertex: LET and VSTACK

With dynamic arrays (Microsoft 365, Excel 2024) you can build the "next vertex" columns inside the formula: VSTACK(DROP(x,1),TAKE(x,1)) moves the first value to the end. Wrapped in LET:

=LET(x,B2:B7,y,C2:C7,nx,VSTACK(DROP(x,1),TAKE(x,1)),ny,VSTACK(DROP(y,1),TAKE(y,1)),ABS(SUMPRODUCT(x,ny)-SUMPRODUCT(nx,y))/2)

It returns the same 1,344 m² from the six corners alone, and you only change two ranges for a polygon with more corners. The LAMBDA guide shows how to save a formula like this as your own function, for example PolygonArea(x, y).

Clockwise, anticlockwise and units

• Direction: anticlockwise order gives a positive sum, clockwise a negative one. The size is the same, so ABS makes the order irrelevant. The sign is still useful: it tells you which way the points run.
• Order matters: the corners must follow the outline. Shuffled corners make the edges cross and the result is wrong, without any error message.
• Units: coordinates in metres give square metres. Divide by 10,000 for hectares (0.1344 ha here). On a drawing at a scale of 1:500, multiply an area measured in drawing units by 500².
• Latitude and longitude: convert them to a projected grid in metres first. Degrees are not equal distances, as the haversine guide explains.

Checks and the centroid

A quick sanity check is the bounding rectangle: the plot spans 45 m × 40 m, 1,800 m², so 1,344 m² is plausible. Test the formula on a shape you know, such as a 10 × 10 square (100). The same cross terms also give the centroid, the balance point of the area: Cx = Σ (xᵢ + xᵢ₊₁) · crossᵢ ÷ (6A) and Cy = Σ (yᵢ + yᵢ₊₁) · crossᵢ ÷ (6A), with the signed area A. It is not the average of the corners.

Common mistakes

• Corners out of order. The vertices must follow the outline. Jumping across the shape creates crossing edges and a wrong area.
• Not closing the polygon. Without the last edge back to the first corner the sum misses one cross term. Repeat the first vertex or use the LET version.
• Forgetting ABS. Clockwise corners give a negative sum. Take the absolute value before halving it.
• Forgetting to halve. The sum of the cross terms is twice the area.
• Shifted ranges of different sizes. SUMPRODUCT(B2:B7,C3:C9) returns #VALUE!. Both ranges must have the same number of rows.
• Using latitude and longitude directly. Degrees are not metres. Project the coordinates first.