|
Restrict cell data input in Excel
Excel allows you to restrict data that is typed into cells to certain values. This is especially helpful for reducing typos when you are absent-mindedly typing a lot of data values.
First, assign a validation condition to a group of cells:
1. Start Excel. A new, blank workbook appears
2. Select cells A1 through B10, inclusively
3. On the Data menu, click Validation
4. On the Settings tab, in the Allow list, select Whole number
5. In the Data list, select less than
6. In the Maximum list, type 100
7. On the Input message tab, in the Title box, type Testing Validation
8. In the Input message box, Type a number less than 100
9. On the Error Alert tab, in the Title box, type Failed Validation
10. In the Error message box, type You must type a number less than 100
- Click OK to assign the validation condition and close the Data Validation dialog box.
Next, type numbers in specific cells to see how validation works:
1. In cell D1, type 105 and press Enter. No validation message is displayed because cell D1 does not contain any validation conditions
2. In cell A1, type 95 and press Enter. No validation message is displayed because the value of cell A1 is less than 100
3. In cell B10, type 105 and press Enter. A validation message appears, stating that you must type a number less than 100
4. Click Retry to try typing a different value
- In cell B10, type 99 and press Enter. No validation message appears because the value of cell B10 is less than 100.
To remove the validation condition:
1. Select cells A1 to B10, inclusive
2. On the Data menu, click Validation
3. Click Clear All. All validation conditions are removed from cells A1 to B10
- Click OK to close the Data Validation dialog box.
Finally, type numbers in specific cells to make sure that the validation condition was removed: In cell A1, type 105 and press Enter. No validation message is displayed because cell A1 does not contain any validation conditions.
| 