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