Complex Filters

You can narrow your view of the data that appears on your worksheet by setting complex filters using multiple operators.

Note
Applying filters creates a temporary view of the worksheet. Filters are not kept if you merge, append, aggregate, duplicate, pivot, unpivot, or refresh the data. If you share a worksheet with other users, the filters are not shared. A filter becomes invalid when you change the data type or delete the column to which it is applied.

To apply a filter using multiple conditions, perform these steps:

  1. Click the Filter icon in the upper left hand corner of your worksheet, select a column header and click the Filter icon from the menu, or select a column and click a distinct column value to add a filter.
  2. Click the + icon that appears under the Filter icon to add a new filter.
  3. Select a column in the list of choices that appear.
  4. Select a value to use as a filter on the column.
  5. Select an operator.
    • If you select AND or OR:

      1. Click the + icon that appears under the Filter icon.

      2. Select a column and value to combine with the first filter.

      3. Repeat the previous two steps to join as many filters as you wish . All of these filters must use the same operator.

    • If you select COMPLEX:

      1. Use the filter tree diagram to create and manipulate your complex filters. You can do the following using the tree:

        • Click the + sign in the tree to select a column to filter on and choose a value.

        • Drag and drop filter values within the tree to rearrange the order of operations.

        • Create a new tree branch by clicking a connecting branch.

          Note
          Creating a new branch automatically uses the opposite of the operator previously used. For example, if you have an existing filter that uses the AND operator between two values, and then you add a new branch, the new operator is an OR by default. You can change this using the drop-down list of operators.

      2. (Optional) To change the view of the tree diagram:

        • Use the tree diagram icons in the upper right-hand corner to view the tree from right to left or left to right

        • Use the zoom icons in the upper right-hand corner, mouse gestures, or a scroll wheel to zoom in or out of the tree diagram

        • Use the reset icon in the upper right-hand corner to reset the tree to its default starting view.

      3. When you have adjusted the tree diagram to your desired complex filter configuration, click Filter.

    The filtered results appear in your worksheet. You can edit the filters by clicking the pencil icon next to the filter operator.