Filter(Microsoft Excel)

Japanese version.

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 scopeResult
FilterNarrow (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”.

Text
Numeric

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

---

Links

Introduction(Microsoft Excel)