Array Functions

SheetX array functions help you manipulate, reshape, compare, and extract data from ranges in Google Sheets™.

These functions are designed to work alongside native Google Sheets™ functions.

Function Overview

Function	Description
SX_DIFFERENCE	Returns values that exist in one range but not the other.
SX_DROP	Removes a specified number of rows or columns from an array.
SX_EXPAND	Expands an array to a specified number of rows and columns.
SX_FIRST	Returns the first n rows or elements of a range.
SX_INTERSECT	Returns values common to two or more arrays.
SX_LAST	Returns the last n rows or elements of a range.
SX_TAKE	Returns a specified number of rows or columns from a range.
SX_TRIMRANGE	Removes empty rows and columns around the edges of a range.
SX_UNION	Combines arrays into a single array of unique values.

SX_DIFFERENCE

Returns values that exist in one array but not in another.

Syntax

=SX_DIFFERENCE(range1, range2, [range3, ...])

Arguments

Argument	Description
range1	The first range to compare.
range2	The second range to compare against.
[range3, ...]	Additional ranges to compare against.

Throws

1. When ranges specified are less than two.
2. When all specified ranges are not of the same number of columns.

Use cases

• Identify customers who have not completed a purchase.
• Find email addresses that have not yet received a message.
• Compare inventory lists to identify missing products.
• Identify new records that have not been processed.
• Detect differences between two lists of employees, transactions, or accounts.

Direction Matters

SX_DIFFERENCE(range1, range2) returns values found in the first range that are absent from the second range or subsequent ranges.

To find values in the second range that are absent from the first, reverse the arguments:

=SX_DIFFERENCE(range2, range1)

SX_DROP

Removes a specified number of rows or columns from a range.

Positive values remove rows or columns from the beginning, while negative values remove them from the end.

Syntax

=SX_DROP(range, [rows], [columns])

Arguments

Argument	Description
range	The source ra ge.
[rows]	The number of rows to remove. Positive values remove rows from the beginning; Negative values remove rows from the end. Default: 0
[columns]	The number of columns to remove. Positive values remove columns from the beginning; negative values remove columns from the end. Default: 0

Use cases

• Remove header rows before calculations.
• Exclude the last few records from an analysis.
• Remove unnecessary columns from imported data.
• Separate data from headers or other unwanted sections.

SX_EXPAND

Expands or pads a range to a specified number of rows and columns.

Additional cells are filled with a specified value, allowing you to standardize array dimensions or prepare data for further calculations.

Syntax

=SX_EXPAND(range, [rows], [columns], [pad_with])

Arguments

Argument	Description
range	The source range to expand.
[rows]	The total number of rows in the expanded range. Default: number of rows of range
[columns]	The total number of columns in the expanded range. Default: number of columns of range
[pad_with]	The value used to fill additional cells. Default: ""

Use cases

• Standardize the dimensions of arrays before combining them.
• Prepare structured datasets for comparison.
• Add placeholder values to incomplete datasets.
• Create consistent output dimensions for downstream calculations.

SX_FIRST

Returns the first n rows or elements of a range.

Syntax

=SX_FIRST(range, [n])

Arguments

Argument	Description
range	The range from which to retrieve value(s).
[n]	The number of rows or elements to return. Defaults to 1.

Throws

1. When range is not specified.
2. When n is less than one.

Use cases

• Retrieve the first few records from a dataset.
• Create dynamic previews of larger datasets.
• Extract a subset of rows for reporting.
• Retrieve the beginning of a sorted or filtered range.

SX_INTERSECT

Returns values that are common to two or more ranges.

Syntax

=SX_INTERSECT(range1, range2, [range3, ...])

Arguments

Argument	Description
range1	The first range to compare.
range2	The second range to compare against.
[range3, ...]	Additional ranges to compare against.

Throws

1. When two or more ranges are not specified.
2. When specified ranges do not same number of columns.

Use cases

• Identify customers appearing in multiple datasets.
• Find employees who are both registered and trained.
• Identify products shared between two inventory lists.
• Compare mailing lists to identify common email addresses.
• Find overlapping records across reporting periods.

SX_LAST

Returns the last n rows or elements of an range.

Syntax

=SX_LAST(range, [n])

Arguments

Argument	Description
range	The range or range from which to retrieve values.
[n]	The number of rows or elements to return. Defaults to 1.

💡 The retrieved records retain their original relative order.

Throws

1. When range is not specified.
2. When n is less than one.

Use cases

• Retrieve the most recent entries from an append-only dataset.
• Display the last few transactions in a report.
• Extract the final records from a filtered or sorted range.
• Create dynamic summaries of recent activity.

SX_TAKE

Returns a specified number of rows or columns from a range.

Syntax

=SX_TAKE(range, [rows], [columns])

Arguments

Argument	Description
range	The source range.
[rows]	The number of rows to return. Positive values take rows from the beginning; Negative values take rows from the end. Default: range's number of rows.
[columns]	The number of columns to return. Positive values take columns from the beginning; Negative values take columns from the end. Default: range's number of columns.

Use cases

• Extract a subset of records for a dashboard.
• Retrieve the first or last few transactions.
• Select specific columns from a large dataset.
• Create dynamic previews of filtered or sorted data.
• Limit the amount of data passed to other formulas.

SX_TRIMRANGE

Removes empty rows and columns from the outer edges of a range, returning only the portion of the array bounded by populated cells.

Syntax

=SX_TRIMRANGE(range, [trim_rows], [trim_cols])

Arguments

Argument	Description
range	The range to trim.
[trim_rows]	How rows should be trimed 0 - None; 1 - Trims leading blank rows; 2 - Trims trailing blank rows; 3 - Trim both leading and trailing blank rows. Default: 3
[trim_cols]	How columns should be trimed 0 - None; 1 - Trims leading blank columns; 2 - Trims trailing blank columns; 3 - Trim both leading and trailing blank columns. Default: 3

Use cases

• Clean imported datasets with unnecessary empty rows and columns.
• Prepare dynamic ranges for charts and reports.
• Reduce the need to manually adjust ranges as datasets change.
• Remove excess blank space from exported or generated data.

SX_UNION

Combines values from multiple array into a single array while returning uniques values.

Syntax

  =SX_UNION(range1, range2, [range3, ...])

Arguments

Argument	Description
range1	The first range to combine.
range2	The second range to cogainst.
[range3, ...]	Additional ranges to compare against.

Throws

1. When two or more ranges are not specified.
2. When specified ranges do not same number of columns.