OzGrid

Lesson 36 - Excel Worksheet Protection

< Back to Search results

 Category: [Excel]  Demo Available 

 

EXCEL WORKSHEET PROTECTION

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

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:

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.

  • 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.
  • 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 Review tab Which Will Display The Protect Tab.

  • Click on Protect Sheet and select the options you require.
  • 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.

 

Disabling Worksheet Protection

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

  • Go To Protect tab.
  • 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 Protect Sheet.
  • Click In The Password box 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 Protect/Unprotect
  • 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.

 

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

 

Go back to:

Lesson 1 - Excel Fundamentals
Lesson 2 - Starting Excel and Excel Workbooks
Lesson 3 - Excel Toolbars and Task Panes
Lesson 4 - Excel Worksheets
Lesson 5 - Excel Cells and Navigating a Worksheet
Lesson 6 - Excel Cut/Copying and Pasting Data
Lesson 7 - Excel Copying with the Fill Handle
Lesson 8 - Excel Paste Special
Lesson 9 - Excel Insert Command
Lesson 10 - Excel's default options
Lesson 11 - Excel's Undo and Redo
Lesson 12 - Excel's Format Painter
Lesson 13 - Excel's Dates and Times
Lesson 14 - Excel's Custom Formats
Lesson 15 - Excel Formulas
Lesson 16 - Excel Cell References
Lesson 17 - Excel: Avoid Typing
Lesson 18 - Excel Formulae Arguments & Syntax
Lesson 19 - Excel Autosum Formula
Lesson 20 - Excel Auto Calculate
Lesson 21 - Excel's Insert Function
Lesson 22 - Excel's Useful Functions
Lesson 23 - Excel's Named Ranges
Lesson 24 - Excel's Constants and the Paste Name Dialog
Lesson 25 - Excel's Calculations
Lesson 26 - Excel Comments Cell
Lesson 27 - Excel Find and Replace
Lesson - 28 - Clear Excel Cell Contents
Lesson 29 - Effective Excel Printing 1
Lesson 30 - Effective Excel Printing 2
Lesson 31 - Sorting in Excel
Lesson 32 - Hide/Show Row/Columns in Excel
Lesson 33 - Auto-Formats in Excel
Lesson 34 - Creating a Basic Excel Spreadsheet
Lesson 35 - Excel Charting Lesson: The Basic Excel Spreadsheet

See also:

Lesson 37 - Excel IF Formula Nesting
Lesson 38 - Excel Function Now/Today Formulas

 

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

 

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

 

 


Gallery



stars (0 Reviews)