Data Validation in Excel

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.

 

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.

 

Example – enter the euro sign in a cell

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.

To check this – in any cell, type the word “euro.” The text changes to the euro sign.

 

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 Error Alert tab has three different options for alerts about errors

 

 

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.

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.

 

Use Validation to enter a formula to catch entry of duplicate data.

1. Select cells A2: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:

=COUNTIF($A$2:$A$20,A2)=1

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.

 

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.

 

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.

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.