pbPassingBI
/
excel/exams/excel-exam-practice

Excel skills assessment

Fifteen questions covering formulas, lookups, pivots and the data tools.

  1. You copy =$B3*C$2 from D3 to E4. Result?

    1. =$B3*C$2
    2. =$B4*D$2
    3. =$C4*D$3
    4. =B4*D2

    Answer: =$B4*D$2 — The $ locks column B and row 2; the unlocked parts move with the copy.

  2. Which function returns a sorted list of unique values matching a condition?

    1. =SORT(UNIQUE(FILTER(...)))
    2. =VLOOKUP(...)
    3. =SUMIFS(...)
    4. =INDEX(MATCH(...))

    Answer: =SORT(UNIQUE(FILTER(...))) — Dynamic array functions compose: FILTER narrows, UNIQUE de-duplicates, SORT orders.

  3. Which wrapper is safest around a lookup?

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

    Answer: IFNA — IFNA hides only #N/A, so real errors such as #REF! remain visible.

  4. A pivot needs a correct count of distinct customers. What enables it?

    1. Grouping
    2. Show Values As
    3. Loading to the Data Model
    4. A slicer

    Answer: Loading to the Data Model — Distinct Count requires the Data Model / Power Pivot engine.

  5. Which formula highlights an entire row where column C exceeds 1000?

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

    Answer: =$C2>1000 — Lock the column, leave the row relative, and apply across the full row range.

  6. You receive one CSV per month and rebuild a report manually. Best solution?

    1. Record a macro
    2. Power Query From Folder
    3. More pivot tables
    4. Data validation

    Answer: Power Query From Folder — From Folder plus Refresh replaces the manual rebuild and scales to new files.

  7. What does search_mode = -1 do in XLOOKUP?

    1. Approximate match
    2. Searches from the bottom up
    3. Returns an array
    4. Sorts results

    Answer: Searches from the bottom up — Bottom-up search returns the last matching record first — useful for most-recent lookups.

  8. Which is NOT a reason to use a Table (Ctrl+T)?

    1. Ranges expand automatically
    2. Structured references
    3. It compresses the file
    4. Filters are built in

    Answer: It compresses the file — Tables do not reduce file size; their value is dynamic ranges and readable references.

  9. Data validation can be bypassed by:

    1. Typing
    2. Pasting
    3. Sorting
    4. Filtering

    Answer: Pasting — Validation fires on entry, not on paste. Circle Invalid Data catches what slipped through.

  10. Which pivot setting shows each row as a share of its parent row total?

    1. Grouping
    2. Show Values As → % of Parent Row Total
    3. Sort descending
    4. Subtotals

    Answer: Show Values As → % of Parent Row Total — Show Values As offers parent-row percentages directly.

  11. Cell protection takes effect when:

    1. Cells are set to Locked
    2. Protect Sheet is applied
    3. The file is saved as .xlsm
    4. A password is set

    Answer: Protect Sheet is applied — Locked is only an attribute until the sheet itself is protected.

  12. Best replacement for VBA when importing and reshaping data?

    1. Office Scripts
    2. Power Query
    3. Conditional formatting
    4. Solver

    Answer: Power Query — Power Query is refreshable and maintainable, and avoids macro-enabled files.

  13. Which error indicates a misspelled function name?

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

    Answer: #NAME? — #NAME? means Excel does not recognise a name or function in the formula.

  14. What is the fastest way to find where a long formula goes wrong?

    1. Retype it
    2. Formulas → Evaluate Formula
    3. Delete and rebuild
    4. Convert to values

    Answer: Formulas → Evaluate Formula — Evaluate Formula steps through each operation showing intermediate results.

  15. Which chart choice is almost always wrong?

    1. Sorted bar chart
    2. 3-D column chart
    3. Line chart for time
    4. Scatter for correlation

    Answer: 3-D column chart — 3-D perspective distorts the values the chart exists to communicate.