The search-range argument represents a range or an array with rows to filter. It can be of any size up to the maximum number of columns in a worksheet minus one (because the first output column may additionally contain hyperlinks).

If any rows are found in the search range, FILTER() returns an array with the corresponding number of columns.

If you specify the hyperlink option (see below) and apply the Format > Hyperlink style to the first column of the returned array, this first column will contain hyperlinks to the original rows in search-range.

Thus the filtered rows or records can be easily edited. After clicking a given link (or pressing SPACE) and completing editing of the source data, click the back bookmark toolbar button (or press Alt+F11) to return to browsing results in the same place.

The filters argument represents a range or an array with filtering expressions for the subsequent columns in the search-range. The number of cells in filters must not exceed the number of columns in search-range. The n-th cell position in filters is the filter expression for the n-th column in search-range. If there is no filter for a given column, the corresponding filter cell may be left empty.

Entering filters in worksheet cells is easy: you can use the Format > Search Filter format style for the desired cells. This displays formatted filters with syntax coloring for Regex expressions and graphic sort order indicators.

When editing such cells, GS-Calc displays a cell-aligned dialog box with all filter specification and sort options:

Example

The filter options are as follows:

• Cell reference - the filter text is a cell reference (for the between filter type: two references)
• Allow empty matches - required by Regex if you want to use expressions like \\A\\z
• Match case
• Word sort order - when sorting and searching for duplicates ignore punctuation and non-word characters
• Sorting and sort key index - for single-column sorting it is always 1st key; for multi-column sorting you must specify 2nd, 3rd, ... 6th key indices. If indices are repeated, FILTER() returns the #VALUE! error code.

Note: The duplicates filters are handled slightly differently than other filters. The options parameter enables you to specify whether searching for duplicates should be performed before or after all other filters.

You can also specify filters directly as text cells, for example if you want to generate them using formulas.

The hyperlink-path argument represents a worksheet or workbook path that will be used along with cell references if the include hyperlinks option is specified.

Examples:

sheet1
folder1\\sheet1
c:\\documents\ \[sample.gsc]sheet1

In the example screen above, the hyperlink path is left empty because both the data (A1:H100000) and the result (=FILTER(A1:H100000, ...)) are in the same worksheet.

The options argument is a number and can be 0 or a combination (sum) of the following values:

• 1 - include hyperlinks to the original search-range rows in the first column of the FILTER() results
• 2 - perform searching for duplicates after all other filters; by default duplicates are searched first
• 4 - by default FILTER() does not create calculation chains with formulas placed in search-range for speed reasons; if some column in search-range contains formulas and you want FILTER() to execute after they are updated, add 4 to options. This will result in significantly slower filtering.

The default value is 0.

Note: If you are using FILTER() for very large data sets and expect to change options frequently, use a cell reference for options instead of a hard-coded value. Re-editing the FILTER() formula cell may activate Undo for most of the target range and cause delays before filtering.

The optional empty-string argument represents a number or a string that FILTER() should return if no matching rows are found. If omitted, FILTER() returns the #N/A! error code.