Array formulas basics in Excel - Cell-by-Cell Formula Trace

Concept Flow

[Select range A1:A3] → [Select range B1:B3] → [Multiply each pair element-wise] → [Return array result]

The array formula takes two ranges of cells, multiplies each corresponding pair of values, and returns an array of results.

Formula

{=A1:A3 * B1:B3}

This is an array formula entered with Ctrl+Shift+Enter in Excel, multiplying each element in A1:A3 by the corresponding element in B1:B3.

Step-by-Step Trace

Step	Operation	Values in A1:A3	Values in B1:B3	Result Array
1	Select range A1:A3	[10, 20, 30]	-	-
2	Select range B1:B3	-	[5, 15, 25]	-
3	Multiply element-wise	[10, 20, 30]	[5, 15, 25]	[50, 300, 750]

The final result is an array of products for each pair of cells.

Variable Tracker

Variable	Value
A1:A3	[10, 20, 30]
B1:B3	[5, 15, 25]
Result	[50, 300, 750]

Key Moments

Why do we need to enter the formula with Ctrl+Shift+Enter?

What happens if the ranges A1:A3 and B1:B3 are not the same size?

Can array formulas return multiple results in multiple cells?

Sheet Trace Quiz - 3 Questions

What does the array formula {=A1:A3 * B1:B3} return?

A Only the product of the first pair

B The sum of all products

C An array of products for each pair of cells

D An error

Key Result

Array formulas in Excel allow you to perform calculations on multiple values at once, returning arrays of results. They require special entry (Ctrl+Shift+Enter) and operate element-wise on ranges of the same size.

Transcript

In this example, we have two columns of numbers. The array formula multiplies each number in column A by the corresponding number in column B. We select the ranges A1:A3 and B1:B3, then multiply them element-wise. The result is an array of products: 50, 300, and 750. To enter this formula correctly, you press Ctrl+Shift+Enter, which tells Excel to treat it as an array formula. If you don't, Excel will only calculate the first product. This is a simple way to perform multiple calculations at once without writing separate formulas for each cell.

Practice

1. What does an array formula in Excel do?
=SUM(A1:A3*B1:B3) is an example of what kind of formula?

easy

A. It only sums values in one column

B. It creates a chart from data

C. It calculates multiple values at once and returns a single result

D. It formats cells automatically

Solution

1. Step 1: Understand what array formulas do

   Array formulas perform calculations on multiple values or ranges at the same time instead of one cell at a time.

2. Step 2: Analyze the example formula

   The formula =SUM(A1:A3*B1:B3) multiplies each pair of cells from A1:A3 and B1:B3, then sums all results together, showing a single total.

3. Final Answer:

   It calculates multiple values at once and returns a single result -> Option C

4. Quick Check:

   Array formula = multiple calculations at once [OK]

Hint: Array formulas handle many values together, not just one [OK]

Common Mistakes:

• Thinking array formulas only work on one cell
• Confusing array formulas with charts or formatting
• Not realizing multiplication happens element-wise

2. Which of these is the correct way to enter an array formula in older Excel versions (before dynamic arrays)?

easy

A. Type the formula and press Ctrl + Shift + Enter

B. Type the formula and press Enter

C. Type the formula and press Alt + F4

D. Type the formula and press Ctrl + C

Solution

1. Step 1: Recall how array formulas were entered before dynamic arrays

   Older Excel versions required a special key combination to tell Excel the formula is an array formula.

2. Step 2: Identify the correct key combination

   The correct way was to press Ctrl + Shift + Enter after typing the formula, which adds curly braces around it.

3. Final Answer:

   Type the formula and press Ctrl + Shift + Enter -> Option A

4. Quick Check:

   Old array formula entry = Ctrl + Shift + Enter [OK]

Hint: Use Ctrl+Shift+Enter for array formulas in old Excel [OK]

Common Mistakes:

• Pressing only Enter for array formulas in old Excel
• Using unrelated shortcuts like Alt+F4 or Ctrl+C
• Not knowing the difference between old and new Excel

3. Given the data:
A1=2, A2=3, A3=4
B1=5, B2=6, B3=7
What is the result of the formula =SUM(A1:A3*B1:B3) entered as an array formula?

medium

A. Error

B. 19

C. 12

D. 56

Solution

1. Step 1: Multiply each pair of cells element-wise

   Calculate 2*5=10, 3*6=18, 4*7=28.

2. Step 2: Sum all the products

   Add 10 + 18 + 28 = 56.

3. Final Answer:

   56 -> Option D

4. Quick Check:

   Sum of products = 56 [OK]

Hint: Multiply pairs then sum for array multiplication [OK]

Common Mistakes:

• Adding values before multiplying
• Multiplying sums instead of element-wise
• Expecting a range output instead of a single sum

4. You entered the formula =A1:A3+B1:B3 in a single cell without pressing Ctrl+Shift+Enter in Excel 2016. What will happen?

medium

A. Excel sums all values and shows a single number

B. Excel returns a #VALUE! error

C. Excel spills the results into multiple cells automatically

D. Excel shows the sum of A1 and B1 only

Solution

1. Step 1: Understand Excel 2016 array formula behavior

   Excel 2016 does not support dynamic arrays, so it cannot spill results automatically.

2. Step 2: Check what happens without Ctrl+Shift+Enter

   Entering an array formula without Ctrl+Shift+Enter causes Excel to treat it as a normal formula, which leads to a #VALUE! error when adding ranges directly.

3. Final Answer:

   Excel returns a #VALUE! error -> Option B

4. Quick Check:

   Old Excel + no Ctrl+Shift+Enter = #VALUE! error [OK]

Hint: Press Ctrl+Shift+Enter for arrays in Excel 2016 [OK]

Common Mistakes:

• Expecting automatic spilling in Excel 2016
• Thinking Excel sums all values automatically
• Assuming partial sums without array entry

5. You want to multiply two columns A1:A4 and B1:B4 and get the product for each row in C1:C4 using a single formula in C1 that spills down. Which formula should you use in Excel 365?

hard

A. =A1:A4*B1:B4

B. =SUM(A1:A4*B1:B4)

C. =ARRAYFORMULA(A1:A4*B1:B4)

D. =PRODUCT(A1:A4,B1:B4)

Solution

1. Step 1: Understand dynamic array behavior in Excel 365

   Excel 365 automatically spills array results into adjacent cells when a formula returns multiple values.

2. Step 2: Choose the formula that returns element-wise products

   The formula =A1:A4*B1:B4 multiplies each pair of cells and spills results into C1:C4.

3. Final Answer:

   =A1:A4*B1:B4 -> Option A

4. Quick Check:

   Dynamic arrays spill element-wise multiplication [OK]

Hint: Use simple range multiplication for spilling in Excel 365 [OK]

Common Mistakes:

• Using SUM which returns a single total, not array
• Using ARRAYFORMULA which is Google Sheets function
• Using PRODUCT which multiplies all values into one number