EXCEL VIDEO TUTORIALS / EXCEL DASHBOARD REPORTS

EXCEL WORKSHEET PROTECTION

FREE EXCEL HELP

Lesson 36 - Excel Worksheet Protection. Protect Worksheet Data. Level 1 Free Training

<< PREVIOUS LESSON |NEXT LESSON>> | BACK TO EXCEL LEVEL 1 TRAINING INDEX

WORKSHEET PROTECTION

In this day and age of computers where we now have many files that have multiple users, you can protect all or part of a Workbook easily. Protecting Workbook data makes it very difficult for specific cell values to be changed, either accidentally or deliberately. Some reasons for protecting your data could be:

To direct others to specific cells that they can input into by making it impossible for them to enter data anywhere else on a Worksheet.

To prevent accidental deletion, or modification of essential values within a Worksheet that may be needed to perform specific calculations.

To prevent accidental deletion, or modification to essential and sometimes very complicated formulas within a Worksheet.

Worksheet protection is a very valuable and useful tool, but using it can sometimes seem a little confusing, as there are actually two separate processes that must be followed to protect data.

The first step is to unlock any cells that will require editing. The second step is to apply Worksheet Protection.

Enabling Worksheet Protection

Let�s have a look at how we would apply Worksheet protection to the file Charting. xls that we used previously , and how we would unlock the December figures for each Department, so that they can type their own figures in there.

As a default, all cells within Excel are locked by default. However, you can still change or edit these cells because the Worksheet or Workbook is unprotected. The first step to using data protection is to unlock the cells that need to be changed when we apply Worksheet Protection.

Open the file Charting. xls if it is not already open.

If you need to, move your pie chart out of the way using the technique described above so that you can see the range M3:M6.

Highlight the range M3:M6 with your mouse, then go to Format>Cells which will display the Format Cells dialog box, and select the Protection tab.

You have two options within this box. The first option Locked, if selected, will prevent a user from changing, moving, resizing or deleting the selected cells. The second option Hidden, if selected, will hide the formulas within your Worksheet so that if a cell containing a formula is selected, you will not be able to see what the formula is in the formula bar. Let�s select the Locked option until it appears without a tick. , then click on OK to unlock our selected cells (M3:M6).

Now we have unlocked our cells, we can apply Worksheet Protection to our data.

Select Tools>Protection/Protect Sheet and the Protect Sheet dialog box will appear.

Depending on which version of Excel you are using, the options may be slightly different in the Protect Sheet dialog. We do not wish to use a password at this stage, and we will just accept the defaults as they are in this case, so just click OK.

OK, let�s have a go at using our protected Worksheet.

Click in cell L3 and type the number 1234. You will notice that as soon as you Start to type, Excel will display a message that tells you the cell or chart you are trying to change is protected and therefore read-only.
Now click in cell M3 and type 1234, then Enter. This time your entry will be allowed, as would any entry that you typed in M4, M5, or M6.

You will note that when you make a change to either M3, M4, M5, or M6, that the formula in M7 will update. This is because locked cells that contain formulas will still change in accordance with the data that is used to calculate them.

Disabling Worksheet Protection

You can unprotect a Worksheet in the same way that you protected it in the first place.

Go to Tools>Protection.
As the Worksheet is already protected, your sub-menu will contain a command to unprotect your Worksheet, so let�s select Unprotect Sheet.

OK, so we have looked now at Worksheet Protection. However anyone with a working knowledge of Excel, could figure out how to unprotect a sheet if they wanted to, so Excel offers you the ability to use a password to protect your Sheet. Passwords are case-sensitive. One thing you MUST be aware of when using a password is that if you lose or forget your password, it cannot be recovered, so it might be a good idea to write down your passwords somewhere and the Worksheets that they relate to and store them in a safe place. It is also a good idea when using a password to use a combination of letters, numbers and symbols.

Select Tools>Protection/Protect Sheet.
Click in the Passwordbox and type in the word password then click OK.
Excel will ask you to confirm your password by retyping it, so lets retype in the word password then select OK.
Now click in N3 and type in 1234 as before you cannot make a change here, and a message box will pop up telling you so.
Now click in M3 and type in 5678 and click Enter. As before, no warning will appear and you are able to change cell M3.

Let�s try and unprotect our sheet now.

Select Tools>Protection/Unprotect Sheet.
You must now type in the correct password for your sheet to be unprotected. Type in the word passwording, then click on OK.
You will get an error message from Excel advising you that the password that you typed is incorrect. You will also get a hint from Excel, that maybe the caps lock key could be on, just in case you have typed the right password in the wrong case.
Click on OK and we will try again.
Select Tools>Protection/Unprotect Sheet and type in the correct password (password).
Select OK and your Worksheet will now be unprotected again.

So remember, if you want to use the Protect facility, you must remember to unlock the cells that you wish to be changed before your apply sheet protection. You can protect a Worksheet with or without a password, but remember that the password does not prevent access to the data, but instead prevents the worksheet being unprotected. Once the protection system is in place, it is impossible for a user to edit, change or delete the contents of a locked cell.

If you try, Excel will display a message advising you of this.

Go To Free Excel Training Lesson 37 . Back to Previous Lesson

Go to Excel Basic/Level 1 Training Index

<< PREVIOUS LESSON |NEXT LESSON>> | BACK TO EXCEL LEVEL 1 TRAINING INDEX

Instant Download and Money Back Guarantee on Most Software

EXCEL VIDEO TUTORIALS / EXCEL DASHBOARD REPORTS

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft