Use the Filter action to filter data from a column in a specific table or worksheet.

Considerations

An error is displayed if any of the following conditions are encountered when you deploy your automation to filter data from table or worksheet:
  • If you provide an incorrect table name or column name/position.

    For example, the table in Excel is named MonthlySales, but you enter Monthly Sales (with space).

  • The Session name that you have provided does not match the one used in the Open action.
  • Excel spreadsheet name or cell range that you have specified does not exist.

    For example, you enter Sheet2 when only Sheet1 exists, or enter B2:D4 when the sheet only has columns A–C.

  • There is data type mismatch between filter type and column data.

    For example, column May in the specified table contains text values like High, Low, but you use Greater than 150.

  • The file that you are trying to access is closed, locked, or inaccessible.
  • There is a conflict between the existing filters and new filter settings.
The filter results are not stored as a variable and reflect directly on the specified Microsoft Excel spreadsheet.

Procedure

  1. Double-click or drag the Filter action from the Excel node in the Actions palette.
  2. Click one of the following tabs:
    • Table
      1. Table name: to specify the name of the table where you want to filter data.
        Note: The table name is a string value and NOT a data table name. For information on how to retrieve the Excel table name, see Rename an Excel table.
      2. Use the Filter for field to specify the column of the table where want to filter the data:
        • Column name: To specify the name of the column.
        • Column position: To specify the position of the column.
        For example, you have a table called Monthly sales that contains three columns: April, May and June. If you want to filter the data of the column May in the table, enter May in the Column name field or enter 2 in the Column position field.
    • Worksheet
      1. Worksheet name: to specify the name of the worksheet where you want to filter data.
      2. Select the Remove existing filter from the sheet check box if you want to remove any existing filters in the specified worksheet.
      3. Use the Cell range drop-down menu to specify one of the following cell ranges:
        • All cells: to filter data across all the cells in the worksheet.
        • Specific: enter the range for the cells in the Range field.

          For example, you have a worksheet that contains five columns: A, B, C, D and E. If you want to filter data from the second to fourth column, enter B2:D4 in the Range field.

      4. Use the Filter for field to specify the column of the table where you want to filter the data:
        • Column name: To specify the name of the column.
        • Column position: To specify the position of the column.
  3. Use the Filter field to select one of the following tabs:
      • Number: If the column you have specified contains number data.
        Click the drop-down menu to select one of the following filters:
        • Equals: Filters the data that is equal to the value you have specified.
        • Does not equal: Filters the data that is not equal to the value you have specified.
        • Greater than: Filters the data that is greater than the value you have specified.
        • Greater than or equal to: Filters the data that is greater than or equal to the value you have specified.
        • Less than: Filters the data that is less than the value you have specified.
        • Less than or equal to: Filters the data that is less than or equal to the value you have specified.
        • Between: Filters the data that is between the two values you have specified.
      • Text: If the column you have specified contains textual data.
        Click the drop-down menu to select one of the following filters:
        • Begins with: Filters the data that begins with the value you have specified.
        • Ends with: Filters the data that ends with the value you have specified.
        • Contains: Filters the data that contains the value you have specified.
        • Does not contain: Filters the data that does not contain the value you have specified.

        From the Monthly sales table example mentioned above, consider a scenario where you want to only view data that is greater than the target number of 150 in the May column. So in Step 3, you select Column name and enter May. You can then use the Number > Greater than option and enter 150 in the field to filter the column values.

  4. Enter the name of the session used to open the workbook with the Open action.

    You can also click the Variable tab and select an existing Excel advanced session variable.

Consider a scenario where there is an Excel workbook titled Monthly Sales.xls and you want to filter the May column and only display the rows that contain value greater 150.
  1. Use the Open action from the Excel package to open your workbook.
  2. Assign the session name as ExcelSession1.
  3. Drag and drop the Filter action into the automation script.
    1. In the Table > Table name, enter Monthly Sales.
    2. Select Filter for > Column name and enter May.
  4. Use the Filter field to select the following:
    1. In the Filter type, select Number.
    2. Click the drop-down menu and select Greater than.
    3. In the value field, enter 150.
  5. In the Session name field, enter ExcelSession1.
  6. Click Save.

When you run this automation, the Excel sheet will only display the rows where May is greater than 150.