Protect Excel cells and sheets

How to protect excel cells and sheets to avoid formulas being overwritten.

• 3:14
• Skill Level: 5
• What's my skill level
  

  All of our videos are listed by user Excel Skill Level.

  To find out your personal skill level, download our quick assessment.

  Once you've completed the test, send it to us and we'll evaluate for you.

  

• 

Protect Excel cells and sheets

In Excel it is possible to protect excel cells against being overwritten or changed. This is particularly important if your spreadsheet has inputs that can change, but formula that should not, and you want to stop people accessing them and making changes.

In this example we have two sections here, the light blue section indicates that these are input areas where it is possible for users to make changes, and here we have formulas which we’d like to control access to, so that users cannot change them.

• If you click on Tools
• you will note that there’s a PROTECTION option
• and when you click Protect Sheet,
• this will pop up,
• if you want you can put a password in so that you can control who can or cannot unprotect a sheet,
• and then you have a number of options to decide how much control you want over the spreadsheets,
• if we just ignore this we leave the password out
• and we say ok,
• what you’ll notice now is if you click on a cell lets stay that one there
• and try and type it’ll clearly tell you that that sheet is protected,

It is very important to understand this, that in Excel when you protect a sheet Excel assumes that every single cell will be protected, unless you tell it otherwise.

So in order to differentiate between input cells that can be overwritten, and formula cells that need to be protected we need to do the following, but first let us unprotect the sheet

• so we go to Tools
• PROTECTION,
• Unprotect Sheet
• And if you had a password set up it would’ve asked you for a password first.
• Click on the cell or cells you want to protect
• click on Format
• and the Cells option
• and you’ll see you have a number of options and right at the end is the PROTECTION option, if you click there,
• you’ll see that it has an item called Locked that is ticked off
• what this is telling you is that this cell, if this sheet is protected will be locked
• what you need to do is unprotect it, so you click it off
• and you say Ok
• and similarly you do it with the entire section
• go Format
• Cells
• PROTECTION
• and you switch that off
• and you say Ok

Now that you have told Excel which cells need to be unprotected you can now

• go to your Tools
• PROTECTION
• Protect Sheet
• you can put your password in if you want
• and when you say Ok
• You’ll discover that it is possible to change these numbers, but you cannot change the formula or any other cell for that matter.