Protect only certain cells/ formula from everything except input changes

Preview
Note: All Excel protection is relatively weak. It is useful to stop casual users but it is not for top secret information.

Another form of protection is to protect certain cells from accidental or purposeful changes. This protection level stops everything except allowing you to enter information into a cell. You can’t format cells, insert or delete columns or rows, use Pivot Tables or anything else. This can be added to a spreadsheet that is workbook protected or not.

To follow along go to 19.3

The trick with this is that it works backwards from what you think you are trying to do.

In your head you want to

  • “protect these cells”,

but the way Excel works, the better wording (which will make it easier for you to remember) is

  • “unprotect the other cells!”

So below we want to protect all the formula cells (the white cells) and we do this by

  • highlighting all the input cells (blue cells that must be unprotected- watch the video to understand Excel’s default status),
  • clicking on CTRL + 1 (or whichever method you use) to get the format cells options,
  • go to the Protection tab, and
  • switch off the locked option by making sure it is unticked (you are telling Excel that when the sheet is protected, these cells must NOT be locked)
Different levels of protecting excel spreadsheets
You need to change all cells to be left open to have the ‘Locked’ box unticked

This is only the first step though. At this stage nothing is protected. You need one more step. All you have told Excel is which cells will be locked or unlocked when you Protect the sheet.

You now need to actually protect the sheet and you do this by going to the Review tab and clicking the Protect Sheet button.

Different levels of protecting excel spreadsheets

It will immediately give you the following screen

Different levels of protecting excel spreadsheets

If you want to use a password, you can type it into the box, but note that you don’t have to use a password. Perhaps you just want to stop accidental errors, so you can leave this blank. If you do enter a password you will be asked to repeat it after you press OK.

The important thing is what you want to allow the users to do with the locked cells.

By default they can only select locked cells and unlocked cells.

This means that they can’t, for example:

  • change the colour of any of the cells
  • change the height or width of rows and columns
  • insert/ delete columns and rows
  • use Sort, Filter or Pivot Tables etc

This is the protection level most people use but just keep in mind who you are sending the file to. The most common reason protection levels are disabled is because the end user can’t do something simple (like increase the column width to see the numbers) and eventually everyone gets the password so they can do their job.

When you add protection levels, put yourself in the other person’s position and try and guess the actions they may need to perform that wouldn’t undermine your security.




Lesson tags: vlookup
Back to: POPIA and GDPR Compliance- Built in Excel Tools that will help > Control access to information in Excel Spreadsheets