ARRAYFORMULA function in Google Sheets - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the ARRAYFORMULA function do in Google Sheets?

ARRAYFORMULA lets you apply a formula to a whole range of cells at once, instead of writing the formula in each cell separately.

beginner

How do you write an ARRAYFORMULA to add two columns A and B for all rows?

Use =ARRAYFORMULA(A1:A + B1:B) to add each value in column A to the corresponding value in column B for all rows.

intermediate

Can ARRAYFORMULA handle logical comparisons like checking if values in column A are greater than 10?

Yes! For example, =ARRAYFORMULA(A1:A > 10) returns TRUE or FALSE for each cell in column A depending on the condition.

beginner

What happens if you use ARRAYFORMULA with a single cell reference like =ARRAYFORMULA(A1)?

It just returns the value of that single cell. ARRAYFORMULA is most useful when working with ranges, not single cells.

beginner

Why might you use ARRAYFORMULA instead of copying a formula down many rows?

ARRAYFORMULA saves time and keeps your sheet cleaner by applying one formula to many rows automatically, avoiding manual copying.

What is the main purpose of ARRAYFORMULA in Google Sheets?

A Create charts automatically

B Apply a formula to a range of cells at once

C Sort data alphabetically

D Format cells with colors

ARRAYFORMULA applies a formula to an entire range, saving time and effort.

Which formula correctly sums columns A and B for all rows using ARRAYFORMULA?

A =ARRAYFORMULA(SUM(A1:A, B1:B))

B =SUM(A1:A, B1:B)

C =ARRAYFORMULA(A1:A + B1:B)

D =A1 + B1

ARRAYFORMULA(A1:A + B1:B) adds each pair of cells row by row.

What will =ARRAYFORMULA(A1:A > 5) return?

A Sum of all values greater than 5

B The number 5 repeated

C Error because ARRAYFORMULA can't do comparisons

D TRUE or FALSE for each cell in A1:A depending on if the value is greater than 5

ARRAYFORMULA can handle logical comparisons and returns TRUE or FALSE for each cell.

If you want to multiply column A by 2 for all rows, which formula is correct?

A =ARRAYFORMULA(A1:A * 2)

B =A1:A * 2

C =MULTIPLY(A1:A, 2)

D =ARRAYFORMULA(MULTIPLY(A1, 2))

ARRAYFORMULA(A1:A * 2) multiplies each value in column A by 2.

What is a benefit of using ARRAYFORMULA instead of dragging a formula down?

A It automatically updates for new rows

B It changes cell colors

C It deletes empty rows

D It creates pivot tables

ARRAYFORMULA automatically applies to new rows without needing to drag formulas.

Explain how ARRAYFORMULA can simplify working with multiple rows in Google Sheets.

Think about how you would add two columns without ARRAYFORMULA.

Describe a real-life example where ARRAYFORMULA would save time and effort.

Imagine you have a list of sales and want to calculate commission for each sale.