Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. Output: Data validation unique values only See below table that is filled up with Student IDs unique to each student, as allowed by Data Validation.Р”Р‡igure 8. We are then presented with three options: Retry, Cancel or seek Help.Р”Вata Validation is very accurate in allowing only the values as specified in the formula or validation criteria. A user has restricted values that can be entered into this cell.РІР‚Сњ For restricted values, Excel shows a default warning message that says: Hence, Data Validation fails and restricts the input of РІР‚Сљ19001РІР‚Сњ into cell C4. This time, the COUNTIF formula returns the count of 2 for РІР‚Сљ19001РІР‚Сњ, which exists in both cells C3 and C4. ID РІР‚Сљ19001РІР‚Сњ entered twice and restricted by Data Validation The Student ID РІР‚Сљ19001РІР‚Сњ is allowed because it is unique in column C, and no other value is entered yet. Unique value РІР‚Сљ19001РІР‚Сњ allowed by Data Validation Let us try and enter РІР‚Сљ 19001 РІР‚Сњ into cell C3. We have now restricted the values in C3:C7 to allow only unique values. Otherwise, Data Validation fails and the value is restricted.

If the formula returns a count less than РІР‚Сљ2РІР‚Сњ, specifically РІР‚Сљ0РІР‚Сњ or РІР‚Сљ1РІР‚Сњ, Data Validation passes and the value is allowed. Our formula checks if the value entered in C3 does not already exist in the range C3:C7. Our COUNTIF function returns the count of the value in C3 within the range C3:C7. The dollar signs РІР‚Сљ$РІР‚Сњ are necessary to fix specific cells in our formula and enable the validation criteria to work properly for all selected cells.Р”Р‡igure 5. Enter the following in the Formula bar: =COUNTIF(C$3:C$7,C3)<2 Click the Allow: drop-down button and select Custom as Validation criteria The Data Validation dialog box will pop up. Click the Data tab, then the Data Validation menu and select Data Validation Select the cells whose values we want to restrict. We can do this with Data Validation by following these steps: We want to restrict the Student IDs entered in column C to be unique for each student. Sample data: Data validation unique values only Allow unique values only We can restrict the values entered in column C and allow unique values only by applying Data Validation.Р”Р‡igure 2. We want to ensure that a Student ID is unique to each student. Our table consists of two columns: Name (column B) and Student ID(column C). criteria РІР‚вЂњ the criteria or condition that determines which cells will be counted.range РІР‚вЂњ the data range that will be evaluated using the criteria.Working formula: =COUNTIF(C$3:C$7,C3)<2 Syntax of COUNTIF FunctionР”В¬OUNTIF returns the count or number of values in a specified range based on a given condition Final result: Data validation unique values only This step by step tutorial will assist all levels of Excel users in allowing unique values only.Р”Р‡igure 1. It automatically checks whether the value entered is allowed based on the specified criteria. Hope this answer the question for others too.How to Use Data Validation to Get Unique Values OnlyР”Вata Validation is a feature in Microsoft Excel which restricts the values or type of data that users enter into a cell. Then, enter TRUE,FALSE in the Source entry field, like this: The selection launches the Data Validation dialog box. Click on Data ValidationРІР‚В¦ in the list, as shown below. You create a data validation list by navigating to the Data Tab (Windows Excel 2010 itРІР‚в„ўs a Ribbon), and choose Validate.

Fortunately, thatРІР‚в„ўs straightforward when you remember that the case insensitive text strings TRUE and FALSE are Boolean values. The desired list was either TRUE or FALSE, and the required behavior was that they function like Boolean variables. Excel reads a set of literal text strings as if theyРІР‚в„ўre a list of cell values, which is why you donРІР‚в„ўt provide the double quotes that you use in every function. You enter the list of values as comma delimited strings, but you donРІР‚в„ўt enclose them in double quotes. You can create a data validation list without referring to a cell range. The question posed: РІР‚СљHow do you create a data validation list without referring to a range of cells?РІР‚Сњ A second Excel 2011 question in as many days, February is off and running.

Data validation excel 2007 tutorial