Let’s take a look. To filter data in Excel, first select a cell anywhere in the list or table you’d like to filter; then, click the Filter button on the Data tab of the ribbon. You’ll know that filtering is enabled when you see the small arrows appear to the right of each column heading. Each arrow provides a drop down menu that contains tools to filter on that column. Let’s set a filter on the color column to show only silver models. First, click the arrow in the Color column to open filter settings. Click Select All to deselect all colors, then click Silver. Click OK to enable the filter. The table now displays only models with a color of silver. Note that filtering actually hides rows that don’t match the current filter criteria. This table has ten rows of data, but now only three rows are visible. There are several visual clues to let you know that data is filtered. The most important clue is blue row numbers. This tells you that some rows are hidden by the filter. You’ll also see that arrows in filtered columns have changed to a filter button. If you hover over this button, Excel will display a description of the applied filter. Finally, when you apply a filter, Excel will display a message in the status bar indicating how many records are visible in the data. In this case, three out of ten records are visible. To clear a filter in a column, select Clear Filter in the filter options. Let’s undo and look at another way to clear filters. You can also use the Clear button next to the Filter button on the ribbon. This will clear all filters, but leave filter mode enabled. Finally, you can just click the Filter button to clear filters. This will clear all filters and disable filter mode entirely. Note that you can also enabling filtering from the Home tab of the ribbon in the Sort & Filter menu. Just select Filter in the menu to enable. Select Filter again to disable.

Dave Bruns

Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.