Filtering protected cells/data
I have a worksheet that contains cells with detailed formulas that I would like to protect, so that these formulas can't be edited, however I still need the data within this worksheet to be able to be filtered/sorted. Is there any way of locking certain cells, but still being able to apply filters on the data, without having to enter a password in order to rearrange/sort the data please?
RE: Filtering protected cells/data
Hi Alex,
Thank you for the forum question.
Yes—Excel does support exactly what you’re trying to do
You can lock formula cells while still allowing filtering/sorting without re-entering the password each time by using sheet protection options properly.
________________________________________
What to do
1. Unlock the cells users can interact with
By default, all cells are locked, so you must first unlock the ones users should be able to filter/sort:
1. Select the range (or entire sheet with Ctrl + A)
2. Press Ctrl + 1 → go to Protection tab
3. Uncheck “Locked” → click OK
________________________________________
2. Lock only the formula cells
Now select only your formula cells:
• You can use:
Home → Find & Select → Go To Special → Formulas
Then:
1. Press Ctrl + 1
2. Go to Protection
3. Check Locked → OK
________________________________________
3. Protect the worksheet with filter/sort enabled
Now the key step:
1. Go to Review → Protect Sheet
2. Enter a password (optional—but recommended)
3. IMPORTANT: Tick the following options:
o Select unlocked cells
o Sort
o Use AutoFilter
Then click OK.
________________________________________
Result
• Formula cells Protected
• Users can:
o Filter data
o Sort data
• Users won’t be prompted for a password just to filter/sort
• Users cannot edit formulas
________________________________________
Extra tips
• Make sure the filter is already applied (Ctrl + Shift + L) before protecting the sheet
• Tables (Ctrl + T) also work well with this setup
• If sorting doesn’t work, ensure:
o The entire dataset range is unlocked except formulas
o No partially locked columns in the sort range
________________________________________
Key idea
Protection in Excel doesn’t block actions like sorting/filtering unless you leave those options unchecked—it’s fully configurable.
________________________________________
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
