Array formulas basics in Excel - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is an array formula in Excel?

An array formula is a special formula that can perform multiple calculations on one or more sets of values, returning either a single result or multiple results.

beginner

How do you enter an array formula in Excel?

You enter an array formula by typing the formula and then pressing Ctrl + Shift + Enter instead of just Enter. Excel will show the formula enclosed in curly braces {}.

beginner

What does the curly braces {} around a formula mean in Excel?

Curly braces around a formula mean it is an array formula. You cannot type these braces manually; Excel adds them when you enter the formula with Ctrl + Shift + Enter.

beginner

Give a simple example of an array formula that sums the product of two ranges A1:A3 and B1:B3.

The formula is =SUM(A1:A3*B1:B3). Enter it with Ctrl + Shift + Enter. It multiplies each pair of cells and sums the results.

beginner

What is the difference between a regular formula and an array formula?

A regular formula works on single values or cells, while an array formula can work on multiple values or ranges at once, performing calculations on each element.

How do you correctly enter an array formula in Excel?

A Press Enter

B Press Ctrl + Shift + Enter

C Press Shift + Enter

D Press Ctrl + Enter

Array formulas must be entered by pressing Ctrl + Shift + Enter to work properly.

What do curly braces {} around a formula indicate?

A The formula is an array formula

B The formula is a comment

C The formula is a text string

D The formula is an error

Excel shows curly braces around formulas entered as array formulas.

Which of these formulas is an example of an array formula?

A =A1+B1

B =SUM(A1:A3)+SUM(B1:B3)

C =SUM(A1:A3*B1:B3)

D =AVERAGE(A1:A3)

The formula =SUM(A1:A3*B1:B3) multiplies arrays and sums the result, requiring array formula entry.

What happens if you type an array formula but press only Enter?

A The formula works as a regular formula, usually incorrect

B The formula works as an array formula

C Excel shows an error

D Excel automatically converts it to array formula

Without Ctrl + Shift + Enter, the formula is treated as regular and may give wrong results.

Array formulas can return:

A No results

B Multiple results

C Only one result

D Either one or multiple results

Array formulas can return a single value or multiple values depending on the formula.

Explain what an array formula is and how to enter it in Excel.

Think about formulas that work on many cells at once.

Describe a simple use case where an array formula is helpful.

Imagine you want to multiply prices by quantities and add them up.