One day you created an Excel spreadsheet, a week later you made changes, a little later you added some more data, and suddenly something in this data went topsy-turvy? If a table in Excel follows this path, then most likely it contains several duplicates. How to find them? Perhaps the problem is the other way around, and you need to find the only unique value in an ever-growing table? Conditional formatting solves both of these problems.
Here are some easy steps on how to find duplicate or unique values in Excel:
- Highlight the table columns in which you want to find duplicates or unique values. Click Conditional Formatting (Conditional Formatting) in the section Styles (Styles) tab Home (Home).
- Select from the dropdown menu New Rules (Create a rule), then select the 5th rule type in the proposed list: Format only unique or duplicate values (Format only unique or duplicate values).
- Нажмите кнопку Size (Format) at the bottom right of the dialog box (see figure above). This will provide visual formatting for duplicate or unique values, making them much easier to spot.
- The most noticeable will be cell formatting with fills, but you can also customize the border, font, or number format. Filling is good because as soon as you notice the right color, you will immediately understand that you have found a duplicate.
If you’re interested in using this conditional formatting rule at work, here are a few more points to keep in mind:
- The biggest disadvantage of this method is that the duplicate or unique values found are not removed, moved, and so on, until you do it yourself.
- Another inconvenience is that the cells will be compared individually. For example, you will not be able to compare two cells at once (First Name and Last Name) from one row with the same cells, but located several lines lower. Dismissing unfair criticism, it is worth noting that there are some things in conditional formatting that can perform such an action.
- Formatting works without too much thought. It will check not one, but ALL columns for duplicates. This rule works quickly and continues to work as you enter new data into the Excel spreadsheet. For this I love him.
Finding duplicates and unique values in Excel just got a little easier! Truth?