Why does my join have more rows than both tables?

At least one key appears more than once on both sides, so the join produces every combination of those rows (a many-to-many match).

Is a one-to-many join wrong?

No. One customer to many orders is normal. It only causes problems if you then sum a column that belongs to the 'one' side, because that value repeats on every row.

How do I stop VLOOKUP creating duplicates?

VLOOKUP never adds rows; it returns the first match only. Joins in Power Query, SQL or pandas add rows. If VLOOKUP gives different numbers from a join, duplicate keys are the reason.

Should I remove duplicates or aggregate?

Remove them if they are the same record entered twice. Aggregate if they are separate real events, such as several orders for one customer.

Why you get duplicate rows after joining two tables

By the Analistable team · Updated 2026-10-10 · 5 min read

A join returns one output row for every pair of rows whose keys match. If a key appears twice on one side, every matching row on the other side is repeated twice; if it appears twice on both sides you get four rows. Count each key on both sides before joining, then either remove true duplicates or aggregate the many side to one row per key.

Part of our guide: How to join spreadsheets on a common column

What fan-out looks like

Fan-out is not a bug in the join. It is the join doing exactly what it is defined to do: pair every row on the left with every row on the right that has the same key. It only looks like duplication because you expected one match per key and the data had more.

Take a customer list and an orders file joined on Customer ID. The customer list should have one row per customer, but C-102 was entered twice after a change of address.

Customers (left) — C-102 appears twice
Customer ID	Name	Region
C-101	Harper Ltd	North
C-102	Okafor & Sons	South
C-102	Okafor & Sons	West
C-103	Lindqvist AB	North

Orders (right) — C-102 has three orders
Order	Customer ID	Amount
O-1	C-101	400
O-2	C-102	250
O-3	C-102	150
O-4	C-102	100
O-5	C-103	300

Joined, C-101 and C-103 produce one row each, but C-102 produces 2 × 3 = 6 rows. The result has 8 rows instead of 5 orders, and total revenue reads 400 + (250 + 150 + 100) × 2 + 300 = 1,700 instead of the real 1,200.

Rows per key after the join
Customer ID	Rows in customers	Rows in orders	Rows after join
C-101	1	1	1
C-102	2	3	6
C-103	1	1	1
Total	4	5	8

One-to-one, one-to-many and many-to-many

One-to-one: each key appears at most once on both sides. Row count stays the same (or drops, for an inner join).
One-to-many: the key is unique on one side and repeated on the other — one customer, many orders. The output has one row per order, and customer details repeat on each. That is usually what you want, as long as you don't then sum a customer-level number such as a credit limit.
Many-to-many: the key repeats on both sides. Rows multiply, and totals from either side are inflated. This is almost always a mistake in spreadsheet work.

Before any join, decide which of these you expect. The general method is covered in joining two spreadsheets on a common column and the wider join spreadsheets guide.

How to find the non-unique keys

In Excel, add a helper column next to each table's key and count how often it occurs:

=COUNTIF($A$2:$A$500, A2)

Filter the helper column for values above 1. In Microsoft 365 you can list the offending keys directly:

=UNIQUE(FILTER(A2:A500, COUNTIF(A2:A500, A2:A500) > 1))

In SQL, group by the key and keep groups with more than one row:

SELECT customer_id, COUNT(*) AS n
FROM customers
GROUP BY customer_id
HAVING COUNT(*) > 1
ORDER BY n DESC;

Run the same check on both tables. A key that repeats on one side is fine if you meant one-to-many; a key that repeats on both is the cause of the explosion. Also compare row counts: if the joined table has more rows than the larger input and you expected one-to-many, something is many-to-many.

Keys that look unique can still collide after cleaning. If you trim spaces or change case to make a join work, run the duplicate check again on the cleaned key.

Fix 1: remove true duplicates

If the repeated rows are the same record entered twice, keep one. In Excel, select the table and use Data → Remove Duplicates, ticking only the key column (Excel keeps the first occurrence). In Power Query, select the key column and choose Remove Rows → Remove Duplicates. In SQL:

SELECT *
FROM customers
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) = 1;

That keeps the most recently updated row per customer. QUALIFY works in DuckDB, Snowflake and BigQuery; in other databases wrap the window function in a subquery and filter on it. Deciding which copy to keep is a business rule — latest, first, or the one with a non-blank email — so write it down.

Fix 2: aggregate the many side first

If both rows are genuine — several orders, several payments — collapse the many side to one row per key before joining. You then join one-to-one and nothing repeats.

WITH order_totals AS (
  SELECT customer_id, COUNT(*) AS orders, SUM(amount) AS revenue
  FROM orders
  GROUP BY customer_id
)
SELECT c.customer_id, c.name, o.orders, o.revenue
FROM customers_clean c
LEFT JOIN order_totals o USING (customer_id);

Result after aggregating orders and de-duplicating customers
Customer ID	Name	Orders	Revenue
C-101	Harper Ltd	1	400
C-102	Okafor & Sons	3	500
C-103	Lindqvist AB	1	300

Revenue now totals 1,200, matching the orders file. In Excel the equivalent is SUMIFS against the customer list rather than a row-level lookup; in Power Query, use Group By on the orders query before the merge.

Fix 3: add the missing part of the key

Sometimes neither side is wrong — the key is just incomplete. Monthly budget rows joined to monthly actuals on Department alone will fan out twelve-by-twelve; the real key is Department plus Month. Join on every column that together identifies a row. In SQL that is ON a.dept = b.dept AND a.month = b.month; in Excel build a combined key such as =A2&"|"&B2 on both sides, or use XLOOKUP with (A2:A500=F2)*(B2:B500=G2) as the lookup array and 1 as the value.

Checks to run after every join

Row count: inner or left join of one-to-many should equal the many side's count (minus unmatched rows for inner).
A control total: sum a numeric column before and after. If revenue grew, rows were duplicated.
Spot-check one key you know has several rows on each side.

Analistable runs joins as SQL in DuckDB in your browser, so you can ask “how many rows per customer ID are in each sheet?” before asking the real question, and the AI-written SQL is shown so you can see whether it aggregated before joining. The inner join in Excel article covers the formula side if you're staying in Excel.

Frequently asked questions

Why does my join have more rows than both tables?: At least one key appears more than once on both sides, so the join produces every combination of those rows (a many-to-many match).
Is a one-to-many join wrong?: No. One customer to many orders is normal. It only causes problems if you then sum a column that belongs to the 'one' side, because that value repeats on every row.
How do I stop VLOOKUP creating duplicates?: VLOOKUP never adds rows; it returns the first match only. Joins in Power Query, SQL or pandas add rows. If VLOOKUP gives different numbers from a join, duplicate keys are the reason.
Should I remove duplicates or aggregate?: Remove them if they are the same record entered twice. Aggregate if they are separate real events, such as several orders for one customer.