Google Sheets で2列間の重複を強調表示する

Tested in: Live formula checks in Microsoft Excel 16.114.4 for Mac (English UI; system region zh-Hans_US), November 8, 2026; Google Sheets (spreadsheet locale zh_CN), November 9, 2026. Results and platform differences are noted below; this is not a certification of every menu workflow.

Which versions this applies to

Google Sheets on desktop. The same formulas work in Excel’s Conditional Formatting > New Rule > Use a formula, with the differences noted in the Excel section.

Example data

Type this into A1:B7:

What each method below should highlight with this data:

Note that “Eli” and “eli” count as a match. COUNTIF is not case-sensitive, according to Google’s documentation. If that’s a problem, use the case-sensitive version in Method 6.

How to add a custom formula rule (do this once per rule)

1. Select the range you want to color, e.g. A2:A100. Start at row 2 so the header isn’t included.
2. Click Format > Conditional formatting. The rules panel opens on the right.
3. Check Apply to range shows the range you selected.
4. Under Format rules, open the Format cells if… drop-down and choose Custom formula is.
5. Paste the formula into the Value or formula box.
6. Pick a fill color under Formatting style, then click Done.

The one rule that trips everyone up: write the formula for the top-left cell of your range. Google’s help page puts it as “write the rule for the first row”. Use $ to lock the range you’re searching, and leave the cell you’re testing (A2) without $ so it moves down row by row.

Method 1: Highlight values in column A that also appear in column B

Apply to range: A2:A100

=AND(A2<>"", COUNTIF($B$2:$B$100, A2)>0)

How it works: for each cell in A, COUNTIF counts how many times its value appears in B. More than 0 means it’s in both lists. A2<>"" stops empty cells from being highlighted.

Your list keeps growing? Sheets accepts open-ended ranges, so you can use A2:A as the range and this formula:

=AND(A2<>"", COUNTIF($B$2:$B, A2)>0)

Method 2: Highlight the matching values in both columns

Add two rules, one per column.

Rule 1, apply to range: A2:A100

=AND(A2<>"", COUNTIF($B$2:$B$100, A2)>0)

Rule 2, apply to range: B2:B100

=AND(B2<>"", COUNTIF($A$2:$A$100, B2)>0)

Each rule looks in the other column. That’s why a value repeated inside one column (Ben in A3 and A7) is not highlighted here. Use Method 3 if you also want those.

Method 3: Highlight duplicates anywhere across both columns

Apply to range: A2:B100

=AND(A2<>"", COUNTIF($A$2:$B$100, A2)>1)

The search range now covers both columns, and the test is >1 because each value always finds itself once. Google uses the same pattern for single-column duplicates in its conditional formatting help (=COUNTIF($A$1:$A$100,A1)>1). Here it’s just widened to two columns.

Because the range starts at A2, the relative A2 in the formula becomes B2, B3 and so on for cells in column B. You don’t need a separate rule.

Method 4: Highlight rows where both columns match on the same row

Apply to range: A2:B100

=AND($A2<>"", $A2=$B2)

The $ before the column letters keeps both cells in the comparison fixed to A and B, while the row number moves. Use this for “before vs. after” lists where row 5 in A should match row 5 in B.

Want to flag rows that don’t match instead? Use:

=AND($A2<>"", $A2<>$B2)

Method 5: Highlight values that are missing from the other column

Apply to range: A2:A100

=AND(A2<>"", COUNTIF($B$2:$B$100, A2)=0)

This is handy for “who was invited but didn’t RSVP” lists. You can stack it with Method 1 using a different color: green for matches, red for missing. Google’s documentation notes that rules are evaluated in the order listed and the first rule that’s true decides the format, so the order matters only when two rules can both be true for the same cell.

Method 6: Case-sensitive matches

COUNTIF ignores case, and so does the = operator. For a case-sensitive match, use EXACT inside SUMPRODUCT:

Apply to range: A2:A100

=AND(A2<>"", SUMPRODUCT(--EXACT(A2, $B$2:$B$100))>0)

With the sample data, A6 “Eli” is no longer highlighted, because B6 is “eli”.

Method 7: Ignore extra spaces

"Ana " (with a trailing space) and "Ana" are different values to COUNTIF. Trim both sides before comparing:

Apply to range: A2:A100

=AND(TRIM(A2)<>"", SUMPRODUCT(--(TRIM($B$2:$B$100)=TRIM(A2)))>0)

If TRIM doesn’t remove the space, it may be a non-breaking space (character 160) from copied web data. See TRIM Not Working in Excel? Remove Non-Breaking Spaces (CHAR 160). The same fix works in Sheets. For a permanent fix, clean the source data with Data > Data cleanup > Trim whitespace.

Method 8: Compare columns on different sheets

