Excel Data Validation: Check Formatting of Entered Data

Excel allows you to set certain cells to accept only a certain data format that you specify. You can prompt the user with guidelines about the proper way to enter their data. You can even specify that data must fall in a certain data range if you wish. When data entered does not match your specifications, Excel will display an Error Alert that will prompt the user to try again with the correct format.Data Validation_image 1

Specify a Data Format

  1. Select the cell or cells that you wish to check during entry.
  2. On the Data tab, in the Data Tools group, click Data Validation to open the Data Validation dialog box.Data Validation_image 2Data Validation_image 3
  3. On the Settings tab, specify the criteria you wish the entered data to meet:
    • Choose the Time data type in the Allow dropdown menu.
    • Choose between from the Data: dropdown menu.
    • Set your range of acceptable start and end times.
  4. On the Input Message tab, you can display a tooltip with instructions to the user before they type.
    • Check the Show input message when cell is selected checkbox to turn on prompts.Data Validation_image 4
  5. On the Error Alert tab, specify what happens when the data does not meet your criteria:Data Validation_image 5
    • Check the Show error alert after invalid data is entered checkbox to turn on alerts.
    • Choose the type of alert you wish:
      • Stop – Prevents Excel from accepting invalid data and only allows the user to Retry or Cancel.
      • Warning – User is warned that the data is invalid, but they can click Yes to enter the data anyway, No to edit the entry, or Cancel to delete the entry.
      • Information – User is warned that the data is invalid, but is not prevented from entering it. User can click OK to enter the data anyway or Cancel to delete it.Data Validation_image 6
  1. Click OK to apply the data validation.

Remove Data Validation

  1. Select the cell or cells affected and click the Data Validation button.
  2. In the Data Validation dialog box, click Clear All, then OK.

Hot Tip: To find invalid data that has already made it into your workbook, set up validation as described above and choose Circle Invalid Data [H] from the Data Validation dropdown menu. Excel will highlight the cells that don’t meet your specified criteria. Click Clear Validation Circles to remove them.

Data Validation_image 7Data Validation_image 8

PRYOR+ 7-DAYS OF FREE TRAINING


Courses in Customer Service, Excel, HR, Leadership, OSHA and more. No credit card. No commitment. Individuals and teams.


Choose a Seminar and Save $10:

Microsoft<small><sup>®</sup></small> Excel<small><sup>®</sup></small>: Beyond the Basics

Microsoft® Excel®: Beyond the Basics

Mar. 23

Central Time Zone

Mar. 24

Eastern Time Zone

Mar. 28

Central Time Zone

Mar. 29

Eastern Time Zone

Mar. 31

Pacific Time Zone

Apr. 4

Eastern Time Zone

Apr. 5

Pacific Time Zone

Apr. 6

Central Time Zone

Apr. 12

Eastern Time Zone

Apr. 13

Pacific Time Zone

Apr. 14

Central Time Zone

Apr. 18

Central Time Zone

Apr. 20

Eastern Time Zone

Apr. 21

Pacific Time Zone

Apr. 25

Pacific Time Zone

Apr. 26

Central Time Zone

Apr. 28

Eastern Time Zone

May. 2

Eastern Time Zone

May. 3

Pacific Time Zone

May. 5

Central Time Zone

May. 9

Central Time Zone

May. 11

Pacific Time Zone

May. 12

Eastern Time Zone

May. 16

Pacific Time Zone

May. 17

Eastern Time Zone

May. 19

Central Time Zone

May. 23

Central Time Zone

May. 25

Pacific Time Zone

Jun. 1

Eastern Time Zone

Jun. 2

Central Time Zone

Jun. 6

Eastern Time Zone

Jun. 8

Pacific Time Zone

Jun. 9

Central Time Zone

Jun. 13

Central Time Zone

Jun. 14

Pacific Time Zone

Jun. 16

Eastern Time Zone

Jun. 21

Eastern Time Zone

Jun. 22

Central Time Zone

Jun. 23

Pacific Time Zone

Jun. 27

Pacific Time Zone

Jun. 28

Central Time Zone

Jun. 29

Eastern Time Zone

Jul. 6

