Excel Filters -- AutoFilter Tips

Contextures - Microsoft Excel Tips and Tutorials
Search Contextures Sites

Home
Excel Tips
Excel Files
Products
Blog
Contact
RSS

Contextures
Excel news
by email

Email:

Name (optional):

We respect your email privacy

Excel Filters -- AutoFilter Tips

Tips for working with an AutoFilter, and some workarounds for problems you may encounter.

Limits to AutoFilter Dropdown Lists

Count of Filtered Records in Status Bar

Video: Status Bar Shows Filter Mode

Workaround #1 - Subtotal

Workaround #2 -- Status Bar AutoCalc

AutoFilter for Text in a Long String

Download the Sample File

Also see: AutoFilter Basics

Limits to AutoFilter Dropdown Lists

In Excel 2003, and earlier versions, an AutoFilter dropdown list will only show 1000 entries. As a result, in a large database, the AutoFilter dropdown may not show all the items in the column. (This limit was raised to 10,000 in Excel 2007.)

You could add a new column, and use a formula to split the list into two groups, e.g.:
   =IF(LEFT(C2,1)<"N","A-M","N-Z")

or

to split the list into three groups, nest one IF formula
inside another, e.g.:
   =IF(LEFT(C2,1)<"I","A-H",IF(LEFT(C2,1)<"Q","I-P","Q-Z"))

Or, for a column with thousands of unique entries, use a formula which extracts the first two or three letters, e.g.:
   =LEFT(C2,2)
Filter on this column first, then by the intended criteria.

Count of Filtered Records in Status Bar

Normally, after you have applied an AutoFilter, the Status Bar shows a count of visible records.

Sometimes it just says, "Filter Mode."

This can happen when your list has many formulas. There are articles in the Microsoft KnowledgeBase that explain:

XL2000: Excel AutoFilter Status Bar Message Shows "Filter Mode" Q213886)
XL: AutoFilter Status Bar Message Shows "Filter Mode" (Q189479)

The Status Bar will also show "Filter Mode" if anything is changed in the list, after a filter has been applied. For example, if you format a cell, or type a number in one of the records, the 'Filter Mode' message will appear in the Status Bar.

You can see the Filter Mode problem and workarounds in the following video, and written instructions are below the video

Video: Status Bar Shows Filter Mode

If the status bar says "Filter Mode", you can follow the workaround steps in this video to see the number of filtered records.

Workaround #1 -- Subtotal

For a record count of the visible rows which contain data, you can use the Subtotal function in a formula in the same row as your headings. For example, to count the visible entries in column D which contain numbers, you could use this formula:
      =SUBTOTAL(2,D:D)
The 2 in the first argument tells Excel to use the COUNT function on the visible cells in the range.

To count rows that contain text, you could change the formula:
      =SUBTOTAL(3,C:C)-1
The 3 is for the COUNTA function, and the -1 removes one for the row which contains the column heading.

NOTE: Blank cells will not be counted -- use a column with no blank cells.

Workaround #2 -- Status Bar AutoCalc

(from Excel MVP Dave Peterson)

1. Select a column that you know has data in each cell.
2. Right-click on the embossed area at the right side of the status bar.
3. Choose Count -- it'll tell you how many are in the selected cells.
4. (If you included the header rows, subtract them.)

To see how many total rows, choose Data>Filter>Show All, select a column that has data in each cell, and look at the bottom of the screen.

With this feature you can also get Min/Max/Average/etc. with just simple mouse clicks and selections.

AutoFilter for Text in a Long String

You can use the Custom option to filter for cells that contain specific text. However, if the text is located after the 255th character in the cell, it won't be found. Also, the long text strings don't appear in the dropdown list in the heading cell.

As a workaround, enter the search text string in a cell on the worksheet. Then add a formula to check for the text.

Insert a new column in the database, and in the heading cell, type the word you're searching for, e.g.: Shop

Enter the following formula in row 2 of the new column:
   =ISNUMBER(SEARCH($B$1,A2))

Copy the formula down to the last row

Filter column B for TRUE

To filter for a different word, type a new string in cell B1, and reapply the filter in column B.

Note: SEARCH is not case sensitive. For a case sensitive filter, use FIND, e.g.:
     =ISNUMBER(FIND($B$1,A2))

Download the Sample File

