Filtering Data

Datameer has a feature to filter data on a worksheet by the criteria specified. Users can apply additional filters to filtered data and can use regular expressions or formulas to create filters.

About Filtering Data

When filtering data from a sheet in the workbook, users can choose between creating a new read-only sheet to display the results or filter the data directly on the current sheet. The options available to filter vary based on the type of data in the column selected. Users can also filter using regular expressions or formulas. See Using Regular Expressions or Filtering Data Using Formulas to learn more about creating regular expressions or using formulas.

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

As of Datameer v6.4

When creating a filtered sheet that displays no data, Datameer informs you that the empty sheet is displayed based 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

Create a filter

  1. From the Data menu, choose Filter, or click the Filter icon on the toolbar.
  2. If you are going to create multiple filters, choose AND or OR. (See AND/OR section below for more information).
  3. Apply Filter to Sheet shows which sheet the filter applies to. It automatically applies to the sheet you are on. Select Create filter in a new sheet to create a new sheet with the filter.
  4. Choose a column to filter.
  5. Select an expression from the list. The available choices are based on the data type of the column you selected.
  6. Enter a value.
  7. To add additional filters, click the + button next to the condition.
  8. Select Use Static Value to enter a static value which is valid for all rows. When the checkbox is unchecked, select a row from the list.
  9. Click Create Filtered Sheet.

Revising a filter

  1. In a sheet that is already using a filter, from the Data menu, choose Filter, or click the Filter icon on the toolbar.
  2. If you are going to create multiple filters, choose AND or OR. 
  3. Choose a column to filter.
  4. Select an expression from the list. The available choices are based on the data type of the column you selected.
  5. Enter a value.
  6. To add additional filters, click the + button next to the condition.
  7. Click Update Filtered Sheet

Applying an additional filter

  1. In a sheet that is already using a filter, from the Data menu, choose Filter, or click the Filter icon on the toolbar.
  2. If you are going to create multiple filters click on the + button to the right or the filter row.
  3. Choose a column to filter.
  4. Select an expression from the list. The available choices are based on the data type of the column you selected.
  5. Enter a value.
  6. To add additional filters, click the + button next to the condition.
  7. Click Apply New Filter to Filtered Sheet

Applying formulas to filtered data

After a worksheet has been filtered it is labeled as a formula sheet. Additional formulas are allowed to be added to the filtered sheet.

Filtered sheets don't automatically update when the original worksheet is updated.

Using Filter Expressions

Data typeExpression nameExpression description
String, Number, Date, ListequalsThe value entered exactly matches a value in the column specified.
String, Number, Date, Listdoes not equalThe value entered does not exactly match a value in the column specified.
String, Number, Date, BooleancontainsThe value entered is present in the column specified.
String, Number Date, Booleandoes not containThe value entered is not present in the column specified.
Stringends withThe value entered match starting with the last characters in the specified column.
Stringnot ends withThe value entered does not match starting with the last characters in the specified column.
Stringstarts withThe value entered match starting with the first characters in the specified column.
Stringdoes not begin withThe value entered does not match starting with the first characters in the specified column.
Stringmatches regexpThe regular expression value matches a regular expression in the specified column.
StringblankDisplays the data where the value of the specified column is an empty string.
Stringnot blankDisplays the data where the value of the specified column is not an empty string.
String, Numbercontained inThe value entered with quotation marks "" includes the entire data from a cell in a specified column.
String, Numbernot contained inThe value entered with quotation marks "" does not include the entire data from a cell in a specified column.
String, Number, Date, List, Booleanis empty cellDisplays the data where the value of the specified column is <null>
String, Number, Date, List, Booleancell not emptyDisplays the data where the value of the specified column is not <null>
NumberlessDisplays numbers less than the number entered.
Numberless equalsDisplays numbers less than or equal to the number entered.
NumbergreaterDisplays numbers greater than the number entered.
Numbergreater equalsDisplays numbers greater than or equal to the number entered.
DateafterDisplays dates for the specified column after the date entered.
Dateafter equalsDisplays dates for the specified column after or equal to the date entered.
DatebeforeDisplays dates for the specified column before the date entered.
Datebefore equalsDisplays dates for the specified column before or equal to the date entered.
Listis empty listDisplays lists that have no elements from a specified column.
Listnot an empty listDisplays lists that contain elements from a specified column.
BooleantrueDisplays boolean values that are true from a specified column.
BooleanfalseDisplays boolean values that are false from a specified column.

Using AND or OR

When you select AND, all of the criteria of each expression much match. For example, if you choose last_name equals Smith and salary greater than 100000 you only find records where someone named Smith makes a six-digit salary.

If you select OR and the same criteria, you get everyone who makes more than 100000 regardless of their last name and anyone whose last name is Smith.

If you create a filter where no criteria match, you can click the filter icon on the toolbar and revise your filter.