I was working on the problem of protecting a worksheet but also using the autofilter and happened upon an interesting Excel quirk. I went through the process of protecting the worksheet but allowing the autofilter to be used. I went through the following steps and got not quite the answer I was expecting. On the Review menu:
Under the Review tab - choose Protect Sheet.
With the Use Autofilter button ticked - click OK.
The autofilter button is greyed out and if you try and use the autofilter shortcut: Ctrl Shift L
Then the following occurs when I tried to autofilter: Greyed out!!!!!!!
This is a curious and frustrating situation. You ticked the autofilter button but nothing is working?
Well what needs to happen is you need to have the Autofilter ON before the sheet gets protected. Then filtering is possible.
Not all that intuitive but once you know you know and it becomes possible.
