“Data Validation” command is used to prevent invalid data being entered into the cell. If you prefer, you can allow users to enter invalid data but warn them by giving an alert message during try to type it in the cell.
How to prepare sheet using some restrictions on columns by data validation in excel.
For e.g. If you want to make an excel sheet like an online form filling sheet those have some restrictions on each columns like in name column we cannot write “0-9”, in age column we cannot write “a-z”, in address column we want limit of word like up to 50 characters, in birth day column we write date (only between 1 to 31 days, Jan to December, and 1952 to 2013) in that case, you have to take following steps in sequential format.
Must Read: How To Use PMT Function in Excel
Step 1: Type following entries in cell address: A1 = Name, B1 = Age, C1 = Address, D1 = Date of Birth
Step 2: Select Cell A3 (Enter Name only text values) press ALT + D + L or Click Data -> Data Validation -> Settings -> Allow -> Custom -> type =ISTEXT(A3) then press OK.
Step 3: Select Cell B3 (Enter Age only numbers) press ALT + D + L or Click Data -> Data Validation -> Settings -> Allow -> Custom -> type =ISNUMBER(B3) then press OK.
Step 4: Select Cell C3 (Enter Address only text entries up to 50 char. long) press ALT + D + L or you have to click Data -> Data Validation -> Settings -> Allow -> Select Text Length -> Data -> Select Less than or equal to -> Maximum -> type 50 then press OK.
Step 5: Select Cell D3 (Enter Dob up to 1/1/1952 till 12/31/2013) press ALT + D + L or Click Data -> Data Validation -> Settings -> Allow -> Select Date -> Data -> Select Between -> Type 1/1/1952 in Start Date and Type 12/31/2013 in End Date -> then press OK.
Step 6: Finally, you get resulted sheet as given below:
For more help, watch this video guide:
I hope you make Restrictions on Columns by Data Validation in Excel and like this post. Give your important suggestions in comment box. Thanks.