pbPassingBI
/
excel/exams/excel-exam-full

Excel full mock exam

12 questions sampled across all 4 modules. Timed like the real thing: answer everything, then review the explanations.

  1. You copy =$A2*B$1 from B2 to C3. What does it become?

    1. =$A2*B$1
    2. =$A3*C$1
    3. =$B3*C$2
    4. =A3*C1

    Answer: =$A3*C$1 — The column lock on A holds, the row moves to 3; the row lock on 1 holds, the column moves to C.

  2. Which error means a referenced cell was deleted?

    1. #VALUE!
    2. #REF!
    3. #N/A
    4. #NAME?

    Answer: #REF! — #REF! indicates the reference itself is no longer valid.

  3. =SUM(Sales[Revenue]) is an example of:

    1. An array formula
    2. A structured reference
    3. A named range
    4. A dynamic array

    Answer: A structured reference — Tables give structured references.

  4. Which is safer around a lookup formula?

    1. IFERROR
    2. IFNA
    3. ISERROR
    4. IFS

    Answer: IFNA — IFNA catches only #N/A, leaving genuine errors like #REF! visible.

  5. What does #SPILL! mean?

    1. The formula is wrong
    2. Something is blocking the spill range
    3. The file is too large
    4. Circular reference

    Answer: Something is blocking the spill range — The formula is valid but cannot write its results because cells in the target area are occupied.

  6. Which correctly calculates complete years between two dates?

    1. (B2-A2)/365
    2. DATEDIF(A2,B2,"y")
    3. YEAR(B2)-YEAR(A2)
    4. DAYS(B2,A2)

    Answer: DATEDIF(A2,B2,"y") — Subtraction and division mishandles leap years.

  7. Which pivot feature gives % of grand total without a helper column?

    1. Grouping
    2. Show Values As
    3. Slicers
    4. GETPIVOTDATA

    Answer: Show Values As — Show Values As offers % of total, running total, rank and difference calculations.

  8. Data has one column per month. Which transformation makes it analysable?

    1. Pivot
    2. Unpivot
    3. Merge
    4. Fill down

    Answer: Unpivot — Unpivot converts month columns into attribute/value rows suitable for pivots.

  9. A pivot grouped by Months only shows 12 rows across 3 years because:

    1. A bug
    2. Months alone pools every year — add Years too
    3. The data is wrong
    4. Wrong aggregation

    Answer: Months alone pools every year — add Years too — Select Years and Months for a time series.

  10. To highlight an entire row where C exceeds 1000, the formula rule is:

    1. =C2>1000
    2. =$C2>1000
    3. =$C$2>1000
    4. =C$2>1000

    Answer: =$C2>1000 — Locking the column with $C makes every cell in the row test column C, while the row stays relative.

  11. When is a secondary axis appropriate?

    1. Always, for clarity
    2. When the two series use genuinely different units
    3. When there are many categories
    4. Never

    Answer: When the two series use genuinely different units — Same-unit series on independent scales can imply relationships that are not real.

  12. Which skill appears most consistently in practical Excel tests?

    1. VBA class modules
    2. Lookups and pivot tables
    3. Custom chart templates
    4. Power Pivot DAX

    Answer: Lookups and pivot tables — Lookups and pivots are near-universal; the advanced tools appear at analyst level and above.