Allow data sorting for all users of a protected Excel Online sheet

Question

I am working on creating a shared Excel Online sheet for communicating many small projects to a contractor. I want the contractor to be able to sort data by any of the columns in the sheet, but I don't want them to be able to change/delete the cells in the same columns. I have turned 'protect sheet' on, and selected 'select locked cells', 'select unlocked cells', 'sort' and 'use autofilter' under options > allow users of this sheet to. I was thinking this should allow the contractor to sort the protected cells, but that is not the case, no one can sort the cells unless they unlock the entire sheet with the password.

I have tried setting up the data as a table, or as a range with a filter, both produced the same result. Using the desktop version of Excel instead of online also produced the same result. Any suggestions or workarounds to this?

The screenshot below shows how things are set up, and the box that pops up when anyone tries to sort data in any column.

Thank you

Answer 1

The following features are unsupported in shared workbooks:

Unsupported items:	Unsupported actions:
Creating or inserting tables	Inserting or deleting blocks of cells
Adding or changing conditional formats	Deleting worksheets
Adding or changing data validation	Merging cells or splitting merged cells
Creating or changing charts or PivotChart reports	Sorting or filtering by format
Inserting or changing pictures or other objects	Using drawing tools
Inserting or changing hyperlinks	Assigning, changing, or removing passwords
Creating, changing, or viewing scenarios	Protecting or unprotecting worksheets or the workbook
Inserting automatic subtotals	Grouping or outlining data
Creating data tables	Writing, recording, changing, viewing, or assigning macros
Creating or changing PivotTable reports	Changing or deleting array formulas
Creating or applying slicers	Adding, renaming, or deleting XML maps
Creating or modifying sparklines	Mapping cells to XML elements
Adding or changing Microsoft Excel 4 dialog sheets	Using the XML Source task pane, XML toolbar, or XML commands on the Data menu
	Using a data form to add new data
Adding threaded comments	Editing, or deleting threaded comments

It does not say you can't sort. However, if you're having a problem with sorting, you might consider the SORT or SORTBY functions. They do work in a protected sheet regardless of the Protect Sort option's settings.

Answer 2

You may use workaround in this link.

Sorting a table in a protected sheet when some cells are locked | MrExcel Message Board

1.Ensure the cells you want users not to be able to edit are locked.
2.Select the entire range you want users to be able to sort (including headers).

3.Use Review>Allow Edit Ranges to name this selected range.

4.Protect the sheet

"Select Locked Cells" _not_ checked

"Select Unlocked Cells" checked

"Sort" checked

"Use AutoFilter" checked