Cosine similarity in Excel: formula and worked example

The cosine similarity formula

cos(A, B) = (A · B) ÷ (‖A‖ × ‖B‖)

• The dot product A · B multiplies the vectors component by component and adds up: SUMPRODUCT.
• The length (L2 norm) ‖A‖ is the square root of the sum of squares: SQRT(SUMSQ(…)).
• The ratio is the cosine of the angle between the vectors: 1 at 0°, 0 at 90° (unrelated) and −1 at 180° (opposite).

Both ranges must have the same size and the same order of dimensions, otherwise SUMPRODUCT returns an error or, worse, a meaningless number.

Step by step in the example

The query vector has a length of =SQRT(SUMSQ(B2:B6)) = 0.9539. For the "Exchange a size" article, the dot product is =SUMPRODUCT(E2:E6,$B$2:$B$6) = 0.71 and the article's length is 0.7533. Cosine similarity = 0.71 ÷ (0.7533 × 0.9539) = 0.988, an angle of only 8.9°. The "Boot size guide" scores 0.893 and the "Returns & refunds" article 0.684. Lock the query range with $ so the formula copies across all documents.

Worked example: which help article answers the question best?

Five-dimensional toy embeddings (yellow) for a customer question and three help articles of a fictional outdoor shop. Rows 7 to 13 compute the lengths, dot products, cosine similarities, angles, Euclidean distances and both rankings.

Worked example: which help article answers the question best?

	A	B	C	D	E
1	Dimension	Query	Returns & refunds	Boot size guide	Exchange a size
2	returns	0.50	1.20	0.10	0.45
3	shipping	0.00	0.40	0.00	0.05
4	sizing	0.80	0.30	0.90	0.60
5	waterproof	0.10	0.00	0.20	0.00
6	warranty	0.10	0.30	0.00	0.05
7	Length ‖v‖	0.9539	1.3342	0.9274	0.7533
8	Dot product with query		0.8700	0.7900	0.7100
9	Cosine similarity		0.6836	0.8930	0.9880
10	Angle (degrees)		46.9	26.7	8.9
11	Euclidean distance		0.9747	0.4359	0.2398
12	Rank by cosine		3	2	1
13	Rank by dot product		1	2	3
14	Best match by cosine	Exchange a size

Toy vectors with readable dimensions for practice. Real embedding models produce much longer vectors.

Why not just use the dot product?

The dot product grows with the length of the vectors, not only with the angle between them. The long returns article has the largest vector (length 1.334) and therefore the highest dot product, 0.87, so a dot-product ranking puts it first. Cosine divides the lengths out and ranks by direction only, which moves the short, precise "Exchange a size" article to the top and the long article to last place. If all vectors are first normalised to length 1, the dot product and cosine are the same number. That is why many vector databases normalise embeddings once and then use the cheaper dot product.

Cosine similarity vs Euclidean distance

Euclidean distance is the straight-line distance between the two points: =SQRT(SUMXMY2(C2:C6,$B$2:$B$6)). Small is close. Here it agrees with cosine: the "Exchange a size" article is closest at 0.240 and the returns article farthest at 0.975. The two measures can disagree when vector lengths differ a lot, because distance penalises a long vector even when it points the right way. For normalised vectors they always give the same order, since distance² = 2 − 2 × cosine.

Ranking documents and picking the best match

Rank all candidates by cosine with =RANK.EQ(C9,$C$9:$E$9) and return the winner's title with =INDEX(C1:E1,MATCH(MAX(C9:E9),C9:E9,0)). With many documents, LARGE returns the top 3 or top 5 scores, which is what a vector search does in a retrieval-augmented generation (RAG) system before the text goes to the language model. To judge whether the right document comes back, use ranking metrics such as NDCG and MRR.

Real embeddings

Real text embeddings have hundreds or thousands of dimensions instead of five, and the dimensions have no readable labels like "sizing". The formula is exactly the same: put each vector in a column, or in a row with the ranges turned around. With very long vectors, keep them in a separate sheet and compute the lengths once in a helper row instead of inside every cosine formula. Excel handles a few thousand dimensions per vector without trouble, but for large collections a vector database or a script is the right tool.

Common mistakes

• Ranking by the dot product on unnormalised vectors. Long documents win because they are long, not because they match. Use cosine or normalise first.
• Forgetting the square root. SUMSQ is the squared length. The length is SQRT(SUMSQ(…)).
• Ranges in a different order. Dimension 1 of the query must meet dimension 1 of the document. Check that both columns cover the same rows.
• Comparing vectors from different models. Cosine between embeddings of two different models, or two model versions, is meaningless.
• Treating 0.8 as a universal cut-off. Typical similarity levels differ by model. Set thresholds from labelled examples.
• A relative query reference. Without $B$2:$B$6 the query range shifts when you copy the formula across documents.