Putting Excel into Lock Down

Locking involves a format

There are a few lock downs in place around Australia at the moment, so let’s look at putting Excel into lock down.

Locking or adding protection in Excel is a bit backwards. The default setting for every cell in a sheet is locked. This means that when you apply protection to a sheet through the Review tab that you can’t change any cell.

To enable people to make changes to certain cells you need to unlock those cells before you apply protection to the sheet.

The other unusual thing about protection in Excel is that you use a format to lock and unlock cells.

To unlock a cell first select it and then press Ctrl + 1. This opens the Format Cells dialog. Click the Protection tab and uncheck the Locked option and click OK.

The cell will now be editable once you apply protection.

When you apply protection you do not have to specify a password.

By omitting the password you limit people to changing just the unlocked cells but you don’t stop them from unprotecting the sheet and making other changes.

If you enter a password please make sure you write it down separately because it is difficult to recover or reset the password.

There is a useful icon to add to your Quick Access Toolbar. It is the Lock Cell icon. This makes it easier to lock and unlock cells and it also shows you if a cell is locked. Below is a picture of the lock cell icon.

To add this to the Quick Access Toolbar right click the toolbar which is either above or below the ribbon. It might just be in the top right corner of the Excel screen. Choose the option Customize  Quick Access Toolbar.

In the top left drop-down on the left select All Commands. Scroll down about halfway. The icons are in alphabetic order. Select the Lock Cell icon and click the Add button. See image below.

This icon changes slightly based on whether the selected cell is locked or not. See the images below.

There is also a keyboard shortcut to lock or unlock a cell/range it is Alt H O L pressed in sequence, not held down.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.