Working with arrays is an important skill for anyone who frequently works with spreadsheets. Often you will need to interpret them and work with functions that can make sense of them. On the other hand, you have to know how to build them. This guide will show you how.
What are arrays in google spreadsheets?
In computing terminology, an array is a collection of similar data elements that are kept close to each other in memory. A program can retrieve each data item directly using its index number. Confused? Let’s see an example.
Suppose you want to track a student’s grades in five subjects. You can create an array that will keep the pieces of data close to each other. So, the subject headings and scores are part of a data matrix.
In Google Sheets, a matrix is a table of values with columns and rows. You can use arrays in your spreadsheet to arrange cell values in a specific order. If we reimagine the subjects and grades example, the matrix will look like this.
Of course you could just write the data individually to 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 matrix in google spreadsheets
Creating an array of rows 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 in the cell where you want the array to start. This will be the cell that contains the first value, as well as the formula for the entire array.
- To start the formula, add a Same (=) sign.
- Now, to create the array, we’ll use square brackets. Add an opening bracket “{“
- Write the first element of the array. This will be the first value displayed in the first cell. In this example, the value is 1.
- Since we want the next value in the row to be added, add a comma (,) to split the values.
- Add as many values as you like, separating them with commas.
- Finally, add a closing bracket “}” to finish the formula.
- press the Get into button to apply the formula.
Make sure the last value in the formula is not a comma. Otherwise, the formula will return an #ERROR. as it is not valid data in Google Sheets.
How to create a column matrix in Google Sheets
Creating a column matrix in Google Sheets is just as easy as a row matrix and only requires you to follow a few simple steps. Essentially, you’re adding the values for each individual cell in a column, separated using a semicolon instead of commas. Still, let’s take a look at how you can do this in your spreadsheet.
Here, our goal is to create a one-dimensional array consisting of ten values.
- Click in the cell where you want to enter the formula. As with the row array, it will include the first value and the formula. It is A2 in our example.
- To start the formula, add a Same (=) sign.
- Now, to create an array, we’ll use square brackets again. Add an opening bracket “{“
- Write the first element of the array. This will be the first value displayed in the first cell. In this case, the value is 10.
- Since we want the next value to be added in the column instead of a row, add a semicolon (;) to split the values.
- Add as many values as you like, separating them with semicolons.
- Finally, add a closing bracket “}” to finish the formula.
- Press Get into.
Make sure the formula does not have a semicolon before the closing bracket, as this will result in an #ERROR!. The formula is only applied on the first cell that contains the first value in 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 of row and column arrays to display their values in a table format. Let’s see how you can use literal arrays to add student names and their grades into a 2D array in Google Sheets. Here are the steps you need to follow:
- Click in the cell where you want to enter the formula. A2 in our example.
- To start the formula, add a Same (=) sign.
- Add an opening bracket “{“
- Type the first element, which in this case is a name. Enclose the name in quotes (“) marks.
- To indicate the formula to move the next value to the next 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 semicolon (;)
- Now follow steps 4 through 7 to add as many values as you like.
- Finally, add a closing bracket “}” to finish the formula.
- Press Get into.
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 items directly to the formula without having to click and add them to cells.
Another advantage of using an array literal is that if you have raw data separated by the common comma delimiter, you can simply copy and paste the data between the brackets. Google Sheets will automatically add the data in one row. This has the additional 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 to formulas can save you a lot of time. A good example of this is sequencing in Google Sheets.
Working with arrays in Google Sheets
Now that you have a basic understanding of creating arrays in Google Sheets, you should take some time to practice your new skills to solidify your knowledge. In the future, you’ll need to memorize how they work to tackle more complex spreadsheet skills.
