Search : in
By :

Excel autofilter stops filtering

Last answer on Sep 25, 2009 12:44:39 pm BST Lucy, on Apr 30, 2008 2:22:38 am BST 
 Report this message to moderators

Hello,
I am working with Excel 2003 professional and just yesterday after entering a bunch of records to my file which has about 19,000 rows I discovered that my autofilter just stoped 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 when I tried to click on them they doesn't work either.

I have removed and reapplied the filter but the problem retained. I did google and tried the manuall calculation mode, that doesn't work for me. Even I tried to copy a dozen of records to a new worksheet and applied the autofilter but it showed nothing.

Please help me guys, I cannot survive without my autofilter.
Thanks a million.
Lucy

Configuration: Windows XP
Firefox 2.0.0.14

Best answers for « Excel autofilter stops filtering » in :
Avoid duplicates in Excel ShowAvoid duplicates in Excel In order to avoid duplication when encoding in a column from an excel sheet: take the conditional format on the first cell under the headings (eg A2) choose the following formula:...
Delete duplicates in an Excel column ShowDelete duplicates in an Excel column To remove duplicates in an Excel column: Click on the Data menu Filter Advanced Filter In this menu, select the column where the duplicates Check the box "Extract without duplication"...
Start/stop a service using command line ShowStart/stop a service using command line The netsh tool (netsh.exe) can automate the startup or shutdown of a service through the following command line: To start a service: Net start name_of_service To stop a service:...
Graphic filters ShowFiltering Filtering consists in applying a transformation (called a filtre) to all or to part of a digital image by applying an operator. There are the following types of filters: Low-pass filters, which consist in attenuating image components...
Spreadsheets - The Excel Interface ShowExcel Introduction Microsoft Excel is the spreadsheet in the Microsoft Office office suite. To start it, simply click on the appropriate icon in the Start menu (in Windows) or click on an Excel file (that has an .xls file extension). A Excel...
Ask your question Reply

1

crazy_phoebe, on May 1, 2008 5:50:05 pm BST

Why are you still using a 2003 software ? [ sorry to answer with another question ]

   
Reply to crazy_phoebe

2

Lucy, on May 1, 2008 11:39:06 pm BST
  • +4

Because we don't want to buy/upgrade to 2007. It's that simple ;)

   
Reply to Lucy

3

Lucy, on May 1, 2008 11:43:40 pm BST
  • +3

Just a good news, I have found out why my filter stopped working. After reading serveral notes about autofilter, I have got this trick to check the filter area: Ctrl+G: _filterdatabase. Removing all the filter and recreate it with the right filterdatabase range had my autofilter go again.

Thanks, guys.

   
Reply to Lucy

4

kathleen, on May 7, 2008 9:45:41 am BST

Hy,

I've got the same problem, but I don't understand your solution. If I press CTRL+G in excel, I get the GOTO screen?

   
Reply to kathleen

5

lucyphan, on May 10, 2008 11:41:02 am BST
  • +4

Yeah, and 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).

   
Reply to lucyphan

15

nikkinik, on Sep 6, 2008 4:23:26 am BST
  • +1

You Are AWESOME!!!!!! I was having the same problem and you fixed it - you made my day. THANKS SO VERY MUCH!

   
Reply to nikkinik

36

kim, on Jul 27, 2009 9:08:58 pm BST
  • +1

Thanks! awesomeness!

   
Reply to kim

17

user, on Oct 17, 2008 3:07:46 pm BST
  • +1

Awesome!

   
Reply to user

7

Collette, on Jun 4, 2008 4:24:16 pm BST
  • +1

Lucy - thank you so much! I had the same issue just happen and googled "excel autofilter won't work" and found your post. Your solution worked. I continue to wonder how I ever survived without the internet. Thanks!

   
Reply to Collette

8

Lucy, on Jun 5, 2008 5:30:44 am BST

My pleasure. I can't survive without the Internet either. Best wish.

   
Reply to Lucy

