Filters on a protected SharePoint file

%3CLINGO-SUB%20id%3D%22lingo-sub-2073718%22%20slang%3D%22en-US%22%3EFilters%20on%20a%20protected%20SharePoint%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2073718%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20have%20a%20excel%20sheet%20that%20all%20staff%20in%20my%20school%20access%20to%20track%20attendance.%20It%20is%20protected%20so%20that%20staff%20can%20only%20change%20the%20cells%20i%20want%20them%20to%20change.%20It%20has%20a%20filter%20to%20allow%20staff%20to%20select%20their%20teaching%20group.%20However%2C%20someone%20keeps%20removing%20the%20filter%2C%20and%20because%20it%20is%20protected%20it%20can%3Bt%20be%20re-added%20whilst%20using%20SharePoint.%3C%2FP%3E%3CP%3ETwo%20questions%20really%3A%3C%2FP%3E%3CP%3E1%20-%20Is%20there%20a%20way%20to%20unlock%20the%20sheet%20whilst%20it%20is%20on%20SharePoint%3F%3C%2FP%3E%3CP%3E2%20-%20How%20can%20i%20stop%20people%20going%20to%20the%20data%20tab%20and%20removing%20the%20filters%3F%20(I%20have%20given%20clear%20instructions%20to%20staff%20on%20how%20to%20use%20it%20and%20what%20not%20to%20press)%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3ENiall%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2073718%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2074193%22%20slang%3D%22en-US%22%3ERe%3A%20Filters%20on%20a%20protected%20SharePoint%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2074193%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F934325%22%20target%3D%22_blank%22%3E%40NiallMegaw%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThere's%20no%20way%20to%20unprotect%20the%20sheet%20while%20in%20the%20online%20version%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOne%20thing%20to%20consider%20is%20educating%20folks%20in%20the%20use%20of%20Sheet%20View%2C%20which%20allows%20individuals%20to%20select%20and%20save%20a%20filtered%20view%20without%20impacting%20others.%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20803px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F247424iD862EE6A37DA30BE%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOr%20if%20you%20can%20put%20your%20data%20in%20a%20Table%20(Ctrl%20T)%20then%20you%20could%20add%20a%20Slicer%20(enabling%20Autofilter%20and%20Objects)%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F247429i84E88374C96FD2C9%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi,

I have a excel sheet that all staff in my school access to track attendance. It is protected so that staff can only change the cells i want them to change. It has a filter to allow staff to select their teaching group. However, someone keeps removing the filter, and because it is protected it can;t be re-added whilst using SharePoint.

Two questions really:

1 - Is there a way to unlock the sheet whilst it is on SharePoint?

2 - How can i stop people going to the data tab and removing the filters? (I have given clear instructions to staff on how to use it and what not to press) 

Thanks,

Niall 

3 Replies

Hi @NiallMegaw 

 

There's no way to unprotect the sheet while in the online version

 

One thing to consider is educating folks in the use of Sheet View, which allows individuals to select and save a filtered view without impacting others.image.png

 

Or if you can put your data in a Table (Ctrl T) then you could add a Slicer (enabling Autofilter and Objects)

image.png

 

Thank you for this. I have tried various ways to educate those to use it.

 

Would a large number of users cause the filters to be removed - I'm guessing not. It is my understanding that you have to go to the Data tab in the ribbon and select filter to remove them? Is there another way that I'm not aware of?

 

@Wyn Hopkins 

Hi
The number of users shouldn't be an issue. To remove the filter option completely then yes, via the data tab.
To clear filters that can be done just by clicking on the filter icon in the column heading