How to use Data Validation in Excel – Easy Guide

Posted by

The data validation feature in Excel allows you to specify the type of data the user may enter in a cell. You can create a drop-down list of your values for the user to choose the form or set a minimum and maximum value for the data they can enter, and much more with data validation.

Data validation in excel lets you display alerts and input messages to inform the user of what they can enter in a given cell. It is quite simple in nature, but you can also use it to validate user input with your custom formulas.

How to use Data Validation in Excel

How to use Data Validation in Excel

  • Select your cell
  • Go to the tab, from the ribbon
  • Click on data validation

Microsoft Excel offers bunch of options to choose from.

Choosing From the Data types

The first tab is labeled settings and contains the validation criteria, in the data validation dialogue. This is where you get to create the data validation rule. You can take control of the kind of data your cell will be approving by choosing it from the drop-down list under Allow.

Microsoft Excel offers tons of options for you to pick from (date, time, text, whole number, list, and more). Nevertheless, you can use your own formula by selecting Custom from the drop-down list.

Adding an Input Message

You can add an input messaging to enlighten the user of the data validation rules with a custom message when they select cell.

Creating an Error Alert

In the error alert tab, you choose how the data validation rule is imposed.

Three Alert styles you can pick from:

Stop: this style prevent users from entering valid data in the cell

Warning: this style warns the user of the data they entered is invalid, but still lets them enter.

Information: this alert style doesn’t do anything to keep users from entering invalid data and information.

Data validation Example: Creating a drop-down list in excel

Dropdown is another way for urging users to select an item from a list, you provide them with, instead of entering their own values. Dropdown list varies from custom lists you can use custom lists in excel to store data you use often.

  1. Choose cell A1 and type Name
  2. In the cells below, add tons of names
  3. Use your cursor under the column header (A) click it when the cursor becomes an arrow. Doing this will select the entire column
  4. Click on the table at the insert tab
  5. Check my table has headers and then click OK. This option will list the top cell (I.e Name) as the header

Table for courses

  1. Choose cell B1 and type COURSE
  2. Right-click under the column name to select the entire column
  3. Select table to create a table from the insert tab
  4. Check my table header and click ok

This basic will help you maximize accuracy in data input from your users.

Leave a Reply

Your email address will not be published. Required fields are marked *