CHAPTER 7 Flashcards
DATA VALIDATION
As we enter data in a spreadsheet, it would be advantageous for us to have a process that helps us validate the accuracy of the data. This is possible, within limits, using Excel’s data validation tool. As you might
expect, this tool is located in the Data ribbon, in the Data Tools group of buttons.
Data Validation helps us in the following ways:
a) Guides the user as to the type of data to enter.
E.g.: Enter whole numbers between 1 to 100 only
b) Checks the data entered conforms to the rules that
we have specified.
E.g.: If our rules specify integer values, and decimal
values are entered, Excel will advise us
accordingly.
c) Alerts the users in one of the three ways:
- Stop: prevents the values from being passed to
the cell
- Warning: prompts the user if the user wants to
continue with data that do not conform to rules
we specified. The user can choose to correct the
data or to continue without corrections.
- Information: lets the user know that the data
does not conform to rules
Data Validation uses
Data validation can also be used to enter information corresponding to specified lists. For instance, if we want to enter a name in a cell, it is possible to require the user to enter only names from an approved list. This ensures that only permitted names are entered, and prevents errors in spelling.
How to invoke data validation by referring to a list of values
Mouse:
DATA ribbon -> Data Tools group -> Data Validation -> Settings -> List
In the List indicate the source for the values
How is Data Validation useful?
Data validation is particularly useful when we create worksheets for others to use. We may want other users to enter data in certain cells and data validation is helpful in ensuring that data is entered correctly.
Excel offers various built-in rules to validate data:
a) Whole number: Enter only whole numbers between a minimum and a maximum.
b) Decimal: Enter decimal values between minimum and maximum
c) List: Enter values from a specified list
d) Date: Enter dates between a start date and an end date
e) Time: Enter dates between a start time and an end time.
f) Text Length: Enter text between a minimum and maximum number of characters
We also have the option of creating our own rules. To do so we use the ‘custom’ option and enter a formula appropriate to our requirements.
Example 1: we want the user to enter – in cell A1 – the course number for courses in Operations Research at UT Dallas. These courses have the prefix ‘OPRE’ followed by a space, and followed by 4-
digit number
We would achieve this result by selecting following data validation option:
Data Ribbon -> Data Validation -> Settings -> Allow: Custom. We would then enter - in the ‘formula’ window:
=AND(LEFT(A1, 5) = “OPRE”, LEN(A1) = 9).
This will ensure that the first five characters in the value entered in cell A1 are “OPRE “ (OPRE followed by a space) and then followed by 4 characters. The length of the entire string is nine characters. (Note that we are not checking whether the four characters to the right are numbers. “OPRE XXXX” will also meet the criteria for a 9-character string beginning with “OPRE “.)
Caution: ‘Smart quotes’ refer to quotation marks that are slanting as in “this example”. In contrast to smart quotes are ‘straight quotes’ as shown here “Straight Quotes”. Smart quotes make it easier to read the text. However, smart quotes are not recognized by Excel. If you copy-paste text from Word or another program that contains smart quotes and paste in a cell in Excel you may experience problems depending on what you are doing.
Example 2: The formula to require the user to enter only a text string that is exactly 4 characters long is:
=AND(ISTEXT(A1), LEN(A1) = 4)
Example 3: The formula to prevent users from adding a space before or after a string in cell A1 is:
=A1 = TRIM(A1)
Example 4: The formula to prevent users from adding ANY space in a text string in cell A1 is:
=A1 = SUBSTITUTE(A1, “ “, “”)
Applying Data Validation rules after data entry
On occasion, we may want to apply validation rules after data has already been entered in a worksheet. Retroactive application of data validation rules is a two-step process:
- First, we need to create the rules for data validation
- Second, we need to apply these rules
The reason we need two steps is that if we create validation rules for a range of cells that already contain data, Excel does not automatically apply these rules to existing data. We need to select the command ‘Circle Invalid Data’ to check if existing data meet the new rules. When we invoke this command, Excel highlights invalid data with a red circle. If, subsequently, we correct the data such that it now conforms to data validation rules, the circle disappears automatically.
Data validation is particularly useful when we create worksheets for others to use. We may want other users to enter data in certain cells and data validation is helpful in ensuring that data is entered correctly.
Limitations of Data Validation
Let us say that we have two ranges of cells, Range A and Range B. We have created validation rules for Range A. We do not have any data validation rules for Range B. When we copy/paste from Range B to
Range A, the default copy/paste is that everything – data, validation rules, formatting etc., – from Range B is copied/pasted to Range A. That is, after the copy/paste, Range A will not have any data validation rules.
Further, when data is filled in a pattern, the validation rules are not applied. Let us say that cell A10 has a data validation rule restricting values in A10 to whole numbers from 10 to 20. The user will be unable to
enter the value 8 in A10. However, if the worksheet already has values 4 and 6 in cells A8 and A9 respectively, then we can select cells A8 and A9
and drag the selection over cells A10, A11, and A12, and we will get the values 8 in A10, 10 in A11, and 12 in A12. The original data validation rule in A10 that would not have permitted the user to directly enter the value 8 in A10 is now overwritten.
We also need to keep in mind that data validation is more helpful with regard to catching errors of data type rather than data accuracy. For instance, if you type a number for age in a column for first name, this
error can be caught. However, if you type the wrong name in the column for first name, this error cannot be caught as easily.
Acceptable data validation rules:
a) To be valid, text has to be a specific length
b) To be valid, a number has to be between 100 and 200
Which if the following statements about the Data Validation tool is FALSE:
a) It can be used to enter information corresponding to specified lists - True
b) One can specify which dates can be entered in a cell - True
c) One can choose which error message is displayed in the case of invalid entry - True
d) It can be used to round numbers to a user-defined scale - False
Which functionality is available in Data Validation?
a) Circle invalid data
b) Provide an error message when invalid data is entered
c) Input message to guide the user as to the type of data that is acceptable
