How To Create Arrays In Google Sheets (a.k.a. Array Literals)

Arrays in Google Sheets are collections of data, consisting of rows and columns. You can use arrays in formulas in the same way that you use regular A1-type ranges.

You construct arrays in Google Sheets with curly brackets: `{ }`

They’re also known as ARRAY LITERALS.

How To Create Arrays In Google Sheets

Row Array

The formula to create this array, in A1, is:

`= { 1 , 2 , 3 }`

The opening and closing curly brackets denote the array.

Commas separate the data into columns.

(Note, if you’re a European user, you use a backslash as the column separator. Read more about syntax differences in your formulas due to your Google Sheets location.)

Column Array

Now consider this column array:

where the formula is:

`= { 1 ; 2 ; 3 }`

Here, semi-colons create new rows in the array.

Two Dimensional Array

Next, consider this example:

This formula, entered into cell A1, creates a 3 by 2 array that puts data in the range A1:B3:

`= { "A" , 1 ; "B" , 2 ; "C" , 3 }`

You can see the combination of curly brackets to create the array, commas to create columns, and semi-colons to create new rows.

Array Literal Notes

You’re not limited to numbers or strings inside the array literals. You can also nest formulas inside the curly brackets, even array formulas. And when you combine formulas inside array literals, you unleash their full power.

Moreover, you can use the outputs of arrays as inputs to other formulas. See below for examples.

Note, there are also other ways to create array outputs, for example, the SEQUENCE function can generate row, column, or 2-d ranges of data.

Examples Of Arrays In Google Sheets

Now that you’ve seen how to create arrays, let’s look at how to use them for practical applications in formulas.

Return Multiple VLOOKUP Columns

With a regular VLOOKUP formula, you’re restricted to returning a single column.

However, if you nest an array literal as the column index, you can return multiple columns at once.

Have a look at this example:

Notice the array literal nested inside the VLOOKUP, shown in red:

`=ArrayFormula(VLOOKUP(\$A\$14,\$A\$1:\$G\$9,{2,3,6,7},FALSE))`

This VLOOKUP will return results from columns 2, 3, 6, and 7 simultaneously.

Combining Formulas in Arrays in Google Sheets

One of the differences between the powerful QUERY function and a Pivot Table, is that the QUERY function doesn’t add a total row by default.

But you can use these arrays in Google Sheets to easily add a dynamic total row.

Essentially, you combine the results of the QUERY function with a SUM function:

`= { QUERY ; "TOTAL" , SUM(range) }`

You can also use arrays in Google Sheets to create default values for cells in Google Sheets.

The array literals create a formula that spills into the adjacent cell. This property is used to create a default value.

Let’s see an example.

In a blank Sheet, write the value “Input” in cell A1. In cell B1, type this formula:

`={"",100}`

Your Sheet will look like this:

Try typing 200 in cell C1, over the top of the 100.

Cell C1 will show the 200, but cell B1 now displays a #REF! error.

Now, delete the value you just typed in cell C1. The error message disappears and the default value of 100 is displayed again.

Finally, hide column B so that the #REF! error is never seen, and you have a default value of 100 set for cell C1.

5 thoughts on “How To Create Arrays In Google Sheets (a.k.a. Array Literals)”

1. Dave says:

The array laterals within Vlookups are super helpful so thank you for sharing. They work really well for user defined tables in Dashboards, especially so if you add an additional Lookup within the array.

Eg:
=ArrayFormula(iferror(vlookup(A1:A10,Data,{vlookup(B1:B10,DateLookUp,2,0)},0),””))

The embedded lookup brings up an array of results from a look up table (in this case dates) to bring back certain column refs. This could also be combined with INDEX/MATCH to bring back the column results the user would like. For example in an organisation the user could select employee age, experience and salary. Then it will look for those columns and bring back the specified from the data behind the scenes. It would array vertically across all the employees, which in turn could be a dynamic list based on other criteria such as team, or department.

Super powerful function so cheers for highlighting it for us!

1. I’m having a hard time visualizing how this works, Dave. Would you be willing to share a copy of your spreadsheet, or a short video screencast showing how this works?

2. kris says:

Thanks for the great explanation about arrays.
But is there maybe a way to call these created array from a string.
The idea is to let user select the required data on personal base to create it a personalised dataset.

When I copy ” { studeertijden!A:A \ studeertijden!F:F \ studeertijden!G:G } ” and add “=” , it works fine.

SCREENSHOT : https://i.postimg.cc/tJn6qyqN/Schermafbeelding-2022-10-29-om-06-46-27.png
remark : europe – \ [backslash] instead of , [comma]