Download a zipped Excel AutoFilter workbook with sample data.

AutoFilter Basics

AutoFilter Tips

AutoFilter Programming

Home

Excel Tips

Excel Files

Products

Blog

Contact

Privacy Policy

Contextures Inc., Copyright �2013
All rights reserved.

Debra Dalgleish

Contextures Excel news by email Email: Name (optional): We respect your email privacy	Excel Filters -- AutoFilter Tips Tips for working with an AutoFilter, and some workarounds for problems you may encounter. Limits to AutoFilter Dropdown Lists Count of Filtered Records in Status Bar Video: Status Bar Shows Filter Mode Workaround #1 - Subtotal Workaround #2 -- Status Bar AutoCalc AutoFilter for Text in a Long String Download the Sample File Also see: AutoFilter Basics Limits to AutoFilter Dropdown Lists In Excel 2003, and earlier versions, an AutoFilter dropdown list will only show 1000 entries. As a result, in a large database, the AutoFilter dropdown may not show all the items in the column. (This limit was raised to 10,000 in Excel 2007.) You could add a new column, and use a formula to split the list into two groups, e.g.: =IF(LEFT(C2,1)<"N","A-M","N-Z") or to split the list into three groups, nest one IF formula inside another, e.g.: =IF(LEFT(C2,1)<"I","A-H",IF(LEFT(C2,1)<"Q","I-P","Q-Z")) Or, for a column with thousands of unique entries, use a formula which extracts the first two or three letters, e.g.: =LEFT(C2,2) Filter on this column first, then by the intended criteria. Count of Filtered Records in Status Bar Normally, after you have applied an AutoFilter, the Status Bar shows a count of visible records. Sometimes it just says, "Filter Mode." This can happen when your list has many formulas. There are articles in the Microsoft KnowledgeBase that explain: XL2000: Excel AutoFilter Status Bar Message Shows "Filter Mode" Q213886) XL: AutoFilter Status Bar Message Shows "Filter Mode" (Q189479) The Status Bar will also show "Filter Mode" if anything is changed in the list, after a filter has been applied. For example, if you format a cell, or type a number in one of the records, the 'Filter Mode' message will appear in the Status Bar. You can see the Filter Mode problem and workarounds in the following video, and written instructions are below the video Video: Status Bar Shows Filter Mode If the status bar says "Filter Mode", you can follow the workaround steps in this video to see the number of filtered records. Workaround #1 -- Subtotal For a record count of the visible rows which contain data, you can use the Subtotal function in a formula in the same row as your headings. For example, to count the visible entries in column D which contain numbers, you could use this formula: =SUBTOTAL(2,D:D) The 2 in the first argument tells Excel to use the COUNT function on the visible cells in the range. To count rows that contain text, you could change the formula: =SUBTOTAL(3,C:C)-1 The 3 is for the COUNTA function, and the -1 removes one for the row which contains the column heading. NOTE: Blank cells will not be counted -- use a column with no blank cells. Workaround #2 -- Status Bar AutoCalc (from Excel MVP Dave Peterson) 1. Select a column that you know has data in each cell. 2. Right-click on the embossed area at the right side of the status bar. 3. Choose Count -- it'll tell you how many are in the selected cells. 4. (If you included the header rows, subtract them.) To see how many total rows, choose Data>Filter>Show All, select a column that has data in each cell, and look at the bottom of the screen. With this feature you can also get Min/Max/Average/etc. with just simple mouse clicks and selections. AutoFilter for Text in a Long String You can use the Custom option to filter for cells that contain specific text. However, if the text is located after the 255th character in the cell, it won't be found. Also, the long text strings don't appear in the dropdown list in the heading cell. As a workaround, enter the search text string in a cell on the worksheet. Then add a formula to check for the text. Insert a new column in the database, and in the heading cell, type the word you're searching for, e.g.: Shop Enter the following formula in row 2 of the new column: =ISNUMBER(SEARCH($B$1,A2)) Copy the formula down to the last row Filter column B for TRUE To filter for a different word, type a new string in cell B1, and reapply the filter in column B. Note: SEARCH is not case sensitive. For a case sensitive filter, use FIND, e.g.: =ISNUMBER(FIND($B$1,A2)) Download the Sample File Download a zipped Excel AutoFilter workbook with sample data.