Forum Discussion

Ordenman's avatar
Ordenman
Copper Contributor
Jul 02, 2025

Filter Button Greyed Out on Protected Sheet Despite Filtering Enabled

 

Hi,

I'm working on a shared Excel workbook that includes data entry areas on the left and a protected table on the right which uses the data from the left to perform calculations. Before protecting each worksheet, I enable filters on the header row (Row 2) using AutoFilter and ensure filtering and sorting is allowed in the protection settings. However despite this the filter button in Data>Sort&Filter>Filter is greyed out. 

The problem:

  • The filter dropdown arrows work fine, and I can filter columns manually.
  • But the "Sort & Filter" button on the ribbon stays greyed out, so users can't use it to clear all filters at once.

This is despite confirming that:

  • The header row is fully unlocked, even in the protected section.
  • Filters are applied and visible before protecting.
  • Filtering and sorting are allowed in protection settings.

I have also  tried:

  • deleting the first row, leaving the headers as the first row
  • enabling autofilter and sorting with an additional right click menu introduced by a vba macro

Environment

Excel for Microsoft 365 (desktop)
File stored in Sharepoint with AutoSave On
Multiple users may be editing at once

The task at hand is to have the filter button clickable when protections are applied.

Any help would be appreciated.

Kind regards,

O.

excel
Macros and VBA
office 365

1 Reply

Sort By
  • Kidd_Ip's avatar
    Kidd_Ip
    MVP
    Jul 03, 2025

    One of the options can be using VBA to Add a custom button:

     

    Sub ClearAllFilters()
    Dim ws As Worksheet
    Set ws = ActiveSheet

    If ws.AutoFilterMode Then
        ws.ShowAllData
    End If
End Sub

     

Resources