Filtering Data

Spectrum provides a feature to filter data on a worksheet by specified criteria. Additional filters can be added as well as regular expressions or formulas can be used.

General Data Filtering#

A filtered worksheet is based on sample data defined from the source of the data. When first creating a filtered worksheet, the displayed data is based on the current data sampling. After the Workbook has been run, the joined sheet calculations are applied to the full data.

When filtering data from a Workbook sheet, you can choose between:

creating a new read-only sheet to display the results
filtering the data directly on the current sheet

The options available to filter vary based on the type of data in the column selected.

A filtered worksheet is based on sample data defined from the source of the data. When first creating a filtered worksheet, the displayed data is based on the current data sampling. After the Workbook has been run, the joined sheet calculations are applied to the full data.

INFO

When creating a filtered sheet that displays no data, Spectrum informs you that the empty sheet is displayed on the current sample data. Running the Workbook again creates a new sampling for the sheets that take into account the calculation of the filtered sheet.

Filtering Data by Using Formulas#

Creating a Filter#

To filter data:

Open the required Workbook and the required sheet. The Workbook content is displayed.
Click on "Edit" and select "Filter" or click the "Filter" icon from the icon bar. The dialog 'Apply Filter' opens.

or
Mark the checkbox when you want to create the filter in a new sheet.
Select the filter column from the drop-down.
Select the expression from the drop-down.
Enter the value.
Select the required filter-argument from the drop down.
If needed, click "+" to add a filter.

INFO: Clicking the "-" deletes the respective filter on the left.
If adding further more filter, select the next filter column, expression, value and filter-argument.
Select the way multiple filters are connected with.
Confirm with "Create Filter". The filter is created and applied to the sheet.

Using the Advanced Filter#

Advanced filter formulas are similar to those used in a Workbook's formula bar though without column name completion and less error handling. You can create a formula that combines multiple conditions using multiple referenced columns or use nests functions and constants.

INFO

Text-based expressions must return a Boolean value and are required to reference columns in the current sheet. When the expression that is applied to the current record returns 'false', the record is dropped, otherwise it remains.

TIP

Spectrum recommends developing and testing your advanced filter formulas in a separate copied worksheet before applying to your actual data.

To filter data with the advanced filter:

Open the required Workbook and the required sheet. The Workbook content is displayed.
Click on "Edit" and select "Filter" or click the "Filter" icon from the icon bar. The dialog 'Apply Filter' opens.

or
Mark the checkbox when you want to create the filter in a new sheet.
Select the tab "Advanced".
Enter the filter criteria from the available expressions.

Example: (#A > 70 && #B == false) || (#A < 30 && #B == true)

INFO: Multiple operators can be used to create an advanced filter.
Confirm with "Create Filter". *The filter is created and applied to the sheet. *

Updating a Filter#

To update a filter from an already filtered sheet:

Click on "Edit" and select "Filter" or click the "Filter" icon from the icon bar. The dialog 'Apply Filter' opens.

or
Change the filter criteria to your needs and confirm with "Update Filter". *The changes apply to the sheet. *

List of Filter Expressions#

Find the filter expressions you need from this list.

Data Type	Expression Name	Expression Description
String/ Number/ Date/ List	equals	The value entered exactly matches a value in the column specified.
String/ Number/ Date/ List	does not equal	The value entered does not exactly match a value in the column specified.
String/ Number/ Date/ Boolean/ List	contains	The value entered is present in the column specified.
String/ Number/ Date/ Boolean/ List	does not contain	The value entered is not present in the column specified.
String	ends with	The value entered match starting with the last characters in the specified column.
String	not ends with	The value entered does not match starting with the last characters in the specified column.
String	starts with	The value entered match starting with the first characters in the specified column.
String	does not begin with	The value entered does not match starting with the first characters in the specified column.
String	matches regexp	The regular expression value matches a regular expression in the specified column.
Any	blank	Displays the data where the value of the specified column is empty.
String	not blank	Displays the data where the value of the specified column is not an empty string.
String/ Number	contained in	The value entered with quotation marks " " includes the entire data from a cell in a specified column.
String/ Number	not contained in	The value entered with quotation marks " " does not include the entire data from a cell in a specified column.
String/ Number/ Date/ List/ Boolean	is empty cell	Displays the data where the value of the specified column is \<null>.
String/ Number/ Date/ List/ Boolean	cell not empty	Displays the data where the value of the specified column is not \<null>.
Number	less	Displays numbers less than the number entered.
Number	less equals	Displays numbers less than or equal to the number entered.
Number	greater	Displays numbers greater than the number entered.
Number	greater equals	Displays numbers greater than or equal to the number entered.
Date	after	Displays dates for the specified column after the date entered.
Date	after equals	Displays dates for the specified column after or equal to the date entered.
Date	before	Displays dates for the specified column before the date entered.
Date	before equals	Displays dates for the specified column before or equal to the date entered.
List	is empty list	Displays lists that have no elements from a specified column.
List	not an empty list	Displays lists that contain elements from a specified column.
Boolean	true	Displays boolean values that are true from a specified column.
Boolean	false	Displays boolean values that are false from a specified column.

Example for Advanced Filters#

CONTAINS(#a;"500") || ISBLANK(#a) || !CONTAINS(#a;"800")

This example can be read like this:

"Filter all entries that are contained in the column named 'a' with the value '500' OR filter entries where the value of the column 'a' is empty OR filter entries from column 'a' which do not contain the value '800'."