Hi, Guys we know very well about the importance of MS Excel. If you have better knowledge about the MS Excel then you will be able to easily prepare MIS report. MIS stand for Management Information System and this report is prepared in different companies as per required formats. But during preparing the MI report most of the person want to know how to prevent duplicate values using Data Validation command in MS Excel. This problem faced by lot of users during doing jobs in any multinational company or facing interview section. So, Today we have to discuss how to prevent duplicate values with Data Validation in MS Excel.
Most of the time when you want to create a large worksheet data but you will face problem about the duplicate values. But after reading this article you will be able to easily prevent duplicate values with Data Validation in MS Excel along with Countif Function. Before discuss this topic you must know about the Data Validation and Countif Function. Have a look….
Must Read: Use Data Validation and VLookup Function in MS Excel
Data Validation:
Data Validation is used to restrict or prevent invalid data from being entered into a particular cell or range of cells. There are lot of situation where you can use Data Validation as per your need. Data Validation command is situated in the Data tab and you can also open Data Validation dialogue box by pressing ALT+D+L.
Countif Function:
Countif() is a very popular statistical function of MS Excel, which can be used to count numeric values from given range of cells as per match given condition. The syntax of this function is given below:
=COUNTIF (RANGE, CRITERIA)
Range: It is a range of non-blank cells, which is used to count number of cells.
Criteria: You have to give and specific condition which you want to give as per your requirement to count the number of cells.
E.g. If you want to prepare a large worksheet data of students and want to prevent duplicate StuID during preparing the worksheet data. Now, you have to take following given steps to easily prevent duplicate values with Data Validation in MS Excel along with Countif Function.
Must Read: How to Use Decimal Option in Data Validation in Excel
Steps how to Prevent duplicate values with Data Validation in MS Excel:
Step 1: Create the following structure in active worksheet as per your need to prevent duplicate values with Data Validation in MS Excel.
Step 2: Select the range of cell from A1:C4 and convert the range in table format by clicking on the Format as Table button from Home tab > Choose any required table format and click OK button.
Step 3: Select the range of cells from A2:A4, Now click on the Name box and type “StuID” to set the name of selected range of cells then press enter key.
Step 3: Select the range of cells of StuID and open the Data Validation dialogue box either by clicking on the Data tab > Data Validation > Data Validation, or you can also press ALT+D+L.
Step 4: In Data Validation dialogue box choose Custom option in Allow category then type the following formula in the box: =countif(StuID,A2)<=1 then press OK button. Drag the range of table format to insert another record. If you insert any unique value then it will be accepted but if you try to insert any duplicate value in the StuID field, MS Excel generate an error message on your screen.
Note: During typing the Countif formula in Data Validation dialogue box press F3 shortcut key to paste the list of StuID.
I hope you like this article and easily prevent duplicate values with Data Validation in MS Excel, kindly give your important suggestions in the comment box and also share your knowledge with your friends, colleague, family members and others. Thanks to all.