How To Use ARRAYFORMULA in Google Sheets

ARRAYFORMULA in Google Sheets

You rarely want to apply a formula to a single cell. Usually, you want to use them across a row or column and apply them to many cells.

One solution is dragging the formula down to apply it to the whole column. Google Sheets automatically adjusts the cell references, so you don’t have to manually update each of them.

But this is still uncomfortable dragging formulas down across hundreds or even thousands of cells. Having so many functions also can make your sheet work slower.

Furthermore, if you want to change the formula, you have to do the whole dragging again to change it across the whole column.

But not if you use an ARRAYFORMULA because it apples the formula to the whole column!

What is the ARRAYFORMULA?

The ARRAYFORMULA function can apply a formula to an entire column.

It converts your original formula into an array, thus allowing you to use the same formula across multiple rows by writing only a single formula. You only need to put a formula in the first cell and define the size of the array.

The way you can use an ARRAYFORMULA is:

=ARRAYFORMULA(array_formula)

This means that the ARRAYFORMULA function is a wrapper function. You can wrap your formulas with it to apply it to a whole range of cells.

When to use ARRAYFORMULA for marketing stuff?

• Applying formulas across large data ranges.
• Being lazy more efficient.

How To Use The ARRAYFORMULA in Google Sheets

In order to work with it, you need to change the single cell references in the original function. Instead of cell references, you would have to pass column or row references.

As an example, let’s see how can you use an ARRAYFORMULA to copy a formula down an entire column. Go back to the example when we counted the number of characters in article titles.

The original function looked like this:

=LEN(A2)

And then we dragged down this function to apply it to all of the titles. In the last row (9th row), the function was as follows:

=LEN(A9)

Now, to avoid having a function in every cell of the column, you can wrap this function in an ARRAYFORMULA and change the single-cell reference to a column reference in the following way:

=ARRAYFORMULA(LEN(A2:A7))

Only the first cell (B2) has a function and it fills the whole array.

Since the input of the function contains array references, its output is an array as well. You need to ensure having sufficient empty area next to your cell containing the ARRAYFORMULA in order to fully expand its results.

In order to work with all the future entries, you can modify the function in the following way:

=ARRAYFORMULA(LEN(A2:A))

This time the array will extend itself until the end of the column which means an infinite array.

The only drawback is that the function will be executed for the empty rows as well, leaving a lot of zeros in this case.

Meanwhile, the advantage is that it doesn’t need any modifications when you insert new values to the sheet. You can choose from the two solutions which one fits your sheet better

Alternatively, you can nest an IF function within this ARRAYFORMULA to avoid these infinite zeros or error. The inside content of an ARRAYFORMULA function can be anything that can be applied to an array, even very complex formulas.

More ARRAYFORMULA Examples

Because you can wrap most formulas in an ARRAYFORMULA, it’s really useful for speeding up marketing work in spreadsheets!

For example, say you have a list of keywords and their ranking position. You want to categorise these to show you keywords that are ranking in positions 1 – 10 in the SERPs.

To do that, you would use this formula:

=IF(B2:B15<=10,"Top 10","Nope")

This IF formula will look at the ranking positions (COLUMN B) and label them “Top 10” if they are in position 1 – 10 and “Nope” if they are beyond the top 10.

Now, instead of dragging this formula down to evaluate all your rankings, you can just wrap it in an ARRAY.

Here’s what that same formula looks like now:

=ARRAYFORMULA(IF(B2:B15<=10,"Top 10","Nope"))

As you can see, it applies the same labels to the whole range. No more dragging down. No more copy and pasting formulas.

Summary

Once you got the hand of using the ARRAYFORMULA, you’ll wonder how you ever made it this far without it.

Bye bye dragging down + copy/pasting formulas.