Applying Data Validation to Cells

Summary

In this article, you will learn the steps to apply data validation to cells within an Excel worksheet.

Body

   Overview   

If you need to specify or limit the type of data that can be entered into a cell, you can apply rules to the cell. This is called data validation.

When adding data validation to cells, you also have the option to add an input message that will appear indicating the allowable data.

   Instructions   

  1. Step 1: Select the appropriate cell(s) to apply data validation.
  2. Step 2: Click the Data tab.
  3. Step 3: Click the Data Validation button.
  4. Step 4: Click the Allow: dropdown list
  5. Step 5: Choose the data format you want to allow for the selected cells.
  6. Step 6: Click the Data: dropdown menu.
  7. Step 7: Click to select the appropriate Data: option.
  8. Step 8: Enter an appropriate value to specify data allowable according to the Data: field.
  9. Step 9: Click the Input Message tab.
  10. Step 10: Enter an appropriate title for your message in the Title: field.
  11. Step 11: Enter the appropriate information in the Input message: field.
  12. Step 12: Click the OK button.
  13. Step 13: The created input message appears.
  14. Step 14: Enter a number that does not match the criteria within the data validation range of cells.
  15. Step 15: The Validation message you created is displayed.

In this article, you will learn the steps to apply data validation to cells within an Excel worksheet.

You can use data validation to create a drop-down list, limiting the types of data that can be entered into the cell.

1. Step 1

step image

From the worksheet, select the appropriate cell(s) to apply data validation.  

Example shown here: column header U .

2. Step 2

step image

With the appropriate cell(s) selected, click the Data tab.

3. Step 3

step image

Click the Data Validation button.

4. Step 4

step image

The Data Validation menu is displayed.

Click the Allow: dropdown list

5. Step 5

step image

From the displayed list, choose the data format you want to allow for the selected cells.

Example shown here: the Decimal  option.

6. Step 6

step image

Click the Data: dropdown menu.

7. Step 7

step image

You can limit the acceptable data in the cells to a particular number or range using the displayed options.

Click to select the appropriate Data: option.

Example shown here: the equal to option allows data equal to a value you will specify.

8. Step 8

step image

In the Value: field, enter an appropriate value to specify data allowable according to the Data: field.  

Example shown here: " 8 " in the Value: field so that the column only accepts data equal to 8.

9. Step 9

step image

To add a message to your data validation rule, click the Input Message tab.

10. Step 10

step image

An input message displays information when a cell with data validation is selected in a worksheet.

Enter an appropriate title for your message in the Title: field.

Example shown here: " Alert " in the Title field.

11. Step 11

step image

To add an input message with your validation rule, enter the appropriate information in the Input message: field.

Example shown here: " Badge Numbers begin with an 8 ".

12. Step 12

step image

Click the OK button.

13. Step 13

step image

Upon selecting a cell with the applied data validation, the created input message appears. 

14. Step 14

step image

To further test the data validation rule you applied to the selected cells, enter a number that does not match the criteria you set in a blank cell within the data validation range of cells.

Example shown here: " 719000122 " in cell 19 , column U .

15. Step 15

step image

The created input error message is displayed, which confirms that the data validation rule you created has been applied.

Details

Details

Article ID: 149051
Created
Sat 9/24/22 11:02 PM
Modified
Fri 1/5/24 5:06 PM