Restrict data on cells for Invalid Data Using Data Validation!

Have you ever had a situation where you send a file or share a file with someone and they enter data that they weren't supposed to. Or may be enter too much data on a cell?

In Excel you have a feature called Data Validation that will help you in setting up the spreadsheet in such as way that users will not be able to enter any invalid data.

Looking at the following screen you can figure out that we need to restrict data on certain cells depending on the heading:

1. In the Initial field (Column) we want user to enter NO MORE than 1 character


2. In the Surname we want the data to be restricted to 15 characters only


3. DOB field shoudl only contain dates between 01/01/1948 and 31/01/98 only


4. No. of Laptops sold should be a Whole number and decimals should not be alowed

In order to restrict the cells with the above mentioned restrictions we need to highlight (select) the the relevant data.

• So we select A2: A19.


• From the menu bar we select Data > Validation...

• Click on the Drop down arrow for the Allow: and then choose Text Length
• From the Drop down of Data: choose Equal to
• In the length box type in 1 as we need users to ONLY enter one character in this field. Please note that there are many other options such as Greater than or equal to, Less than or Equal to , etc..
• Press OK.

I hope you get the idea how it works.

Following are the options that are available for various validations:

• Whole Number: This is if you wanted user to enter ONLY whole numbers and not Decimals. In our example it will be No. of Laptops field as you can not sell 1.5 laptops. Once you have mentioned whole number you can again set it to be between two values or greater than, less than, Equal to, etc...
• Decimal places: is where you want user to enter certain values meeting your specific criteria
• List: You can have drop down arrow appear in the cells that can show you various entries you would like to appear in the cells. You just have to enter the data separating them by a comma. In our example the Dept. field will have this type of data Validation.
• Date: This is for restricting cells to contain the dates meeting the specific requirements that you enter in the data validation dialog box.

You can also have specific messages appear on the screen if the user don't follow the guidelines that you set in the data validation dialog box.

For further details on this topic as well as other interesting topics please refer to the MOUSE Training link

Thank you for visiting and hope this topic was helpful!

Raj