ID: q181323
The information in this article applies to:
Data validation is a new feature that is available in Microsoft Excel 98 Macintosh Edition. It allows you to do the following:
This article describes some of the ways to use the new data validation feature in Microsoft Excel 98 as well as examples to illustrate how to implement this feature.
NOTE: The examples below assume you are working with a new Microsoft Excel 98 workbook and that you start with the first example and continue through the examples in the order in which they are presented.
You can make a list of the entries you will accept for a cell on a worksheet. You can then restrict the cell to accept only entries taken from the list by using the data validation feature.
To create a drop-down list and restrict values in the cell to these entries, do the following:
1. Select cell A1.
2. On the Data menu, click Validation.
3. On the Settings tab, click List in the Allow list.
4. By default, the Ignore Blank and In-cell check boxes are selected. Do
not change them.
NOTE: You can also enter a named range or cell reference if it contains
a list of values. Both must be preceded by an equal sign.
Cell A1 now has a list next to it and you can use this list to select
the value to enter in the cell.
This value is entered in the cell.
NOTE: You can manually enter "a", "b", or "c", (without the quotation marks) in the cell; you do not have to select these from the list. If you try to manually enter anything other these values, a stop message appears and you are unable to keep the value in this cell. Your only options are Retry or Cancel.
You can create a message that explains the type of data that can be entered in a cell. When you select the cell, the message appears near the cell. You can move this message, if necessary, and it remains until you move to another cell or press the ESC key. If the Office Assistant is visible, the message appears in a balloon above the Office Assistant.
Continuing with the previous example, follow these steps:
1. Select cell A1.
2. On the Data menu, click Validation, and then click the Input Message
tab.
NOTE: Make sure the "Show input message when cell is selected" check
box is selected.
marks).
This is the title for the message box that appears. It is in
bold text.
from the drop-down list" (without the quotation marks).
This is the body of the message that appears.
The message appears because cell A1 is selected.
The list next to cell A1 and the message for cell A1 both disappear.
Both the list and the prompt message appear next to cell A1.
NOTE: If the Office Assistant is visible, the prompt message appears
in a balloon above the Office Assistant.
When you select another cell and then select cell A1 again, the message
appears in its new location.
NOTE: There is no way to change the formatting for this message.
The style of the error message Microsoft Excel displays when you type incorrect data determines whether the restrictions are enforced. You can display a message that prevents you from entering data values except those specified. You can also display explanatory and warning messages that let you to enter out-of-range data, or you can set limits on the data but not display any messages.
Continuing with the previous example, follow these steps:
1. Select cell A1.
2. On the Data menu, click Validation, and then click the Error Alert
tab.
NOTE: Make sure the "Show error alert after invalid data is entered"
check box is selected.
Create a Stop Alert Message
---------------------------
If you create a Stop Alert message and enter invalid data in the cell,
your only options are to click Retry or Cancel; you are not allowed to
enter invalid data in the cell. To create the message, follow these
steps:
a. In the Style list, click Stop.
b. In the Title box, enter "Warning for cell A1" (without the
quotation marks). This is the title for the message box that
appears.
c. In the Error Message box, enter "The only allowable values for
cell A1 are a, b, or c", (without the quotation marks). This is
the body of the message that appears and is limited to 225
characters.
d. Click OK.
3. Manually enter "t" (without the quotation marks) in cell A1.
The Stop Alert message you created appears and your only options are
Retry or Cancel.
4. Click Cancel on the Stop Alert message box.
5. Select cell A1.
6. On the Data menu, click Validation, and then click the Error Alert
tab.
NOTE: Make sure the "Show error alert after invalid data is entered"
check box is selected.
Create a Warning Alert Message
------------------------------
If you create a Warning message and enter invalid data in the cell,
your options are more flexible than the options for a Stop Alert
message. A Warning Alert message has three options: Yes, to allow
entry of the invalid data; No, to give you a chance to enter valid
data; and Cancel, to remove the invalid entry. To create the message,
follow these steps:
a. In the Style list, click Warning.
b. In the Title box, enter "Warning for cell A1" (without the
quotation marks). This is the title for the message box that
appears.
c. In the Error Message box, enter "The only allowable values for
cell A1 are a, b, or c" (without the quotation marks). This is the
body of the message that appears and is limited to 225 characters.
d. Click OK.
7. Manually enter "j" (without the quotation marks) in cell A1.
The Warning message you created appears asking if you want to
Continue.
8. Click Yes.
The invalid value of "j" is entered in cell A1.
9. Select cell A1.
10. On the Data menu, click Validation and then click the Error Alert tab.
NOTE: Make sure the "Show error alert after invalid data is entered"
check box is selected.
Create an Information Alert Message
-----------------------------------
If you create an Information message and enter invalid data in the
cell, your options are the most flexible. When an Information Alert
message appears, you can click OK to accept the invalid value or you
can click Cancel to reject it. To create the message, follow these
steps:
a. In the Style list, click Information.
b. In the Title box, enter "Warning for cell A1", (without the
quotation marks). This is the title for the message box that
appears.
c. In the "Error message" box, enter "The only allowable value for
cell A1 are a, b, or c" (without the quotation marks). This is the
body of the message that appears and is limited to 225 characters.
d. Click OK.
11. Manually enter "p" (without the quotation marks) in cell A1.
The Information Alert message you created appears and prompts you to
accept or reject the value you entered.
12. Click OK.
The invalid value of "p" is entered in cell A1.
After your data is entered, you can look for entries that are outside the limits you set. When you click Circle Invalid Data on the Auditing toolbar, circles appear around cells that contain incorrect entries. If you correct an invalid entry, the circle disappears.
Continuing with the previous example, follow these steps:
1. On the Tools menu, click Customize.
2. In the Customize dialog box, click the Toolbars tab.
3. In the list of Toolbars, select the Auditing check box (if it's not
already selected), and then click Close.
A1 is circled.
6. Click the arrow next to cell A1, and then click one of the
entries in the list. The circle disappears.
You can place limits on the data that can be entered in a cell. You can set minimums and maximums or check for the effect an entry might have on another cell. To set a limits on the data, follow these steps:
1. Select cell A5.
2. On the Data menu, click Validation and click the Settings tab.
3. In the Allow list, click Whole number.
4. In the Data list, click between.
5. In the Minimum box enter 1.
6. In the Maximum box enter 10.
NOTE: You can use cell references for Steps 5 and 6 to specify cells
that contain the minimum and maximum values.
7. Click OK.
8. Enter the value 3 in cell A5.
The value is entered without error.
9. Enter the value 33 in cell A5.
Because the data validation settings you created for cell A1 (an
Information Alert) do not apply to those for cell A5, you receive a
Stop Alert message (which is the default value), and your only options
are to click Retry or Cancel.
The value of 3 appears in the cell.
In the Data Validation dialog box you can use a formula, an expression or a reference to a calculation in another cell to determine if the entry you make is valid. To do this, follow these steps:
1. Enter the value 1 in cell B10.
2. Select cell A10.
3. On the Data menu, click Validation and click the Settings tab.
4. In the Allow list, click Custom.
5. In the Formula box enter the following formula:
=IF(A10>B10,TRUE,FALSE)
NOTE: The formula you enter must begin with an equal sign and must
evaluate to either true or false. It is not limited to the IF function.
7. Enter the value -1 in cell A10.
You receive a Stop Alert message stating the value is invalid.
9. Enter the value 3 in cell A10.
You do not receive any error message because this value is greater than
the value you entered in cell B10.
For more information about data validation, click the Index button in Microsoft Excel 98 Help, type the following text
data validation, overview
Additional query words: 8.00 XL98
Keywords : kbualink97 kbdta xlformat xlformula kbfaq
Platform : MACINTOSH
Issue type : kbhowtoLast Reviewed: February 6, 1999