Protect a Sheet but Still Allow Sorting
Protecting a sheet locks everything, including the sorting people need. The protection dialog lets you allow sorting and filtering while keeping cells locked, but the option is easy to miss.
TL;DR / Quick Fix
Quick fix: unprotect the sheet, then protect it again and check the allow sorting and allow filtering boxes in the dialog. Test sorting as a viewer would, and confirm the cells stay locked and test it as a viewer would afterwards.
Follow these steps
- Unprotect the sheet first. Turn off the current protection so you can redo it with the right options. You cannot add permissions to an active protection.
- Open protect sheet again. Choose protect sheet from the review or data menu. The dialog lists everything users may still do while the sheet is protected.
- Check allow sorting. Tick the box that permits sorting. This is the option that keeps the useful action available while cells stay locked.
- Check allow filtering too. Tick the filtering box as well if people filter the data. Sorting and filtering usually go together in real use.
- Set a password you will remember. Add a password and store it somewhere safe. A forgotten sheet password locks you out of your own protection.
- Test as a viewer. Try sorting a column and editing a cell. Sorting should work and editing should be blocked, which proves the setup is right.
What NOT to do
- Do not protect without checking the allow boxes. The defaults lock sorting too, which is the exact problem you are fixing.
- Do not use a password you will forget. A lost password turns your protection into a lockout, so store it safely.
- Do not assume protection is security. Sheet protection stops accidents, not determined people, so treat it as a guardrail.
- Do not leave the sheet unprotected while you think. Redo the protection right away, since an unprotected shared sheet invites accidents.
Frequently asked questions
Can a protected sheet still be sorted?
Yes, when you check the allow sorting box in the protection dialog before protecting.
What does sheet protection actually stop?
Accidental edits to locked cells. It is a guardrail against mistakes, not strong security.
What if I forget the sheet password?
You can be locked out of unprotecting it, so store the password somewhere safe from the start.