Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

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.

Click in cell A1 and holding down the left mouse button highlight down and across to cell H10. Notice that cell A1 is the only active cell, this is for reasons I will explain in step 3. Go to Data>Validation and you will see the "Data Validation" dialog box. From the "Allow:" box select "Custom" and the "Formula:" box should appear.
In the "Formula" box type this formula: =COUNTIF($A$1:$H$10,A1)=1 It is important to note the Absolute of A1:H10 with the dollar signs. The reason why we haven't absoluted A1 is because it is the active cell and leaving it as a relative reference will let Excel know we want B1 to have the same formula as A1 but replace A1 with B1, C1 to have C1 etc.
Click the "Input Message" tab and type in a message that the user will see when they select the cell. Or leave it blank.
Click the "Error Alert" tab and type a message the user will see if they type in a duplicate entry. Now set the error level in the "Style" box to the left. To get detailed information about this click on the "?" at the top the dialog box and then click on the "Style" box. When you are happy with all your settings, click "OK".

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

Click in cell A1 and holding down the left mouse button highlight down and across to cell H10.
Go to Data>Validation and you will see the "Data Validation" dialog box. From the "Allow:" box select "Custom" and the "Formula:" box should appear.
In the "Formula" box type this formula: =""
Click the "Input Message" tab and type in a message that the user will see if they happen to select the cell. Something like "FORMULA CELL!"
Click the "Error Alert" tab and type a message the user will see if they type in the cell. Now set the error level from the "Style" box to the left. To get detailed information about this click on the "?" at the top the dialog box and then click on the "Style" box. When you are happy with all your settings, click "OK".

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.

In a safe area of the Worksheet (lets say Column IV) type the word "Red" in cell IV1, Blue in cell IV2 and Green in cell IV3. Now while cell IV3 is still selected go to Format>Column>Hide, this will hide our list from users
Click in cell A1 and holding down the left mouse button highlight down and across to cell H10.
Go to Data>Validation and you will see the "Data Validation" dialog box. From the "Allow:" box select "List" and the "Source:" box should appear. Make sure the "In cell drop down" box is checked.
In this Source box type =$IV$1:$IV$10. The reason we use the range IV1:IV10 is so that we can add to our list if needed. The good thing is Excel wont show us all the blank cells within our list.
Click the "Input Message" tab and type in a message that the user will see if they happen to select the cell. Or leave it blank.
Click the "Error Alert" tab and type a message the user will see if they type an entry in the cell that is not part of our list. Now set the error level from the "Style" box to the left. To get detailed information about this click on the "?" at the top the dialog box and then click on the "Style" box. When you are happy with all your settings, click "OK".

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.

Click in cell A1 and holding down the left mouse button highlight down and across to cell H10. Notice that cell A1 is the only active cell, this is for reasons I will explain in step 3
Go to Format>Conditional Formatting and you will see the "Conditional Formatting" dialog box. From the box directly below "Condition 1" select "Formula is"
In this Formula box type this formula: =COUNTIF($A$1:$H$10,A1)>1 It is important to note the Absolute of A1:H10 with the dollar signs. The reason why we haven't absoluted A1 is because it is the active cell and leaving it as a relative reference will let Excel know we want B1 to have the same formula as A1 but replace A1 with B1, C1 to have C1 etc.
Click the "Format" button and then select the format you want to use on all duplicate entries. Probably changing the Background colour on the "Patterns" tab to a nice bright colour will do the trick.
When you are happy with the format conditions you have set click "OK". You will no doubt notice the "Add>>" button, this will allow you to set up to 3 Conditional Formats and I will show you an example of this next..

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.

Click in cell A1 and holding down the left mouse button highlight down and across to cell H10. Notice that cell A1 is the only active cell, this is for reasons I will explain in step 3.
Go to Format>Conditional Formatting and you will see the "Conditional Formatting" dialog box From the box directly below "Condition 1" select "Formula is"
In this Formula box type this formula: =COUNTIF($A$1:$H$10,A1)>3 It is important to note the Absolute of A1:H10 with the dollar signs. The reason why we haven't absoluted A1 is because it is the active cell and leaving it as a relative reference will let Excel know we want B1 to have the same formula as A1 but replace A1 with B1, C1 to have C1 etc.
Click the "Format" button and then select the format you want to use on all entries that occur more than 3 times. Probably changing the background colour on the "Patterns" tab to red will do the trick.
When you are happy with the format conditions you have set click "OK". Now click the "Add>>" button and again select "Formula is" in the Formula box type this formula: =COUNTIF($A$1:$H$10,A1) =3 Click the "Format" button again and set the format you want, e.g. changing the background colour on the "Patterns" tab to yellow. Now click "OK".
Click the "Add>>" button again and repeat step 5 above, but type this formula: =COUNTIF($A$1:$H$10,A1) =2 and set the background colour on the "Patterns" tab to blue. Now click "OK" for the last time.

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 [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

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