Ozgrid, Experts in Microsoft Excel Spreadsheets

Excel Conditional Formatting & Validation for Duplicates in Excel

| | Information Helpful? Why Not Donate.


Handy Hints For Excel Duplicates. See Also: Excel Duplicate Manager

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only $145.00. $59.95 Instant Buy/Download, 30 Day Money Back Guarantee & Free Excel Help for LIFE!

Got any Excel Questions? Free Excel Help

Data Validation for Duplicates in Excel and Conditional Formatting for Duplicates in Excel
Since Excel 97 Excel has some very handy built in features that are ideal for spreadsheets. All that is needed is a bit of imagination and know how and these can be adopted to make life just that bit easier. The examples below make use of Data Validation and Conditional Formatting to both find duplicates in Excel and prevent them. If you do a lot of duplication hunting/managing in Excel see our Excel Duplicates Manager Excel Add-in.

Validation For Duplicates

Go here if you are not familiar with Data Validation

Validation can be found by going to Data>Validation on the Toolbar. The main purpose of this function is to set up restrictions so users can only enter data that meet the criteria that you set, but as you will also see it can be used in other ways. For the purpose of all examples we will assume the range of cells that users will be entering data in is A1:H10.

*IMPORTANT* Validation does not prevent users from deleting or pasting.

Preventing Duplicates in Excel

In this example we will use Validation to stop users from entering numbers or text that are already within the range. In other words, prevent duplicates.

Now try and type the same word or number in any 2 cells within the range A1:H10.

Preventing Accidental Overtyping

As you are no doubt aware it is possible to stop users from typing in cells by going to Tools>Protection>Protect Sheet, but this can sometimes be like using a sledge hammer to push in a thumb tac as it also locks you out of lot a other Excel features. Here is a way to stop users from typing in cells that may contain entries like formulas etc yet still allow access to Excels other features.

In this example we will use Validation to stop users from accidentally typing over the top of the range A1:H10

Now try and type in any cell within the range A1:H10 and you will receive the error message that you set..

Creating a List of Allowed Entries

When developing Spreadsheets it is good practice to make them as user friendly as possible. One great way to achieve this is to give the user a selection of pre-typed entries when they select the cell(s). This is possible with Validation.

Now select any cell within our range and you will see a drop arrow, click this and you will see the list we set. If they try and type an entry that is not part of the list they will receive the error message you set.

Now push Ctrl+g to display the "Go to" dialog box, type IV1 in the "Reference" box then go to Format>Column>Unhide. Type the word "Yellow" in cell IV4. Now click in any cell within range A1:H10 and click the drop arrow and you will see that the new entry has been added to your list.

Conditional Formatting For Duplicates in Excel

Go here if you are not familiar with Conditional Formatting

Conditional Formatting can be found by going to Format>Conditional Formatting. The purpose of this function is very similar to "Validation" except instead of stopping or warning the user when they enter a entry that does not meet the set criteria it formats the cell to how it's format condition has been set. For the purpose of all examples we will assume the range of cells that users will be entering data in is A1:H10.

Highlighting Duplicates in Excel

When you have large list or table of data you may need to go through the list to identify some or all-duplicate entries. This can be a very time consuming and tedious task, but with Conditional Formatting they can be identified in seconds or better still already identified. Follow the steps below to see what I mean.

Now if you already have duplicates within your table or list they will all appear formatted as you set, or if the range has no entries, try typing in 2 entries the same within your range.  If you only need one color, see Highlight Duplicates in Excel

Colour Code Highlighting Duplicates in Excel

Supposing you want separate the duplicates from the triplicates and the entries that occur more than 3 times . This can easily be done by using Conditional Formatting.

Now all your entries that occur twice will appear in one format eg; blue background, all entries that occur three times will have another format eg; yellow background and all entries that occur more than three times will have yet another, eg; red background.

Find-Delete Duplicates in Excel with the the Duplication Manager

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to special@ozgrid.com 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software


Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft

Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates