If you are worried about changes to 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 (and sometimes this is recommended when you want to hide the source information).

If you need to protect a sheet (stop people changing inputs and formula) but you want to allow them to continue to use the Sort, Filter and Pivot Table options, you need to use different settings. For the most part these options don't affect the numbers but it would be difficult for a user to work with the data if they can't use these tools on a protected sheet.

If you are worried about changes to 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 (and sometimes this is recommended when you want to hide the source information).

Everything before is focused on allowing or not allowing a user to do something in a cell. So they either can change a cell or not. But what if you want them to change the contents of a cell but limit the options e.g. they must enter a number bigger than 10%.

When you protect a sheet you typically stop users from changing your formulas. However, you can also control whether a user can see a formula on a cell. If you want the formula to be hidden you need to follow a slightly different process.

If you want to have more control over the people that can open the file and only view it versus people who can open the file AND make changes you need to add a Password to Modify.

With the previous protect sheet methods you had 2 states. Either the person knows the password and can change any cell, or they don't and they can't change the locked cells. But you can also specify a password per cell or range of cells. This way only certain people can change certain cells.

This is a trick to be able to send out one spreadsheet which contains everyone's information, but set it up so that a single person can only see their data and not the other users.

At a file level you can put a password on so that it is required before the spreadsheet is opened. So when someone wants to open the file, they first must enter the correct password. This means that only people who know the password can open/ change the file.

Protection Examples

The problem with the Protect Sheets is that it does apply some limits and some users want everything to be open EXCEPT that they don't want users to insert or delete rows and columns (this is a very common cause of spreadsheet errors or 'templates' corrupting). This is a trick to disable the insert/ delete option without protecting the sheet and no VBA.

Everything before is focused on allowing or not allowing a user to do something in a cell. So they either can change a cell or not. But what if you want them to change the contents of a cell but limit the options e.g. they must enter a number bigger than 10%.

It is common for users to send summarised reports in the format of Pivot Tables where confidential information is hidden in the source data, but it is assumed that it is not visible in the Pivot Table. However, even if you created the pivot table from information in another workbook, Excel will remember the data source. So if you want to make sure that no one can see the raw data in a Pivot Table you need to follow some steps.

If you need to protect a sheet (stop people changing inputs and formula) but you want to allow them to continue to use the Sort, Filter and Pivot Table options, you need to use different settings. For the most part these options don't affect the numbers but it would be difficult for a user to work with the data if they can't use these tools on a protected sheet.

When you are working with password protection, you often need to differentiate which cells are inputs and which are formula. You can manually do it (check every cell manually) or use some of Excel's inbuilt tools to make the process faster.

Protection Examples

You can lock cells but allow colour formatting or comments which don't affect the security of the formula but allow a user to provide some sort of feedback to you. Note that adding comments is different from formatting so it requires a different setting.

With the previous protect sheet methods you had 2 states. Either the person knows the password and can change any cell, or they don't and they can't change the locked cells. But you can also specify a password per cell or range of cells. This way only certain people can change certain cells.

We find that most problems are caused by users inserting or deleting rows and columns when this is not allowed, so we normally don't allow this option. But if you want to lock cells but allow insert or delete of columns/ rows then this is the way to do it.

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.