Catalytic is now PagerDuty Workflow Automation

Excel: Apply filters

Use this action to filter out rows of an Excel or CSV file using criteria in one or more columns and save the result as a new file. This action supports .XLSX, .XLS, .XLSM, and .CSV files.

Use Case

  • To gather only certain information from an Excel file
  • To gather information that is dependent upon other information
  • To generate reports for various people from a single report
  • To start new Workflows from specific rows in a spreadsheet (see Excel: Start Process for Each Row)

How to configure this action

This action will create a new file in which the rows that are filtered out are completely deleted, not just hidden. The initial file will remain untouched.

There are two ways to apply filters using this action.

  1. Use the 1st & 2nd column filter fields
  2. Use the Advanced filter logic field

With the 1st & 2nd column filter fields, you can enter a string or integer and filter out all rows that don’t meet a single criteria. This is the simpler filter method and will always look for an exact match of your criteria. For example:

Example criteria Filter effect
4 This will filter for just results that equal 4
Banana This will filter for just results that equal banana
{{field-names}} This will filter for just results that equal the {{field-name}}

With the Advanced filter logic field, you can create more complex logic and filter out all rows that don’t meet multiple criteria, or you can use operators like > and <= to create complex filtering. For example:

Example filter Filter effect
columns[1] > 4 This will filter for just rows greater than 4 in column 1
columns[1] == 'banana' || columns[1] == 'pear' This will filter for just rows that equal banana or pear in column 1
columns[1] !== 'customer' This will filter for just rows that don’t equal customer in column 1
columns[1] > 4 && !IS_EMPTY(columns[2]) This will filter for just the rows where column 1 is greater than 4 and the column 2 is not empty

Restrictions with this action

Because of how Excel handles third party tools, some Excel formatting and features may not be compatible with this action. Here is a list of some features that do or do not work in Excel:

  • If your Excel file has a pivot table, the pivot table will be lost—even if it is on another sheet.

Fields for this action

  • Spreadsheet file

    • This is the Excel file you want to filter. Use a field reference to a file uploaded in a prior task or instance.
  • Sheet Name

    • The sheet name within the file. By default, Excel sheet names are “Sheet1”, “Sheet2”, etc.
  • Header row number

    • Enter the row number that contains the names of the columns. Leave blank if the file does not have a header row.
    • ✅   Heads-up: If blank, columns can only be referenced by Excel or column order reference style.
  • Column Reference Type

    • Enter the column reference type to use during configuration; Column Name, Excel, or Column Order. Subsequent fields must be configured according to the column reference type you choose.
    Column name If a header row is set, you can reference columns directly by their name in {{column-name}} format.
    Excel You can reference columns by their Excel letter, such as A, B, C.
    Column order You can reference columns by their order, such as 1, 2, 3.
    • If Header row number is not specified, only Excel or Column Order references can be used.
  • 1st column to filter

  • 1st column filter criteria

    • Filter the 1st column to filter for rows with this value. Enter the string, integer, or field reference you’d like to filter for. To find empty cells, enter IS_EMPTY.
      • The filter will be based on exact match and only rows that meet all the filter criteria are retained.
    • 💡   Tip: Other filter options are available using advanced filter logic. If advanced filter logic is used, this filter is ignored.
  • 2nd column to filter

  • 2nd column filter criteria

    • Filter the 2nd column filter criteria for rows with this value. Enter the string, integer, or field reference you’d like to filter for. To find empty cells, enter IS_EMPTY.
      • The filter will be based on exact match and only rows that meet all the filter criteria are retained.
    • 💡   Tip: Other filter options are available using advanced filter logic. If advanced filter logic is used, this filter is ignored.
  • Advanced filter logic

    • Create an advanced filter to filter out rows based on operators like > and <=. Creating an advanced filter logic is like writing a formula, like (columns[1] < 10). Columns should be referenced by the column reference type defined above.

    • To create an advanced filter, combine references to columns with different operators. See Examples of advanced filters for completed examples.

    • You can use the following operators to create a filter.

    Operator Description
    > greater than
    >= greater than or equal to
    < less than
    <= less than or equal to
    == equal to
    !== not equal to
    || or, used only to separate multiple operators
    && and, used only to separate multiple operators
    () use parentheses to create nested logic

    Use IS_EMPTY(columns[{column-number}]) to filter for empty fields and !IS_EMPTY(columns[{column-number}]) to filter for non-empty fields.

    ✅   Heads-up: All strings must be enclosed in "" quotation marks.

  • Output field name

    • To help keep output fields organized, choose a prefix that will be added to the beginning of the output field name. The name you specify will become the output field prefix for the field.

Examples of advanced filters

Example filter Filter effect
columns[1] > 4 This will filter for just rows greater than 4 in column 1
columns[1] == 'banana' || columns[1] == 'pear' This will filter for just rows that equal banana or pear in column 1
columns['customer type'] !== 'customer' This will filter for just rows that don’t equal customer in column ‘customer type’
columns[1] > 4 && !IS_EMPTY(columns[2]) This will filter for just the rows where column 1 is greater than 4 and the column 2 is not empty
columns[1] >= 5 && ( columns['Account Type'] !== "Customer" || IS_EMPTY(columns[2] ) This will filter for just the rows where column 1 is greater than or equal to 4 and where the account type is not Customer or column 2 is empty

What will this output?

This action may generate multiple fields. To help keep output fields organized, the prefix above will be added to the beginning of each of the output field names, separated by two dashes. Each field will result as:{{output-field-prefix--output-field}}. Learn more

Output fields for this action

  • Filtered Spreadsheet

    • The field where the filtered spreadsheet file is stored
  • Sheet Name

    • The sheet name in the new spreadsheet where the filtered information is stored.

Tips

  • Use an Instance Field to upload your Excel file so that it is available to every step of the Workflow. Doing so will also ensure that a file is always uploaded before your instance begins.

Sorry about that. What was the most unhelpful part?









Thanks for your feedback

We update the Help Center daily, so expect changes soon.

Link Copied

Paste this URL anywhere to link straight to the section.