Tip of the week # 537
13 March 2018

How do I know if a table has been filtered? (Microsoft Excel) *

If you can't find the data you're looking for in a worksheet, it might be hidden by a filter. When you apply a filter, entire rows are hidden if values in one or more columns don't meet the filtering criteria. There are three quick ways to see whether a filter has been applied to the sheet.

Here's how:

  1. If the button next to one of the column headings displays a funnel it means a filter has been applied. OR
  2. In the bottom left corner of the workbook, on the Status Bar, the number of records found is displayed or it might simply say "Filter Mode". OR (my favourite)
  3. The Clear button on the Data tab has a red cross next to it.

To get rid of all of the filters in a worksheet at once click the Clear button on the Data tab. Or better still, right-click the Clear button and select Add to Quick Access Toolbar. In future, if you want to know whether or not a table has been filtered you check whether the Clear button on the Quick Access Toolbar has a red cross and if so, you simply click it to clear all filters.

By the way, if you still cannot find the data, you might have hidden rows or columns. See tip 478 for help on how to find these.

With thanks to Ian for this week's tip inspiration!

Related tips:
Tip 505: Filter dates by month (tip_505.php) Tip 478: Find hidden rows and columns in your worksheet (tip_478.php)
Tip 474: Filter data in a PivotTable and PivotChart using Slicers (tip_474.php)
Tip 429: Quickly format and enable filtering of your data (tip_429.php)
Tip 426: Fast filtering (tip_426.php)


* Unless stated otherwise, these tips were written for Microsoft Office 2010.