Bird
Raised Fist0
Excelspreadsheet~8 mins

Array formulas basics in Excel - Dashboard Guide

Choose your learning style10 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash
Dashboard Mode - Array formulas basics
Dashboard Goal

Understand how to use array formulas to calculate multiple results at once in Excel. This dashboard shows how to sum, multiply, and filter data using array formulas.

Sample Data
ItemPriceQuantityCategory
Apple1.210Fruit
Banana0.815Fruit
Carrot0.520Vegetable
Tomato0.912Vegetable
Orange1.18Fruit
Dashboard Components
  • Total Sales (All Items)
    Formula: =SUM(B2:B6*C2:C6)
    Explanation: Multiplies Price by Quantity for each row, then sums all results.
    Result: 1.2*10 + 0.8*15 + 0.5*20 + 0.9*12 + 1.1*8 = 12 + 12 + 10 + 10.8 + 8.8 = 53.6
  • Sales per Item
    Formula (entered in E2 and spilled down): =B2:B6*C2:C6
    Explanation: Multiplies Price by Quantity for each item, showing sales per item.
    Results:
    Apple: 12
    Banana: 12
    Carrot: 10
    Tomato: 10.8
    Orange: 8.8
  • Total Fruit Sales
    Formula: =SUM(IF(D2:D6="Fruit", B2:B6*C2:C6, 0))
    Explanation: Multiplies Price by Quantity only for items in the Fruit category, then sums.
    Result: Apple + Banana + Orange sales = 12 + 12 + 8.8 = 32.8
Dashboard Layout
+----------------------+---------------------+
| Total Sales (All)    | Total Fruit Sales   |
|        53.6          |        32.8         |
+----------------------+---------------------+
| Sales per Item (List)                      |
| Apple: 12                                 |
| Banana: 12                                |
| Carrot: 10                                |
| Tomato: 10.8                              |
| Orange: 8.8                               |
+------------------------------------------+
Interactivity

Add a filter dropdown for Category (Fruit, Vegetable). When a category is selected, the Sales per Item list and Total Sales update to show only items from that category. The Total Fruit Sales card updates only when Fruit is selected.

Self Check

If you add a filter to show only "Vegetable" category, which components update and what are their new values?

  • Total Sales (All Items) updates to sum only vegetables: Carrot (0.5*20=10) + Tomato (0.9*12=10.8) = 20.8
  • Sales per Item shows only Carrot: 10 and Tomato: 10.8
  • Total Fruit Sales updates to 0 because no fruits are shown
Key Result
Dashboard demonstrating basic array formulas to calculate total sales, sales per item, and filtered sales by category.

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