We may be compensated for your purchase of any of the products featured on this page – it helps us keep the lights on :)
Filters in Excel can help you sift through data that can otherwise be overwhelming.
Excel filters work by listing all the available items within the column you’re filtering and creating a drop-down filter menu that allows you to select only those items that you want to see.
FYI: Filtering does not remove any data, it only hides data to display only what you want to see.
WATCH: An Overview of Filters in Excel
How to Add Filters in Excel
There are a couple of ways you can add a filter in Excel. You can either let Excel detect what columns to add a filter to or you can manually select the range of cells to filter.
You’ll find the filter options in the Data tab, in the Sort & Filter section.
NOTE: Unless you create Tables (or convert your data to a Table format), you can only apply filters to a single data range per sheet.
Let Excel Detect the Data Range to Filter
You can quickly add filters by placing your cursor anywhere on your data, then clicking the Filter button.
Excel will do its best to figure out what data you want to apply filters to and then place filtering drop down menus on the top row of your data. It assumes the top row contains your data headers.
Just be aware that this may not always work correctly. While Excel can include blank cells in its filtering, if it finds an entire portion of a column or row that is blank within the data, it will assume that this is the end of your data set and exclude any other rows or columns.
Notice that, in the example above, no filter buttons were applied to the Product and Sales columns. And, if we look at the Associate names that we can filter for, Henry is missing from the options. This is because of the blank column (column C) and row (row 16) that separates them.
Manually Select What Columns to Filter
Your other option is to manually select the range of cells to apply filters to.
To do this, you can use your cursor to select the cell range.
How to Use Excel Filters
Filtering works by listing all available items in a column, allowing you to select only the items you want to see from that list and hiding any unselected items.
Select What Items to Filter For
To quickly filter for an item, open the filter drop-down and check to select the item you want to display.
You may need to first uncheck “Select All” to clear the selections and then check the item you need.
This hides all the rows, except for the ones selected.
Because the other data is now hidden, it is also removed from the filter options in the other columns.
Notice that after filtering for Jamie, if we look at the filter options in the Months column, we can only select to filter for January and February. That’s because those are the only 2 options currently displaying Jamie’s results.
When it comes to values, you have additional filtering options available.
Filter by Dates
If you’re filtering dates, you can set a specific date, a range of dates or any of the other preset options under “Date Filters”.
Filter by Numerical Values
You have similar options when working with numbered data.
Filter Text
And also when filtering text.
Filter by Color
You can also filter by the color of the cell and by the color of the font used.
Clear Filtered Items
To clear the filtered selections, you can uncheck them on the list or click on “Clear” in the Sort & Filter section of the Data tab in your ribbon.
FYI, this was created using the desktop version of Excel in Microsoft 365.
Leave a Reply