Our Recommendation for You Search your Query, You can find easily. for example search by book name or course name or any other which is related to your education


Duplicates data in Excel 2013

How to prevent Duplicate entry in an 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 be 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 2013

If you want to emphasize and highlight color duplicates in Excel 2013 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'.

Prevent Duplicates 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="";"";"")
On tab 'Input Message' type warning message ie. Input message as instructions for filling cells.

How to prohibit entry into the cell provided in another cell