To understand the importance and utility of the ARRAYFORMULA in Google Sheets, let us first go through a fundamental concept we already know.
Many a time, We tend to use structurally similar formulas across the length of a column in a data range. In doing so, we take the advantage of ‘relative referencing’. Meaning, the Google Sheets automatically adjusts the formula if we copy and paste it in the subsequent rows. Let us consider a basic example. The data set is a list of students along with their test scores. We should now calculate the totals in column F.
The total score, for Alfred, is calculated using a simple formula “=B2+C2+D2+F2”. We copy and paste this formula in the cells below to repeat such calculations for all the students. So, for Angela, the formula automatically becomes “=B3+C3+D3+F3”. For Bob it is “=B4+C4+D4+F4”, and so on and so forth. Though this approach is seemingly convenient, there are inherent problems to doing this.
- If in the above case the data set is huge, we end up with a lot of formulas. This could bloat the spreadsheet and make it a tad slower.
- If we need to make any changes to the formula, this has to be repeated across all the formulas.
- What if a new student by name Charlotte joins the class? When we include a new row for her below row # 7, the formula isn’t copied automatically. In a sense, this approach is not dynamic enough.
The ARRAYFORMULA solves all the above mentioned problems.
- As opposed to a bunch of similar formulas that individually calculate values, we can have one single ARRAYFORMULA that processes the data in a batch.
- Since this is a single formula, we can make changes in just one place and the effect takes place across the data range.
- We can induce the dynamism that was missing with a bunch of individual formulas, in that the ARRAYFORMULA got us covered even if a new row is introduced that needs similar formula calculations.
- array_formula – this parameter can either be
- a range,
- a mathematical expression using one cell range or multiple ranges of the same size, or
- a function that returns a result greater than one cell.
Building on the previous example, in order to calculate the totals, we can use the ARRAYFORMULA as shown below. Notice that, unlike before where we’ve added individual cells, we are now adding the ranges. And we key this in the very first total cell, F2. All the following Total cells should be clear of any values or formulas, so that ARRAYFORMULA can show us the results without any errors.
To quickly fix the zeroes at the end, we change the formula in just one place (i.e. cell F2). Yet, it affects the calculations across the range, as shown below. Notice that even the expression used in the condition within the IF formula is a range A2:A.
Now, We’ll include a row for Charlotte below row # 7. Let’s find out whether the ARRAYFORMULA automatically calculates the Total for her.
Well, not surprisingly, it does. For more information on ARRAYFORMULA, please check out this link.