What Are Arrays in Google Sheets?
In computer terminology, an array is a collection of similar data elements kept close to one another in memory. A program can retrieve each data element directly by using its index number. Confused? Let’s look at an example.
Suppose you wish to keep track of a student’s grades across five subjects. You can create an array that will keep the data pieces close to one another. So, the subject headings and the scores are part of an array of data.
In Google Sheets, an array is a table of values with columns and rows. You can use arrays in your spreadsheet to arrange the values of the cells together in a specific order. If we imagine the example of subjects and scores again, the array will look something like this.
Of course, you could just type the data individually into the sheet, and it would still count as an array. But here, we will cover how to build an array from a single cell.
How to Create a Row Array in Google Sheets
Creating a row array is very simple. Let’s take a look at how you can do this in your sheet. Here are the steps you need to follow:
Click on the cell where you want the array to start. This will be the cell containing the first value, as well as the formula for the whole array. To start off the formula, add an Equal (=) sign. Now, to create the array, we will use curly brackets. Add an opening curly bracket “{“ Type in the first element of the array. This will be the first value shown in the first cell. In this example, the value is 1. As we want the next value to be added in the row, add a comma (,) to divide the values. Add as many values as you desire while separating them using commas. Finally, add a closing curly bracket “}” to finish the formula. Press the Enter button to apply the formula.
Ensure the last value in the formula isn’t a comma. Otherwise, the formula will return an #ERROR! as it’s not valid data in Google Sheets.
How to Create a Column Array in Google Sheets
Creating a column array in Google Sheets is just as easy as a row array and only requires you to follow a few simple steps. Essentially, you are adding the values for each individual cell in a column, separated using a semi-colon instead of commas. Still, let’s take a look at how you can do this on your spreadsheet.
Here, we aim to create a one-dimensional array consisting of ten values.
Click on the cell where you want to enter the formula. Just like with the row array, it will include the first value and the formula. It’s A2 in our example. To start the formula, add an Equal (=) sign. Now, to create an array, we will again use curly brackets. Add an opening curly bracket “{“ Type in the first element of the array. This will be the first value shown in the first cell. In this case, the value is 10. As we want the next value to be added in the column rather than a row, add a semi-colon (;) to divide the values. Add as many values as you desire while separating them using semi-colons. Finally, add a closing curly bracket “}” to finish the formula. Press Enter.
Ensure the formula doesn’t have a semi-colon before the closing curly bracket, as this will result in a #ERROR!. The formula is only applied in the first cell that contains the first value of the array, so if you need to make any changes to the formula or add new values to your array, do so by making changes to the first cell.
How to Create a Two-Dimensional Array in Google Sheets
Creating a two-dimensional array combines the steps for row and column arrays to show your values in a table format. Let’s look at how you can use array literals to add student names and their marks in a 2D array in Google Sheets. Here are the steps you need to follow:
Click on the cell where you want to enter the formula. A2 in our example. To start the formula, add an Equal (=) sign. Add an opening curly bracket “{“ Type in the first element, which in this case is a name. Type in the name enclosed in quotation (“) marks. To signify the formula to move the next value into the following cell in the same row, add a comma (,) Add the numerical figure for the marks. To move the next value to the cell below, add a semi-colon (;) Now follow steps 4 to 7 to add as many values as you like. Finally, add a closing curly bracket “}” to finish the formula. Press Enter.
Why Do This Instead of Just Putting the Data Into Individual Cells?
Using this method to create an array makes it flexible, as you can add new elements directly to the formula without needing to click and add them to the cells.
Another advantage of using an array literal is that if you have raw data separated using the common comma delimiter, you can simply copy and paste the data into the curly brackets. Google Sheets will automatically add the data into a row. This has the added advantage that you won’t need to copy and paste the data individually.
As your skills progress, and you write longer and more complex formulas, being able to add array values directly into formulas can save you a lot of time. One good example of this is building sequences in Google Sheets.
Working With Arrays in Google Sheets
Now that you have a basic understanding of building arrays in Google Sheets, you should take some time to practice your new skills to cement your knowledge. In the future, you’ll need to memorize how they work to tackle more complex spreadsheet skills.
