Microsoft Excel 2010 - Level 3
© Watsonia Publishing Page 53 Protecting Data
CHAPTER 5 PROTECTING DATA
Data in worksheets can be protected, for instance, to prevent
accidental erasure or modification of key values or complex
formulas, or even to prevent users from entering data anywhere
else in the worksheet or workbook other than specific input cells.
Protection is most often applied to worksheets that are used
frequently or by multiple users, such as timesheets or expense
reports.
In this session you will:
understand data protection
learn how to provide access to cells
learn how to protect a worksheet
learn how to work with a protected worksheet
learn how to disable worksheet protection
learn how to provide restricted access to cells with
passwords
learn how to password protect a workbook
learn how to open a password protected workbook
learn how to remove passwords from a workbook.
INFOCUS
WPL_E835
Microsoft Excel 2010 - Level 3
© Watsonia Publishing Page 54 Protecting Data
UNDERSTANDING DATA PROTECTION
1
All cells in an Excel worksheet are, by default,
locked. However the worksheet is also
unprotected by default and so the cells remain
accessible. By unlocking cells and then applying
protection to a worksheet, you are able to limit a
user’s access to only the unlocked cells. You can
also apply passwords when protecting a worksheet
or a workbook.
Unlocking Cells
All cells in an Excel worksheet are locked.
Prior to applying protection to the
worksheet, these cells can still be edited.
Once protection is applied, the cells will
become locked. You have the option of
unlocking some cells prior to applying
protection, so that these cells will remain
accessible to, and able to be edited by,
users. To indicate whether a cell will be
locked or unlocked, you simply select an
option in the Format Cells dialog box.
2
Protecting a Worksheet
Having decided which cells are to be locked or
unlocked, you then need to protect the worksheet.
You have the ability to apply a password to further
protect a worksheet and this also enables you to
provide restricted access to selected users. When
you are protecting a worksheet, you are able to
select from a range of actions that users can
access, such as formatting, deleting or inserting
rows and the like. By ticking the options, you
enable the users to apply these features.
Protecting a Workbook
Alternatively you can apply passwords to protect
the workbook as a whole. Via the Save As dialog
box, you can access password options to further
restrict user access. There are two passwords
that you can apply: one for opening the workbook
and one for modifying the workbook. If the user
has the open password but not the modify
password, the workbook will open as Read-Only.
You can also set workbook protection, using
options on the Review tab of the ribbon.
3
Microsoft Excel 2010 - Level 3
© Watsonia Publishing Page 55 Protecting Data
PROVIDING TOTAL ACCESS TO CELLS
Try This Yourself:
Open
File
Before starting this exercise
you MUST open the file E835
Protection_1.xlsx...
Click on cell F6
This cell contains a formula
that references data in cells E6
and C6. We want to be able to
access the data in these two
columns...
Select the range C6:C22
On the Home tab of the
Ribbon, click on Format
in the Cells group, then select
Format Cells to display the
Format Cells dialog box
Click on the Protection tab
Notice that the Locked setting
is ticked...
Click on Locked until it
appears without a tick, then
click on [OK]
Nothing appears to have
happened, but these cells will
now be accessible even if you
protect the worksheet...
Repeat steps 2 to 5 for the
range E6:E22
For Your Reference…
To unlock cells:
1. Select the range that you want unlocked
2. On the Home tab, click on Format and
select Format Cells
3. Click on the Protection tab, then click on
Locked to remove the tick and click on [OK]
Handy to Know…
You can unlock several non-contiguous
ranges of cells simultaneously by selecting
them while holding down .
Alternatively, to change the lock/unlock
nature of cells, select the cell(s) then click on
Format on the Home tab and click on
Lock Cell.
1
4
All cells in an Excel worksheet are, by default,
locked. However, the worksheet is also by
default, unprotected and so the cells remain
accessible. It sounds a little back to front, but the
first task in protecting cells is to unlock the ones
that you want the user to be able to change. Then,
when the worksheet is protected, you will have
total access to only the unlocked cells.
Microsoft Excel 2010 - Level 3
© Watsonia Publishing Page 56 Protecting Data
PROTECTING A WORKSHEET
Try This Yourself:
Same
File
Continue using the previous
file with this exercise, or
open the file E835
Protection_2.xlsx...
On the Home tab of the
Ribbon, click on Format
in the Cells group, then
select Protect Sheet to
display the Protect Sheet
dialog box
Spend a moment scrolling
through the list of options
that you can allow or
disallow a user access to
Click on Select locked
cells until it appears without
a tick
Click on [OK]
No obvious changes will
occur on the screen,
although you might notice
that more Ribbon options
are unable to be selected,
than usual
For Your Reference…
To protect a worksheet:
1. On the Home tab, click on Format then
select Protect Sheet
2. Select the options you wish to allow (tick) or
disallow (no tick)
3. Click on [OK]
Handy to Know…
You can control 15 different aspects of
worksheet editing, such as inserting and
deleting rows and columns, sorting and
formatting cells. For users providing
workbooks for data entry, these features are
particularly useful.
1
3
Once the relevant cells have been unlocked (if
necessary), you can then apply worksheet or
workbook protection. Worksheet protection
applies only to the worksheet that is active. Once
the protection is applied, you can only change the
cells that have been previously unlocked. However,
formulas in locked cells will update if you change
the data upon which they are dependent.
Microsoft Excel 2010 - Level 3
© Watsonia Publishing Page 57 Protecting Data
WORKING WITH A PROTECTED WORKSHEET
Try This Yourself:
Same
File
Continue using the previous
file with this exercise, or
open the file E835
Protection_3.xlsx...
Click on B6
Excel will not allow you to
click on a locked cell of a
protected worksheet.
Let’s try an unlocked cell...
Click on C10
As you can see, the cell is
selected and the value of the
cell is displayed in the
formula bar...
Type 90000 and press
to change the value
This time the edit is allowed,
as it was made to a cell that
was unlocked prior to
protecting the worksheet. In
addition, cells F10, C23 and
F23 will be updated even
though they are locked, as
they contain formulas that
reference the edited cell
For Your Reference…
To work with a protected worksheet:
1. Click on the unlocked cell that you want to
change
2. Type the new data
3. Press
Handy to Know…
Keep in mind that reversing the locked
nature of the worksheet is not rocket science
– most users would be able to locate the
option and remove this level of protection.
Password protection offers a tighter level of
security.
3
Protected worksheets work slightly differently to
your average unprotected worksheet. While you
are used to having full control with most
worksheets, protected worksheets will ignore
your requests to make modifications unless you are
trying to enter data in unprotected (that is,
unlocked) cells.
When you click on B6 (which is locked) the active cell
(which is unlocked) does not change…
1
Microsoft Excel 2010 - Level 3
© Watsonia Publishing Page 58 Protecting Data
DISABLING WORKSHEET PROTECTION
Try This Yourself:
Same
File
Continue using the previous
file with this exercise, or open
the file E835
Protection_4.xlsx...
On the Home tab of the
Ribbon, click on Format
in the Cells group to display
the menu
As the worksheet is
protected, the Protect Sheet
option has changed to
Unprotect Sheet...
Select Unprotect Sheet
You will now be able to
modify any cell in the
worksheet once more. Notice
also that all of the options on
the ribbon are available again
For Your Reference…
To unprotect a worksheet:
1. On the Home tab, click on Format in
the Cells group
2. Select Unprotect Sheet
Handy to Know…
Remember that protection applies to each
worksheet individually. If you don’t see the
correct command in the Format menu, check
that you have the correct worksheet
selected.
1
2
Worksheet protection is easily removed, which
can seem to defeat the purpose of applying it in
the first place. However, it is still useful for
avoiding the inadvertent modification of cell data,
especially for yourself or less experienced users.
To disable worksheet protection that is not
password protected, you use the same command
as the one you used to apply it.
Microsoft Excel 2010 - Level 3
© Watsonia Publishing Page 59 Protecting Data
PROVIDING RESTRICTED ACCESS TO CELLS
Try This Yourself:
Same
File
Continue using the previous file
with this exercise, or open the
file E835 Protection_5.xlsx...
On the Home tab of the
Ribbon, click on Format ,
then select Protect Sheet to
display the Protect Sheet
dialog box
Click in Password to
unprotect sheet and type
protect
Your password will be disguised
as a series of dots...
Click on [OK] to display the
Confirm Password dialog box
Type protect again and click on
[OK]
The sheet is protected again
and some of the Ribbon options
are unavailable. Let’s try to
unprotect the sheet...
On the Home tab, click on
Format then select
Unprotect Sheet
The password is required to
unprotect the sheet...
Type protect and click on [OK]
Protection is removed and you
can now edit any cell
For Your Reference…
To password protect a worksheet:
1. On the Home tab, click on Format then
select Protect Sheet
2. Type a Password and click on [OK]
3. Retype the password and click on [OK]
Handy to Know…
You can also apply workbook-level
passwords, but be very careful to create
passwords that you can recall, because
without them you will not be able to access
the workbook again!
2
3
If you protect a worksheet without applying a
password, anyone with a working knowledge of
the protection process can simply disable the
protection. They then have access to modify or
delete the data and/or the formulas. However,
some users might need access to the data to edit it,
so to provide access to restricted users, you can
apply a password.
5
Microsoft Excel 2010 - Level 3
© Watsonia Publishing Page 60 Protecting Data
PASSWORD PROTECTING A WORKBOOK
Try This Yourself:
Same
File
Continue using the previous file
with this exercise, or open the file
E835 Protection_6.xlsx...
On the Review tab of the Ribbon,
click on Protect Workbook in
the Changes group, to display the
Protect Structure and Windows
dialog box
Ensure that both Structure and
Windows appear with ticks
Click in Password and type
Elements, then click on [OK]
You will be asked to confirm the
password...
Type Elements again and click
on [OK]
Click on the File tab and select
Save As to display the Save As
dialog box
Click on and select
General Options
Type Open as the Password to
open, press and type Edit as
the Password to modify
Click on [OK] and type Open,
then click on [OK] and type Edit
to reconfirm the passwords
Click on [OK], click on [Save]
then click on [Yes] to replace the
existing file
For Your Reference…
To password protect a workbook against
structural and window changes:
1. On the Review tab, click on Protect
Workbook
2. Ensure that both Structure and Windows
appear with ticks, type and confirm the
Passwords and click on [OK]
For Your Reference…
To password protect a workbook against
opening or editing changes:
1. Click on the File tan, select Save As,
click on and select General
Options
2. Type and confirm passwords for Open
and/or Modify then save the workbook
2
3
There are three ways to protect a workbook using
a password. You can prevent users from
opening the workbook without a password,
prevent them from editing the workbook without
a password and you can prevent users from
inserting, unhiding or deleting worksheets without a
password. Your settings will depend entirely on
your requirements.
6
7
Microsoft Excel 2010 - Level 3
© Watsonia Publishing Page 61 Protecting Data
OPENING A PASSWORD PROTECTED WORKBOOK
Try This Yourself:
Before starting this exercise you
MUST ensure that Excel has
started and that all workbooks
are closed...
Click on the File tab of the
Ribbon and select Open to
display the Open dialog box
In the course files folder, locate
and click on
E835 Protection_7.xlsx, then
click on [Open]
You will be prompted for a
password to open the workbook...
Type Open and click on [OK]
You will now be prompted for a
password to edit the workbook...
Type Edit and click on [OK]
The workbook will open...
On the Home tab, click on the
drop arrow for Insert in the
Cells group, to display the menu
The menu option “Insert Sheet” is
currently unavailable. This is
because the workbook structure
is also password protected...
Click on the drop arrow for Insert
in the Cells group, to close
the menu
For Your Reference…
To open a password protected workbook:
1. Click on the File tab and select Open
2. Locate and click on the workbook and click
on [Open]
3. Type the password(s) and click on [OK]
Handy to Know…
If a user only has the Open password they
can open the workbook in read-only mode. If
they also have the Edit password, they will
be able to open and edit the workbook.
2
3
You can open password protected workbooks
as long as you know the passwords, of which
there may be two required to open a workbook.
The first password ensures that you have access
to the workbook and without it, the workbook will
remain closed. The second password can be used
to control who has edit access to a workbook and
can enable read-only access.
5
Microsoft Excel 2010 - Level 3
© Watsonia Publishing Page 62 Protecting Data
REMOVING A PASSWORD FROM A WORKBOOK
Try This Yourself:
Same
File
Continue using the previous file
with this exercise, or open the file
E835 Protection_8.xlsx with the
passwords Open and Edit...
Click on the Review tab of the
Ribbon, then click on Protect
Workbook in the Changes
group, to display the Unprotect
Workbook dialog box
Type Elements in Password and
click on [OK]
Click on the Home tab, then click
on the bottom half of Insert in
the Cells group, to display the
menu
Notice that Insert Sheet is
available again. The password
has been removed as long as you
save the workbook...
Press to hide the menu
Click on the File tab, select Save
As, then click on and
select General Options to display
the Save Options dialog box
Press to delete the password
for open then select the password
for modify and press
Click on [OK] then click on [Save]
and click on [Yes]to replace the
existing file
For Your Reference…
To remove a password for structural and window
changes:
1. Click on the Review tab then click on
Protect Workbook
2. Type the password and click on [OK]
For Your Reference…
To remove a password for opening or editing:
1. Click on the File tab, select Save As then
click on and select General
Options
2. Delete the passwords and click on [OK]
3. Click on [Save]
1
Passwords can be removed from workbooks
as long as you know what the passwords are to
begin with. You can protect workbooks using
passwords in two ways. You can prevent them
from being opened and/or being modified and you
can prevent changes to the structure and window
size. Therefore you must use two different
commands to remove passwords.
5
6
3