Google’s conditional formatting help says custom formulas can only reference the same sheet directly. To look at another tab, wrap the reference in INDIRECT:

Apply to range: A2:A100 on Sheet1

=AND(A2<>"", COUNTIF(INDIRECT("Sheet2!B2:B100"), A2)>0)

If the tab name has spaces, quote it: INDIRECT("'RSVP list'!B2:B100"). Note that INDIRECT uses text, so the range won’t update if you rename the tab.

Two COUNTIF quirks to know

Google’s COUNTIF documentation explains two behaviors that can create false matches:

1. Wildcards. * and ? in a value act as wildcards. A cell containing A* would match anything starting with “A”. Escape them with ~, or use the literal comparison below.
2. Numbers stored as text. A text criterion that contains a number is checked for equality with numbers, so the text "0123" can match the number 123.

If either matters for your data (product codes, IDs with leading zeros), use a plain equality test instead of COUNTIF. It doesn’t use wildcards and doesn’t treat text and numbers as equal:

=AND(A2<>"", SUMPRODUCT(--($B$2:$B$100=A2))>0)

For mixed text and numbers, it’s usually better to fix the data first: Convert Numbers Stored as Text in Excel and Google Sheets (5 Methods).

Excel vs. Google Sheets

Excel’s Duplicate Values shortcut, as Microsoft documents it, highlights duplicates within the selected cells. With A and B selected, it behaves like Method 3 (repeats within a column are included). For cross-column-only matches, use the Method 1/2 formulas in New Rule. In Excel, make sure the active cell is A2 when you enter the formula, because the relative reference is based on it.

Common mistakes

1. Formula written for the wrong row. If your range starts at A2 but the formula says A1, every highlight is shifted by one row. Match the formula to the top-left cell.
2. Missing $ on the lookup range. COUNTIF(B2:B100, A2) moves down as the rule is applied, so lower rows search a shrinking list. Use $B$2:$B$100.
3. Adding $ to the tested cell. COUNTIF($B$2:$B$100, $A$2) tests only A2 for every row, so either everything or nothing is highlighted.
4. Including the header row. If both headers say “Name”, they’ll be highlighted as duplicates. Start at row 2.
5. Forgetting blanks. Without A2<>"", an empty cell in A becomes an empty criterion, COUNTIF counts the empty cells in B, and blank rows get highlighted. Keep the blank check.

FAQ

How do I highlight duplicates between two columns in Google Sheets?

Select the first column, open Format > Conditional formatting, choose Custom formula is, and enter =AND(A2<>"", COUNTIF($B$2:$B$100, A2)>0). Add a matching rule on column B that looks at column A to highlight both sides.

How do I highlight duplicates in two columns but not within the same column?

Use two rules, one per column, each with COUNTIF looking at the other column only. A single rule over both columns with >1 also flags repeats inside one column.

Is the COUNTIF duplicate check case-sensitive?

No. COUNTIF treats “Eli” and “eli” as the same. Use =AND(A2<>"", SUMPRODUCT(—EXACT(A2, $B$2:$B$100))>0) for case-sensitive matching.

Can conditional formatting compare columns on different sheets?

Yes, but in Google Sheets you have to reference the other sheet with INDIRECT, for example COUNTIF(INDIRECT(“Sheet2!B2:B100”), A2)>0.

Why is nothing highlighted even though there are matches?

Check that the formula refers to the first row of the range, that the lookup range has $ signs, and that the values don’t differ by hidden spaces. Try the TRIM version of the formula to rule out spaces.

Related tutorials

• Convert Numbers Stored as Text in Excel and Google Sheets (5 Methods) (cluster hub)
• TRIM Not Working in Excel? Remove Non-Breaking Spaces (CHAR 160)
• Extract Email Addresses from Text in Google Sheets with REGEXEXTRACT
• Dependent Drop-Down List in Google Sheets That Works on Every Row
• COUNTIF vs COUNTIFS vs SUMPRODUCT: When to Use Each (With Examples)

Live-test findings

The TRIM + SUMPRODUCT custom conditional-formatting rule was installed in Sheets, not merely evaluated in helper cells. The effective cell formatting was green for A2, A4 and A6, with the other sample rows unchanged.

Sources

• Google Docs Editors Help: Use conditional formatting rules in Google Sheets (custom formulas, duplicates example, write the rule for the first row, INDIRECT for other sheets, rule order): https://support.google.com/docs/answer/78413
• Google Docs Editors Help: COUNTIF (not case-sensitive, wildcards and tilde, numeric strings checked for equality): https://support.google.com/docs/answer/3093480
• Microsoft Support: Find and remove duplicates (Highlight Cells Rules > Duplicate Values): https://support.microsoft.com/en-us/office/find-and-remove-duplicates-00e35bea-b46a-4d5d-b28e-66a552dc138d