Menu
best practices question on array formulas in google spreadsheet
say i have a column of value in column A. In column B, I'm multiplying by 10. I can use an array formula for the whole range or make the first individual formula in column B and paste down.
my question is which is better to use? is the array faster to calculate? is there any benefits from a speed point of view? I have big spreadsheets, I was wondering if this would make a difference.
826
It's much, much slower. Combining VLOOKUPs with Array Formulas to return multiple column values with a single lookup is significantly slower than simply using multiple individual VLOOKUPs.
The ArrayFormula function in Google Sheets applies a single formula to every cell in your pre-defined data range. The function turns your original formula into an array, allowing you to write only one formula and use it across multiple rows.
But if you want to use them for the same task, array formulas can be a bit faster, but a bit more difficult for editing. For example, you can expand range of array formula by mouse, but you cannot shrink it etc. As a side effect, you can't easily overwrite array formula ranges.
Disadvantages of using Array Formulas To many array formulas will slow down recalculation, saving, opening and closing. Array formulas use less memory and they can eliminate the need for intermediate formulas, although they can slow the recalculation time of your spreadsheet and make it more complicated.
I really can't provide any references for this answer, it is based on anecdotal evidence and discussion with other Google Sheets users.
For a relatively simple calculation, like the one in your question, I believe the array solution has better performance for very large data sets. It also mitigates against the possibility of bumping the formula limit in Sheets (40,000, but CONTINUE functions populated by array formulae do not contribute to this count).
However, for very complicated calculations, both options really have the ability to grind the spreadsheet to a halt. Notorious examples of this are array formulae where we need to resort to string manipulation (eg CONCATENATE-ing arrays together, and SPLIT-ting them apart again). In this case, I personally would go to plan C, and write a Google Apps Script custom function, that takes an array (arrays) as an argument (arguments), uses pure Javascript to manipulate the data, and outputs an array.
183
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
