Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old September 20th 07, 03:52 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2007
Posts: 4
Default Auto filter does not pick up all values in a column

Hi, when I apply an auto filter to a range and I try to filter by a certain
column, the filter does not show all the values in that column. How can I fix
this?

Tx

  #2   Report Post  
Old September 20th 07, 04:11 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 8,651
Default Auto filter does not pick up all values in a column

Did you select the entire range before applying the filter? If there are
gaps in the data, Excel might guess the extent of the range differently from
what you intended.

The other possible problem if you have too many different values in the
column. The drop-down list will show only 1000 unique values, so you may
need to split out, for example, the first letter for an initial filter. I
do this with a helper column =LEFT(A1) to give an A-Z list, and then pick
the individual name I want after that.
http://www.contextures.com/xlautofilter02.html#Limits
--
David Biddulph

"Dries" wrote in message
...
Hi, when I apply an auto filter to a range and I try to filter by a
certain
column, the filter does not show all the values in that column. How can I
fix
this?

Tx



  #3   Report Post  
Old September 20th 07, 04:16 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 964
Default Auto filter does not pick up all values in a column

Excel 2003 and earlier versions limit the Auto Filter list to the first 1000
entries. The filter still works on all rows, but only the first 1000 will be
selectable from the drop-down list. Excel 2007 increases this limit to 10000
entries.

There really isn't a way to increase this limit, but there may be some ways
around it. You can use the "Custom" field on the filter, then type in the
value you want to filter by. Or, depending on your data, you may be able to
filter by a different column first, thus reducing the number of entries to
choose from in your desired column.

HTH,
Elkar



"Dries" wrote:

Hi, when I apply an auto filter to a range and I try to filter by a certain
column, the filter does not show all the values in that column. How can I fix
this?

Tx

  #4   Report Post  
Old September 21st 07, 08:44 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2007
Posts: 4
Default Auto filter does not pick up all values in a column

Thanks, this solved my problem. There were blank cells and just like you
said, it looked like Excel assumed the blanks might have been the end of the
data I wanted to filter.

Thanks a lot for your help (Thanks to Elkar too!)

Ciao

"David Biddulph" wrote:

Did you select the entire range before applying the filter? If there are
gaps in the data, Excel might guess the extent of the range differently from
what you intended.

The other possible problem if you have too many different values in the
column. The drop-down list will show only 1000 unique values, so you may
need to split out, for example, the first letter for an initial filter. I
do this with a helper column =LEFT(A1) to give an A-Z list, and then pick
the individual name I want after that.
http://www.contextures.com/xlautofilter02.html#Limits
--
David Biddulph

"Dries" wrote in message
...
Hi, when I apply an auto filter to a range and I try to filter by a
certain
column, the filter does not show all the values in that column. How can I
fix
this?

Tx






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
NO values in Auto-filter drop-down Jane Excel Discussion (Misc queries) 3 May 18th 07 12:04 AM
compare values in auto filter uw805 Excel Worksheet Functions 4 July 6th 06 08:44 AM
Is there a way to link Auto Filter values to a Cell SteveC Excel Worksheet Functions 2 May 1st 06 07:47 PM
How do I Auto-Filter with multiple values in a cell in Excel? Burghthing Excel Discussion (Misc queries) 2 November 23rd 05 04:41 PM
Averaging Values in Auto Filter Mr. Jan Park Excel Worksheet Functions 1 August 3rd 05 03:51 PM


All times are GMT +1. The time now is 04:11 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017