How to protect excel cells and sheets to avoid formulas being overwritten.
- For updated video clips in structured Excel courses with practical example files, have a look at our MS Excel online training courses . You can even try the Free MS Excel tips and tricks course.
- To see if this video matches your skill level (see the suggested skill score below) do our free MS Excel skills assessment.
- If you are based in South Africa look at the live courses we offer in Johannesburg and Cape Town.
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
- 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
- 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
- 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.