Messages appear only when data is typed directly into the cells. Your message won't appear:

• When a user enters data in the cell by copying or filling.
• When a formula in the cell calculates a result that isn't valid.
• When 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.

To identify cells that contain invalid data regardless of how the data was entered, point to Formula Auditing on the Tools menu, click Show Formula Auditing Toolbar, and then click Circle Invalid Data .

Notes

• To cycle through all the invalid cells in your worksheet, click Error Checking on the Tools menu.
• To see data errors in lists (list: A series of rows that contains related data or a series of rows that you designate to function as a datasheet by using the Create List command.), select the List data validation error check box on the Error Checking tab of the Options dialog box. To display the Options dialog box, click Options on the Tools menu. Select this check box to display a green triangle in cells within a list that contain errors. Clicking cells with errors will display a smart tag that you can use to display the validation restrictions for the active cell.

Is validation applied to the cells?   Find the cells on the worksheet that have data validation: click Go To on the Edit menu, click Special, click Data validation, and then click All.

Are users entering data by typing?   Make sure that users click each cell and then type the data. If users copy or fill data in validated cells, validation messages don't appear, and entry of invalid data isn't prevented.

Is manual recalculation turned on?   If so, uncalculated cells may prevent data from being validated properly. To turn off manual recalculation, click Options on the Tools menu, click the Calculation tab, and then click Automatic under Calculation.

Are formulas error free?   Make sure formulas in validated cells don't result in errors, such as #REF! or #DIV/0!. Microsoft Office Excel ignores the data validation until you correct the error.

Are cells referenced in formulas 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.

Are the referenced cells on the same worksheet or a different workbook?  Cell references have to be to cells on the same worksheet. If you have to refer to a different worksheet or a different workbook, use a defined name and make sure that the workbook is already open.

Data validation is designed to display messages and prevent invalid entries only when users type data directly in a cell. When data is copied, filled, or calculated by a formula, the messages don't appear.

Note  You cannot override data validation for lists (list: A series of rows that contains related data or a series of rows that you designate to function as a datasheet by using the Create List command.) that are linked to a SharePoint site.

Try the following:

