Get all your live data in a spreadsheet
Actiondesk connects to your SaaS & databases.
Pull raw data, build auto-updated reports, dashboards and find the real-time information you need.
Try for free
When could you find the ARRAYFORMULA useful? To picture a very simple example, lets 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.
Thats 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! Youd 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. Lets 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 didnt 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.
Lets just check an example, it will make things much clearer.
Use case: Add calculated columns to a data set
Lets 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, wed like to calculate the number of sales of phones per month.
What most people would do in this case:
- Write the formula for the first record:
- Copy and paste the formula on all the records:
The Top 3 Problems You Will Avoid Thanks To ARRAYFORMULA
This techniques might be fine if you were to stop at this point but might cause the following problems:
1. Performance 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.
2. Painful process with the rows of your column
More importantly, if your data set gets new records added, youll have to copy paste the formula again to make sure it applies to the right number of rows. Such a pain!
- One workaround Ive often seen is to copy paste in advance the formulas in more rows than needed. That might do the work but might lead you to forget when your number of rows is finally more than the number of rows on which you pasted your formulas.
- Also, this might lead to even more performance problems.
3. Painful process with all similar columns
Lastly, if youre adding many such calculated columns, it can be cumbersome every time to write the formula, and then copy paste it down the whole column.
A Formula Example With ARRAYFORMULA
Ok, enough with the problems. Lets get to the solution:ARRAYFORMULA.
Lets 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:
- Instead of referring to an individual cell, we refer to the whole range (from C2 to the end of the sheet: C1000)
- We put ARRAYFORMULA ahead of the rest of the formula
The result is that my formula applies to the whole column. Now, we have two problems:
- What if I end up having more than 1000 rows, Ill have a problem again!
- The formula applies also to rows where I have no data. We said earlier we dont want that for performance reasons.
Lets fix this!
Apply ARRAYFORMULA To The Whole Column, Whatever The Size Of The Spreadsheet
Lets replace C1000 by C:
This way, the formula will apply to the end of the column whatever the size of the spreadsheet.
Avoid ARRAYFORMULA To Apply To Rows With No Data
We said earlier we dont want the formula to be applied to rows where we have no data for performance purposes.
To fix that, well add a condition to say that if theres not data in the range C2:C, then we should return nothing:
That gives the following formula:
Now lets 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:
Bonus Pro Tip!
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, lets consider the following dataset:
Lets imagine I want to return only the first 2 rows and the first 3 column, I can do this:
The formula is:
Now lets use the ARRAY_CONSTRAIN function to make sure our ARRAYFORMULA doesnt 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.
Access My Google Sheet To Play With ARRAYFORMULA
All right, thats it for today. Heres 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, well get to that in a different article coming soon!