google-spreadsheetUsing arrays in Google Sheets

Syntax

  • ={item1,item2}
  • ={item2,item2;item3,item4}
  • ={{item1;item2},{item3,item4;item5,item6}}

Parameters

ParameterDetails
itemNIt could be a value, a cell reference, a range reference or a function

Remarks

Overview

An array of literals is written between curly brackets. Separators depends on the spreadsheet's regional configuration settings.

  • To separate columns, if the decimal separator is . use , but if the decimal separator is , then use \.
  • To separate rows use ;.

Official Documentation

Google Docs editors Help

Array of literals

Formula in A1

={"Item name","Quantity";"Apples",2;"Blueberries",5}

Important: In certain countries the comma is used as a decimal separator (e.g: €1,00). If that's your case, you would need to use backslashes ( \ ) instead: (Docs)

={"Item name"\"Quantity";"Apples"\2;"Blueberries"\5}

Result

RowAB
1Item nameQuantity
2Apples2
3Blueberries5

Returning a range as an array

RowAB
1FruitApple
2WeekdayMonday
3AnimalDog

Formula on C1

={A1:A3}

Result

RowC
1Fruit
2Weekday
3Dog

Alternative formula

=ARRAYFORMULA(A1:A3)

Append column with row numbering

A1:A4 have A,B,C,D.
B1 have the following formula:

=ARRAYFORMULA({A1:A4,ROW(A1:A4)})

Result

ABC
1AA1
2BB2
3CC3
4DD4