Excel tips: Avoid mistakes with protect sheet

Have you ever been working on a spreadsheet and got distracted… and before you know it, you’ve overwritten all of your clever formulas? Or, someone else accesses your file and overwrites your formula’s with values?

Well, this is exactly where protect sheet can help, and I use it constantly to protect me from myself.

What is protect sheet?

This function can be used to protect a single worksheet, so that it cannot be edited or amended.

How to protect sheet?

From the ‘review’ ribbon, select ‘protect sheet’:

This box will appear:

You can simply press ‘OK’ here and the sheet will be Protected with all of the default settings.

The first tick box above explains what this means: The worksheet and the contents of the locked cells will be protected i.e. the user will be unable to edit anything on the entire sheet, unless specific cells have been Unlocked.

How to tell if a sheet is protected?

There are a couple of ways to tell if the sheet has been protected:

• The easiest way is to see that the icon in the ‘review’ ribbon has changed from ‘protect sheet’ to ‘unprotect sheet’:

• Lots of icons on other ribbons may be greyed out (depending what other options have been allowed (see later)).

• Right click on the sheet name and ‘Unprotect Sheet’ will appear on the menu instead of ‘protect sheet’:

• If you try to edit a locked cell while the sheet is protected, this error message will appear:

Locking & Unlocking Cells

By default every single cell in a worksheet is ‘locked’, therefore when you ‘protect sheet’, ALL cells will be locked for editing.

However you may want, or need, to be able to edit some of the cells while protecting others. In this instance, you can select the specific cells that you want to be able to edit, and ‘unlock’ them:

• Select cells to unlock
• Right click mouse
• Select ‘format cells’
• Select ‘protection’ tab
• Untick ‘locked’ box
• Click ok

Now, the selected cells will be editable, once the sheet has been protected.

There is no limit to how many cells can be unlocked.

Key tip: I usually change the colour of cells that have been unlocked, as a visual guide to the cells that can and cannot be edited.

Hidden cells

When the sheet is protected, the user will still be able to see the contents of cells (values, text or formulas), unless the cells have been ‘hidden’. This can be really useful if you want to prevent a user from seeing any formulas:

• Select cells to hide contents
• Right click mouse
• Select ‘format cells’
• Select ‘protection’ tab

• Tick ‘hidden’ box
• Click ok

Now, the contents of the selected cells will not be visible in the formula bar, once the sheet has been protected.

Key tip: Locked & hidden can be used independently of each other.

User permissions in a worksheet

On the ‘protect sheet’ menu, the top two items are selected by default. This means that when the sheet is protected, the only things the user will be able to do is select locked & unlocked cells:

The user can edit this list by ticking (or unticking) the relevant boxes. This will enable the user to do more, whilst the sheet is protected.

Any items not ticked, will be greyed out within the ribbons.

Some of the most useful items that I always use are:

• Format Columns – This allows the user to amend column widths, and hide/unhide columns.
• Insert Hyperlinks – This allows the user to insert hyperlinks.
• Use Autofilter – This allows the user to apply filters.

Key tip: There is a bug with the ‘sort’ box, and even when this is ticked, the user will still not be able to use this function.

Password Protect

Sheets can be protected with or without a password:

Without Password

Simply leave the Password box empty, and click ok:

To ‘unprotect’ the sheet, simply click on ‘unprotect sheet’ in the ‘review’ ribbon and the protection will be removed.

Key tip: I tend to leave the password off if it’s only me working on a spreadsheet, so it’s quicker and easier to remove if required.

With Password

Enter a Password into this box:

When creating passwords, best practice dictates they:

• are case sensitive
• can be as long or short as required
• can include characters (such as / – #@ etc)
• can include numbers.

Once the password has been entered, the user will need to confirm it (obviously this needs to match the original entry), then click ok:

To ‘unprotect’ the sheet, simply click on ‘unprotect sheet’ in the ‘review’ ribbon and enter the password:

Key tip: If I’m sending the spreadsheet to someone and I don’t want them to be able to remove it, I would always use a password.

Recovering Passwords

There is no official way in Excel to recover a password, so please ensure you use something memorable, so it’s not forgotten.

Once a spreadsheet has been unprotected, excel will not remember that password, so a new one can be used the next time the sheet is protected, or it can be protected without a password if required.

It’s possible to remove a password from a spreadsheet using VBA coding language, but this should be used as a last resort.

Security

I always urge people to treat the spreadsheet protection as a tool to prevent data from being accidentally overwritten, and not a method for keeping confidential information hidden.

If somebody wanted to get the information enough, they could remove the passwords using the VBA as described above. If information is sensitive and not to be shared then, I’d always advise it should not be kept in a spreadsheet that will be shared.

Read more:

• Excel tips: Save time with named ranges
• How to amend a CSV file without a direct bank feed
• Excel tips: Vlookup vs Index/Match

Browse the full range of AAT study support resources.

Traci Williams is a self confessed ‘Spreadsheet Geek’ and has spent more than half of her life working with Excel Spreadsheets. She launched her own Excel training business, www.excelace.co.uk, and has since trained over 500 Delegates, and worked with over 200 businesses.