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:
- If the button next to one of the column headings displays a funnel it means a filter has been applied. OR
- 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)
- 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:
Find hidden rows and columns in your worksheet
Filter data in a PivotTable and PivotChart using Slicers
Quickly format and enable filtering of your data
15 March 2018
Karen Roem offers software training and support through her company Roem Ltd. Contact her by email (Karen@roem.co.uk) or visit her website at www.roem.co.uk