Eastern Time Zone

Jul. 11

Eastern Time Zone

Jul. 12

Pacific Time Zone

Jul. 14

Central Time Zone

Jul. 18

Central Time Zone

Jul. 19

Eastern Time Zone

Jul. 20

Central Time Zone

Jul. 21

Pacific Time Zone

Jul. 25

Central Time Zone

Jul. 27

Pacific Time Zone

Jul. 28

Eastern Time Zone

Microsoft<small><sup>®</sup></small> Excel<small><sup>®</sup></small> Basics

Microsoft® Excel® Basics

Mar. 23

Eastern Time Zone

Mar. 27

Central Time Zone

Mar. 28

Eastern Time Zone

Mar. 30

Pacific Time Zone

Apr. 3

Eastern Time Zone

Apr. 4

Pacific Time Zone

Apr. 5

Central Time Zone

Apr. 11

Eastern Time Zone

Apr. 12

Pacific Time Zone

Apr. 13

Central Time Zone

Apr. 17

Central Time Zone

Apr. 19

Eastern Time Zone

Apr. 20

Pacific Time Zone

Apr. 24

Pacific Time Zone

Apr. 25

Central Time Zone

Apr. 27

Eastern Time Zone

May. 1

Eastern Time Zone

May. 2

Pacific Time Zone

May. 4

Central Time Zone

May. 8

Central Time Zone

May. 10

Pacific Time Zone

May. 11

Eastern Time Zone

May. 15

Pacific Time Zone

May. 16

Eastern Time Zone

May. 18

Central Time Zone

May. 22

Central Time Zone

May. 24

Pacific Time Zone

May. 31

Eastern Time Zone

Jun. 1

Central Time Zone

Jun. 5

Eastern Time Zone

Jun. 7

Pacific Time Zone

Jun. 8

Central Time Zone

Jun. 12

Central Time Zone

Jun. 13

Pacific Time Zone

Jun. 15

Eastern Time Zone

Jun. 20

Eastern Time Zone

Jun. 21

Central Time Zone

Jun. 22

Pacific Time Zone

Jun. 26

Pacific Time Zone

Jun. 27

Central Time Zone

Jun. 28

Eastern Time Zone

Jul. 5

Eastern Time Zone

Jul. 10

Eastern Time Zone

Jul. 11

Pacific Time Zone

Jul. 13

Central Time Zone

Jul. 17

Central Time Zone

Jul. 18

Eastern Time Zone

Jul. 19

Central Time Zone

Jul. 20

Pacific Time Zone

Jul. 24

Central Time Zone

Jul. 26

Pacific Time Zone

Jul. 27

Eastern Time Zone

Advanced Microsoft<small><sup>®</sup></small> Excel<small><sup>®</sup></small>-Macros, PivotTables, Charts and More

Advanced Microsoft® Excel®-Macros, PivotTables, Charts and More

Mar. 30

Central Time Zone

Mar. 31

Eastern Time Zone

Apr. 4

Central Time Zone

Apr. 13

Eastern Time Zone

Apr. 17

Pacific Time Zone

Apr. 21

Central Time Zone

Apr. 26

Eastern Time Zone

Apr. 27

Pacific Time Zone

May. 1

Central Time Zone

May. 2

Pacific Time Zone

May. 10

Eastern Time Zone

May. 11

Central Time Zone

May. 17

Central Time Zone

May. 18

Eastern Time Zone

May. 23

Pacific Time Zone

Jun. 2

Eastern Time Zone

Jun. 7

Eastern Time Zone

Jun. 9

Central Time Zone

Jun. 13

Pacific Time Zone

Jun. 19

Central Time Zone

Jun. 20

Eastern Time Zone

Jun. 29

Pacific Time Zone

Jul. 5

Pacific Time Zone

Jul. 10

Central Time Zone

Jul. 14

Eastern Time Zone

Jul. 20

Eastern Time Zone

Jul. 25

Central Time Zone

Related Excel® Articles

PRYOR+ 7-DAYS OF FREE TRAINING


Courses in Customer Service, Excel, HR, Leadership, OSHA and more. No credit card. No commitment. Individuals and teams.

Categories