Excel - Autofilter stopped filtering!

Latest update on November 8, 2012 at 12:36 PM by Jean-François Pillou .

Microsoft office software provides an excellent application called Microsoft Excel. Excel users can create spreadsheets and charts for tracking, organizing and analyzing data. The Excel office software application is also used for filtering and sorting data to find specific information. The Excel Autofilter (Excel Filter) allows you to view particular rows in Excel worksheet while hiding other rows in the spreadsheet. When an Excel filter is applied to the header row of a worksheet, a drop down list appears on every cell of the header row. This can be used to stipulate which rows you want to display. In case, your autofilter has stopped working then you need to remove all the filters added and recreate them with the right filter database range.

https://ccmet/faq/3865-windows-xp-home-add-the-missing-security-tab

Issue

I am working with Excel and just yesterday after entering a bunch of records to my file which has about 19,000 rows I discovered that my autofilter just stopped filtering completely. When I clicked the arrow there is no value in the dropdown list just All, Custom, and top 10. But even these options don't work either.

I have removed and reapplied the filter but the problem remains. I did Google and tried the manual calculation mode, but that didn't work for me. I even tried to copy a dozen records to a new worksheet and applied the autofilter but it showed nothing.

Solution

I have found out why my filter stopped working. After reading serveral notes about autofilter, I got this trick to check the filter area: Ctrl+G: _filterdatabase.

Removing all the filter and recreating it with the right filterdatabase range had my autofilter working again.

Type in the name of your current sheet, followed by ! and the "_filterdatabase" parameter, e.g. sheet1!_filterdatabase. Excel will highlight the area the current filter is applied to. If your filterdatabase area is wrong, select the whole sheet, remove the filter. I think you should also do a data sort first, then select the right area and reapply the autofilter. I hope this will help (it works for me).

Note that

Thanks to Lucy for this tip on the forum.