Use Advanced Filter in LibreOffice Calc

LibreOffice Calc provides the Advanced Filter tool to help you query and filter large datasets or database records based on complex, multi-column criteria. This guide outlines how to configure a criteria range, apply the Advanced Filter dialog, and output the filtered results within your spreadsheet.

1. Prepare the Filter Criteria Range

Unlike the Standard Filter, the Advanced Filter reads filtering conditions directly from cells in your sheet.

  1. Copy the column headers from your main data table that you want to filter by.
  2. Paste these headers into an empty area of the sheet (for example, above or to the side of your data).
  3. Enter your filter conditions directly beneath the copied headers:
    • AND Logic: Place criteria in the same row under different headers (e.g., Department = “Sales” AND Salary > 50000).
    • OR Logic: Place criteria in different rows under the headers (e.g., Department = “Sales” on row 1, Department = “Marketing” on row 2).
    • Operators: You can use comparison operators such as >, <, >=, <=, <>, or exact text matches.

2. Open the Advanced Filter Dialog

  1. Click any cell inside your database or dataset, or select the entire data range including the headers.
  2. Navigate to the top menu and select Data.
  3. Hover over More Filters and click Advanced Filter….

3. Configure the Filter Settings

  1. In the Read filter criteria from section, select the criteria range you created in Step 1 (including the criteria headers). You can click the shrink icon to select the cells with your mouse.
  2. Click the Options expander button to configure additional settings:
    • Case sensitive: Distinguishes between uppercase and lowercase text.
    • Range contains column labels: Ensures Calc treats the first row as headers.
    • Copy results to: Check this box and specify a destination cell if you want to keep the original data intact and display the filtered results elsewhere.
    • No duplications: Excludes duplicate rows from the filtered output.
    • Enable regular expressions / Wildcards: Allows patterns such as .* or ? for partial text matching.

4. Apply the Filter

Click OK. LibreOffice Calc will evaluate the criteria and instantly hide rows that do not match (or display the matching subset at the specified output location).

5. Remove or Reset the Filter

To restore the full view of your data: 1. Click anywhere within the filtered table. 2. Go to Data > More Filters > Reset Filter (or Remove Filter).