Filters > AutoFilter > Troubleshoot AutoFilter Tips and Troubleshooting See how to remove an Excel Filter Mode message in the status bar, or number the visible rows in filtered list. And if you don't like date grouping in the Autofilter drop downs, see how to stop that from happening!

1) Filter Mode in Status Bar

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

Occasionally though, you might see a different message in the Status Bar, that just says, "Filter Mode."

Video: Filter Mode in Status Bar

You can see the Filter Mode problem and workarounds in the following video, and there are written steps, and details on the problem, below the video

Details: Filter Mode in Status Bar

1.1) Why Filter Mode Appears

1.2) Workaround #1 - SUBTOTAL

1.3) Workaround #2 -- Status Bar AutoCalc

1.4) Workaround #3 -- Manual Calculation

1.5) Microsoft KnowledgeBase Articles

1.1) Why Filter Mode Appears

While researching this Filter Mode problem, I found two reasons why the "Filter Mode" message can appear

1. Your list has many formulas that contain cell references
2. Something is changed in the list, after a filter has been applied. For example, you format a cell, or type a number in one of the records

Note: For details on these issues, go down to the Microsoft KnowledgeBase Articles section, below.

1.2) Workaround #1 -- SUBTOTAL

If you want to see a record count of the visible rows that contain data, you can use the Excel SUBTOTAL function.

For example, in the screen shot below, the following SUBTOTAL formula shows the count of visible numbers in the Excel table's Units column.

• =SUBTOTAL(2,tblSales[Units])

How SUBTOTAL Formula Works

Here's a quick look at how that SUBTOTAL formula works:

• The 2 in the first argument is a function number.
  • #2 tells Excel to use the COUNT function on the visible cells in the range
  • COUNT function counts only the numbers and dates (which are stored as numbers)
• To count rows that contain any type of data, use 3 as the first argument
  • #3 tells Excel to use the COUNTA function on the visible cells in the range
  • COUNTA function counts numbers, text, and other types of data

▶

SUBTOTAL Formula Tips: I like to put the SUBTOTAL formula in the same row as the table headings. Blank cells will not be counted -- use a column with no blank cells

1.3) Workaround #2 -- Status Bar AutoCalc

(Filter Mode tip from Dave Peterson)

For a quick count of visible rows in a filtered table, follow the steps below, for a Status Bar calculation.

1. First, select a column in the filtered table, that you know has data in each row.
2. Next, look in the Status Bar, at the bottom of the Excel window.
3. At the right end of the Status Bar, in the AutoCalc area, you might see Count, or Numerical Count, or other calculations, like Sum

Add Calculations to Status Bar

If you don't see the calculations that you need, follow the steps below, to add them:

• First, right-click anywhere in the Excel Status Bar
• Next, in the pop-up menu, click on a calculation that you want to add
• Or, if a calculation already has a check mark, click on that calculation, to remove it from the Status Bar
• Note: Count will count all types of data
  • Numerical Count will only count numbers and dates

1.4) Workaround #3 -- Manual Calculation

In one of the old Knowledge Base articles, Microsoft suggested temporarily changing to the calculation settings to Manual Calculation, instead of Automatic Calculation.

To change the Excel calculation settings, follow the steps below:

• Onn the Excel Ribbon, go to the Formula tab
• At the right end, click on Calculation Options
• Click on the calculation setting that you want - Automatic, Automatic Except for Data Tables, or Manual

1.5) Filter Mode - Microsoft Knowledge Base Articles

Here are the details for the reasons why Filter Mode appears that I mentioned above, in section 1.1.

I found those reasons, long ago, in articles from the old Microsoft KnowledgeBase (KB). Unfortunately, those articles have been removed from the Microsoft site. So, for reference, I've put excerpts from one of the articles below, along with its KB number.

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

This problem may occur when all four of the following conditions are true:

1. You have 51 or more formulas in a list.
2. You filter the list in-place (AutoFilter).
3. Each formula contains at least one cell reference.
4. Excel makes a change to the status bar to indicate the calculation of formulas before the filter process is completed.

More Information about Filter Mode:

• Calculation of formulas may cause Excel to show the Calculating number% message in the status bar.
• Depending on the complexity of the formulas, this may occur when there are more than 50 formulas.
• The act of filtering causes recalculation of formulas.
• When you filter a list in-place, Excel displays the number of number records found message, unless the status bar is changed before the filter is completed.
• If you do not have a large number of formula cells to calculate, Excel does not indicate the calculation with the status indicator, but displays the typical number of number records found message.
• However, if calculation is indicated in the status bar, even though it is very brief, the status bar message changes to the "Filter mode" message.

2) Ungroup Dates in Filter Drop Down

By default, when you turn on an AutoFilter, dates are automatically grouped in the drop down list. When I click the arrow in the Date column heading, the dates are grouped into years, instead of showing the individual dates.

