Data Validation

G. I. G. O.

Garbage In – Garbage Out

Data Validation

2

When designing a workbook, a good design should consider who is entering the data and how important it is that the data being entered conforms to certain established rules.

SEX might be defined to only allow for “M” or “F”RACE might be limited to “B”, “W”, “H”, “A”, “O”Zip-Code might be defined to be a 5 digit number (US standard)Department might be limited by a select LIST of values (Accounting, HR, Production, Audit)Numeric Data might be defined to only be “Whole Numbers” or to be within a certain range or be bounded by limitsState or Name may be limited to a certain number of charactersCheck input for a valid date or date range value

What is the impact of invalid data?For Example:

DATA VALIDATION

3

Data Validation is used to “trap” errors at the time of Data Entry.The IF and other Logic Functions can also be used to find “errors”

Select the Range of data for the Data Validation rule

RIBBON [Data] tab

Data Validation

There are three tabs1) Data Validation for new rules2) Circle Errors to identify errors that were entered BEFORE the validation rule was applied3) Clear Circles to remove the indication of data validation exceptions.

Conditional Formatting could also be applied to provide a visual for incorrect data

Data Validation There are 3 tabs to a Data Validation Dialog: {Settings} {Input Message} {Error Alert}

4

1) Allow a) What kind of data do you want to check?b) What do you want to do with “Blank” data?c) Do you want the data entry to be via the keyboard or from a “Selection Box”

2) Input Message I suggest leaving this blank since it will “repeat” for every cell. You could define two rules, 1 for the 1st cell with a PROMPT and then another rule for the additional cells.

3) Error Processinga) STOPb) Warningc) Informational

Exercise Apply Data Validation Rules

5