Highlighting if text is the same

Hi Shannon,

Thank you for the forum question.

You can do this easily with Conditional Formatting in Excel.
Here are two common scenarios — choose the one that matches what you want.
________________________________________
Scenario 1 — Highlight a cell if its text appears anywhere in another column
Example: Highlight Column A if its value appears in Column B.
Steps
1. Select the range you want to highlight (e.g., A:A or A2:A100).
2. Go to Home → Conditional Formatting → New Rule.
3. Choose “Use a formula to determine which cells to format.”
4. Enter this formula:
=COUNTIF($B:$B, A1) > 0
5. Click Format, choose your highlight colour, and press OK.
Result: Every cell in A that also exists in B gets highlighted.
________________________________________
Scenario 2 — Highlight matching values in both columns
Example: Highlight cells in both columns if they appear in both.
For Column A:
Formula:
=COUNTIF($B:$B, A1) > 0
For Column B:
Formula:
=COUNTIF($A:$A, B1) > 0
Apply each rule to the corresponding column.
________________________________________
Scenario 3 — Highlight duplicates across multiple columns
If you want to highlight ALL duplicates anywhere, including duplicates inside a single column:
1. Select the entire range (e.g., A:B).
2. Go to: Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values.
3. Choose the colour and click OK.
________________________________________




Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector