Home

Protecting a Spreadsheet

 

Protecting a Workbook

 

Introduction

You can protect a workbook to make sure that not just anybody can modify it. You can protect a workbook when saving it for the first time or you can protect an existing workbook. You have many options.

When protecting a workbook, you give it a password. Anybody who wants to use that workbook would need the password. You can also control whether people would be allowed to open and only view the workbook, or be able to open and modify its contents.

 

Protecting a Workbook

To protect a workbook when you save it for the first time, click the Office Button and click Save. In the lower section of the dialog box, click Tools and click General Options...

This would display the General Options dialog box with two text boxes:

General Options

If you want to allow only a person with a valid password to open the workbook, type that password in the Password to open text box. If you click OK, a Confirm Password text box would come up. If you want a person who opens the workbook to be able to change its content, click the Password to modify text box, type a password (the same or another) and click OK. A Confirm Password would come up. In the Confirm Password dialog box(es), you must type the same password you had provided in the corresponding first text boxes.

When a workbook has been protected with one or two passwords, a user can open it to view or change. If you give only the Open password, when opening the workbook, a user can enter that password and click Read Only. When a workbook is open with this option, its title bar would display Read-Only on the right side of the name:

Practical LearningPractical Learning: Protecting a Workbook

  1. Start Microsoft Excel
  2. In cell C2, type College Park Auto Repair
  3. Click cell B4 and type Year
  4. Complete the worksheet with the following values
     
    Year Make Model Part Name Unit Price
    2006 Dodge Caravan SE L4 2.4 Radiator Cap 6.65
    2000 Toyota Rav4 2WD/4-Door Thermostat Gasket 4.95
    2006 Kia Rio 1.6DOHC16V 4-DR Thermostat 14.45
  5. To save the workbook, click the Office Button and click Save
  6. Change the name to cpar1
     
    Save As
  7. Click the Tools button and click General Options...
  8. In the Password to Open text box, type P@ssword1 and press Tab
  9. In the Password to modify text box, type Passw0rd1
     
  10. Click OK
  11. In the Confirm Password dialog box, type P@ssword1
     
  12. Click OK
  13. In the second Confirm Password dialog box, type Passw0rd1 and press Enter
  14. Click Save
  15. To close the workbook, click the Office Button and click Close
  16. To open the workbook, click Office Button and click cpar1.xlsx
     
  17. In the Password dialog box, type P@ssword1
  18. Click OK
     
  19. In the second dialog box, click Read Only
  20. Click Year and type Car Year
  21. To save, press Ctrl + S
  22. Read the message box and click OK
  23. On the Save As dialog box, click Cancel
  24. To close the workbook, click the Office Button and click Close
  25. When asked whether you want to save, click No
  26. To re-open the workbook, click Office Button and click cpar1.xlsx
  27. In the Password dialog box, type P@ssword1 and click OK
  28. In the second Password dialog box, type Passw0rd1
     
    Password
  29. Click OK
  30. Click Year and type Car Year
  31. To save the workbook, press Ctrl + S

Unprotecting a Workbook

If you don't need to workbook protected anymore, you can remove its password. To do this, start saving the workbook. You can click the Office Button, position the mouse on Save As, and click Save Excel Workbook. Click Tools and click General Options. Delete the password and click OK. After deleting the password, click Save. You would be asked whether you want to overwrite the file. Click Yes.

Practical LearningPractical Learning: Removing a Password

  1. Click the Office Button -> Save As -> Excel Workbook
  2. In the lower section of the dialog box, click Options -> General Options...
  3. As the password is selected, press Delete
  4. Press Tab
  5. Press Delete
     
    General Options
  6. Click OK
  7. Click Save
  8. When asked whether you want to replace the file, click Yes

Protecting Worksheets

 

Protecting a Worksheet

If you protect a workbook, all of its worksheets are protected and would follow the rules of workbook protecting. An alternative is to protect the worksheets individually. You can protect all worksheets, some worksheets, or just one worksheet.

To protect a worksheet, after displaying it, on the Ribbon, click the Home tab. In the Cells section, click Format -> Protect Sheet... This would display the Protect Sheet dialog box:

Protect Sheet

To protect a worksheet, you must provide a password. After typing it in the Protect Sheet dialog box, click OK. A Confirm Password dialog box would come up. You must type the same password. After opening the workbook, to change anything on the worksheet, the user would be presented with a message box.

Un-Protecting a Worksheet

If you don't need to keep protecting a worksheet, you can remove its password. To do this, on the Ribbon, click Home. In the Cells section, click Format and click Unprotect Sheet...

Unprotect Sheet

In the Unprotect Sheet dialog box, enter a valid password and click OK.

 
 
 

Protecting Cells

 

Protecting Cells

Instead or protecting a whole workbook or a whole worksheet, you can just choose what cells to exclude from editing. Once again, you have many options.

When protecting cells, you can choose to lock some of them so their values cannot change. In reality, the cells that you don't want anybody to change must be unlocked. By default, when you start a worksheet, Microsoft Excel unlocks all cells so that you can write values in them. To check this, in the Cells section of the Home tab of the Ribbon, click Format and you would notice that the Lock Cell item is checked:

Lock Cell

You can also display the Format Cells dialog box and click its Protection tab:

Protection

To unlock the cells, you can click Format -> Lock Cell to remove the check box. if you are using the Format Cells dialog box, uncheck the Locked check box in the Protection tab.

When protecting a worksheet, you can choose the options in the Protect Sheet dialog box. To display it, in the Cells section of the Home tab of the Ribbon, click Format and click Protect Sheet.

Practical LearningPractical Learning: Protecting Cells

  1. From the resources that accompany these lessons, open the CPAR1 workbook
  2. In the lower part of the interface, click the Repair Invoice tab sheet
  3. Click Cells D4:E5
  4. Press and hold the Ctrl key down
  5. Click the following cells:
    1. D8:J9
    2. D10:E10
    3. G10:H10
    4. J10
    5. D12:E13
    6. G12:J13
    7. B16:I21
    8. B24:J29
    9. J33
  6. Release Ctrl
     
    College Park Auto Repair
  7. On the Ribbon, click Home if necessary.
    In the Cells section of the Ribbon, click Format and click Format Cells...
  8. Click the Protection tab
  9. Click the check box of Lock to remove its check mark
     
    Protection
  10. Click OK
  11. In the Cells section of the Ribbon, click Format and click Protect Sheet...
  12. In the text box, type P@ssw0rd1
     
  13. Click OK
  14. In the Confirm Password dialog box, type P@ssw0rd1
     
    Confirm Password
  15. Click OK
  16. Click Invoice # and type Receive
  17. Notice that you receive a message box. Read it and click OK
  18. Click the cell under Job Performed
  19. Type Changed the brakes
  20. Press Tab
  21. Type 65.95 and press Tab
  22. Notice that the next cell under the Job Performed receives focus

Unprotecting Cells

Since you protect cells using the worksheet, to unprotect them, you use the description we saw for the worksheet. After unprotecting the worksheet, re-lock it.

 
 
   
 

Previous Copyright 2009 FunctionX, Inc. Home