spreadsheets Hub

How Conditional Formatting Helps You Find Duplicate Entries

When you're adding information to a household tracker, it's easy to enter the same item twice. Highlighting repeated entries can help you spot possible duplicates before your list becomes confusing.

The Real Question

How can I find duplicate entries in a spreadsheet? Is there an easy way to highlight repeated names? How can I check a list for duplicates without comparing every row?

The practical question is: How can I automatically highlight entries that appear more than once in my household records?

Answer Sheets

Google Sheets Conditional Formatting can highlight repeated entries using a rule that counts how often the same value appears.

And here's what that looks like for me. In my Household Supplies tab, I've accidentally entered light bulbs twice:

Household SupplyQuantity On Hand
HVAC filters3
AA batteries12
light bulbs6
trash bags24
light bulbs4
1. Select the supply names in column A, starting at A2.

2. Choose Format > Conditional formatting.

3. Under Format cells if, choose Custom formula is.

4. Enter:
   =AND(A2<>"",COUNTIF($A$2:$A,A2)>1)

5. Choose a highlight color.

6. Click Done.

Now I can spot repeated supply names and review whether those entries should remain separate or be combined.

Put Conditional Formatting On Repeated Entries

  • Collectors. Identify repeated item names in collection records.
  • Genealogists and Family Historians. Spot repeated names in research lists.
  • Household Managers. Find duplicate household inventory entries.
  • Online Sellers and Resellers. Highlight repeated product identifiers.
  • Solo Entrepreneurs. Identify duplicate entries in business lists.
  • Students and Independent Researchers. Find repeated references in research records.
  • Teachers and Tutors. Spot duplicate entries in resource lists.

← Back to the spreadsheets Hub