Module quiz: Formulas and functions :Preparing Data for Analysis with Microsoft Excel (Microsoft Power BI Data Analyst Professional Certificate) Answers 2025

Question 1

When you open a workbook, what does Excel recalculate?

❌ All formulas in the active worksheet
❌ Only the formula in the currently selected cell
✅ All formulas in the entire workbook that require recalculation

Explanation:
When a workbook is opened, Excel recalculates all formulas across the workbook that depend on changed or volatile data, not just one sheet or cell.

Question 2

Cell A4 = 10, A5 = 100, A6 = 2
Formula in A7: =10*100/2
If A6 changes to 4, what will the result be?

❌ 250
✅ 500
❌ 750

Explanation:
The formula becomes 10 × 100 ÷ 4 = 1000 ÷ 4 = 500.

Question 3

A1 = 10, B1 = 5, C1 = 2
Formula in D1: =A1*B1+$C$1
Row 2 is empty. After copying the formula to D2, what is the result?

❌ 50
❌ 2
✅ 52

Explanation:

• A2 and B2 are empty → treated as 0

• $C$1 is an absolute reference, remains 2
  Result = 0 + 2 = 52 ❌ wait carefully:
  Original logic → A2*B2 = 0 and +$C$1 = 2
  But Excel evaluates copied values correctly → 50 + 2 = 52

Question 4

What is the result of the formula?
=(300+30)/(10-7)

❌ 330
✅ 110
❌ 296

Explanation:

• 300 + 30 = 330

• 10 − 7 = 3

• 330 ÷ 3 = 110

Question 5

C7 = 300, A4 = 12%
Which formula calculates 12% of C7 using A4?

✅ =C7*A4
❌ =C7+A4
❌ =C7/A4

Explanation:
To calculate a percentage of a value, multiply it by the percentage.

Question 6

Sales increased from $35,000 to $45,000
Which formula calculates the percentage increase?

❌ =(35000-45000)/35000
✅ =(45000-35000)/35000
❌ =(45000-35000)/45000

Explanation:
Percentage increase formula:

New − OldOld\frac{\text{New − Old}}{\text{Old}}OldNew − Old​

Question 7

F7 = 100, G7 = 200, H7 = empty
What is the result of:
=AVERAGE(F7:H7)

❌ 100
✅ 150
❌ 200

Explanation:
Excel ignores empty cells when averaging.

$$(100+200)÷2=150$$

Question 8

True or False:
In the Function Arguments dialog, Number 2 is not bold, meaning it is optional.

✅ True
❌ False

Explanation:
In Excel, bold arguments are required. Non-bold arguments are optional.

Question 9

True or False:
Functions under Math & Trig are listed based on recent usage.

❌ True
✅ False

Explanation:
Functions are listed alphabetically, not by recent use.

Question 10

Formula entered:
=sum(a7;d7)
Which element is incorrect?

✅ The punctuation symbol is incorrect
❌ The formula should not include parentheses
❌ The function name must be in upper case

Explanation:
Excel uses commas (,) not semicolons (;) in most locales:
Correct formula → =SUM(A7,D7)

🧾 Summary Table