Change data validation settings for cells and drop down lists

You can change the data validation settings in Excel 2003 for cells and drop down lists, such as the type of data required in a cell, the items listed in a drop down list, or error messages that the user sees when invalid data is entered.

Learn more about designating valid cell entries.

  1. If the workbook is shared (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.), stop sharing it.

ShowHow?

  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.

ShowHow?

  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 Button image.
    • To copy the history to another workbook, select the cells that you want to copy, click Copy Button image, switch to another workbook, click where you want the copy to go, and click Paste Button image.

 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.

  1. On the Tools menu, click Share Workbook, and then click the Editing tab.
  2. Make sure that you are the only person listed in the Who has this workbook open now box.
  3. 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.

ShowHow?

  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.
  1. When prompted about the effects on other users, click Yes.
  1. If the worksheet is protected, unprotect it.

ShowHow?

ShowRemove user permissions for a range

  1. Switch to the protected worksheet.
  2. On the Tools menu, point to Protection, and then click Unprotect Sheet.
  3. If prompted, enter the protection 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 worksheet.
  4. On the Tools menu, point to Protection, and then click Allow Users to Edit Ranges.
  5. In the Ranges unlocked by a password when cell is protected list, click the range for which you want to remove permissions.
  6. Click Delete.

ShowRemove protection from a worksheet

ShowRemove protection from a workbook

ShowRemove protection from a shared workbook

ShowRemove the password for viewing or editing a workbook file

  1. Open the workbook.
  2. On the File menu, click Save As.
  3. On the Tools menu, click Options, and then select Security.
  4. In the Password to open or Password to modify box, or both boxes, double-click the asterisks and then press the DELETE key.
  5. Click OK, and then click Save.
  6. Click Yes to replace the existing workbook.
  1. Click a cell that has data validation settings that you want to change.
  2. On the Data menu, click Validation.
  3. On each tab, select and change the options that you want.

If you want the same changes for other cells on the worksheet, select the Apply these changes to all other cells with the same settings check box on the Settings tab.

 Note   You cannot change the data validation for cells in 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.

 
 
Applies to:
Excel 2003