Filters > AutoFilter > Troubleshoot AutoFilter Tips & TroubleshootingRemove Excel Filter Mode message in status bar, Excel Autofilter limit and workarounds, number the visible rows in filtered list, stop filter drop down date grouping, and how to filter long text strings. |
Video: Problem Pasting in Filtered ListThis 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. Tip: To follow along with these videos, go to the Filtered List Paste Problems page and download the sample file. 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. |
Ungroup Dates in Filter Drop DownBy 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. Ungroup Dates in Filter Drop DownBy default, when you turn on an AutoFilter in Microsoft Excel, 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:
|
Filter Mode in Status BarNormally, 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 Workaround #2 -- Status Bar AutoCalc Why Filter Mode AppearsThis 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. Workaround #1 -- SubtotalFor 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: To count rows that contain text, you could change the formula: NOTE: Blank cells will not be counted -- use a column with no blank cells. Workaround #2 -- Status Bar AutoCalc(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. |
Number Visible Rows Only, in Filtered ListIf you need to print a filtered list, a helpful trick is to use the Excel AGGREGATE function, to number only the visible rows in that list.
In the animated screen shot below,
AGGREGATE Formula to Number Visible RowsHere is the formula in cell B2, and the formula was automatically copied down to the other table rows below:
There are 3 required arguments in the AGGREGATE formula syntax:
Last Row Stays VisibleIf you use AGGREGATE or SUBTOTAL formulas in a filtered list, the last row might always be visible, even if it does not meet the filter criteria. If that happens, it's because:
If you have a problem with the last row remaining visible, add two minus signs after the equal sign. Note: I added spaces, to make it easier to see the minus signs at the start of the formula:
After you add those minus signs, Excel should stop treating the last row as the table's Totals row. |
AutoFilter for Text in a Long StringYou 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 drop down 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.: Get the Sample FileAutoFilter Tips: Get a zipped Excel AutoFilter workbook with sample data. Copy Paste Filter: Download the Filtered List Copy Paste sample file used in the videos on this page. The zipped Excel file is in xlsx format, and does not contain macros More Filter TutorialsFiltered List Paste Problems page |
Last updated: August 17, 2022 7:24 PM