How to Protect Cells in Excel - Lock, Edit Ranges, Secure Workbook
Microsoft Excel

QUICK TIP: HOW TO PROTECT CELLS IN EXCEL

After spending hours preparing a complex spreadsheet, the last thing you want is your formulas or layout tampered with. Accidents can happen, particularly when there are many fingers in the pie.

Stay confident by learning how to protect cells in Excel:

  • Lock cells
  • Allow edit ranges
  • Set up passwords
  • Protect entire workbooks

How to Protect Cells in Excel

Lock Cells

1. Select the entire worksheet.

2. Home tab → Format Cells (via 1. font group, 2. alignment group or 3. Format drop-down list).

Format Cells

3. Go to Protection tab → untick ‘Locked’ → Ok.

Protection Tab - untick locked

4. Select the cells you want to lock.

5. Go back to Format Cells/Protection tab and tick ‘Locked.’

6. Go to Review tab → Protect Sheet.

7. You’re given a variety of restriction options (or you can stick with pre-selected 2).

Protect sheet

8. There is an option to create a password → Create password and confirm.

9. If anyone tries to alter the locked cells, they will be prompted with the message below. The other cells will still be accessible to edit.

Protected cell warning

To remove: go to Review tab → Unprotect Sheet.

Password options: if you don’t want a password, just leave it blank and press Ok. This means anyone can edit the cells by pressing ‘Unprotect Sheet.’

Allow Edit Ranges

This allows a user to unlock certain cells on a protected worksheet. Rather than unprotecting the entire worksheet, the user can alter specific cells using a password.

1. Firstly, make sure your worksheet is currently unprotected (check via ‘Unprotect Sheet’ button).

2. Select the range of cells you want to allow for editing.

3. Go to Review tab → Allow Edit Ranges.

4. Your selected range should appear in the white box. Otherwise, press ‘New’, click on the ‘Refers to Cells’ box and select your cells on the worksheet. The fixed formula should appear in this box.

Allow Edit Ranges

5. You can create a password, or leave blank to allow anyone to edit this range.

6. You can give some users permission to edit without a password by clicking the ‘Permissions’ button and adding their username (optional).

7. To add more ranges, repeat steps 4 and 5.

8. Click the ‘Protect sheet’ button → Ok.

Tip: you can also alter passwords in this box via the ‘Modify’ button.

Protect Entire Workbook

To protect the workbook structure:

1. Go to Review tab → Protect Workbook.

2. You’re given the option to set a password, leave blank if you don’t want to → Ok.

Protect Workbook password option

3. If you right-click on the worksheet tab, the options to modify (insert, delete, rename) are now inaccessible.

>>Click here to learn more about Excel tabs<<

You can also confirm protection status in the Review tab (‘Protect Workbook’ is highlighted). Or in File/Info (‘Protect Workbook’ is highlighted).

Protect Workbook activated

Password Protect Entire Workbook

1. File → Save As.

2. Type in a file name.

3. Click on ‘More Options’ → Tools → General Options.

Save As Tools

General Options

4. Create a file-sharing password and confirm. There’s also the option to recommend ‘Read Only’ → Save.

5. Users will be prompted for the password every time the file is opened. If you select ‘Read Only’ option, it will encourage users to open in that mode unless they need to make changes.

Protect Your Work

It’s worth it to take a few minutes to learn these functions. Protecting your spreadsheets can save you potential frustration and hours of rework from accidental edits.

Please follow, like, comment or share

Advertisements

2 thoughts on “QUICK TIP: HOW TO PROTECT CELLS IN EXCEL”

  1. Hi CJ, thanks for this post. I already knew how to protect a workbook but I didn’t know it was possible for individual cells and worksheets. I like the extra layer of security so I’ll be using this.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s