Calc spreadsheet file used in video: Right-click to download file
The advanced filter provides a way of filtering out specific records in a data set. The conditions are entered directly into the spreadsheet. Up to 8 filter conditions can be met.
First, create a criteria table with the column headers matching the table to filter exactly.
The rows below the headers specify the conditions for the filter.
In this example, the sales above $1000 between 1/18/15 and 1/20/15 for the Sales Representatives Anderson and Torby.
Two date headers are used to extract records between 2 dates.
The criteria arranged in a row is considered AND criteria (Date >=1/18/15 AND Date <=1/20/15 AND Sales Rep = Torby AND Total >1000).
Criteria arranged down a column is considered OR criteria (Sales Rep = Torby OR Sales Rep = Anderson).
Select the entire table to be filtered then open the Advanced filter dialog by going to the Data Menu - choose Filter then Advanced Filter Or use the menu shortcut:
hold down Alt and press D F A
When the Advanced Filter dialog appears, drag and highlight the Criteria table
Click Options to make the search case sensitive, supress any duplicate records. To place the results in a different area on the spreadsheet check copy results to...
then click in a cell on the spreadsheet where you want the results to appear.
Press OK
When the filter is on and the option to place the results in a different area of the page, the rows are temporarily hidden. To view hidden rows again go to the Format menu choose Row then Show or use the menu shortcut hold down Alt and press O R S