Tips for working with an AutoFilter, and troubleshooting AutoFilter problems
This video shows the problems that you can have when you try to copy and paste data into a filtered list. See why the problem occurs, and a couple of workarounds, to avoid the problem. Also, watch the second video, to see a keyboard shortcut for pasting in filtered rows.
To download the sample file used in these videos, click here: filtercopypaste.zip The file is in xlsx format, and does not contain macros.
Also, the Paste Buddy add-in, from Excel Campus, has a Paste Visible feature that lets you paste the copied data into the visible cells only.
If you are copying and pasting in the same rows in a filtered list, you can use the shortcut Ctrl + R, to fill right, or use a Ribbon command to fill left. Watch this video to see the steps.
By default, when you turn on an AutoFilter, dates are grouped in the drop down list. You can turn this feature off, to show the full list of dates.
Watch this short video to see the steps for turning off date grouping in an Excel filter. Written instructions are below the video.
By default, when you turn on an AutoFilter, dates are grouped in the drop down list.
You can manually change a setting, to ungroup them in the current workbook. You can also use programming to turn the grouping on or off.
Follow these steps to turn off the Date Grouping feature in the current workbook:
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.:
to split the list into three groups, nest one IF formula
inside another, e.g.:
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.
Normally, after you have applied an AutoFilter, the Status Bar shows a count of visible records.
Sometimes it just says, "Filter Mode."
You can see the Filter Mode problem and workarounds in the following video, and written instructions are below the video
This can happen when your list has many formulas. There are articles in the Microsoft KnowledgeBase that explain:
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.
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:
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:
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.
(from Dave Peterson)
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.
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.
Note: SEARCH is not case sensitive. For a case sensitive filter,
use FIND, e.g.:
Download a zipped Excel AutoFilter workbook with sample data.
Last updated: October 21, 2020 4:01 PM
Contextures RSS Feed