If you regularly perform complex filters, you may prefer to make use of the Advanced Filter option. It allows you to store a set of criteria which you can re-use.
Below we have a database and to the side we have set up some cells containing the criteria. Note that the criteria has exactly the same headings as the database. In I2 row we have written SP002 and >500 in the various product sales. The full criteria is in I1 to M3.
Excel will interpret this as you want to find all rows that
- have SP002 in the Sales Person Code Column, and
- Product A sales are bigger than 500 and
- Product B sales are bigger than 500 and
- Product C sales are bigger than 500 OR
- There is SP003 in the Sales Person Code Column
When you use the Advanced filter, Excel will filter the rows and only leave you with the rows that meet all your criteria. So below, note that all the SP002 have values bigger than 500, but the SP003’s have a variety of values.
Next month when you get new data you can just point at it and rerun the criteria. If you have used the normal filter you would need to manually work through the columns to set it up.
To see the full course contents click here.