Validating data
By selecting validation criteria, you can prevent entry of incorrect data or data that does not meet the selected criteria. This technique is discussed in more detail later in the chapter.Moving Selection After Enter
Depending on the nature of your data entry, you may want
the cellpointer to automatically move to the right or down after entering a
value. This is easy to control.
1.
Choose File,
Options
and click the Advanced tab.
2.
Be sure the box next to Move Selection After Enter Direction is checked.
3.
Change the direction of Move selection after Enter as desired.
Prevent moving to the next
cell when you finish entering data Press Ctrl
+ Enter.
To temporarily override the move selection after enter
direction, use the Down Arrow key
instead of Enter to move the
cellpointer down one row. Use the Right
Arrow key instead of Enter to
move the cellpointer to the right.
Selecting a Range
Selecting a range will help you enter data efficiently.
Select a range of cells, and begin entering data. Use Enter to move from cell to cell. The data will be entered in the
range of cells selected. When you have entered data into the last cell selected
in a column, the cursor will automatically move to the first cell in the next
column of the selected range.
Example: Select cells A1through D5.Assuming the Enter direction is set to down, begin
entering data. After you have entered data into cell A5, press Enter, and cell B1 will be selected
automatically.
In the cell, enter the formula =CHAR(128),
press F2,and then F9(Paste
Special, Values). Copy the euro
sign from the formula bar by using the Ctrl
+ C shortcut. From the File menu,
select Option then select Proofing and then select AutoCorrect. In the Replace box, type “euro”, and in the With box,
press Ctrl + V (Paste). Click Add, and click OK.
Validating Data
With data validation, Excel validates the data entered in
a cell against the validation criteria you defined. If the data does not
validate, it will not be entered into the cell.
Example: You can set criteria for a range of cells
to only allow dates for 2002.
1. Select cellsA1:A15.
2.
Choose Data, Validation.
3.
Select the Settings
tab, and in the Allow box, click
Date.
4.
In the Start date box,
enter 1-4-2017.
5.
In the End date box,
enter 15-4-2017.
6.
Select
the Input Message tab.
7. In the Title
box, enter Date validation.
8. In the
Input message box, enter the criteria
or any message you like.
9.
Error Alert – Select the Error Alert tab, and in the Title box, enter the title of the
alert. In the Error message box,
enter the date validation criteria. This error alert will appear when the date entered
into the cell is found to be invalid.
The Stop
option prevents you from typing data into a cell if the data does not meet
the criteria. The Warning option
allows you to enter invalid data, after acknowledging the error in the warning
box shown above. The Information option
only presents information about the criterion, but allows the user to enter
invalid data
Validation is only performed when data is entered into a
cell manually. The validation technique
does not work on data that is pasted into cells.
List
Validation by list allows you to attach lists to cells. By
doing this, you can select text from a list or enter text manually. The text
will be validated against the list, which actually serves as the validation criteria
for the text.
You can create several types of validation lists, such as a list of
company employees, customer list, account list, inventory list and others.
In the Data
Validation dialog box, select the Settings
tab. In the Allow
box, select List.
1. Select the customer list in column A.
2.
Press Ctrl+F3 to define a name for the list. In the Names in workbook box, enter the name Fruit and click OK.
3. Select cellsD1:D10.
4.
Choose Data, Validation.
5.
Select the Settings
tab, and in the Allow box,
select List.
6. In the
Source box, press F3 and paste the name Fruit.
7. Click OK.
8.
Select cell D1 and open the customer list (click the
small arrow on the right side of the cell). Select one of the customers.
Preventing duplicate data entry
1. SelectcellsA2:A20.
2.
Choose Data, Validation.
3.
Select
the Settings tab.
4.
In the Allow box,
select Custom.
Note – The title of the third box in the dialog
box changed to Formula.
Enter the following formula into the box:
5. Select the Error Alert tab.
6. In the Title
box, enter Duplicate.
7.
In the Error
message box, enter The value you
entered already appears in the list.
Validating text entries
The Allow box
in the Settings tab does not include
criteria for validating text. You can validate a text entry, but you cannot
check whether the entry is text or not.
Solution: Enter a formula that will check whether the data is
text.
1.
Choose Data, Validation.
2. Select the Settings tab.
3. In the Allow
box, select Custom.
4. In the Formula
box, enter =ISTEXT(A1) (A1 is the first cell in the range).
5. Click OK.
Copying validation
When copying a cell that contains validation
criteria to a different cell, the criteria are copied along with the text,
formula and format.
Use Paste
Special to copy only the validation criteria. To do this: copy the cell
which contains the validation, select a new cell, right-click, select Paste Special from the shortcut menu,
click Validation and then OK.
Deleting validation criteria
Locate, select and delete
all validation criteria defined for cells.
1. Press F5,
and click Special. Select Data validation, and click OK.
2. Choose Data,
Validation.
3. In the Data
Validation dialog box, click Clear
All.
Comments
Post a Comment