If you can’t find specific data in a worksheet, it may be hidden by a filter. For example, if you have a column of dates in your worksheet, that column may have a filter that restricts the values to specific months.
There are several options:
-
Clear a filter from a specific column
-
Clear all filters
-
Remove all filters
Clear a filter from a column
Click the Filter button next to the column heading, and then click Clear Filter from <"Column Name">.
For example, the figure below depicts an example of clearing the filter from the Country column.
Note: You can't remove filters from individual columns. Filters are either on for an entire range, or off. If you don't want someone to filter a particular column, you can consider hiding it.
Clear all the filters in a worksheet
Click the Data tab, and then click Clear.
How do I know if the data has been filtered?
If a filtering is applicable to a table in a worksheet, you’ll see either of these buttons in the column heading:
Button |
Description |
---|---|
A filter is available and it hasn’t been used to sort the data in the column. |
|
A filter has been used to filter or sort the data in the column. |
In the following worksheet, a filter is available for the Product column, but it hasn’t been used. The filter in the Country column has been used to sort the data.
Remove all the filters in a worksheet
If you want to completely remove filters, go to the Data tab and click the Filter button, or use the keyboard shortcut Alt+D+F+F.
Need more help?
You can always ask an expert in the Excel Tech Community or get support in Communities.