<h2>Protection Ex 1</h2> <ul> <li>Find all the Input Cells and make them blue and unlock them (watch out for the overwritten formula - you need to fix it)</li> <li>Protect the sheet so that you can widen rows and columns if necessary</li> <li>Create a different password for the Inflation Rate in cell C6</li> <li>Make sure no one can enter a marketing percentage less than 10%</li> </ul> To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/advanced-excel-course/">here</a>.

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.

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.  

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.

When you lock cells you need to be particularly careful about how much you lock. If the size of the numbers within a cell may change significantly, a locked spreadsheet may not allow the user to see the numbers as they will appear as ###### and the user won't be able to resize the column or row.

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. To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/advanced-excel-course/">here</a>.

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.

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).

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. To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/advanced-excel-course/">here</a>.

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.

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.

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%. <img class="aligncenter size-full wp-image-5684" src="https://www.auditexcel.co.za/wp-content/uploads/2016/04/Different-levels-of-protecting-excel-spreadsheets-12.jpg" alt="Different levels of protecting excel spreadsheets" width="892" height="554" /> To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/advanced-excel-course/">here</a>.

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.

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. To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/advanced-excel-course/">here</a>.

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.

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.  

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). <img class="aligncenter size-full wp-image-5680" src="https://www.auditexcel.co.za/wp-content/uploads/2016/04/Different-levels-of-protecting-excel-spreadsheets-8.jpg" alt="Different levels of protecting excel spreadsheets" width="719" height="409" /> To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/advanced-excel-course/">here</a>.

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.

Protection Examples

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.

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. <img class="aligncenter size-full wp-image-9590" src="https://online-excel-training.auditexcel.co.za/wp-content/uploads/2017/09/Lock-cells-but-allow-colour-formatting-or-comments-1.png" alt="Lock cells but allow colour formatting or comments" width="1068" height="847" /> To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/advanced-excel-course/">here</a>.

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.  

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.  

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. <img class="aligncenter size-full wp-image-9595" src="https://online-excel-training.auditexcel.co.za/wp-content/uploads/2017/09/Protect-cells-but-allow-filter-pivot-table-sort-1.png" alt="Protect cells but allow filter pivot table sort" width="274" height="308" /> To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/advanced-excel-course/">here</a>.

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.