Create a drop-down list from a range of cells

Excel 2007

Tags  create list; menu

What are tags?

To make data entry easier, or to limit entries to certain items that you define, you can create a drop-down list of valid entries that is compiled from cells elsewhere in the workbook. When you create a drop-down list for a cell, it displays an arrow in that cell. To enter information in that cell, click the arrow, and then click the entry that you want.

To create a drop-down list from a range of cells, use the Data Validation command in the Data Tools group on the Data tab.

1. To create a list of valid entries for the drop-down list, type the entries in a single column or row without blank cells. For example:

2.  Note    You may want to sort the data in the order that you want it to appear in the drop-down list.
3. If you want to use another worksheet, type the list on that worksheet, and then define a name (name: A word or string of characters that represents a cell, range of cells, formula, or constant value. Use easy-to-understand names, such as Products, to refer to hard to understand ranges, such as Sales!C20:C30.) for the list.

How to define a name

1. Select the cell, range of cells, or nonadjacent selections (nonadjacent selection: A selection of two or more cells or ranges that don't touch each other. When plotting nonadjacent selections in a chart, make sure that the combined selections form a rectangular shape.) that you want to name.
2. Click the Name box at the left end of the formula bar (formula bar: A bar at the top of the Excel window that you use to enter or edit values or formulas in cells or charts. Displays the constant value or formula stored in the active cell.).

3. Type the name for the cells, for example, ValidDepts.
4. Press ENTER.
4. Select the cell where you want the drop-down list.
5. On the Data tab, in the Data Tools group, click Data Validation.

Issue: The Data Validation command is unavailable.

6. In the Data Validation dialog box, click the Settings tab.
7. In the Allow box, click List.
8. To specify the location of the list of valid entries, do one of the following:
   1. If the list is in the current worksheet, enter a reference to your list in the Source box.
   2. If the list is on a different worksheet, enter the name that you defined for your list in the Source box.

In both cases, make sure that the reference or name is preceded with an equal sign (=). For example, enter =ValidDepts.

9. Make sure that the In-cell dropdown check box is selected.
10. To specify whether the cell can be left blank, select or clear the Ignore blank check box.
11. Optionally, display an input message when the cell is clicked.

How to display an input message

1. Click the Input Message tab.
2. Make sure that the Show input message when cell is selected check box is selected.
3. Type the title and text for the message (up to 225 characters).
12. Specify how you want Microsoft Office Excel to respond when invalid data is entered.

How to specify a response to invalid data

1. Click the Error Alert tab, and make sure that the Show error alert after invalid data is entered check box is selected.
2. Select one of the following options for the Style box:
   • To display an information message that does not prevent entry of invalid data, click Information.
   • To display a warning message that does not prevent entry of invalid data, click Warning.
   • To prevent entry of invalid data, click Stop.
3. Type the title and text for the message (up to 225 characters).

 Note    If you don't enter a title or text, the title defaults to "Microsoft Excel" and the message to: "The value you entered is not valid. A user has restricted values that can be entered into this cell."

 Notes 

• The width of the drop-down list is determined by the width of the cell that has the data validation. You may need to adjust the width of that cell to prevent truncating the width of valid entries that are wider than the width of the drop-down list.
• The maximum number of entries that you can have in a drop-down list is 32,767.
• If the validation list is on another worksheet and you want to prevent users from seeing it or making changes, consider hiding and protecting that worksheet.

Related Office Online discussions

Read related questions and answers from other Microsoft Office customers.

• How do you format data that is part of a drop down menu?
• Ho Do I create drop down menu?
• How can I make a drop down box visible within a worksheet?
• How do I chg the text size in my validation drop down box?
• Can you edit a combo box?
• How do I enter a list in a Combo box?
• Increase number of items shown in drop down list?
• Can you make a drop down list in a drop down list?
• Dropdown list in PivotTable has deleted items. How to omit?
• Is it possible to create a dropdown list with pictures/icons ?

See Also

• Apply data validation to cells
• Create or change a cell reference

 

 

Excel > Validating data

Data validation error alert

Excel 2007

When you try to enter or change data in a worksheet (worksheet: The primary document that you use in Excel to store and work with data. Also called a spreadsheet. A worksheet consists of cells that are organized into columns and rows; a worksheet is always stored in a workbook.) cell, you see a data validation error alert. This alert signifies that the owner of the workbook applied data validation (data validation: An Excel feature that you can use to define restrictions on what data can or should be entered in a cell, and to display messages that prompt users for correct entries and notify users about incorrect entries.) to the cell to prevent users from entering invalid data, and implemented the error alert to let you know that the data you entered is invalid.

You can enter only valid data in cells that have data validation applied. If you are not clear about the validity of the data that you can enter, you should contact the owner of the workbook.

If you inherited the workbook, you can modify or remove the data validation unless the worksheet is protected with a password that you do not know. If possible, you can contact the previous owner to help you unprotect the worksheet. You can also copy the data to another worksheet, and then remove the data validation. For more information about how to change or remove data validation, see Change data validation settings or Remove data validation.

 

 

Excel > Validating data

Apply data validation to cells

Excel 2007

You use data validation to control the type of data or the values that users enter into a cell. For example, you may want to restrict data entry to a certain range of dates, limit choices by using a list, or make sure that only positive whole numbers are entered.

This article describes how data validation works in Excel and outlines the different data validation techniques available to you. It does not cover cell protection, which is a feature that lets you "lock" or hide certain cells in a worksheet so that they can't be edited or overwritten. For more information about how to protect cells, see the links in the See Also section.

In this article

What is data validation?

When is data validation useful?

Data validation messages

Tips for working with data validation

Add data validation to a cell or range

Restrict data entry to values in a drop-down list

Restrict data entry to a whole number within limits

Restrict data entry to a decimal number within limits

Restrict data entry to a date within a time frame

Restrict data entry to a time within a time frame

Restrict data entry to text of a specified length

Calculate what is allowed based on the content of another cell

Use a formula to calculate what is allowed

What is data validation?

Data validation is an Excel feature that you can use to define restrictions on what data can or should be entered in a cell. You can configure data validation to prevent users from entering data that is not valid. If you prefer, you can allow users to enter invalid data but warn them when they try to type it in the cell. You can also provide messages to define what input you expect for the cell, and instructions to help users correct any errors.

For example, in a marketing workbook, you can set up a cell to allow only account numbers that are exactly three characters long. When users select the cell, you can show them a message such as this one:

If users ignore this message and type invalid data in the cell, such as a two-digit or five-digit number, you can show them an actual error message.

In a slightly more advanced scenario, you might use data validation to calculate the maximum allowed value in a cell based on a value elsewhere in the workbook. In the following example, the user has typed $4,000 in cell E7, which exceeds the maximum limit specified for commissions and bonuses.

If the payroll budget were to increase or decrease, the allowed maximum in E7 would automatically increase or decrease with it.

Data validation options are located in the Data Tools group.

You configure data validation in the Data Validation dialog box.

 Top of Page

When is data validation useful?

Data validation is invaluable when you want to share a workbook with others in your organization, and you want the data entered in the workbook to be accurate and consistent.

Among other things, you can use data validation to do the following:

• Restrict data to predefined items in a list  For example, you can limit types of departments to Sales, Finance, R&D, and IT. Similarly, you can create a list of values from a range of cells elsewhere in the worksheet. For more information, see Create a drop-down list from a range of cells.

• Restrict numbers outside a specified range  For example, you can specify a minimum limit of deductions to two times the number of children in a particular cell.
• Restrict dates outside a certain time frame  For example, you can specify a time frame between today's date and 3 days from today's date.
• Restrict times outside a certain time frame   For example, you can specify a time frame for serving breakfast between the time when the restaurant opens and 5 hours after the restaurant opens.
• Limit the number of text characters  For example, you can limit the allowed text in a cell to 10 or fewer characters. Similarly, you can set the specific length for a full name field (C1) to be the current length of a first name field (A1) and a last name field (B1), plus 10 characters.
• Validate data based on formulas or values in other cells  For example, you can use data validation to set a maximum limit for commissions and bonuses of $3,600, based on the overall projected payroll value. If users enter more than $3,600 in the cell, they see a validation message.

 Top of Page

Data validation messages

What users see when they enter invalid data into a cell depends on how you have configured the data validation. You can choose to show an input message when the user selects the cell. This type of message appears near the cell. You can move this message, if you want to, and it remains until you move to another cell or press ESC.

Input messages are generally used to offer users guidance about the type of data that you want entered in the cell.

You can also choose to show an error alert that appears only after users enter invalid data.

You can choose from three types of error alerts:

You can customize the text that users see in an error alert message. If you choose not to do so, users see a default message.

Input messages and error alerts appear only when data is typed directly into the cells. They do not appear under the following conditions:

• A user enters data in the cell by copying or filling.
• A formula in the cell calculates a result that is not valid.
• A macro (macro: An action or a set of actions that you can use to automate tasks. Macros are recorded in the Visual Basic for Applications programming language.) enters invalid data in the cell.

 Top of Page

Tips for working with data validation

In the following list, you will find tips and tricks for working with data validation in Excel.

• If you plan to protect (protect: To make settings for a worksheet or workbook that prevent users from viewing or gaining access to the specified worksheet or workbook elements.) the worksheet or workbook, protect it after you have finished specifying any validation settings. Make sure that you unlock any validated cells before you protect the worksheet. Otherwise, users will not be able to type any data in the cells.
• If you plan to share the workbook, share it only after you have finished specifying data validation and protection settings. After you share a workbook, you won't be able to change the validation settings unless you stop sharing, but Excel will continue to validate the cells that you have designated while the workbook is being shared.
• You can apply data validation to cells that already have data entered in them. However, Excel does not automatically notify you that the existing cells contain invalid data. In this scenario, you can highlight invalid data by instructing Excel to circle it on the worksheet. Once you have identified the invalid data, you can hide the circles again. If you correct an invalid entry, the circle disappears automatically.

• To quickly remove data validation for a cell, select it, and then open the Data Validation dialog box (Data tab, Data Tools group). On the Settings tab, click Clear All.
• To find the cells on the worksheet that have data validation, on the Home tab, in the Editing group, click Find & Select, and then click Data Validation. After you have found the cells that have data validation, you can change, copy, or remove validation settings.
• When creating a drop-down list, you can use the Define Name command (Formulas tab, Defined Names group) to define a name for the range that contains the list. After you create the list on another worksheet, you can hide the worksheet that contains the list and then protect the workbook so that users won't have access to the list.

If data validation isn't working, make sure that:

Users are not copying or filling data   Data validation is designed to show messages and prevent invalid entries only when users type data directly in a cell. When data is copied or filled, the messages do not appear. To prevent users from copying and filling data by dragging and dropping cells, clear the Enable fill handle and cell drag-and-drop check box (Excel Options dialog box, Advanced options), and then protect the worksheet.

Manual recalculation is turned off   If manual recalculation is turned on, uncalculated cells can prevent data from being validated correctly. To turn off manual recalculation, on the Formulas tab, in the Calculation group, click Calculation Options, and then click Automatic.

Formulas are error free   Make sure that formulas in validated cells do not cause errors, such as #REF! or #DIV/0!. Excel ignores the data validation until you correct the error.

Cells referenced in formulas are correct   If a referenced cell changes so that a formula in a validated cell calculates an invalid result, the validation message for the cell won't appear.

 Top of Page

Add data validation to a cell or range

In the next sections, you will learn the different techniques for adding validation to cells in a worksheet.

Restrict data entry to values in a drop-down list

Restrict data entry to a whole number within limits

Restrict data entry to a decimal number within limits

Restrict data entry to a date within a time frame

Restrict data entry to a time within a time frame

Restrict data entry to text of a specified length

Calculate what is allowed based on the content of another cell

Use a formula to calculate what is allowed

Restrict data entry to values in a drop-down list

It is not possible to change the font or font size for items in a list.

1. Select one or more cells to validate.
2. On the Data tab, in the Data Tools group, click Data Validation.

Issue: The Data Validation command is unavailable.

3. In the Data Validation dialog box, click the Settings tab.
4. In the Allow box, select List.
5. Click the Source box and then type the list values separated by the Microsoft Windows list separator character (commas by default).

For example:

• To limit entry to a question, such as "Do you have children?", to two choices, type Yes, No.
• To limit a vendor's quality reputation to three ratings, type Low, Average, High.

You can also create the list entries by referring to a range of cells elsewhere in the workbook. For more information, see Create a drop-down list from a range of cells.

 Note    The width of the drop-down list is determined by the width of the cell that has the data validation. You might need to adjust the width of that cell to prevent truncating the width of valid entries that are wider than the width of the drop-down list.

6. Make sure that the In-cell dropdown check box is selected. Otherwise, you won't be able to see the drop-down arrow next to the cell.
7. To specify how you want to handle blank (null) values, select or clear the Ignore blank check box.

 Note    If your allowed values are based on a cell range that has a defined name and there is a blank cell anywhere in that range, selecting the Ignore blank check box allows any value to be entered in the validated cell. This is also true for any cells that are referenced by validation formulas: if any referenced cell is blank, selecting the Ignore blank check box allows any value to be entered in the validated cell.

8. Optionally, display an input message when the cell is clicked.

How to display an input message

• Click the Input Message tab.
• Make sure the Show input message when cell is selected check box is selected.
• Fill in the title and text for the message.
9. Specify how you want Microsoft Office Excel to respond when invalid data is entered.

How to specify a response to invalid data

• Click the Error Alert tab, and make sure that the Show error alert after invalid data is entered check box is selected.

 Note    If you want to allow users to type entries that are not in the list, clear the Show error alert after invalid data is entered check box instead.

• Select one of the following options for the Style box:
  • To display an information message that does not prevent entry of invalid data, select Information.
  • To display a warning message that does not prevent entry of invalid data, select Warning.
  • To prevent entry of invalid data, select Stop.
• Fill in the title and text for the message (up to 225 characters).

 Note    If you don't enter a title or text, the title uses the default string "Microsoft Excel" and the message is (by default): "The value you entered is not valid. A user has restricted values that can be entered into this cell."

10. Test the data validation to make sure that it is working correctly.

Try entering both valid and invalid data in the cells to make sure that your settings are working as you intended and your messages are appearing when you expect.

Tip  If you change the validation settings for a cell, you can automatically apply your changes to all other cells that have the same settings. To do so, open the Data Validation dialog box, and then select the Apply these changes to all other cells with the same settings check box on the Settings tab.

Restrict data entry to a whole number within limits

1. Select one or more cells to validate.
2. On the Data tab, in the Data Tools group, click Data Validation.

Issue: The Data Validation command is unavailable.

3. In the Data Validation dialog box, click the Settings tab.
4. In the Allow box, select Whole number.
5. In the Data box, select the type of restriction that you want. For example, to set upper and lower limits, select between.
6. Enter the minimum, maximum, or specific value to allow. You can also enter a formula that returns a number value.

For example, to set a minimum limit of deductions to two times the number of children in cell F1, select greater than or equal to in the Data box and enter the formula, =2*F1, in the Minimum box.

7. To specify how you want to handle blank (null) values, select or clear the Ignore blank check box.

 Note    If your allowed values are based on a cell range with a defined name, and there is a blank cell anywhere in the range, setting the Ignore blank check box allows any values to be entered in the validated cell. This is also true for any cells that are referenced by validation formulas: if any referenced cell is blank, setting the Ignore blank check box allows any values to be entered in the validated cell.

8. Optionally, display an input message when the cell is clicked.

How to display an input message

1. Click the Input Message tab.
2. Make sure the Show input message when cell is selected check box is selected.
3. Fill in the title and text for the message.
9. Specify how you want Microsoft Office Excel to respond when invalid data is entered.

How to specify a response to invalid data

1. Click the Error Alert tab, and make sure that the Show error alert after invalid data is entered check box is selected.
2. Select one of the following options for the Style box:
   • To display an information message that does not prevent entry of invalid data, select Information.
   • To display a warning message that does not prevent entry of invalid data, select Warning.
   • To prevent entry of invalid data, select Stop.
3. Fill in the title and text for the message (up to 225 characters).

 Note    If you don't enter a title or text, the title defaults to "Microsoft Excel" and the message to: "The value you entered is not valid. A user has restricted values that can be entered into this cell."

10. Test the data validation to make sure that it is working correctly.

Try entering both valid and invalid data in the cells to make sure that your settings are working as you intended and your messages are appearing when you expect.

Tip  If you change the validation settings for a cell, you can automatically apply your changes to all other cells that have the same settings. To do so, open the Data Validation dialog box, and then select the Apply these changes to all other cells with the same settings check box on the Settings tab.

Restrict data entry to a decimal number within limits

1. Select one or more cells to validate.
2. On the Data tab, in the Data Tools group, click Data Validation.

Issue: The Data Validation command is unavailable.

3. In the Data Validation dialog box, click the Settings tab.
4. In the Allow box, select Decimal.
5. In the Data box, select the type of restriction that you want. For example, to set upper and lower limits, select between.
6. Enter the minimum, maximum, or specific value to allow. You can also enter a formula that returns a number value.

For example, to set a maximum limit for commissions and bonuses of 6% of a salesperson's salary in cell E1, select less than or equal to in the Data box and enter the formula, =E1*6%, in the Maximum box.

 Note    To let a user enter percentages, for example 20%, select Decimal in the Allow box, select the type of restriction that you want in the Data box, enter the minimum, maximum, or specific value as a decimal, for example .2, and then display the data validation cell as a percentage by selecting the cell and clicking Percent Style in the Number group on the Home tab.

7. To specify how you want to handle blank (null) values, select or clear the Ignore blank check box.

 Note    If your allowed values are based on a cell range with a defined name, and there is a blank cell anywhere in the range, setting the Ignore blank check box allows any values to be entered in the validated cell. This is also true for any cells that are referenced by validation formulas: if any referenced cell is blank, setting the Ignore blank check box allows any values to be entered in the validated cell.

8. Optionally, display an input message when the cell is clicked.

How to display an input message

1. Click the Input Message tab.
2. Make sure the Show input message when cell is selected check box is selected.
3. Fill in the title and text for the message.
9. Specify how you want Microsoft Office Excel to respond when invalid data is entered.

How to specify a response to invalid data

1. Click the Error Alert tab, and make sure that the Show error alert after invalid data is entered check box is selected.
2. Select one of the following options for the Style box:
   • To display an information message that does not prevent entry of invalid data, select Information.
   • To display a warning message that does not prevent entry of invalid data, select Warning.
   • To prevent entry of invalid data, select Stop.
3. Fill in the title and text for the message (up to 225 characters).

 Note    If you don't enter a title or text, the title defaults to "Microsoft Excel" and the message to: "The value you entered is not valid. A user has restricted values that can be entered into this cell."

10. Test the data validation to make sure that it is working correctly.

Try entering both valid and invalid data in the cells to make sure that your settings are working as you intended and your messages are appearing when you expect.

Tip  If you change the validation settings for a cell, you can automatically apply your changes to all other cells that have the same settings. To do so, open the Data Validation dialog box, and then select the Apply these changes to all other cells with the same settings check box on the Settings tab.

Restrict data entry to a date within a time frame

1. Select one or more cells to validate.
2. On the Data tab, in the Data Tools group, click Data Validation.

Issue: The Data Validation command is unavailable.

3. In the Data Validation dialog box, click the Settings tab.
4. In the Allow box, select Date.
5. In the Data box, select the type of restriction that you want. For example, to allow dates after a certain day, select greater than.
6. Enter the start, end, or specific date to allow. You can also enter a formula that returns a date.

For example, to set a time frame between today's date and 3 days from today's date, select between in the Data box, enter =TODAY() in the Minimum box, and enter =TODAY()+3 in the Maximum box.

7. To specify how you want to handle blank (null) values, select or clear the Ignore blank check box.

 Note    If your allowed values are based on a cell range with a defined name, and there is a blank cell anywhere in the range, setting the Ignore blank check box allows any values to be entered in the validated cell. This is also true for any cells that are referenced by validation formulas: if any referenced cell is blank, setting the Ignore blank check box allows any values to be entered in the validated cell.

8. Optionally, display an input message when the cell is clicked.

How to display an input message

1. Click the Input Message tab.
2. Make sure the Show input message when cell is selected check box is selected.
3. Fill in the title and text for the message.
9. Specify how you want Microsoft Office Excel to respond when invalid data is entered.

How to specify a response to invalid data

1. Click the Error Alert tab, and make sure that the Show error alert after invalid data is entered check box is selected.
2. Select one of the following options for the Style box:
   • To display an information message that does not prevent entry of invalid data, select Information.
   • To display a warning message that does not prevent entry of invalid data, select Warning.
   • To prevent entry of invalid data, select Stop.
3. Fill in the title and text for the message (up to 225 characters).

 Note    If you don't enter a title or text, the title defaults to "Microsoft Excel" and the message to: "The value you entered is not valid. A user has restricted values that can be entered into this cell."

10. Test the data validation to make sure that it is working correctly.

Try entering both valid and invalid data in the cells to make sure that your settings are working as you intended and your messages are appearing when you expect.

Tip  If you change the validation settings for a cell, you can automatically apply your changes to all other cells that have the same settings. To do so, open the Data Validation dialog box, and then select the Apply these changes to all other cells with the same settings check box on the Settings tab.

Restrict data entry to a time within a time frame

1. Select one or more cells to validate.
2. On the Data tab, in the Data Tools group, click Data Validation.

Issue: The Data Validation command is unavailable.

3. In the Data Validation dialog box, click the Settings tab.
4. In the Allow box, select Time.
5. In the Data box, select the type of restriction that you want. For example, to allow times before a certain time of day, select less than.
6. Enter the start, end, or specific time to allow. You can also enter a formula that returns a time.

For example, to set a time frame for serving breakfast between the time when the restaurant opens (the value in cell H1) and five hours after that, select between in the Data box, enter =H1 in the Minimum box, and then enter =H1+"5:00" in the Maximum box.

7. To specify how you want to handle blank (null) values, select or clear the Ignore blank check box.

 Note    If your allowed values are based on a cell range with a defined name, and there is a blank cell anywhere in the range, setting the Ignore blank check box allows any values to be entered in the validated cell. This is also true for any cells that are referenced by validation formulas: if any referenced cell is blank, setting the Ignore blank check box allows any values to be entered in the validated cell.

8. Optionally, display an input message when the cell is clicked.

How to display an input message

1. Click the Input Message tab.
2. Make sure the Show input message when cell is selected check box is selected.
3. Fill in the title and text for the message.
9. Specify how you want Microsoft Office Excel to respond when invalid data is entered.

How to specify a response to invalid data

1. Click the Error Alert tab, and make sure that the Show error alert after invalid data is entered check box is selected.
2. Select one of the following options for the Style box:
   • To display an information message that does not prevent entry of invalid data, select Information.
   • To display a warning message that does not prevent entry of invalid data, select Warning.
   • To prevent entry of invalid data, select Stop.
3. Fill in the title and text for the message (up to 225 characters).

 Note    If you don't enter a title or text, the title defaults to "Microsoft Excel" and the message to: "The value you entered is not valid. A user has restricted values that can be entered into this cell."

10. Test the data validation to make sure that it is working correctly.

Try entering both valid and invalid data in the cells to make sure that your settings are working as you intended and your messages are appearing when you expect.

Tip  If you change the validation settings for a cell, you can automatically apply your changes to all other cells that have the same settings. To do so, open the Data Validation dialog box, and then select the Apply these changes to all other cells with the same settings check box on the Settings tab.

Restrict data entry to text of a specified length

1. Select one or more cells to validate.
2. On the Data tab, in the Data Tools group, click Data Validation.

Issue: The Data Validation command is unavailable.

3. In the Data Validation dialog box, click the Settings tab.
4. In the Allow box, select Text Length.
5. In the Data box, select the type of restriction that you want. For example, to allow up to a certain number of characters, select less than or equal to.
6. Enter the minimum, maximum, or specific length for the text. You can also enter a formula that returns a number value.

For example, to set the specific length for a full name field (C1) to be the current length of a first name field (A1) and a last name field (B1) plus 10, select less than or equal to in the Data box and enter =SUM(LEN(A1),LEN(B1),10) in the Maximum box.

7. To specify how you want to handle blank (null) values, select or clear the Ignore blank check box.

 Note    If your allowed values are based on a cell range with a defined name, and there is a blank cell anywhere in the range, setting the Ignore blank check box allows any values to be entered in the validated cell. This is also true for any cells that are referenced by validation formulas: if any referenced cell is blank, setting the Ignore blank check box allows any values to be entered in the validated cell.

8. Optionally, display an input message when the cell is clicked.

How to display an input message

1. Click the Input Message tab.
2. Make sure the Show input message when cell is selected check box is selected.
3. Fill in the title and text for the message.
9. Specify how you want Microsoft Office Excel to respond when invalid data is entered.

How to specify a response to invalid data

1. Click the Error Alert tab, and make sure that the Show error alert after invalid data is entered check box is selected.
2. Select one of the following options for the Style box:
   • To display an information message that does not prevent entry of invalid data, select Information.
   • To display a warning message that does not prevent entry of invalid data, select Warning.
   • To prevent entry of invalid data, select Stop.
3. Fill in the title and text for the message (up to 225 characters).

 Note    If you don't enter a title or text, the title defaults to "Microsoft Excel" and the message to: "The value you entered is not valid. A user has restricted values that can be entered into this cell."

10. Test the data validation to make sure that it is working correctly.

Try entering both valid and invalid data in the cells to make sure that your settings are working as you intended and your messages are appearing when you expect.

Tip  If you change the validation settings for a cell, you can automatically apply your changes to all other cells that have the same settings. To do so, open the Data Validation dialog box, and then select the Apply these changes to all other cells with the same settings check box on the Settings tab.

Calculate what is allowed based on the content of another cell

1. Select one or more cells to validate.
2. On the Data tab, in the Data Tools group, click Data Validation.

Issue: The Data Validation command is unavailable.

3. In the Data Validation dialog box, click the Settings tab.
4. In the Allow box, select the type of data that you want.
5. In the Data box, select the type of restriction that you want.
6. In the box or boxes below the Data box, click the cell that you want to use to specify what is allowed.

For example, to allow entries for an account only if the result won't go over the budget in cell E4, select Decimal for Allow, select less than or equal to for Data, and in the Maximum box, enter =E4.

7. To specify how you want to handle blank (null) values, select or clear the Ignore blank check box.

 Note    If your allowed values are based on a cell range with a defined name, and there is a blank cell anywhere in the range, setting the Ignore blank check box allows any values to be entered in the validated cell. This is also true for any cells that are referenced by validation formulas: if any referenced cell is blank, setting the Ignore blank check box allows any values to be entered in the validated cell.

8. Optionally, display an input message when the cell is clicked.

How to display an input message

1. Click the Input Message tab.
2. Make sure the Show input message when cell is selected check box is selected.
3. Fill in the title and text for the message.
9. Specify how you want Microsoft Office Excel to respond when invalid data is entered.

How to specify a response to invalid data

1. Click the Error Alert tab, and make sure that the Show error alert after invalid data is entered check box is selected.
2. Select one of the following options for the Style box:
   • To display an information message that does not prevent entry of invalid data, select Information.
   • To display a warning message that does not prevent entry of invalid data, select Warning.
   • To prevent entry of invalid data, select Stop.
3. Fill in the title and text for the message (up to 225 characters).

 Note    If you don't enter a title or text, the title defaults to "Microsoft Excel" and the message to: "The value you entered is not valid. A user has restricted values that can be entered into this cell."

10. Test the data validation to make sure that it is working correctly.

Try entering both valid and invalid data in the cells to make sure that your settings are working as you intended and your messages are appearing when you expect.

Tip  If you change the validation settings for a cell, you can automatically apply your changes to all other cells that have the same settings. To do so, open the Data Validation dialog box, and then select the Apply these changes to all other cells with the same settings check box on the Settings tab.

Use a formula to calculate what is allowed

1. Select one or more cells to validate.
2. On the Data tab, in the Data Tools group, click Data Validation.

Issue: The Data Validation command is unavailable.

3. In the Data Validation dialog box, click the Settings tab.
4. In the Allow box, select Custom.
5. In the Formula box, enter a formula that calculates a logical value (TRUE for valid or FALSE for invalid entries). For example:

6. To specify how you want to handle blank (null) values, select or clear the Ignore blank check box.

 Note    If your allowed values are based on a cell range with a defined name, and there is a blank cell anywhere in the range, setting the Ignore blank check box allows any values to be entered in the validated cell. This is also true for any cells that are referenced by validation formulas: if any referenced cell is blank, setting the Ignore blank check box allows any values to be entered in the validated cell.

7. Optionally, display an input message when the cell is clicked.

How to display an input message

1. Click the Input Message tab.
2. Make sure the Show input message when cell is selected check box is selected.
3. Fill in the title and text for the message.
8. Specify how you want Microsoft Office Excel to respond when invalid data is entered.

How to specify a response to invalid data

1. Click the Error Alert tab, and make sure that the Show error alert after invalid data is entered check box is selected.
2. Select one of the following options for the Style box:
   • To display an information message that does not prevent entry of invalid data, select Information.
   • To display a warning message that does not prevent entry of invalid data, select Warning.
   • To prevent entry of invalid data, select Stop.
3. Fill in the title and text for the message (up to 225 characters).

 Note    If you don't enter a title or text, the title defaults to "Microsoft Excel" and the message to: "The value you entered is not valid. A user has restricted values that can be entered into this cell."

9. Test the data validation to make sure that it is working correctly.

Try entering both valid and invalid data in the cells to make sure that your settings are working as you intended and your messages are appearing when you expect.

Tip  If you change the validation settings for a cell, you can automatically apply your changes to all other cells that have the same settings. To do so, open the Data Validation dialog box, and then select the Apply these changes to all other cells with the same settings check box on the Settings tab.

 Top of Page

See Also

• Create a drop-down list from a range of cells
• Create or change a cell reference
• Protect worksheet or workbook elements
• Remove data validation
• Unlock specific areas of a protected worksheet
• Use a shared workbook to collaborate

 

 

Excel > Validating data

Copy data validation settings

Excel 2007

After you define data validation in a cell, you may want to copy the data validation settings to other cells. For example, you may want to ensure that a user can only enter date values for the current year in a column of data, or you may want to use the same input message and error alert, but slightly modify the data validation settings in the other cells.

1. Select the cell, and then on the Home tab, in the Clipboard group, click Copy .

Keyboard shortcut  You can also press CTRL+C.

2. Select the cells for which you want to duplicate the settings.

How to select cells, ranges, rows, or columns

3. On the Home tab, in the Clipboard group, click the arrow below Paste, and then click Paste Special.
4. Click Validation.

 Note    In the Paste Special dialog box, the All and All except borders options also paste the data validation settings.

See Also

• Apply data validation to cells
• Create or change a cell reference
• Data validation error alert
• Find cells that have data validation

 

 

Excel > Validating data

Find cells that have data validation

You may want to easily locate which cells in a worksheet have data validation. Perhaps you inherited a worksheet to update that a former employee created, or your worksheet is large, and you forgot in which cells you have added data validation. Fortunately, you can easily highlight cells with data validation.

What do you want to do?

Find all cells with data validation

Find cells that match certain data validation settings

Find all cells with data validation

1. On the Home tab, in the Editing group, click the arrow next to Find & Select, and then click Go To Special.

2. Click Data Validation, and then click All.

If any cells are found that contain data validation, they are highlighted; otherwise, the message "No cells were found" is displayed.

 Top of Page

Find cells that match certain data validation settings

1. Click a cell that has the data validation settings for which you want to find matches.
2. On the Home tab, in the Editing group, click the arrow next to Find & Select, and then click Go To Special.

3. Click Data Validation, and then click Same.

If any cells are found that contain matching data validation, they are highlighted.

 Top of Page

See Also

• Apply data validation to cells
• Copy data validation settings
• Data validation error alert
• Display or hide circles around invalid data
• Find and correct errors in formulas
• Remove data validation

 

 

Excel > Validating data

Display or hide circles around invalid data

Excel 2007

Data validation (data validation: An Excel feature that you can use to define restrictions on what data can or should be entered in a cell, and to display messages that prompt users for correct entries and notify users about incorrect entries.) is designed to display messages and prevent invalid entries only when users type data directly in a cell. Validation messages won't appear and invalid data can be entered when a user enters data in a cell by copying or filling, a formula in the cell calculates a result that isn't valid, or a macro  enters invalid data in the cell.

You may want to audit your worksheets to look for incorrect data that may cause inaccurate calculations or results. You can identify cells with data validation that contain invalid data by displaying a red circle around them so that you can easily find and correct any problems.

What do you want to do?

Circle invalid cells

Hide validation circles

Circle invalid cells

1. On the Data tab, in the Data Tools group, click the arrow next to Data Validation, and then click Circle Invalid Data.

Microsoft Office Excel displays a red circle around any cells that contain invalid data.

All cells that don't meet their data validation criteria are circled, including values that were typed, copied, or filled in the cells, calculated by formulas, or entered by macros.

 Top of Page

Hide validation circles

Do one of the following:

• To remove the circle from a single cell, enter valid data in the cell.
• On the Data tab, in the Data Tools group, click the arrow next to Data Validation, and then click Clear Validation Circles.

 Top of Page

See Also

• Apply data validation to cells
• Data validation error alert
• Find cells that have data validation

 

 

Excel > Validating data

Remove data validation

Excel 2007

1. Select the cells where you no longer want to validate data.

To remove data validation from all similar cells or from all cells that have validation on a worksheet, find the cells.

How to find cells with data validation

1. On the Home tab, in the Editing group, click the arrow next to Find & Select, and then click Go To Special.

2. Click Data Validation, and then click All.
3. Click a cell that has the data validation settings for which you want to find matches.
4. On the Home tab, in the Editing group, click the arrow next to Find & Select, and then click Go To Special.

5. Click Data Validation, and then click Same.
2. On the Data tab, in the Data Tools group, click Data Validation.

Issue: The Validation command is unavailable.

3. Do one of the following:
   1. If you are prompted to erase the current settings and continue, click OK, and then click Cancel.
   2. If the Data Validation dialog box appears, click the Settings tab, and then click Clear All.