Highlight Duplicates Data
How to Prevent Duplicate Entry Values in a Excel Table
When it comes duplicates in Excel if you want to sort table data set ban the entry of duplicate or prevent the user to enter a duplicate then we can using the Data Validation tool to solve preventing duplicate entries in an Excel table.
Select the range of data you want to ban entry of duplicates in Excel. On the ribbon tab 'Data' click => 'Data Validation' and the 'Settings' tab from the drop down menu 'Allow' choose 'Custom' option. In the 'formula', type the following formula: =COUNTIF($A$1:$A$27;A1)=1
After setting the rules for entering data into a table, Excel will prevent the entry of data that already exists enrolled in a defined range of data. So Excel will not allow duplicate entry in the table.
To highlight and display the duplicates using Conditional Formatting in Excel
If you want to emphasize and highlight color duplicates in Excel then use the option you have in Excel 2013 and that is that you Excel automatically color all cells that contain duplicate data within a certain range.
Select the desired range of data and then click on the Home tab => Conditional Formatting => Highlight Cell Rules => Duplicate Values. Then open the new dialog box select the drop down menu 'Duplicate' a color'.
How to prohibit entry into the cell provided in another cell (Custom Data Validation in Excel)
If you need to prohibit entry into a cell when the condition in another cell, make the 'Data Validation'. Take for example the picture to the right where we have cell A1 in which data is entered.
We require in cell B1 does not allow entry of some data if the data already exists in cell A1.
So if A1 contains data ban the entry in cell B1
- Select the cell B1
- Start the Data Validation
- In the zone 'Allow' Set 'Custom'
- In the zone 'Formula' set the formula =IF(A1="";"";"")
- On tab 'Input Message' type warning message ie. Input message as instructions for filling cells.
Tutorials Examples related for Data Validation in Excel.