Filters are fantastic. They enable you to drill down through your data to find exactly what you’re looking for in seconds, but without having to manually search every row. Best of all, they’re non-destructive – once you’ve seen what you need to see, simply switch off the filters you’ve applied and your original table will be restored.

The way they work is by following rules, identifying which cells match the rules and which ones don’t. If a cell matches the rule, its row stays in your table; if it doesn’t, the row is hidden. You might, for example, want to show only rows where the dates are in the last two weeks, or where a name begins with ‘Chris’. Once you get the hang of them, you’ll be the master of your tables.

Step-by-step: Set rules in the Filter panel

1 Open the Filter panel

Select your table, open the Filter panel and then click the Add a Filter button. Select one of your column headers from the drop-down – we’re going for Customer in our example.

2 Add a rule

Since Customer is a text field, select the Text rules heading from the fly-out menu. To find an exact match for the name, choose ‘is’ and type a name into the space provided.

3 Apply the rule

When you hit Enter, the table will update to show just the rows containing the Customer name that you typed in. You can delete the rule using the Trash icon beside its name.

4 Edit a rule

To edit a rule, click on of the buttons to change its option. Here, we’ve set it to look for first names using ‘text starts with’, so we get every customer named ‘Chris’.

5 Replace the filter

Now for a figure filter – delete your first one. Add a filter for Profit and pick ‘in the top’ from the bottom of the Numbers rules list. Set it to 25 percent and hit Enter.

6 Add another rule

You’ll now see just the rows that have a Profit figure that’s in the top 25% of all of them. If you want to narrow that down, click the Or button and add another field.

7 Add a second filter

Since you added it with ‘Or’, the two rules are grouped into the same filter. This means either can be true for the row to be displayed. Click the Add a Filter button again.

8 Give it a rule

Now add a Numbers rule to your second filter, to identify any entries in the Discount column that are different to ‘1’, which is the full price (ie 0.8 means a 20% discount).

9 Set ‘all’ or ‘any’

Your new filter is separated from the other one with dividers in the panel. Currently, either filter can be met to show rows, but you can set this to ‘all filters’ with the top button.

In detail: the Filter panel

Click on the annotated image below to view it at full size.