≡ Menu

Data Validation in Excel 2013 with Example

In this tutorial, I will teach you about Data Validation tool in Excel 2013.

Data validation is a convenient tool and a real time saver when you do data entry with repetition. It also enables you to avoid incorrect entries with spelling mistakes or any other duplication. By this we can also specify what type of data is allowed in the cell.

For example we can take a list of cities that we would like to put a list with all the cities in our records. To use this type of Data Validation, go to the Data tab and click on Data Validation button.

This will open up the Data validation drop down list box. It will have multiple options for data type.

First we will explore the most commonly used validation tool that is the drop down list option.

List: This option will restrict to a set of text entries that you would select to populate a drop down value. For example we will have a list of city that we want to include in the list. Select the range of cities in the list.

Click OK. This will give a drop down pointer beside the cell which will list all the cities specified.

Other type of options of the data validation dialogue box is following.

Any Value: This will cancel any data validation enabling the user to enter any data. This can be done in order to cancel any data validations in the cell

Whole Number: This will restrict the entry to a whole number which will be based on the minimum or maximum values.

Decimal: It will restrict the entry to a decimal number that falls within a minimum and maximum values of a certain range.

Date: This will restrict the entry to the date that will be in a range or before or after certain date.

Text Length: Restricting the length of the text characters within the range.

Custom: By using certain formula to restrict data entry to the parameters of a worksheet.

Note: By selecting a particular range of cells, you are doing it because the data is unlikely to change. However, if you have a list that will populate or change, it is better to use the following method.

You can create a list and save it as an Excel Table. In order to do this, go to the Home tab and select styles and then click on Format as table. This will create a table for the values and you can add/remove values from this table.

Now we have saved it as a table, we can easily use this reference in the data validation box.

This is a handy method when you will have a growing list or changing data.

{ 0 comments… add one }

Leave a Comment