Blue Flower

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

How to prevent duplicate entry in Excel

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.

Denied entry duplicates in Excel

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'.

highlight duplicate in excel

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

  1. Select the cell B1
  2. Start the Data Validation
  3. In the zone 'Allow' Set 'Custom'
  4. In the zone 'Formula' set the formula =IF(A1="";"";"")
  5. On tab 'Input Message' type warning message ie. Input message as instructions for filling cells.

Not Allowed to enter value into the cell, if the 'A1' cell is not empty

Tutorials Examples related for Data Validation in Excel.

- Data Validation in Excel
- Data Validation Other Examples