protect sheet but allow sorting

Question

I have a shared document that I have protected. I want to unprotect one column only as to allow other users to sort the price from smallest to largest. How can I do this?

Answer 1

Hi Krystal Hamilton,

I go through your post carefully and as per your description, you want to allow users to sort column data from smallest to largest in a protected Excel worksheet.  

To allow sorting/filter in a protected Excel sheet, please try the below following steps:

1.Select a column range you will allow users to sorting and filtering, click Data > Filter to add the Filtering icons to the headings of the range. See screenshot：

2.Then keep the selected column range selected and click Review > Allow Users to Edit Ranges.

3.In the Allow Users to Edit Ranges dialog, click New to display the New Range dialog, give the column range a name.

4.Click OK to go back to Allow Users to Edit Ranges dialog, and then click Protect Sheet. 

5. In the Protect Sheet dialog, type the password in the Password to unprotect sheet text box, and in Allow all users of this worksheet to list to check Sort and****Use AutoFilter options.

6. Click OK and retype the password in the textbox in the****Confirm Password dialog. Click OK to finish the sheet protecting. 

Then the users can sort and filter in this protected sheet.

Here is screenshot for your reference: 

For your reference article : Sorting Data on Protected Worksheets under Unlock ranges on a protected worksheet for users to edit. 

I appreciate your patience and understanding.

Best Regards

Waqas Muhammad

Answer 2

Hello, I have tried this and didn't have much luck. I have 7+ columns with filters for a document that has housing listing. I only want the team to be able to sort by column E "rent amount". When I follow those steps and allow edit ranges for just that column, the users still cannot sort the data by rent amount. 

I also tried selecting all columns, when following those directions. this allows users to sort the data, however as you may guess, they can also edit all of the cells.

Answer 3

Hi Krystal Hamilton,

Thanks for sharing updates with us. I apologize if my above provided steps did not work form your side. 

Based on my test the above steps should work . I will show you my test result screenshot and you may refer my below screenshot and I hope it will help you to allow sorting/filter in a protected Excel sheet.

In below screenshot you can see I can only allow one "column B" to sort data from small to larger(even not edit cell in sort column B, just user can sort/filter data only) and other column data(column A and column C) users are unable to do anything. 

Step1:

After select a column range you will allow users to sorting and filtering and click Data > Filter to add the Filtering icons to the headings of the range then right-click on column>Format cell>protection and click on locked the cell(by default is locked). See image below.

Step2:

Step3:

Step4:

Step5:

Result: 

Meanwhile, I share my test workbook via PM, please access Private Message to get workbook for your reference. 

Moreover, if still you are not able to sorting/filter data column in a protected Excel sheet and if it is convenient for you, could you please send the Excel file to me so that I can take a look from my side and help you to fix it for you .?

To protect your privacy you can send it to me in Private Message

I appreciate your patience and understanding.

Best Regards

Waqas Muhammad

Answer 4

I have a shared document that I have protected. I want to unprotect one column only as to allow other users to sort the price from smallest to largest. How can I do this?

Hi Krystal

1. The best way to protect your data is to save it as a csv file.

2. Do a power query against that csv file into a new worksheet
3. Do all the formatting, formulas and other things
4. Share that new query worksheet with other users in an unprotected state, and it gives your users latitude to do whatever they need to do without you having to worry about your data being corrupted by someone else.

Answer 5

Hi Krystal Hamilton,

Feel free to post back if you need further assistance.

Best Regards,

Waqas Muhammad