Advanced Filters in Excel

Question

Hi - we have an advanced filter in place on a shared Excel file - no idea how it got there but we can't get rid of it - and that means our standard filters are not filtering the entire data set. The 'clear' button is greyed out and if I click on the Advanced tab it is displaying list range:&nbsp;$A$1:$U$1. We just want to disable this and ideally prevent it ever being reapplied...

nowshadahmed · Answer

Could you share any visual or file?

martinleg · Answer

Thanks Nowshad - I don't seem able to paste a screenshot on this board but have saved into a Word doc here: https://researchconnection.sharepoint.com/:w:/s/ResearchConnections/EciBLNeJCQ5Gg3NuDfwU5jsBgyUiPLw9DAW4Kk4OGOTBow?e=cedYMo

nowshadahmed · Answer

I could be wrong but I am guessing your data is plain and no filter has been applied, hence the greyed 'clear' button.
Select the headers and click on the 'Filter' button next to 'clear' button. Your standard filters will be back.
Alternately, you can apply excel table format on your data. If it is already formatted as Table, go to design tab (shows when you click a data in the table) and check Filter Button check box.

The range $A$1:$U$1 when clicking on Advanced filter is the data range excel sees, although your screenshots show data till P.

martinleg · Answer

NowshadAhmed&nbsp;Thanks for the follow up and yes, we have had this with standard filter on - but found that it was not filtering all the data in the dataset. Is it not possible to remove the Advanced filter altogether? It doesn't show in any other spreadsheet...

nowshadahmed · Answer

MartinLeG&nbsp;Assume this is your data:Note that the clear option is greyed off. I have no filter or formatting applied here. Now, if I click on Advanced Filter, this is what I get. Presumably your case. Just ignore this part and follow down.To make standard filters appear, do the following:Option 1. Select the header and click on the Filter buttonOption 2: Select the whole data and convert it to excel table. If an option shows up, select My Table has headers.&nbsp;For either methods you can click on the arrows buttons to select your filter option.&nbsp;Customise your table here. If your data is already part of a table, check 'Filter Button' option.&nbsp;&nbsp;&nbsp;

Forum Discussion

Advanced Filters in Excel

Share

Resources