When could you find the ARRAYFORMULA useful? To picture a very simple example, let’s imagine that we have 3 columns: 1st with price, 2nd with quantity and 3rd multiplying them. What do we tend to do in such cases? Write the formula in the first row and copy-paste it on the other rows.
That’s fine if you have just a few, but if you have more than a 100 rows, what a pain! Besides, it slows down your calculations. How great it would be to use a formula that could output a range of cells instead of just a single value! You’d be able to apply a formula to an entire column and add it automatically to future rows.
Actually, the Google Sheets function ARRAYFORMULA is a great way to solve this problem! Array formulas exist in Excel but Google Sheets implement them in a different and interesting way. Let’s get into it.
According to Google Sheets documentation, ARRAY FORMULA “enables the display of values returned from an array formula into multiple rows and/or columns and the use of non-array functions with arrays”.
You didn’t understand what this means? Me neither.
The way I see it, ARRAYFORMULA is a function that lets you write formulas on ranges rather than individual cells which enables lots of interesting use cases.
Let’s just check an example, it will make things much clearer.
Let’s consider the following data set:
We might want to add a column to identify the month and year of each record. That could come in handy if later, we’d like to calculate the number of sales of phones per month.
What most people would do in this case:
This techniques might be fine if you were to stop at this point but might cause the following problems:
If your data set is much bigger than the 6 rows in the example dataset and you have several such computed columns, having so many formulas might lead to performance problems in your Google Sheets.
More importantly, if your data set gets new records added, you’ll have to copy paste the formula again to make sure it applies to the right number of rows. Such a pain!
Lastly, if you’re adding many such calculated columns, it can be cumbersome every time to write the formula, and then copy paste it down the whole column.
Ok, enough with the problems. Let’s get to the solution: ARRAYFORMULA.
Let’s do the same thing as above with ARRAYFORMULA this time:
The formula to enter is:
As you can see, this is pretty much the same formula as before with two differences:
The result is that my formula applies to the whole column. Now, we have two problems:
Let’s fix this!
Let’s replace C1000 by C:
This way, the formula will apply to the end of the column whatever the size of the spreadsheet.
We said earlier we don’t want the formula to be applied to rows where we have no data for performance purposes.
To fix that, we’ll add a condition to say that if there’s not data in the range C2:C, then we should return nothing:
That gives the following formula:
Now let’s see the magic!
As I add new rows to the dataset, my “Month_array_formula” column will apply to all records without me having to do anything:
To ensure your formula will not return values for empty rows, you have another way than the one introduced before. You can use the function ARRAY_CONSTRAIN.
The function ARRAY_CONSTRAIN the number of rows and the number of columns returned by a formula that returns a range.
For example, let’s consider the following dataset:
Let’s imagine I want to return only the first 2 rows and the first 3 column, I can do this:
The formula is:
Now let’s use the ARRAY_CONSTRAIN function to make sure our ARRAYFORMULA doesn’t apply to more rows than necessary:
The formula is:
The use of COUNTA enables us to pass the number of existing records as the number of rows parameter. That way, when the number of records increases, our constraint on the number of rows will increase as well.
All right, that’s it for today. Here’s the Google Sheets that I use to show the examples. Feel free to make a copy of it and play with the formulas.
Another interesting use case of ARRAYFORMULA is to build a simple dynamic aggregation table based on a dataset.
Stay tuned, we’ll get to that in a different article coming soon!
Google Sheets QUERY function lets you easily analyze, transform and aggregate data.
Learn how to sort your data in a pivot table, ordering it in ascending or descending order in Google Sheets, or sort from smallest to largest in Excel.
The VLOOKUP formula is super useful in Google sheets. This 1-minute video is just the perfect way to get how it works.
If you want to delete your pivot table and keep a backup of the resulting data, learn it with this 1-minute video that explains it all
Learn how to automatically retrieve data from my SQL database to Google Sheets with a script - with the actual code to make it work.
Learn how to export SQL data to Excel and update the export automatically (MySQL, Microsoft SQL Server, PostgreSQL).
Learn how to make a simple automated dashboard on Google Sheets for your metrics using SPARKLINE, VLOOKUP, Data Validation and Pivot Table.