How to Restrictions on Columns by Data Validation in Excel

“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

Data-Validation-Sheet-Apply-Restrictions-on-Each-Columns

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.

Data-Validation-Sheet-Only-For-Text-Entries

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.

Data-Validation-Sheet-Only-For-Numeric-Values

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.

Data-Validation-Sheet-Text-Length

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. Data-Validation-Sheet-Date

Step 6: Finally, you get resulted sheet as given below:

Data-Validation-Sheet-Apply-Restrictions-on-Each-Columns

Must Read: Switch / Transpose Row And Column Data in MS Excel

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.

Leave a Reply

Your email address will not be published. Required fields are marked *