How to Create Arrays in Google Sheets
Working with arrays is an important skill for anyone who frequently works with spreadsheets. Often, you’ll 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 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:
Ensure the last value in the formula isn’t a comma. Otherwise, the formula will return an #ERROR! as it’s notvalid 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.
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:
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 isbuilding 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.
With ARRAYFORMULA, you may replace a series of functions with just one in a selected row and column. Here’s how to use it in Google Sheets.
You can’t call this offline, Notion.
Taming data is easier than it looks.
Anyone with more than a passing interest in motorsports must see these films.
I found my TV was always listening—so I shut it down.
Unlock a world of entertainment possibilities with this clever TV hack.
