Use the command AutoFilter to filter the rows of a cell range:
If you select a cell range and choose the command Data | group Sort and filter | AutoFilter, an arrow will be displayed from now on above each column of this cell range. If you click on one of these arrows, a menu will open with all the cell contents of this column – and various conditions by which you can filter.
Note 1: The AutoFilter can only be inserted once per worksheet; two separate filters cannot be set on one worksheet. Otherwise, you cancel the previously applied filter by executing the AutoFilter command again. If you have created Tables in worksheets, they have their own AutoFilters – even on the same worksheet.
Note 2: Newly added or updated values are not automatically sorted by the previously set filter conditions. To integrate changed data into existing AutoFilters, use the command Reapply filter.
To apply the AutoFilter, proceed as follows:
| 1. | Select the desired cell range. Important: The first row of this range must contain headings for the data below. |
| 2. | Choose the command Data | group Filter | AutoFilter  . |
The AutoFilter function is now enabled. You can tell by the fact that a downward arrow is displayed in the first row of each column in the selected range.
Clicking on one of these arrows 
will open a menu which includes a list of all cell contents that occur in the corresponding column. By default, all values are selected in this list, thus there is no filtering.
To filter the data in the cell range, use this menu as follows:
▪Sort ascending 
: This option sorts the filter results of the applied AutoFilter range in ascending order.
▪Sort descending 
: This option sorts the filter results of the applied AutoFilter range in descending order.
▪Sort by color: 
: This option sorts the filter results of the applied AutoFilter range by cell color or text color, if these were used in cells. In the submenu, select the cell color/text color of the cells to be sorted upwards. The last selected color appears at the top. Clicking on More opens a dialog box with differentiated settings for sorting. For more information, see Sorting cells.
▪Filter by color: This option lets you filter a cell range by visual criteria (cell color/text color), if applied to cells.
▪More filters (contextual): Text filters, Number filters and Date filters open a submenu with additional filters (see below).
▪(All): This menu entry is a useful placeholder: It allows you to add/remove all values the column contains with just one click.
| The check mark in front of the entry indicates that all cell contents are currently included in the filter. |
| If you now click on the entry (All), all cell contents will be immediately removed from the filter (and the check mark will disappear).If you click on it again, all cell contents will be added to the filter again (and the check mark will reappear). |
| If the filter does not contain the contents of all cells but contains those of at least one cell, a gray area will be displayed instead of the check mark. |
▪(Blank): If you have empty cell contents in your column, you can use this selection to show/hide all empty rows.
▪List of cell contents: The most important part: All cell contents included in the column are listed here. By clicking on a value, you can add it to the filter (a check mark will appear in front of it) or remove it again by clicking on it again (the check mark will disappear).
Note: For the last three aforementioned options (All), (Blank) and List of cell contents, please always note the following: To confirm your selection, you have to press OK.
Example
If, for example, you want to filter a cell range in such a manner that only those rows that contain the name "Smith" are shown in a column, proceed as follows:
Select the cell range (including the column headings) and choose the ribbon command Data | group Filter | AutoFilter to enable the AutoFilter.
An arrow now appears next to each column heading. Click on the arrow of the column heading "Name" to open the AutoFilter menu for this column.
First, click on the entry (All) in this menu in order to remove the check mark. This deselects all cell contents from the filter. Then, select the menu entry "Smith" and confirm with OK. You have now created a filter condition "Name equals Smith" using the AutoFilter function. PlanMaker will hide all other rows that do not match this condition.
The red color of the arrow indicates that an AutoFilter is applied.
Filter result after the name "Smith" has been selected in the AutoFilter list
If you would also like to see all Millers listed, simply select the menu entry "Miller". To remove the Millers again, click on the entry "Miller" once more to deselect it. Press OK to confirm in each case.
If necessary, apply additional filter conditions in the other columns also (e.g., "Ms" for all female matches of a given name) to narrow down the results even further.
As you can see, the values displayed in a column can be included or removed from the filter in any desired combination.
More filters: Text filters, Number filters, Date filters
Depending on the format category of the filtered columns, PlanMaker automatically sets additional filter options for Text filter, Number filter or Date filter in the AutoFilter menu. The Text filter is offered for text-only values and the Date filter for date-only values. If the formats are mixed, the Number filter is applied.
Use the offered filter in each case to obtain specialized filter conditions, for example:
Text filter
▪Equals... Show only rows with exact matches.
▪Does not equal... Hide rows with exact matches.
▪Greater than... You will see the rows with the text values that are alphabetically after the filter value you entered.
▪Greater than or equal to... See above, but including the entered value.
▪Less than... You will see the rows with the text values that are alphabetically before the filter value you entered.
▪Less than or equal to... See above, but including the entered value.
▪Starts with... Show only rows with specific word beginnings.
▪Doesn't start with... Hide rows with specific word beginnings.
▪Ends with... Show only rows with specific word endings.
▪Doesn't end with... Hide rows with specific word endings.
▪Contains... Show only rows that contain specific strings as part of the text.
▪Doesn't contain... Hide rows that contain specific strings as part of the text.
Number filter
▪Greater than... This works like the operator > . You will see the rows with values that are greater than the filter value entered.
▪Greater than or equal to... This works like the operator ≥ . You will see the rows with values that are greater than or equal to the filter value entered.
▪Less than... This works like the operator < . You will see the rows with values that are less than the filter value you entered.
▪Less than or equal to... This works like the operator ≤ . You will see rows with values that are less than or equal to the filter value you entered.
▪Between... Here, define the range of numbers which contains the values of the rows that are to be displayed.
▪Not between... Here, define the range of numbers which contains the values of the rows that are to be hidden.
▪Top 10... Show only those rows where, for example, one of the 10 highest (or 10 lowest) values is in this column. You can customize this selection when the dialog box Top 10... has opened: In the left field, choose between Top or Bottom values. In the middle field, you have the option of setting the number of top/bottom values. In the right field, you can choose between absolute values (Items) and relative values (Percent).
| An example: |
| If you want to obtain 50% of the lowest values from 60 specified values, then set the following: |
| Left field: Lowest Middle field: 50 Right field: Percent |
▪Only empty: This option only displays rows where the value in this column is empty.
▪Non-empty: This option only displays rows where the value in this column is not empty.
▪Above average: This option only displays rows where the value is above the average of the values in this column.
▪Below average: This option only displays rows where the value is below the average of the values in this column.
Date filter
▪Equals... This displays only rows with exact date matches.
▪Does not equal... This hides rows with exact date matches.
▪Before... This displays only rows in which the date values are earlier than the entered date value.
▪Before or equal... See above, but including the entered value.
▪After... This displays only rows in which the date values are later than the entered date value.
▪After or equal... See above, but including the entered value.
▪Between... This displays rows where the date values are within a defined date range.
▪Not between... This hides rows where the date values are within a defined date range.
▪Day, Week, Month, Quarter, Year: Here, you can make further selections to quickly narrow down the desired date ranges.
| Note: If the AutoFilter has been applied to date values, you will notice in the arrow menu of the AutoFilter that the single days have already been sorted there at year and month level. Click on the plus sign in front of a year/month level to expand the level and view the single values grouped within it. If only single values are selected in a date level, a gray area is displayed in front of this date level (month/year) instead of a check mark. It is only when all existing values of a date level are selected that a check mark appears in front of this level. If no value of a date level is selected, you will see a white area in front of it. This allows you to always quickly see whether all, none or single values of a date level have been selected. |
There are also the following options for each of the offered filter methods Text filters, Number filters or Date filters:
▪Custom filter: This opens a dialog box in which you can define individual filter conditions.
▪Delete filter: This option is only available if criteria have been set via the Text filter, Number filter or Date filter selection. Press Delete filter to remove only the criteria applied via these filters.
Showing all hidden rows again
If you want all rows that were hidden by AutoFilter to become visible again, choose the ribbon command Data | group Filter | Show all.
If values in the cell range set by AutoFilter have changed, you can use the ribbon command Data | group Filter | Reapply AutoFilter to update the selection you have already defined.
For example, you have specified that all rows with the name "Smith" should not be displayed and further entries with this name have been subsequently added. You can choose the command Reapply AutoFilter to filter out such subsequently created entries again without having to redefine the conditions of the filter.
Of course, this function is particularly useful for dynamic formula values and dynamic date values.
Disabling the AutoFilter
To completely disable the AutoFilter function, choose the ribbon command Data | group Filter | AutoFilter once again. The downward arrows displayed at the top of the cell range will disappear, and all cells will be displayed again.