How to allow Sort & filter’ > ‘Clear’ in protected sheets?

Resolved · Medium Priority · Version 2016

Tue 14 Nov 2017, 08:45 Delegate Kay said...

Kay has attended:
Excel Advanced course
Excel Advanced - Formulas & Functions course

How to allow Sort & filter’ > ‘Clear’ in protected sheets?

Allow sorting and filtering in a protected sheet

I have a protected sheet however I often have to play to find the filter if someone saves the sheet whilst filtered, rather than use ‘Sort & filter’ > ‘Clear’ (as this is greyed out when protected)

I have tried to change the settings "In the Allow Users to Edit Ranges dialog, by clicking New to display the New Range dialog however this is still not working - Sort and Filter> Clear is still greyed out

I would like to protect the sheet however enable ‘Sort & filter’ > ‘Clear’. Is it possible to do this?

Appreciate your help on this

Tue 14 Nov 2017, 12:10 Trainer Richard said...

RE: How to allow Sort & filter’ > ‘Clear’ in protected sheets?

Hi Kay

Thank you for using the forum to ask a question.

Unfortunately there isn't a way to clear filters while a sheet is protected. This is a known problem with Excel. It means that you need to keep removing protection to clear all filters before protecting it again.

Kind regards

Richard Bailey
Microsoft Certified Trainer

Best 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

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Manually rotating a 3D chart

To manually rotate a 3D chart;
Select the chart (edit mode) and click on one corner of the chart display area. A selection border will appear, using the left mouse button on one of the chart's "corners" (mouse pointer changes to a cross) drag the image which becomes a line representation of the chart (as you begin to drag) and rotate the chart to a view of your choice.
If you dont get it right first time, dont worry, the manoeuver is a little bit tricky.

View all Excel hints and tips