Bird
Raised Fist0
Excelspreadsheet~5 mins

Array formulas basics in Excel - Cheat Sheet & Quick Revision

Choose your learning style10 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash
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.
Click to reveal answer
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 {}.
Click to reveal answer
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.
Click to reveal answer
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.
Click to reveal answer
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.
Click to reveal answer
How do you correctly enter an array formula in Excel?
APress Enter
BPress Ctrl + Shift + Enter
CPress Shift + Enter
DPress Ctrl + Enter
What do curly braces {} around a formula indicate?
AThe formula is an array formula
BThe formula is a comment
CThe formula is a text string
DThe formula is an error
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)
What happens if you type an array formula but press only Enter?
AThe formula works as a regular formula, usually incorrect
BThe formula works as an array formula
CExcel shows an error
DExcel automatically converts it to array formula
Array formulas can return:
ANo results
BMultiple results
COnly one result
DEither one or multiple results
Explain what an array formula is and how to enter it in Excel.
Think about formulas that work on many cells at once.
You got /3 concepts.
    Describe a simple use case where an array formula is helpful.
    Imagine you want to multiply prices by quantities and add them up.
    You got /3 concepts.

      Practice

      (1/5)
      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
      Recommended

      Ready to prove it?

      Pass the test and this pattern moves unknown strong while earning +1 Grip toward your certificate.

      10 questions~5 mineasy hard