How to Use Curly Brackets to Create Arrays in Google Sheets

0
165
Curly Brackets to Create Arrays in Google Sheets

In Google Sheets, one can use Curly Brackets or Curly Braces to create Arrays. Array is none other than table. An array consists rows and columns, not a single cell. I know, you have plenty of queries related to Arrays in Google Sheets. With this post, I wish, I could shed some light in to the use of Curly Brackets to create arrays in Google Sheets.

Curly Brackets can do a lot in Google Doc Spreadsheet. Once learned, you can use it with many formulas where multiple ranges are acceptable.

Understand the Use of Curly Brackets in Google Sheets

Here is one example that shows why curly braces are necessary to create arrays.

Range can't reproduce in any other cells without array

You can refer a range in a formula by simply mentioning “A1:B5”. But you can’t pull or reproduce data from such ranges on any other cells without using Curly Brackets. That’s why the result shows error in the above example.

But there are exception! Google Sheets IMPORTRANGE function can produce array as the output spread across columns and rows. But the limitation is it can only use between different spreadsheet files.

Now one thing is clear. The result of the IMPORTRAGE function is an array or you can say IMPORTRANGE function in Google Sheets Creates Array. Then why curly braces require to create Array? This is because, as already told above, IMPORTRANGE function can only use to create array from an external file or two different spreadsheets, not within the same spreadsheets.

In the above example, instead of simply using “=A1:B5”, you can use curly brackets as below to pull data.

={A1:B5}

How to Use Curly Brackets to Create Arrays in Google Sheets?

Example 1:

Simply go through the below images to understand the use of Curly Braces.

simple use of curly brackets in google sheets

Example 2:

practical use of curly brackets in google sheets

From the above two examples, you may think that why this type of array creation is required. The below example is an answer to it.

Example 3

You can combine data from two different ranges along side (horizontally) or one under another (vertically) using Curly Brackets.

Horizontal data placing using Curly Brackets

Horizontal data placing using Curly Brackets

Vertical data placing using Curly Brackets

Vertical data placing using Curly Brackets

Note: In the above two formulas, you can see that punctuation marks are used to decide whether to create array in vertically or horizontally. You can use comma for horizontal array and semicolons for vertical array. But in some countries like UK, you may need to use backslash instead of comma.

At the beginning, I told you, IMPORTRANGE function can create array. But there is the limitation. We can only use this function to create array between two Google Sheets files. Also we have just learned how to create array by our own using curly brackets within sheet. Now let us know how to create or use curly braces to create array within different sheets in a same file.

To do this you should use the QUERY function in Google Sheets. Here is one example.

Use of Curly Brackets with Query

See the content in “Sheet1” and “Sheet2”. I can combine the same in “Sheet3” using a Query formula as below. See how I’ve used Curly Brackets in Google Sheets Query.

=query({Sheet1!A1:B5;Sheet2!A1:B5},”Select * where Col1 is not null”)

The above formula combine and pull data vertically and in the same way the below formula combine the data horizontally.

=query({Sheet1!A1:B5,Sheet2!A1:B5},”Select * where Col1 is not null”)

The above same technique I’ve applied to create data consolidation in Google Doc Spreadsheets that without Add-on. Hope you may check that detailed tutorial.

Hope you have learned the creation of Arrays in a single sheet, in multiple sheet tabs and two different files. That’s all now about Curly Brackets.

LEAVE A REPLY

Please enter your comment!
Please enter your name here