Array formulas basics in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants you to calculate the total sales for each product category and also find the average sales per product using array formulas.

📊 Data: You have a table with columns: Product, Category, and Sales. Each row shows a product's sales amount and its category.

🎯 Deliverable: Create array formulas to calculate total sales by category and average sales per product, and present the results clearly.

Sample Data

Product	Category	Sales
Shirt	Clothing	150
Jeans	Clothing	200
Hat	Accessories	50
Belt	Accessories	70
Shoes	Footwear	300
Socks	Footwear	80
Jacket	Clothing	250
Scarf	Accessories	60

1

Step 1: List unique product categories in a new column starting from cell E2.

Manually type Clothing, Accessories, Footwear in cells E2, E3, and E4.

Expected Result

E2=Clothing, E3=Accessories, E4=Footwear

2

Step 2: Calculate total sales for each category using an array formula in cell F2 and copy down to F4.

=SUM(IF($B$2:$B$9=E2,$C$2:$C$9,0))

Expected Result

F2=600, F3=180, F4=380

3

Step 3: Calculate average sales per product using an array formula in cell G2.

=AVERAGE($C$2:$C$9)

Expected Result

G2=145

4

Step 4: Press Ctrl+Shift+Enter to enter the array formula in step 2 so Excel treats it as an array formula.

No formula, just confirm array entry.

Expected Result

Formula in F2 shows curly braces { } indicating array formula.

Final Result

Category    Total Sales
Clothing   600
Accessories 180
Footwear   380

Average Sales per Product: 145

✓Clothing category has the highest total sales of 600.

✓Accessories have the lowest total sales of 180.

✓Average sales per product across all categories is 145.

Bonus Challenge

Create a single array formula that returns total sales for all categories in one step without listing categories manually.

Show Hint

Use UNIQUE function to get categories and SUMIF inside an array formula.

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