You can turn this feature off, to show the full list of dates.

Video: Turn Off Date Grouping

Watch this short video to see the steps for turning off date grouping in an Excel filter. Written instructions are below the video.

2.1) Ungroup Dates in Filter Drop Down

If the dates are grouped in an AutoFilter in Microsoft Excel, you can manually change a setting, to ungroup them in the current workbook.

• Tip: You can also use my macros to turn the grouping on or off.

Follow the steps below, to turn off the Date Grouping feature in the current workbook:

1. On the Ribbon, click the File tab, then click Options
2. Click the Advanced category
3. Scroll down to the Display Options for This Workbook section
4. Remove the check mark from Group Dates in the AutoFilter menu.
5. Click OK to apply the setting change.

4) Video: Problem Pasting in Filtered List

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.

Tip: To follow along with these videos, go to the Filtered List Paste Problems page and download the sample file.

Video: Use Fill Right Shortcut

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.

5) Limits to AutoFilter Drop Down Lists

In Excel, an AutoFilter drop down list will show a maximum of 10,000 entries.

As a result, in a large database, with lots of unique items in a column, the AutoFilter feature drop down may not show all the items in the column.

▶

Fun Fact: The AutoFilter list items limit was only 1,000 items, in Excel 2003 and earlier!

Avoid Missing Items

To avoid the possibility of missing items, you could try this:

• Add a new column, with a formula to split the list into two or more groups
• Filter on this formula column first
• Next, filter for the intended criteria

Two Groups

For example, this IF function formula divides customer names into two groups, based on the first letter in the customer name.

• =IF(LEFT(C2,1)<"N","A-M","N-Z")

Three Groups

And the following formula splits the customer list into three groups, using the LEFT function, with 2 IF functions.

• =IF(LEFT(C2,1)<"I","A-H", IF(LEFT(C2,1)<"Q", "I-P","Q-Z"))

Multiple Groups

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)

Number Visible Rows Only, in Filtered List

If you need to print a filtered list, a helpful trick is to use the Excel AGGREGATE function, to number only the visible values in that list.

• Note: The AGGREGATE is available in Excel 2010 and later versions. In earlier versions, you can number a filtered list by using the SUBTOTAL function instead.

In the animated screen shot below,

• Using a Slicer, the list is filtered to show a specific product - Paper or Staplers
• AGGREGATE formula in table column B numbers the visible rows -- 1, 2, 3, 4
• Numbers change to show correct sequence, ignoring hidden rows

AGGREGATE Formula to Number Visible Rows

Here is the formula in cell B2, and the formula was automatically copied down to the other table rows below:

• =AGGREGATE(2,3,C$1:C2)

There are 3 required arguments in the AGGREGATE formula syntax:

1. function number: 2 is the code number for the COUNT function -- it will count numbers in the visible rows
2. Options: 3 is the options setting, to tell Excel what to ignore
3. Ref1: C$1:C2 is the range with numbers (dates) to count.
   • The starting point is locked at row 1 (C$1), and goes down to the current row (C2).

Problem: Last Row Stays Visible

If 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 of this Total Row problem:

• Excel has decided the last row is special, and contains Totals for the table columns.
• In an Excel table, the Total row is not included in the filter range, so that's why the row stays visible.

Change the Formula

If you have a problem with the last row remaining visible, add two minus signs after the equal sign.

Note: In the formula below, added spaces, to make it easier to see the minus signs at the start of the formula:

• = - - AGGREGATE(2,3,C$1:C2)

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 String

You can use the Custom option to filter for cells that contain specific text.

However, there are a couple of problems with long text filters

• If the text is located after the 255th character in the cell, it won't be found.
• Long text string values don't appear in the drop down list in the heading cell.

Long Text Workaround

As a workaround, you can enter the search text string in a cell on the worksheet. Then add a new column to your table, with a formula to check for the text.

1. Insert a new column in the Excel Table
2. In the heading cell, type the word you're searching for, e.g.: Shop
3. Enter the following formula in row 2 of the new column:
      =ISNUMBER(SEARCH($B$1,A2))



4. Copy the formula down to the last row
5. Use the drop-down arrows to filter the the formula column for TRUE
6. To filter for a different word, type a new string in cell B1, and reapply the filter in column B.    

▶	Notes - SEARCH Function: SEARCH is not case sensitive. For a case sensitive filter, use a FIND function formula, such as: =ISNUMBER(FIND($B$1,A2))

Get the Excel Files ✅

• AutoFilter Tips: Get a zipped Excel AutoFilter workbook with sample data. The zipped Excel file is in xlsx format, and does not contain macros
• 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 Tutorials

AutoFilter Basics

AutoFilter Programming

Drop Down from Filtered List

Filtered List Paste Problems page

Advanced Filter Introduction

Advanced Filter Criteria Slicers

Advanced Filter Macros