Different levels of protecting excel spreadsheets

Microsoft allows different levels of protecting excel spreadsheets and as the versions of Excel progress the protection is becoming more resilient.

Below the various protection possibilities, how they work, and when you may want to use then.

Protect the workbook from opening or being changed

Good For- controlling who can see and/ or modify the entire spreadsheet

At a file level you can put a password on so that it is required before the spreadsheet is opened.

This means that only people who know the password can open/ change the file. You do this when you save the file, so either when you first save it, or when you do a Save As.

As shown below, there is a ‘Tools’ button and the one you want is the ‘General Options’.

Different levels of protecting excel spreadsheets
You access this via the Excel Save As screen

When you click on this you get the following options:

Different levels of protecting excel spreadsheets

They work by either entering your password into the block or ticking the box. If you enter a password it will ask you to confirm it.

The feature of each area is:

  1. If you complete the ‘Password to Open’, then the file can only be opened if the user knows the password.
  2. If you complete the ‘Password to Modify’ then the user needs to know this password to be allowed to make changes and save the file.
    1. If ‘Password to Open’ and ‘Password to Modify’ is completed then, when it is opened, the user would need to complete both to be able to modify it.
    2. If they only fill in the Password to Open then it will open as a Read only.
  3. A less secure but good option is to tick the ‘Read Only recommended’. In this case when the file is opened the person has a choice whether to open it as a Read Only or not. If they do open it as Read only they won’t be able to save over the existing file, only make a new file.

Watch the video clip on protecting a spreadsheet workbook

Protect cells within a sheet from everyone

Good For- ensuring that users only work in the allowed cells and/ or hiding the fomrula in a spreadsheet

Another form of protection is to protect certain cells from accidental or purposeful changes. This can be added to a spreadsheet that is workbook protected (as above) or any other spreadsheet.

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 certain cells, but the way Excel works, you actually need to tell it which cells to leave unprotected!

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),
  • clicking on CTRL + 1 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.

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.

The important thing is what you want to allow the users to do with the locked cells (keep in mind that as the unlocked cells are unprotected they can do most things with them).

By default they can only select locked cells and unlocked cells. However, note some of the other options. The ones we like and the reasons are:

  • Format cells, columns or rows- they can’t change the details of the cells but can make it red for example if they have a problem with them or as a note.
  • Use Autofilter, Pivot Tables & Pivot Charts (shown lower in the screen)- this allows the user to analyse a spreadsheet but not make changes

We generally don’t allow users to insert or delete cells, columns or rows so we avoid this.

Watch a video clip on how to protects cells in a spreadsheet

Protect cells within a sheet from certain people only

Good For- ensuring that users only work in the allowed cells, but allowing certain users to change some of the protected cells

The option above is great but perhaps you have certain cells that you want most people to be locked out of, but perhaps certain people should be able to access it.

So in the example below, we have decided that in row 13 we want most people to be locked out, but certain people can change the way the Selling Price per unit is calculated.

We highlight the cells and under the Review Tab, we click Allow users to Edit Ranges and then click the NEW button.

Different levels of protecting excel spreadsheets

As shown below you now can give it a name, specify more cells to include and then give it its own password.

Different levels of protecting excel spreadsheets

This is not the end, as you still need to Protect the Sheet as mentioned above and shown below.

Different levels of protecting excel spreadsheets

Now if someone clicks on any other white cell, they can’t get it. However, if they click on these cells and try to change it, they will be asked for the password. If they know it, they can change the cells, otherwise it is blocked

Stop changes to the Workbook structure (can’t move sheets around etc)

If you are worried about the structure of your workbook (the sheet names, number of sheets, the order they are in, whether they are hidden or visible etc), you can even protect this.

Under the Review tab, you can click on Protect Workbook and add a password (again a password is not required unless you really don’t want someone to make changes)

Different levels of protecting excel spreadsheets

When you do this, if you try any thing with the sheets you will notice that most of the options are greyed out and cannot be used (this spreadsheet has a sheet that is hidden but as you can see you don’t have an option to unhide it)

Different levels of protecting excel spreadsheets

Stopping the insertion of columns or rows with no password (a trick)

The most common issue we are trying to stop is not allowing users to insert or delete rows or columns. The protecting cells option shown above can do this, but sometimes you want to avoid protecting the sheet, but still want to control the rows and columns.

A way to do this is to use a simple Array Formula.

You can find out lots about Array Formula elsewhere, but for now all you need to know is that when you enter an array formula over a number of cells, you cannot break up that array by insertion of deletion.

So in the example below in column K and row 28 I have highlighted the cells, typed =1 and then held CTRL & SHIFT and pressed enter (this is the only way to enter an array formula). If you look at the cells now you will see it says  {=1}. The { shows it is an array formula.

Different levels of protecting excel spreadsheets

If I now go to row 14 and try insert a row I get a message that I cannot change a part of an array (in column K)

Different levels of protecting excel spreadsheets

You could hide this column and row now and have some level of comfort that rows and columns won’t be inserted or deleted.

Control what can be input into a cell (even if it is unlocked)

Good For- stopping finger problem and spelling errors amongst others

Everything above is focused on allowing or not allowing a user to do something. So they either can change a cell or not.

Another useful level of protection is controlling what they put into a cell and this is possible with the Data Validation feature.

So as an example, if we want to control what will be entered as a sales price in cell C4 below (remember that previously we left this cell unlocked as it is an input, but now we are going to protect ourselves from finger trouble).

We can click on the cell, and in the Data tab we click Data Validation.

You can then specify what you want to allow (we have said the user can enter a number with a decimal) but it must be between 80 and 120.

Different levels of protecting excel spreadsheets

You can even control what message must be given (Input Message tab) and what type of error should be shown (Error Alert tab) if they get it wrong.

In this case now, if you enter 85 in the cell nothing will happen and the spreadsheet will continue to work, but if you accidentally type 8.5 it will not allow the number, as it is too low.

Conclusion

By mixing and matching the various options you can get to a fair level of protection. It will never be as sophisticated and safe as a formal IT system but it is better than leaving everything open!