• If a worksheet has data validation   Tell users to type data one cell at a time.
• To prevent users from filling and copying data using cell drag and drop   Click Options on the Tools menu, click the Edit tab, and then clear the Allow cell drag and drop check box. Then protect the data validation settings.

  How?

  Protect worksheet elements

  Protect worksheet elements from all users

  1. Switch to the worksheet that you want to protect.

  2. Unlock any cells you want users to be able to change: select each cell or range, click Cells on the Format menu, click the Protection tab, and then clear the Locked check box.

  3. Hide any formulas that you don't want to be visible: select the cells with the formulas, click Cells on the Format menu, click the Protection tab, and then select the Hidden check box.

  4. Unlock any graphic objects that you want users to be able to change.

     How?

     You don't need to unlock buttons or controls for users to be able to click and use them. You can unlock embedded charts, text boxes, and other objects created with the drawing tools that you want users to be able to modify. To see which elements on a worksheet are graphic objects, click Go To on the Edit menu, click Special, and then click Objects.

     1. Hold down CTRL and click each object that you want to unlock.
     2. On the Format menu, click the command for the object that you selected: AutoShape, Object, Text Box, Picture, Control, or WordArt.
     3. Click the Protection tab.
     4. Clear the Locked check box, and if present, clear the Lock text check box.

  5. On the Tools menu, point to Protection, and then click Protect Sheet.

  6. Type a password (password: A way to restrict access to a workbook, worksheet, or part of a worksheet. Excel passwords can be up to 255 letters, numbers, spaces, and symbols. You must type uppercase and lowercase letters correctly when you set and enter passwords.) for the sheet.

     Note  The password is optional; however, if you don't supply a password, any user will be able to unprotect the sheet and change the protected elements. Make sure that you choose a password that you can remember, because if you lose the password, you cannot gain access to the protected elements on the worksheet.

  7. In the Allow all users of this worksheet to list, select the elements that you want users to be able to change.

  8. Click OK, and if prompted retype the password.

  Give specific users access to protected ranges

  You must have Windows 2000 to give specific users access to ranges.

  1. On the Tools menu, point to Protection, and then click Allow Users to Edit Ranges. (This command is available only when the worksheet is not protected.)

  2. Click New.

  3. In the Title box, type a title for the range you are granting access to.

  4. In the Refers to cells box, type an equal sign (=), and then type a reference or select the range.

  5. In the Range password box, type a password (password: A way to restrict access to a workbook, worksheet, or part of a worksheet. Excel passwords can be up to 255 letters, numbers, spaces, and symbols. You must type uppercase and lowercase letters correctly when you set and enter passwords.) to access the range.

     The password is optional; if you don't supply a password, any user will be able to edit the cells.

  6. Click Permissions, and then click Add.

  7. Locate and select the users to whom you want to grant access. If you want to select multiple users, hold down CTRL while you click the names.

  8. Click OK twice, and if prompted retype the password.

  9. Repeat the previous steps for each range for which you are granting access.

  10. To retain a separate record of the ranges and users, select the Paste permissions information into a new workbook check box.

  11. Protect the worksheet: On the Tools menu, point to Protection, click Protect Sheet, make sure the Protect worksheet and contents of locked cells check box is selected, type a password for the worksheet, click OK, and retype the password to confirm.

      Note  A sheet password is required to prevent other users from being able to edit your designated ranges. Make sure that you choose a password that you can remember, because if you lose the password, you cannot gain access to the protected elements on the worksheet.

  Help protect workbook elements and files

  Protect workbook elements

  1. On the Tools menu, point to Protection, and then click Protect Workbook.

  2. Do one or more of the following:

     • To protect the structure of a workbook so that worksheets in the workbook can't be moved, deleted, hidden, unhidden, or renamed, and new worksheets can't be inserted, select the Structure check box.

     • To use windows of the same size and position each time the workbook is opened, select the Windows check box.

     • To prevent others from removing workbook protection, type a password (password: A way to restrict access to a workbook, worksheet, or part of a worksheet. Excel passwords can be up to 255 letters, numbers, spaces, and symbols. You must type uppercase and lowercase letters correctly when you set and enter passwords.), click OK, and then retype the password to confirm it.

  Protect a shared workbook

  1. If the workbook is already shared, and you want to assign a password (password: A way to restrict access to a workbook, worksheet, or part of a worksheet. Excel passwords can be up to 255 letters, numbers, spaces, and symbols. You must type uppercase and lowercase letters correctly when you set and enter passwords.) to protect the sharing, unshare the workbook.

     How?

     1. Have all other users save and close the shared workbook (shared workbook: A workbook set up to allow multiple users on a network to view and make changes at the same time. Each user who saves the workbook sees the changes made by other users.). If other users are editing, they will lose any unsaved work.
     2. Unsharing the workbook deletes the change history (change history: In a shared workbook, information that is maintained about changes made in past editing sessions. The information includes the name of the person who made each change, when the change was made, and what data was changed.). If you want to keep a copy of this information, print the History worksheet (History worksheet: A separate worksheet that lists changes being tracked in a shared workbook, including the name of the person who made the change, when and where it was made, what data was deleted or replaced, and how conflicts were resolved.) or copy it to another workbook.

        How?

        1. On the Tools menu, point to Track Changes, and then click Highlight Changes.
        2. In the When box, click All.
        3. Clear the Who and Where check boxes.
        4. Select the List changes on a new sheet check box, and then click OK.
        5. Do one or more of the following:
           • To print the History worksheet, click Print .
           • To copy the history to another workbook, select the cells you want to copy, click Copy , switch to another workbook, click where you want the copy to appear, and click Paste .

           Note  You may also want to save or print the current version of the workbook, because this history might not apply to later versions. For example, cell locations, including row numbers, in the copied history may no longer be current.

     3. On the Tools menu, click Share Workbook, and then click the Editing tab.
     4. Make sure that you are the only person listed in the Who has this workbook open now box.
     5. Clear the Allow changes by more than one user at the same time check box.

        If this check box is not available, you must unprotect the workbook before clearing the check box.

        How?

        1. Click OK, point to Protection on the Tools menu, and then click Unprotect Shared Workbook.
        2. Enter the password (password: A way to restrict access to a workbook, worksheet, or part of a worksheet. Excel passwords can be up to 255 letters, numbers, spaces, and symbols. You must type uppercase and lowercase letters correctly when you set and enter passwords.) if prompted, and then click OK.
        3. On the Tools menu, click Share Workbook, and then click the Editing tab.
     6. When prompted about the effects on other users, click Yes.

  2. Set other types of protection if you want to. Give specific users access to ranges, protect worksheets, protect workbook elements, and set passwords for viewing and editing.

  3. On the Tools menu, point to Protection, and then click Protect Shared Workbook or Protect and Share Workbook.

  4. Select the Sharing with track changes check box.

  5. If you want to require other users to supply a password to turn off the change history (change history: In a shared workbook, information that is maintained about changes made in past editing sessions. The information includes the name of the person who made each change, when the change was made, and what data was changed.) or remove the workbook from shared use, type the password in the Password box, and then retype the password when prompted.

  6. If prompted, save the workbook.

  Protect a workbook file from viewing or editing

  1. On the File menu, click Save As.

  2. On the Tools menu, click General Options.

  3. Do either or both of the following:

     • If you want users to enter a password (password: A way to restrict access to a workbook, worksheet, or part of a worksheet. Excel passwords can be up to 255 letters, numbers, spaces, and symbols. You must type uppercase and lowercase letters correctly when you set and enter passwords.) before they can view the workbook, type a password in the Password to open box, and then click OK.

     • If you want users to enter a password before they can save changes to the workbook, type a password in the Password to modify box, and then click OK.

  4. When prompted, retype your passwords to confirm them.

  5. Click Save.

  6. If prompted, click Yes to replace the existing workbook.

• To identify cells in which invalid data was copied, filled, or calculated   Point to Formula Auditing on the Tools menu, click Show Formula Auditing Toolbar, and then click Circle Invalid Data .

The list may be linked to a SharePoint site   You cannot add data validation to a list (list: A series of rows that contains related data or a series of rows that you designate to function as a datasheet by using the Create List command.) that is linked to a SharePoint site. To add data validation, you must unlink the list or convert the list to a range.

You may be currently entering data   The Validation command is not available on the Data menu while you are entering data in a cell. To finish entering, press ENTER or ESC.

The worksheet might be protected   To remove the protection, point to Protection on the Tools menu, and then click Unprotect Sheet.

The workbook might be shared   In a shared workbook (shared workbook: A workbook set up to allow multiple users on a network to view and make changes at the same time. Each user who saves the workbook sees the changes made by other users.), Excel can validate data, but you can't change the settings. To make the Validation command available, stop sharing the workbook.