Filter using the advanced filter

Application: Spreadsheet Home Edition

To filter a range using an advanced filter by condition, follow these steps:

2.Click editor_filter_on_drop_active Sort and filter in the upper cell of the column where you want to filter the data.

3.In the window that opens, select the By condition filter type and select the Advanced filter option.

filtering_condition_custom_window

4.In the Condition 1 block, select the first condition from the drop-down list in the upper box:

Comparison condition for Advanced filter	Description
Equal to	The value in the cell should be equal to the value of the argument.
Not equal to	The value in the cell should not be equal to the value of the argument.
Greater than	The value in the cell should be greater than the value of the argument.
Greater than or equal to	The value in the cell should be greater than or equal to the value of the argument.
Less that	The value in the cell should be less than the value of the argument.
Less than or equal to	The value in the cell should be less than or equal to the argument value.
Start with (available if the range contains data of Text type)	The value in the cell starts with the value of the argument.
End with (available if the range contains data of Text type)	The value in the cell ends with the value of the argument.
Contain (available if the range contains data of Text type)	The value in the cell includes the value of the argument.
Do not contain (available if the range contains data of Text type)	The value in the cell does not include the value of the argument.

5.In the Condition 1 block, in the Value box, click the t_down button and select a value from the range in the drop-down list, or enter another value manually. When you enter a value, a search is performed within the range, and the corresponding options are suggested in the drop-down list. When entering text, you can use wildcards. To clear the input field, click the clear_input_field button.

6.In the Condition 2 block, select the second condition from the drop-down list in the upper box.

If you want to do advanced filtering based on one condition, leave the Not selected option in the drop-down list with the condition in one of the blocks (Condition 1 or Condition 2).

7.In the Condition 2 block, enter or select the value in the Value box in the same way as for the first condition (see step 5 above).

8.Select either the AND or OR operator:

•AND operator: The value is included in the filter results if both conditions are met.

•OR operator: The value is included in the filter results if at least one of the conditions is met.

If mutually exclusive conditions are used, the filtering result will be an empty range.

9.Click Apply.

The appearance of the sort and filter button may differ from the one shown here because it depends on whether sorting has been previously applied, the sort order (ascending or descending), and whether a filter has been previously applied:

– editor_filter_on_drop_active : No sorting or filtering applied.

– filter_applied : Filter applied, no sorting applied.

– sorting_ascending_icon : Sorted in ascending order with no filter applied.

– sorting_descending_icon : Sorted in descending order with no filter applied.

– sorting_ascending_filter_icon : Sorted in ascending order with filter applied.

– sorting_descending_filter_icon : Sorted in descending order with filter applied.

When using the advanced filter, data types such as Date, Logical, and Error are interpreted by the application as numbers. When checking for compliance with conditions, operations are applied to them as if they were numbers.

The date December 30, 1899 numerically corresponds to zero, December 31, 1899 corresponds to the number 1, and so forth.

The correspondence between error types and numbers is shown in the table below:

Type of error	Numerical value
#NULL!	1
#DIV/0!	2
#VALUE!	3
#REF!	4
#NAME?	5
#NUM!	6
#N/A	7
#GETTING_DATA	8

The correspondence between logical values and numbers is shown in the table below:

Logical value	Numerical value
FALSE	0
TRUE	1

Was this helpful?

Yes