STATS_PAIRWISE – Keep only complete rows across columns in Google Sheets™
What it does
Keeps only the complete rows: a row is removed when any column of the range has no valid value in it (an empty cell, the sign ".", or a value of the other type than its column).
The columns stay the same — as many columns come out as go in, only rows are removed. Use it to give several variables the same observations before an analysis.
The function takes only the range: to clean some columns, pass a range with just those columns (there is no column filter).
Syntax
=STATS_PAIRWISE(columns)Parameters
columns — required
Cell range with data, e.g., A1:D100 or A:D.
Usually the first row should contain column names.
You can also add labels as the first row and column names as the second row.
Labels should use formats like "male:1,female:2" or "low:1:3,mid:3:5,high:5:7".
Examples:
A1:D100— use 4 columnsA1:C200— use 3 columnsA:F— use 6 columnsA1:D100 where row 1 contains labels and row 2 contains column names— use labels from the sheet
If every non-empty cell of the first row holds at least two label rules ("low:1,high:2"), it is the labels row, and the next row holds the column names; an empty cell there is a column without labels.
One rule in a cell ("yes:1") is read as a name, so a header such as "T:1", "Ratio 1:2" or "Note:" stays the names row. Give such a column a second rule, or pass one label in the labels parameter, where one rule is valid.
If the first row contains ordinary text, it is treated as the column names row as before.
A number, a date or a checkbox in the names row is a name: 2025, 2025-01-01, TRUE.
Column names must be unique: two identical headers are an error "Duplicate column name: x".
Every column with values needs a name: an empty header cell over values is an error "The name of column 2 is empty: a column with values needs a name", even when colFilter does not select that column. A column with neither a name nor values (the empty right edge of a range such as A:Z) is dropped without an error.
Column names that start with "#" are reserved/unsupported in filters, because "#" is used for index-based colFilter rules (for example #1, #2-#5).
What counts as a number (the same in every function):
- A column is numeric when it holds more numbers than text; with as many texts as numbers, or more, it is a text column.
- Every cell is typed by itself: a number written as text ('10', '007') is the number, a checkbox is 1 (checked) or 0 (unchecked). The texts 'TRUE'/'FALSE' stay text.
- A cell that does not match the type of its own column (text in a numeric column, a number in a text column) is missing for calculations and is counted in missing.
- An empty cell is a missing answer; the sign "." means "not applicable" — it is not a missing answer, not a category and not counted in missing.
- A text column with value labels (a labels row above the names) is analysed by the codes of its labels. A text column without labels is not analysed as numbers: lists of columns skip it with a note, tests on a pair of columns and models show an error.
An empty tail of the range (rows below the data) is dropped — only blank cells make it: a row of "." at the end stays and counts as not applicable, as in the middle. A column with neither a name nor values is dropped; a named empty column stays.
Examples
- — the sample data (50 rows) all examples use
- — the rows of A:F where every column has a value
=STATS_PAIRWISE(Data!A1:T51)
What the result contains
2D array: the first row holds the column names, then the complete rows. Numbers written as text become numbers, checkboxes 1 and 0.
A named column that is empty in every row removes every row: leave such columns out of the range.
About STATS_PAIRWISE
STATS_PAIRWISE keeps only the complete rows of a range. A row is removed when any of its columns has no valid value: an empty cell, the sign "." (not applicable), or a value of the other type than its column, such as text in a numeric column. This is listwise deletion: afterwards all variables share the same observations, which is what you want before comparing or combining them in an analysis.
The columns stay the same — as many columns come out as go in, only rows are removed. The function takes only the range and has no column filter: to clean some of the columns, pass a range with just those columns. In the result, numbers written as text become numbers, and checkboxes become 1 and 0. The result recalculates by itself when new responses arrive from Google Forms.
One thing to watch: a named column that is empty in every row removes every row, because no row is complete. Leave such columns out of the range.