• Practice
  • 15 practice questions

Final self-assessment: spreadsheet problems

Test yourself on the whole course: 15 questions with answers explained.

If something looks wrong, please tell us.

Attempt all 15 questions first, working each one out on paper, then check your answers and read the explanations.

Final self-assessment

  1. 1. How many cells are in the range D2:G7?

    1. 18
    2. 20
    3. 30
    4. 24
    Show answer

    Answer: D. Columns D to G are 4 and rows 2 to 7 are 6, so 4 x 6 = 24 cells.

  2. 2. Which range starts at B2 and covers exactly 3 columns and 5 rows?

    1. B2:E7
    2. B2:D5
    3. B2:D6
    4. B2:C6
    Show answer

    Answer: C. Three columns from B are B, C, D; five rows from 2 are 2 to 6. So the range is B2:D6.

  3. 3. How many cells are in the range A1:B10?

    1. 20
    2. 10
    3. 12
    4. 18
    Show answer

    Answer: A. Two columns (A and B) and 10 rows: 2 x 10 = 20 cells.

  4. 4. What does =(8+4)/2*3 return?

    1. 2
    2. 14
    3. 24
    4. 18
    Show answer

    Answer: D. Brackets first: 12. Then left to right: 12 / 2 = 6 and 6 x 3 = 18.

  5. 5. A1 to A4 hold 10, 20, 30 and 40. What does =SUM(A1:A4)-AVERAGE(A1:A4) return?

    1. 25
    2. 100
    3. 70
    4. 75
    Show answer

    Answer: D. SUM = 100 and AVERAGE = 100 / 4 = 25, so 100 - 25 = 75.

  6. 6. What does =AVERAGE(4,8,12)*2 return?

    1. 12
    2. 48
    3. 16
    4. 24
    Show answer

    Answer: C. AVERAGE = (4 + 8 + 12) / 3 = 24 / 3 = 8, then 8 x 2 = 16.

  7. 7. B2 holds =$A2*B$1 and is copied to D5 (two columns right, three rows down). What does D5 hold?

    1. =$A2*D$1
    2. =$C5*D$4
    3. =$A5*B$1
    4. =$A5*D$1
    Show answer

    Answer: D. $A2: column A is locked, row moves 3 down to 5, giving $A5. B$1: row 1 is locked, column moves 2 right to D, giving D$1.

  8. 8. A tax rate is in E1. C2 holds =B2*E1 without any dollar signs and is copied to C4. What does C4 hold?

    1. =B4*E1
    2. =B2*E3
    3. =B4*$E$1
    4. =B4*E3
    Show answer

    Answer: D. Both references are relative, so both move 2 rows down: B2 to B4 and E1 to E3. This is why the rate should be written $E$1.

  9. 9. Which reference keeps its column fixed even when the formula is copied across, while its row still changes when the formula is copied down?

    1. A$1
    2. $A$1
    3. A1
    4. $A1
    Show answer

    Answer: D. In $A1 the dollar locks the column A, so it never changes; the row has no dollar and moves when copied down. A1 would change its column when copied across, A$1 locks the row and $A$1 locks both.

  10. 10. Marks 55, 72, 38, 81 and 64 are filtered to show 60 and above, then the filter is cleared. How many rows are visible now?

    1. 3
    2. 2
    3. 5
    4. 0
    Show answer

    Answer: C. Filtering only hides rows. Once the filter is cleared, all 5 rows show again.

  11. 11. The values 3.5, -2, 0 and 10 are sorted in ascending order. What is the result?

    1. -2, 0, 3.5, 10
    2. 0, -2, 3.5, 10
    3. 10, 3.5, 0, -2
    4. -2, 3.5, 0, 10
    Show answer

    Answer: A. Ascending means smallest to largest, and -2 is smaller than 0: -2, 0, 3.5, 10.

  12. 12. Which chart best shows the relationship between hours studied and marks scored by 30 students?

    1. Pie chart
    2. Line chart
    3. Scatter chart
    4. Doughnut chart
    Show answer

    Answer: C. A scatter chart shows the relationship between two numbers, here hours and marks.

  13. 13. A1 = 6, A2 = 4, A3 = 2 and A4 holds the word nil. What does =SUM(A1:A4)/COUNT(A1:A4) return?

    1. 4
    2. 3
    3. 12
    4. #VALUE!
    Show answer

    Answer: A. SUM adds only the numbers: 12. COUNT counts only the numbers: 3. So 12 / 3 = 4.

  14. 14. B1 holds 200. What does =B1*5%+B1 return?

    1. 10
    2. 210
    3. 205
    4. 1200
    Show answer

    Answer: B. 5% of 200 = 200 x 0.05 = 10, then 10 + 200 = 210.

  15. 15. A1 holds =$B$2+C3 and is copied three columns right to D1. What does D1 hold?

    1. =$E$2+F3
    2. =$B$2+C3
    3. =$B$2+F3
    4. =$B$2+C6
    Show answer

    Answer: C. $B$2 is absolute and stays. C3 is relative and moves 3 columns right to F3; the row does not change.

Questions 1-3: lesson 1 (Cells, rows, columns and ranges). Questions 4-6: lesson 2 (Formulas and basic functions). Questions 7-9: lesson 3 (Cell references: relative, absolute and mixed). Questions 10-12: lesson 4 (Sorting, filtering, charts and data tools). Questions 13-15: lesson 5 (Spreadsheets mixed practice). Revisit a lesson for the questions you missed.

Privacy · Terms · Refunds · Delivery