In addition to the AutoFilter introduced in the previous section, there are further options to filter the data in a cell range by one or more combinable conditions much more complex: with the ribbon command Data | group Filter | Special filter.
Proceed as follows:
| 1. | Select the desired range of cells. |
| 2. | Choose the command Data | group Filter | Special filter  . |
| 3. | In the following dialog box, define one or more filter conditions (see below). |
| 4. | Confirm with OK. |
All rows that do match the filter conditions will now be hidden.
Setting up filter conditions
To specify one or more filter conditions in the dialog box of the command Special Filter, proceed as follows:
In the section 1st condition, first select on the left the column to apply to the filter condition. In the middle, select the arithmetic operator. On the right, enter the value to compare against.
Some examples:
▪The condition "Column D equals Los Angeles" only shows entries where column D contains the text "Los Angeles".
▪The condition "Column E greater than 100000" only shows entries where column E contains a value greater than 100000.
If one condition is not sufficient for formulating your filters, you can set up to three filter conditions by filling out the sections 2nd condition and 3rd condition as well.
Using "wildcard characters": In conditions, the characters * and ? can be used as "wildcards": * represents any number of arbitrary characters, and ? represents a single arbitrary character. For example, "M*er" would call out "Mister", "Miller", "Mary's mother", etc., whereas "?ouse" would call out "mouse", "house", "rouse", etc.
Options of the dialog box
The dialog box for the special filter offers the following options:
▪Range contains headings
| If the first row and/or column of the selected cell range contains headings, enable this option. PlanMaker will ignore this line or column while filtering. |
▪Case sensitive
| If this option is enabled, PlanMaker will distinguish between uppercase and lowercase letters in conditions. For a condition such as "COLUMN A equals Smith", the condition will match only if the cell contains the text "Smith". "SMITH" and "smith" will not be included in the filtering results. |
▪Mode
| Determines whether rows or columns will be filtered. |
| Selecting Filter row by row, all rows will be filtered out that do not satisfy the filter condition. |
| Selecting Filter column by column, all columns will be filtered out that do not satisfy the filter condition. |
▪Results
| Determines whether the filter will be applied to the actual data or a copy of it: |
| Filter at current position – Select this option and the original data will be filtered. Rows/columns that do not satisfy the filter condition will be hidden at exactly that point where you set the filter. |
| Copy to position – If you select this option instead, PlanMaker creates a copy of the original cells at a cell address you specify. This copy contains only the filtered data and the original data remains unchanged in its place. |
| In the latter case, enter the cell address of the destination cell in the input field. You can either specify a single cell address (which will be the starting point of the output range) or a cell range where the copy of the data shall be placed. Copying to other worksheets is also possible. Caution: If the copy of the data is larger than the cell range entered, cells will be cut off (exception: you enter a single cell address as the starting point). |
Making all filtered rows visible again
If you want all rows hidden by filter to become visible again, choose the ribbon command Data | group Filter | Show all.