If the data you want to filter requires complex criteria
you can use the special filter. Defines a filter that can combine
up to eight different filter criteria.
- Copy the column headers of the sheet ranges to be filtered
into an empty area of the sheet, and then enter the criteria for
the filter in a row beneath the headers. Horizontally arranged data
in a row is logically connected with AND, and vertically arranged
data in a column is logically connected with OR. Please refer to the
following example for instructions.
- Create a table contains 3 columns, the headers are City,
Population and Average Salary. 3 rows in the table, Bei Jing, Tokyo
and Shang Hai. Refer to the following table:
Table 1. Sample tableCity |
Population |
Average Salary |
Bei Jing |
17400000 |
5000 |
Toyko |
10000000 |
7000 |
Shang Hai |
19000000 |
6000 |
- Copy the column headers into an empty area of the sheet,
they are column City, Population and Average Salary.
- To filter the city name, e.g. target cities are in China,
input ="Bei Jing" in one row beneath City, press the accept button
on the formula toolbar. Input "=Shang Hai" in another row beneath
City, press the accept button on the formula toolbar.
- To filter the city whose population is greater than
15,000,000 and Average Salary is less than 5000 RMB, input ">15,000,000"
in one row beneath Population, press the accept button on the formula
toolbar. Input ">5000" in the same row of ">15,000,000" and different
column, press the accept button on the formula toolbar.
- After you have created a filter matrix, select the sheet
ranges to be filtered. Click , and define the filter conditions.
- Click OK, and only the rows from
the original sheet whose contents have met the search criteria are
still visible. All other rows are hidden until you click .