✅ How to Highlight Duplicates in Google Sheets
Duplicate values in your spreadsheet can cause confusion, errors in analysis, or incorrect summaries. Fortunately, Google Sheets offers a simple way to visually highlight duplicates using Conditional Formatting. Follow the steps below to clean up your data like a pro!
🔍 Step-by-Step Guide to Highlight Duplicates
✅ 1. Select the Range
Start by selecting the column or range of cells where you want to check for duplicates.
👉 Example: Select A1:A100 if you want to find duplicates in column A.
✅ 2. Open Conditional Formatting
Go to the top menu and click on:
Format → Conditional formatting
✅ 3. Use a Custom Formula
In the right-hand panel that appears:
- Under Format rules, choose: "Custom formula is"
- Enter the following formula:
👉 This formula checks how many times the value in A1 appears in the range A1:A100. If it appears more than once, it's a duplicate.
✅ 4. Set the Formatting Style
Choose a background color (like light red) to highlight the duplicates.
You can also make the text bold or change the text color.
✅ 5. Apply the Rule
Click Done to apply the formatting.
Now, all duplicate values in the selected range will be highlighted!
🧠 Tips:
- 🔁 If you're working with another column, change the references accordingly. For example, use
$B$1:$B$100andB1for column B. - 📊 You can apply this method to multiple columns by adjusting the range in the formula.
🎯 Why It Matters
Highlighting duplicates is crucial when:
- Cleaning mailing lists
- Analyzing survey results
- Ensuring data accuracy
- Removing repeated entries in reports
📝 Final Thoughts
Using Conditional Formatting with a custom formula in Google Sheets is a powerful and easy way to spot duplicates. This simple trick can save you hours of manual checking and improve the accuracy of your work.
📌 If you found this guide helpful, don’t forget to share it with your friends or bookmark it for later!
✍️ Written by: RomaxHub – Your Hub for Smart Tips and Tutorials