Validating data in excel
For this list, click in the Source box and then go and select the cells that contain the items.
In this example, the items were in range A1: A5 of a sheet called Names.
This is a guest post by Alan Murray from Computergaga. It often goes unnoticed as Excel users are eager to learn the highs of Pivot Tables, charts and formulas.
It controls what can be input into a cell, to ensure its accuracy and consistency. In this blog post we will explore 11 useful examples of what Data validation can do.
For the dependent list in cell G2, the selection in cell F2 needs to be converted into a reference to the named ranges.When you need a simple list such as Open and Closed, or Yes and No, then typing the entries in makes sense.When you need a more dynamic list for items that change over time such as lists of products, places and people, then referring to a range makes sense.The formula below counts the occurrences of the inputted value in the range A2: A8.
If the answer is 0 then the value is unique and allowed.
To apply these Data Validation rules; You may need to ensure that data is entered in uppercase, such as this example of UK postcodes being entered.