Filters are useful when you need to view only those rows of data that match a specific criteria and hide the remaining rows. Filters can be applied to one or more columns depending on your need. After the first filter, the subsequent filters are applied on the resulting subset of data. Filters option is available undet the Data tab, and can be applied on columns containing text, date and numerical data.
The following screenshot shows a range of data, before the filters were applied:
This screenshot shows the filtered data, after the filters were applied to display only those countries which have won 7 or more Gold medals.
How to use Filters
To enable filters in any sheet, first identify the range of data on which the filter has to be aplied.
To choose a range:
- Simply click on any cell in the sheet and Zoho Sheet automatically detects the range. [The continuous block of data surrounding the selected cell is selected.]
- Choose only the header row and the filter is automatically applied to the range.
- Choose the range by manually selecting the area.
To apply the filter:
- In the Data tab, click the Filters icon.
- In the Data drop-down list, click Filter.
- Use the following keyboard shortcut:
- Ctrl + Shift + L [Windows]
- Cmd + Shift + F [Mac]
- An inverted triangle is displayed next to every column title.
- To open the filter dialog box for that column, click the inverted triangle.
- From the dialog box, choose to apply either an auto filter or specify criteria to apply a custom filter.
- After the filter is applied across one column, the inverted triangle on the column header changes to a filter like icon to indicate that a filter has been applied on that column.
Filtering by list values
You can also filter data by the list of values displayed under that column. The filter dialog box lists unique entries contained in the column.
To apply auto filter, select one or more options from the entries listed, and only the rows containing the selected value(s) is displayed.
Custom filters provide the option for constructing a criteria based on which the data should be filtered. A Custom filter can be used to specify advanced criteria for filtering the data. A two part criteria can be specified using the "and" and "or" operators.
Depending upon the data type of the data contained in the column, the conditional operators are listed in the criteria builder.
The different types of custom filters are explained below
Filter by text
For columns containing "text" data, the following conditional operators can be used for applying custom filters.
- does not equal
- begins with
- does not begin with
- ends with
- does not end with
- does not contain
- does not match
Using these operators, a criteria can be built as per requirement.
For Example: Consider the below table listing out medal winners in different events of the Olympics.
- For displaying athletes who have won a medal either in "Archery" or in "Judo", the criteria would be:
- equals "Archery" OR contains "Judo".
- For displaying athletes who have won a medal in sports other than "Gymnastics" and "Badminton", the criteria would be:
- does not equal "Gymnastics" AND does not contain "Badminton".
Filter by date
For date columns, apart from the text operators, the following conditional operators are available for applying custom filters.
- is after
- is on or after
- is before
- is on or before
- For displaying all data whose data is on or before 06/12/2012, the criteria would be:
- is on or before 06/12/2012
Filter by number
For numerical data, apart from the text operators, the following conditional operators are available for building a criteria.
- is greater than
- is greater than or equal to
- is less than
- is less than or equal to
- For displaying only those countries which have won more than 7 Gold medals, the criteria would be
- is greater than or equal to 7
In addition to filter by list, a few other filtering options are available exclusively for numbers. These options are explained below:
Filter Top or Bottom N items
N takes any value between 1 to the maximum number of rows in the sheet.
This option is useful when you need to view just the 10 records with the highest numerical data in the column. The Bottom N items option works the other way round, displaying records with the least numerical data in the column.
- The Top 10 items would display those countires which have won the most medals and the Bottom 10 items would display those countries which have won the least medals.
Top 10 Countries in terms of medals won:
Bottom 10 Countries in terms of medals won:
Top or Bottom N percentage
While accessing large volumes of data, its easier to specify limits in percentage than using exact numbers. The Top N percentage displays rows with the numerical values that form the Top N percentage of the total data. The Bottom N percentage displays rows with the least values that make up the Bottom N percentage.
You can also remove filters, when they are no longer required by using any one of the following methods:
- To remove the filter applied across only one column, click the filter icon in the column header, and click the "Clear Filter" button in the filter dialog box.
- To remove the filters applied across the entire sheet, click the "Filters" option from the "Data" tab. The filters will be removed.
Tips & Facts
- If any of the arguments of the SUBTOTAL function fall within the filtered range, the resulting value of the function will be modified according to the subset data in the range.
- Find will also be performed only on the subset data, displayed after the application of the filter. Clear filters to search across all rows of the actual data.
- In the list of unique entries, a maximum of 100 entries is displayed.
- The Filter dialog also provides options to sort the data in ascending or descending order based on the entries in the selected column.
- Only one filtered range per worksheet is supported.
- For best results, it is advisable not to mix datatypes in one column. i.e., If a column contains text and numerical data, the filter results might not be accurate.