24

priya, on Jan 22, 2009 9:00:07 am GMT
  • +4

Really awsome...........

   
Reply to priya

9

trebroN, on Jun 8, 2008 12:23:33 pm BST

Thank you Lucy!
I've an urgent work but without your solution I can't finish it.

   
Reply to trebroN

10

roulette_21, on Jun 21, 2008 5:25:05 am BST

Hi..

I also have problem in autofilter, i used excel 2003. I have tried the solution above but it still not working properly.

I am working on an excel file that have approximately 3000 rows and 30 collumns and using autofilter..

The autofilter not working properly for some collumn but working for other collumns.

For example for collumn A i have 3000 rows with maybe around 1000 different items (some is duplicated with different input in other collumns), when i try to use the drop down list in collumn A, the list is not showing all the item available in collumn A (eg 'AAA').

But when I filter collumn B (eg 'BBB'), then i want to filter again using column A, the items 'AAA' appears in the drop down list.

The question is why the items 'AAA' not appear in the drop down list when I haven't filtered other collumns first?

Please help, your reply is greatly appreciated.

Thanks

   
Reply to roulette_21

11

Lucy, on Jun 22, 2008 10:16:29 pm BST
  • +5

Hi roulette_21

Which Excel are you using, 2003 or 2007?

If 2003, I guess this may be the answer to your problem:

"Excel 2003 limits the list to 1000 visible unique items (http://support.microsoft.com/kb/q295971/)"
So MS suggests:
"To work around this issue, use the AutoFilter command on several smaller sections of the data instead of on all the data at the same time. All list items will be visible if each subset has no more unique members than the limit for the particular version of Excel."

I know another method which splits your list into smaller lists with less than 1000 items but could not remember exactly how to do it. (Please google).
Another way to check is to use the trial version of Excel 2007 from MS download website. Excel 2007 lifts the limit from 1,000 to 10,000.

Please let me know if you solve the problem or not, thanks.

   
Reply to Lucy

12

mTim, on Jul 4, 2008 7:09:39 am BST
  • +1

Lucy, thank you so much for posting this trick! It worked as a charm! :)

   
Reply to mTim

13

Marshall, on Jul 7, 2008 3:38:54 am BST

Thanks Lucy, great help. I can accross via a google search. cheers

   
Reply to Marshall

16

Lucy, on Sep 13, 2008 5:52:08 am BST

To: K8ster598

Sorry I could not help you, I'm a Windows girl not a Mac:) Try Mac forums may help you. anyway, hope that you already fixed it by the time this message posted.

Everyone

Thank you for all the thank you's ;) I think everyone gets help from the Internet all the time so don't worry.

If the solution does not work, pls post/ create a new thread.

Moderator, pls close this discussion. Thank you.

   
Reply to Lucy

18

Lori, on Oct 28, 2008 12:24:42 am GMT

I can not understand what you are saying about how to reset the filter. Where do you type in the "sheet!_filter etc.)? My filter mode works one minute and not the next. How do you change to make it automatic.

   
Reply to Lori

19

NightPorter, on Oct 28, 2008 3:28:20 pm GMT
  • +1

I tried the GOTO thing, it selected what looked like the right area - although some columns did not have the non-working drop down arrows. I did an Autofilter command and it added arrows on every column that did work. When I did it again, it removed the working ones, leaving the non-working ones as they were before I did anything. I redefined the name to be the entire area. I can add and remove new drop down arrows as much as I want, but those three useless bits of grey picture are still there. Any ideas?

   
Reply to NightPorter

20

DI4BL0S, on Nov 10, 2008 7:30:27 am GMT
  • +3

The following also solves the problem:

Instead of selecting the Row that you want to apply the autofilter on,
Select all the columns (A, B, C ...) and then apply the autofilter

The above solution does work however, but I Think this is much easier
We've been solving this problem for many of our customers and so far it has always worked.

   
Reply to DI4BL0S

