Core navigation & data handling — quiz

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.

Why prefer a Table over a plain range for a pivot source?

1. It looks better
2. It expands automatically as rows are added
3. It compresses the file
4. It enables VBA

Answer: It expands automatically as rows are added — Structured references grow with the data, so downstream pivots and charts pick up new rows.

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.

Cell protection takes effect when:

1. Cells are marked Locked
2. The sheet is protected
3. The file is saved
4. A password is set on the file

Answer: The sheet is protected — Locked is only an attribute; it does nothing until Protect Sheet is applied.

Why build a pivot table on a Table rather than a range?

1. It is faster
2. The Table expands, so new rows are included automatically
3. It uses less memory
4. Ranges cannot be pivoted

Answer: The Table expands, so new rows are included automatically — A fixed range misses rows added later.

=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.

Custom format #,##0,,"M" displays 1250000 as:

1. 1,250,000
2. 1M
3. 1,250K
4. 1.25

Answer: 1M — Two commas divide by a million.

SUM returns 0 over cells that clearly contain numbers. Cause?

1. Wrong formula
2. The numbers are stored as text
3. Hidden rows
4. Circular reference

Answer: The numbers are stored as text — Text to Columns → Finish is the quickest fix.