Protect a sheet from editing, but allow AutoFilter AND sorting capabilities

[sinapourazima]

Hello All,
I have a sheet that contains formulas and references to other sheets. I want to protect this sheet from editing, BUT allow all users to use AutoFilter with sorting capabilities on this sheet. I tried protecting this sheet, with the option "Allow all users of this worksheet to:", and selected "Use Autofilter". However this does not work. There is a very unclear Note about this in the Excel online help. Please guide me.
Thanks.

[prabhuduraraj09]

Hi Sinapourazima,

see you again....

Auto Filter :

1) Before protecting the work sheet, Apply Autofilter in the Range where you want users to user auto filter
2) Protect work sheet , with option "Allow all users of this worksheet to:" and by selecting option "Use Autofilter"

Give a try, the Core point here is.......you must apply Autofilter and keep it ready to use by other users before protecting.
Once you have protected the sheet. since the Auto filter is already available, User will be allowed to use the auto filter.....but they will be denied to edit data and will be denied to remove or apply auto filter.

Sorting :
When a cell is locked, not all worksheet protection properties operate as you’d expect. Four of the properties do not work when a cell is locked:

AllowSort
AllowDeleteColumns
AllowDeleteRows
AllowInsertHyperlinks
This is because in order to use these features the affected cell’s content must be changed. For example, using “Sort” does not just change the order of how the cells are viewed, it actually changes the values of the cells so that they are sorted. Due to this implementation of “Sort,” this worksheet protection property does not work when the cells are locked.

Using “Allow Users to Edit Ranges” to Allow Locked Cell Sorting (RECOMMENDED)
This solution takes advantage of how allowing users to edit ranges makes locked cells behave like unlocked cells.

Step 1: Make cells editable so that sorting will work.

Add cells we want to sort to a range and make that range editable in “Allow Users to Edit Ranges.” This allows users to edit these cells when the worksheet is protected, even if they are locked cells.

Select all the cells you would like the user to be able to sort, including their column headings.
Go to the Data tab and click Filter. An arrow should appear next to each column header.
Go to Review tab-> Allow Users to Edit Ranges
Click “New…”
Give the range a title.
“Refers to Cells” should already contain the cells you want to allowing sorting on.
If you want to allow only certain people to sort, give the range a password.
Click “OK”
Step 2: Prevent users from editing these cells

When protecting the worksheet, uncheck “Select Locked Cells” worksheet protection property. This will prevent users from editing the cells.

In the “Allow Users to Edit Ranges” dialog:
Click “Protect Sheet…”
Give the worksheet a password
Uncheck the worksheet protection property called “Select Locked Cells”
Check the “Sort” property and the “AutoFilter” properties
Click “OK”
This solution allows users to use the Auto Filter arrows in the column names or the Sort buttons in the Data tab to sort data. Another benefit is that you have the option of allowing only certain users to sort by giving the range a password. Please note that this range password is separate from the password you set to protect the sheet.


Add reputation, if my answers helping you for your questions. and mark this thread as solved , with appropriate rating

[bsacheri]

A side-effect of unchecking "Select Locked Cells" is that users cannot select locked column headings if they want to copy and paste data elsewhere. This creates an awkward user experience.

Is there a way to lock column headings and still allow users to select them, but not change them, and allow the autofilter feature to Sort that column work? Excel 2016.

Ben

[AliGW]

Administrative Note:

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread