Feeds:
Posts
Comments

Archive for the ‘MS Excel’ Category

Whenever we design any metric in Excel which requires input from different people then there are always chances that people enter wrong or inconsistent information which eventually result in lot of data massaging to get the data into correct format.

To prevent users entering incorrect or inconsistent information, data validation is a great tool in excel. As the name suggests, it provide users a conditional mechanism by applying the rule on the cells where the data need to be entered.

Let’s explore this with couple of examples.

1. Allowing cells to only accept the text values –

a) Select the range on which you want to apply the validation

b) Goto Data -> Validation -> Allow -> Custom

c) Under formula enter =istext(A1:A10)  … given that your range is A1 to A10 where you need to apply the validation.

d) Lastly press OK

 

2. Allowing cells to accept only limited no. of characters –

a) Select the cell range on which you want to apply the validation

b) Goto Data->Validation->Allow->Custom

c) Under formula enter =len(A1:A10)<6  …this will allow less than 6 characters in range A1 to A10.

d) Press Ok

 

3) Entering only unique values in a cell range

a) Select the cell where validation is required

b) Goto Data->Validation->Allow->Custom

c) Under formula enter =countif($A$1:$A$10,A1)=1

d) Press Ok

e) Copy the validated cell and paste it into the cells below where you need the unique below.

Note: In this formula we used $ sign to freeze the range where we want to apply the validation because when we copy the validated cell and paste it to the cell below and if the $ is not applied on range then the range will change and the validation will not produce the accurate results. Try do it by both freezing and not freezing the range.

Advertisements

Read Full Post »