Contact
12 programs
Explore the site

FILTER in Collabora Office: Conditions, Arrays and Empty Results

Array Intermediate Collabora Office

Filter complete rows in Collabora Office Calc 26.04 with reproducible condition masks, multi-condition examples, empty-result handling and array-entry cautions.

Verification status: See the evidence and test scope described in this guide. How formula examples are checked

FILTER builds a result from records that satisfy a condition. Unlike hiding rows with the sheet’s AutoFilter command, it produces a separate formula result. That makes it useful for a report area that should reflect the source data without rearranging the original table.

This guide follows the Collabora Office 26.04 FILTER documentation. The worked results come from manually evaluating the supplied records; no native Collabora run is claimed.

Availability and version history

  • First introduced: not established. The verified evidence is documentation in the 26.04 Help set, not a release note identifying introduction.
  • Current documented status: supported in Collabora Office Calc 26.04 Help, checked October 13, 2026.
  • Last supported version: no retirement endpoint established. 26.04 is the verified documentation scope, not a final working release.
  • Platform and edition: desktop Calc workflow. Collabora Online and mobile require their own deployed-version check.
  • Native test status: not executed. Automatic spill, resizing and exact error presentation were not tested in a Collabora application.

Syntax and the shape of a condition

=FILTER(range; criteria; [return_if_empty])

range is the block to return records from. criteria is a Boolean array indicating which rows or columns qualify. For row filtering, its row count must match the input block’s row count. For column filtering, its column count must match the block’s column count. The optional third argument supplies a fallback when nothing qualifies.

For example, A2:C6 has five rows. A row condition based on B2:B6 produces five decisions. B2:B5 would be one row short. Including the header in one range but not the other is a common source of an invalid shape.

The official FILTER example explicitly uses an array formula. Do not type displayed braces {} yourself. In desktop Calc, select the intended output area, enter the formula, and use Ctrl+Shift+Enter for array entry. This guide does not assume that every deployed Collabora build automatically spills or expands like Excel.

Enter the example data

Use A1:C6 for this input. Enter Qty values as numbers, not strings with apostrophes or unit suffixes. Keep the formula output in column E or later.

Row A: Item B: Region C: Qty
1 Item Region Qty
2 Pen East 12
3 Pad West 8
4 Box East 5
5 Tape East 0
6 Folder West 15

Example 1: all East records

For this known dataset, select E2:G4, a three-row by three-column destination, and enter:

=FILTER(A2:C6;B2:B6="East";"No matches")

Confirm using the documented array-formula workflow. The condition decisions, in source-row order, are TRUE, FALSE, TRUE, TRUE, FALSE.

Expected result: three data rows. The column labels below are explanatory; FILTER does not return an extra header row because A1:C1 is outside the input range.

Item Region Qty
Pen East 12
Box East 5
Tape East 0

Zero quantity does not make a row disappear unless a quantity condition says so. FILTER is evaluating the Region column here. It also preserves source order; filtering is not sorting.

Example 2: two conditions joined with AND

Suppose the report should contain East records with positive stock. A transparent approach is to build the condition in a helper column instead of relying on an unexplained array arithmetic shortcut.

  1. Put Include in D1.
  2. Enter this in D2 and fill it down through D6:
=AND(B2="East";C2>0)
  1. The expected D2:D6 values are TRUE, FALSE, TRUE, FALSE, FALSE.
  2. Select E9:G10 and enter:
=FILTER(A2:C6;D2:D6;"No matches")

Expected result: the Pen row (East, 12) followed by the Box row (East, 5). The Tape row is excluded because its quantity is zero.

The helper-column method is easy to audit: if the report contains the wrong row, inspect that row’s Boolean value before changing FILTER. For an OR rule, replace the helper formula with an appropriate OR expression. Keep the helper outside the returned A:C block unless the extra Boolean column is intentionally part of the report.

Example 3: a parameter-driven report

Enter West in J2 and a minimum quantity of 10 in J3. In D2, use:

=AND(B2=$J$2;C2>=$J$3)

Fill D2 through D6 and select a one-row, three-column output area for the current test:

=FILTER(A2:C6;D2:D6;"No matches")

Expected result: Folder | West | 15. The dollar signs keep J2 and J3 fixed while the row references move during fill-down.

Change J2 to East and J3 to 5. The matching records become Pen and Box, so the output now needs two rows. In a deployment using fixed array ranges, adjust the output array area appropriately. Do not promise automatic resizing without testing that build.

Example 4: no records qualify

=FILTER(A2:C6;B2:B6="South";"No matches")

Expected result: No matches. This is a text fallback, not a table with a numeric zero. If downstream formulas expect a three-column table, decide how they should handle that alternate shape. A fallback message helps a reader, but it does not create a blank record with the same schema.

Troubleshooting

Symptom Check first
A range or array error Does the criteria array have one decision per source row, without an extra header?
Unexpected missing records Inspect criteria values row by row; distinguish >0 from >=0.
Text quantities fail comparisons Confirm that imported values are real numbers and clean the source explicitly.
Old or partial output Check array-entry method, output dimensions and recalculation.
A no-results message appears unexpectedly Inspect spelling, spaces and the parameter cells before changing the fallback.

An error in the criteria calculation is not the same as “no matches.” Diagnose a broken helper expression on its own. The third argument is intended for an empty filtered result, not as a general replacement for every formula error.

Compatibility and checking the result

The official Help assigns COM.MICROSOFT.FILTER and says FILTER is outside OpenFormula 1.3. Check recalculation in the receiving application before treating an ODS or XLSX transfer as successful. Avoid assuming Google Sheets’ argument structure or Excel’s spill behavior is interchangeable with this documented Calc workflow.

A small acceptance test should record the application build and verify: three East rows; two East rows with positive Qty; one West row at or above 10; and the no-match fallback. Save a copy, reopen it, and recalculate when performing a future native test. None of those native steps is claimed to have been completed here.

Official reference