Quick Links
Duplicate values make their way into Excel spreadsheets more often than we’d like. Thankfully, you can quickly highlight duplicates in your Excel spreadsheet with conditional formatting.
How to Highlight Duplicates in Excel
Excel has a useful built-in feature that allows you to highlight duplicate values within a selected range. Using this feature, you don’t need to create complex formulas, as it will automatically create a newconditional formatting rule in Excel. You can choose from the predefined style formats or create your own custom format.
To highlight duplicates in Excel, follow these steps:
Excel will now highlight the duplicate values in the selected range in your chosen format. Keep in mind that any value that occurs multiple times will be highlighted in the same way.
you may use the Conditional Formatting Rules Manager to edit the rules. ClickConditional Formattingin theHometab and then selectManage Rulesat the bottom of the menu. Change the scope toThis Worksheetto see all the rules and select the relevant rule to edit or delete.
How to Highlight Duplicates in Two Columns in Excel
Sometimes, it’s more practical to highlight duplicates across two Excel columns instead of the entire sheet. This lets you compare two columns and see if they share any attributes or if you’ve mistakenly made a duplicate entry.
In this example spreadsheet, some fictional characters and their attributes are laid out in a table. Suppose you want to see what attributes Thor and Green Goblin have in common. you’re able to quickly highlight the duplicates in the two columns to achieve this.
You’ll see the duplicate values in the two columns highlighted with the chosen format. This example highlights the duplicate values in columns C and H.
The formula is a simple logical function that checks whether each cell in column C equals the corresponding cell in column H, starting from the second row. Excel then checks this formula for the selected range (C2:C7, H2:H7) and formats values that return true, which are duplicates.
Note that the column references C and H are absolute references (fixed with the dollar sign), while the row references are relative, meaning they change with the selected cell. This ensures that the formula applies to each row in the selected range. Having a good understanding ofabsolute and relative references in Excelis essential for writing efficient conditional formatting formulas.
Duplicates are usually bad news, as they mean that the integrity of your data is compromised. Moreover, manually searching through massive spreadsheets to find each duplicate is a tedious task. Now that you know how to highlight duplicates in Excel, incorporate these practices into your Excel workflow and navigate your data with newfound ease and precision.
