Filters make it easy to extract data from a table that meets specific criteria.
Search is similarly a function to find data that meets specific conditions, but has different characteristics as follows.
| - | Search scope | Result |
|---|---|---|
| Filter | Narrow (specific rows). | Multiple. |
| Search | Wide. (entire sheet/book) | Single. If there are multiple cells, check while moving one cell at a time. |
The FILTER function has been added in Excel 2019 and later.
This is more suitable for routine work, and we recommend that those who use filters a lot also consider this one.
Basic Usage
Setup
Select range of cells and choose “Filter” from “Data”.
The first line has a ▼ button and enters filter mode.
Filter by setting conditions
Clicking ▼ displays a list of values present in the column.
If unchecked, the line will be hidden.
If you enter text in the "Search" box, you can narrow down the list to a few candidates.
Clearing Extraction
Select “Clear” from the “Data” menu to cancel the extraction condition and return to the original display.
Sort
You can sort by selecting ▼ and choosing “ascending” or “descending”.
Note that only one column can be used as a key for sorting in the filter.
If you want to use multiple columns as keys, use the Sort.
Release filter
Select a filtered cell and press the filter button again to release it.
Additional Usage
Color Filter
When a background or font color is set, a color filter appears in the ▼ menu to select the background or font color.
This filter allows you to select only one color through the cell and font.
If you continue to select other colors, the filter will be canceled.
Text Filter
If the column to be filtered is a text string,
The ▼ menu displays a text filter that allows you to specify the details of the filter.
- Equals
- Does Not Equal
- Begins With
- Ends With
- Contains
- Does not contain
Custom Autfilter
Selecting any of these will bring up the auto-filter options and move on to the detailed settings.
- Two conditions can be set (AND,OR).
- Allows fuzzy searches with wildcards.
Numeric Filter
If the column to be filtered is numeric, the Numeric filter appears in the ▼ menu and allows you to specify detailed conditions for the numeric value.
- Equals
- Does Not Equal
- Greater than
- Greater than or Equal To
- Less than
- Less than or Equal To
- Between
- Top 10
- Above Average
- Below Average
Custom Autfilter
Auto Filter Options
Selecting one of these options will bring up a list of options.
The usage is similar to that of the text filter.
However, the “Above Average” and “Below Average” options are not displayed, and the Top Ten displays a special option.
Top 10 Autfilter
- Top or Buttom
- Threshold
- Items or Percent
Date Filter
For dates, the normal filter format changes to drill down from year > month > day.
The filter criteria will also be date-specific, such as year, month, week, or quarter.
- Equals
- Before
- After
- Between
- Tomorrow
- Today
- Yesterday
- Next week
- This Week
- Last Week
- Next month
- This month
- Last month
- Next Quarter
- This Quarter
- Last Quarter
- Next Year
- This year
- Last Year
- Year to Date
---