Microsoft Excel by default allows you to enter any type of data into a cell however sometimes when you are developing a spreadsheet for use by your staff or external people you will want to control what data can be entered into a specific cell. Why??Well in certain situations like in Business Models or spreadsheets that calculate, having invalid data in one cell can cause your whole spreadsheet to fail so in those situations it is important to apply data validation to your cells to ensure that the user cannot put a text value into a cell requiring a number. Another example would be preventing a user from putting a date value into a number field.
To achieve this in Microsoft Excel we have a tool called Data Validation that allows you to set specific data types to be accepted on a cell. In fact it goes further than that and allows you to control not just the data type but also other aspects like whether a number falls within a range of values, whether you have a certain volume of text in your cells or you can provide a list of values a person can select from.
So how do we do this?The first step in the process of applying validation to your cells is to select the cells you what to create the validation for. It should be noted that you can select multiple cells and apply the validation to multiple cells at the one time.
Once you have selected the cells you want to apply validation to, select the Data menu and from the drop down menu choose the Validation command from your drop down menu. This command will open the Data Validation dialog box. From here you can now choose which type of data your cell should accept. As an example lets choose the Whole numbers option from the Allow drop down box. In this sample we want the user to be able to type in any whole number greater than zero, therefore we have to select the Greater Than function from the Data drop down menu and then type in 0 in the Minimum text box. Finish off by pressing the OK button.
Now enter in a value of 22.3 into the cell. What you will notice is that the cell as soon as you press enter will come up with an error that says “The value you have entered is not valid. A user has restricted values that can be entered into this cell". You will not be able to move off this cell until you have entered the correct value.
You can restrict your cells to holding just dates or times, by simply choosing the Date or Time option from the Data Validation dialog box. One thing to know though is that you will have to put in a date range. The easiest way to do this is to set the Data drop down box to “Greater Than" and in the Start Date drop down box set the value to 1/1/1900. This means that all dates you enter will be valid after the 1 January 1900 and remember to finish off by pressing the OK button. Now try and enter an invalid date. You will notice as soon as you try to move off the cell it will generate an error.
Data validation is an important area to implement when you are developing an excel spreadsheet for use by others. The data validation rules play two important roles –
1. Prevents the user from accidentally putting the wrong value into your cell.
2. Ensures that the entered data is within the range you anticipated
The other area you should give consideration to using data validation is when you are developing Excel Spreadsheets that will be used as Microsoft Excel Templates. Using Data validation in your spreadsheets will save you a lot of time and hassle when providing your spreadsheets for use by third parties.
Chris Le Roy has developed a number of Microsoft Office Tutorials including a
Microsoft Access Training Advanced Program,
Microsoft Excel Tutorial and a
Microsoft Word Tutorial.
tweet this!