Ozgrid, Experts in Microsoft Excel Spreadsheets



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



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.

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.

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.

Letís try and unprotect our sheet now.

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


Instant Download and Money Back Guarantee on Most Software


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

Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates