Forum Discussion

Ramandeep Singh's avatar
Ramandeep Singh
Copper Contributor
May 05, 2018
Solved

Filter in a protected xlsx sheet.

I have created a protected sheet where there is a table and several columns have a read only data. The user can only fill data in a few of columns. I want to enable filtering in this sheet but that o...
Excel on Mac
  • Haytham Amairah's avatar
    Haytham Amairah
    May 05, 2018

    Hi,

     

    Please try to enable the filter before you protect the worksheet.

    And when you go to protect it, check the Filter check box to allow users to use it.

     

    Elements available for protection within a sheet

     

     

    It works in Excel for Windows, and I think it will work on Mac as well.

     

    Good luck

Haytham Amairah's avatar
Haytham Amairah
Silver Contributor
Jul 30, 2019

wlmexcelfun

 

I just discover a fairly good solution for this.

  • keep all cells in the locked state which is the default
  • Enable the AutoFilter
  • Before you enable the protection, go to Review >> Protect >> Allow Edit Ranges
  • Add the range that you want as the below screenshot
  • Press the Protect Sheet button in the same dialog

 

  • In the Protect Sheet, deselect the first two options and select only: Sort and Use AutoFilter
  • Hit OK

 

This will prevent the users from accidentally delete any cell's content by preventing them from even select the cells but they will still be allowed to use the Sort and Filter!

 

Hope that helps

Resources