Data Validation

When setting up your spreadsheet you can set validation criteria to help you detect cells that contain wrong types of data or values that are outside the expected range.  In an invoice we would expect the rate of VAT to be between 0 and 50 percent (or less).  By clicking on the cell containing the VAT rate and then clicking on the Data drop-down menu and choosing Validation we can display the settings for that cell as shown below.  In this case we have set the value to be a decimal number between 0 and 50, if the cell is blank the validation is not applied.

By clicking on the Error Alert tab at the top of this window you can also set a message to appear when an invalid value is entered as below:

Here is an example of the error message that appears when a negative rate of VAT is entered in the VAT rate cell.

Another use of validation is to provide a drop-down list from which people entering data can choose an item.  It is a good idea to set the list up in another area of the worksheet, for example in a column away from your working data which can later be hidden.  First, enter the list of options cell-by-cell underneath each other somewhere in the workbook, the example below shows a list for months of the year.

Now click in the cell where you want to create your drop-down list, click on the Data drop-down menu and choose Validation.  Choose List in the Allow box and then click in the Source box and highlight the cells on your worksheet containing the list items.  Make sure there is a tick in the In-cell dropdown box and click OK.

You can now choose items from the drop-down list for that cell.  Furthermore, the cell with validation can be copied elsewhere in the worksheet by copying and pasting or dragging the fill handle and will still retain its drop-down list.  By hiding column A we can make our source list invisible and it should not be accidentally unhidden.

Click here to return to the Excel index.