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 Supply | Quantity On Hand |
|---|---|
| HVAC filters | 3 |
| AA batteries | 12 |
| light bulbs | 6 |
| trash bags | 24 |
| light bulbs | 4 |
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.