21

eleron, on Nov 26, 2008 12:28:56 pm GMT

This is a profi advice. Thanx a million..

   
Reply to eleron

26

mk, on Feb 25, 2009 9:40:50 pm GMT

Thanks!! "GOTO" just said that the text was not a valid reference, or, if I selected all first, made all the cells appear blank but didn't resolve the issue. This worked for me. Really appreciate it!

   
Reply to mk

37

Goran, on Sep 24, 2009 6:35:56 pm BST

Thanks... this worked like a charm. I've been struggling with this for days...

   
Reply to Goran

22

David, on Dec 11, 2008 7:56:51 pm GMT

Using Excel 2003, I am having a problem filtering in that the most of the rows do not show up in the filter list. I have determined that if the cell contains 255 characters or less it shows in the list, if it contains 256 or more characters it does not show up in the filter list.

   
Reply to David

27

Mayya, on Mar 6, 2009 8:17:08 am GMT

Hi, David!
I have now discovered the same problem and so far the same conclusion you have posted. If you have found back then any solution to deal with this characters limit, could you shear it with me, please! Thank you in advance!

   
Reply to Mayya

23

David, on Dec 11, 2008 8:02:08 pm GMT

In 2003, if the cell contains 255 characters or less it will filter, if 256 or more, it will not

   
Reply to David

28

VIC, on Mar 12, 2009 1:21:37 am GMT
  • +1

For autofilter to work properly, make sure that your data range does not have any empty columns when filtering.
If you do have empty columns, put a space or anything in the autofilter heading or body to allow it to form as part of the range so the empty colums are picked up.

The empty colums spilts the auto filter into 2 or 3 sections. Basically telling it to autofill the sections independently.

   
Reply to VIC

29

mahesh, on Mar 18, 2009 1:45:03 pm GMT

Hi... actually i been using the advanced filter in my excel sheet and its being working fine...but now since i have been upgraded to excel 2007 the excel has stopped working. I have a source data sheet, a filter criteria in another sheet & i copy the filtered rows to another sheet.
Here how the code goes


Sheets("H_DataSheet").Range("Database").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets("H_fullFiletrs").Range("BigBugger"), CopyToRange:=Sheets("PIL").Range("B9:BZ9")

The code works fine in excel 2003 but doesn't returns any filtered rows......i.e the sheet comes blank after filter code.

Please if any any can suggest any thing.........
I have been stuck with this for long now...........
Please help...........

   
Reply to mahesh

30

Asten, on Mar 27, 2009 12:28:00 pm GMT

Deselect autofilter under tools > filter.
Then select your whole worksheet and reselect autofilter. You will then no longer lose data after using the autofilter settings.

-Asten

   
Reply to Asten

31

David A of Avenues, on Apr 9, 2009 5:42:31 pm BST
  • +1

To solve the advanced or auto filter problem when upgrading from excel 2003 to 2007 ensure that no criteria tests contain names, absolute field positions must be used.

   
Reply to David A of Avenues

32

Martine, on Apr 29, 2009 9:09:20 pm BST

My auto filter stopped working- the dropdown box is empty and it's not over 1000 lines - i think it's something to do with blank characters when I copied/pasted from web based application, but I don't see them.

I tried to copy the whole spreadsheet to a new one - didn't work
I tried to copy some lines of the spreadsheet - didn't work

so I resorted to making a pivot table including the same columns and recreated the same spread sheet this worked! filter is functional again.

If anyone knows why this happens please advise.

Thanks,
Martine

   
Reply to Martine

33

Craig, on May 1, 2009 6:31:21 pm BST

All,

Now that I understant the problem more, another thing I just tried and that worked is to simply disable auto-filter, select the ranges to be fitlered, and while selected re-apply auto-fitler. Easy.

-Craig

   
Reply to Craig
38 answers 12 Next
Ask your question Reply
They need your help