How To Create A Spreadsheet In Excel 2003

(Archives) Excel 2003/2004: Protecting Your Worksheet

Last updated Monday, Aug. 31, 2020, at 10:25 a.m.

This article is based on legacy software.

Protection of documents and cells can help prevent inadvertent changes to your worksheet. This can be especially helpful if you have someone who is unfamiliar with worksheets or doing your data entry, or if you spent many hours struggling to get the worksheet right.

Excel offers the option of protecting the entire document, individual objects, structure of a window, and/or specific cells. With Excel, you can even add a password to the file. If you want to prevent changes to sheets or cells, you need to lock the cells and then protect the sheet.

Protection Options

When you are protecting your workbook, you have two primary options:

Prevent data entry for select cells
Users can access the worksheet and view the information; however, access for making changes is restricted. For more information, refer to Locking & Unlocking Cells below.
Restrict or prevent access to the file
Users can be prevented from viewing the worksheet, or users can view the workbook but not make changes to it. For more information, refer to File Level Protection below.

Locking & Unlocking Cells

By default, Excel protects cells, graphics, charts, and other worksheet objects. This protection will only take effect by turning on the Worksheet Protection option in the Protect Sheet dialog box. Therefore, if you enable protection, no changes can be made to a cell until you unlock that cell.

Locking Cells

You can easily lock any cell in a worksheet.

NOTE: Make sure to lock the cells before you protect the sheet or document. Once a sheet or a document has been protected, you cannot access menu selections that allow you to make changes to cells.

Select the cell(s) to be locked.
From the Format menu, select Cells...
The Format Cells dialog box appears.
Select the Protection tab.
Select Locked .
There will be a check before it.
Click OK.
You must now protect the worksheet.

Unlocking Cells

In order to unlock cells, sheet protection must first be turned off. For more information, refer to Worksheet Level Protection or Workbook Level Protection.

Unprotect the worksheet.
Select the cells you want to unlock.
From the Format menu, select Cells...
The Format Cells dialog box appears.
Select the Protection tab.
Deselect Locked .
There will no longer be a check before it.
Click OK.
The cells are unlocked.

Workbook Level Protection

You can prevent a workbook from having its structure and windows modified or resized by another user.

Protecting the Workbook

From the Tools menu, select Protection » Protect Workbook...
The Protect Workbook dialog box appears.

In the Protect Workbook dialog box, select the appropriate option(s):

Option	Description
Structure	Prevents the user from changing the order of the sheets within a workbook. This includes adding or deleting worksheets.
Windows	Prevents the user from being able to resize or move the window.
Password	Allows only those who know the password to turn the workbook protection off.

Click OK.
The workbook is protected.

Unprotecting the Workbook

From the Tools menu, select Protection » Unprotect Workbook…
The workbook is unprotected. Users can now modify the structure and windows of the workbook.
NOTE: If you included a password when you turned the protection on, you must enter the password to turn the protection off.

Worksheet Level Protection

You can protect a single worksheet from being modified by another user.

Protecting the Worksheet

Windows:

From the Tools menu, select Protection » Protect Sheet...
The Protect Sheet dialog box appears.

In the Protect Sheet dialog box, select the appropriate options:

Option	Description
Protect worksheet and contents of locked cells	Prevents changes to locked cells.
Password to unprotect sheet	Allows only those who know the password to unprotect the worksheet.
Allow all users of this worksheet to	Checked boxes are aspects that any user can access.

Click OK.
The worksheet is protected.

Macintosh:

From the Tools menu, select Protection » Protect Sheet...
The Protect Sheet dialog box appears.

In the Protect Sheet dialog box, select the appropriate options:

Option	Description
Contents	Prevents changes to locked cells, columns or rows, chart sheet, and any formulas in the cells.
Objects	Prevents changes to graphic objects (e.g., text boxes, an embedded chart).
Scenarios	Prevents changes to scenarios (i.e., any input values you substituted in a worksheet).
Password	Allows only those who know the password to unprotect the worksheet.

Click OK.
The worksheet is protected.

Unprotecting the Worksheet

From the Tools menu, select Protection » Unprotect Sheet…
The worksheet is unprotected. Users can now modify the worksheet.
NOTE: If you included a password when you turned the protection on, you must type the password in the Password text box to turn the protection off.

File Level Protection

You have two options when saving your workbook file with a password. You can save the file so only those who know the password can open or modify the file.

Protecting the File

From the File menu, select Save As...
The Save As dialog box appears.
Windows: From the Tools pull-down list, select General Options...
Macintosh: Click Options...
The Save Options dialog box appears.
To prevent unauthorized access to the file, in the Password to open text box, type a password .
To prevent unauthorized changes to the file, in the Password to modify text box, type a password .
Click OK.
The Confirm Password dialog box appears.
In the Reenter password to proceed/modify text box, type the password(s) in the order of the password(s) that you typed in the Save Options dialog box.
HINT: If you type apple in the Password to open text box and orange in the Password to modify text box, you must type apple in the first confirmation dialog box and orange in the second confirmation dialog box.
Click OK.
Click Save.

Unprotecting the File

If you no longer need to password-protect the file, you can remove the password.

From the File menu, select Save As…
The Save As dialog box appears.
Windows: In the Save As dialog box, from the Tools pull-down list, select General Options...
Macintosh: Click Options.
In the Password to open text box, press [Delete].
In the Password to modify text box, press [Delete].
Click OK.
The file is no longer password protected.
To save the file, click Save.
A confirmation dialog box appears.
Windows: Click Yes.
Macintosh: Click Replace.

Was this article helpful? Yes No

View / Print PDF