COLLATE function
Combines multiple arrays or ranges into a single array
Harry Lewis avatar
Written by Harry Lewis
Updated over a week ago

Velixo recommends the use of Microsoft 365 for this feature. Experimentally, Excel 2010 or higher may be used. See the Prerequisites for additional information.


The COLLATE function combines multiple arrays or ranges into a single array.

Any valid Excel reference is supported. Including:

  • An Excel 365 spill range (ex: A1#)

  • A standard range (ex: N13:N15)

  • An individual cell (ex: N15)

  • A direct value (ex: "Hello")

  • An inline array of rows or columns:

    • Rows are separated by semicolons: {"Row A" ; "Row B"}

    • Columns are separated by commas: {"Column A" , "Column B"}

The arrays and ranges do not have to contain the same number of columns; the resulting array will be as wide as the widest range passed to COLLATE()


  COLLATE(Array [or range], Array [or range]) 


The COLLATE function uses the following parameter:




Arrays [or ranges]


The arrays and/or ranges to be combined

(note that, by nature, the function uses multiple arrays and/or ranges)


Example #1

Let us consider an Excel 365 worksheet with two arrays (one defined in cell A1, the other defined in cell A13):


Using the COLLATE function...


...we can combine them into one array:


Example #2

Given those same arrays, plus some additional data in column N:


Using the COLLATE function, we can combine all of those, plus additional data:

=COLLATE(A1#,A13,N13:N15,"A single value",{"Row A";"Row B"},{"Column A","Column B"})

Did this answer your question?