Spreadsheet
Practice questions
A sheet prints with its last two columns on a separate page. Which setting fixes this most directly?
- Freeze the first row
- Scale the sheet to fit all columns on one page (and use landscape)
- Apply a filter
- Turn on AutoFill
Answer and explanation
Answer: B
Scaling to one page wide, usually together with landscape orientation, brings all columns onto the same page. Freezing and filtering affect the screen and the rows shown, not the page width.
A chart must show a company's monthly profit for three years so that rises and falls are easy to see. Which chart fits best?
- Line chart
- Pie chart
- Bar chart sorted by profit
- Pie chart for each month
Answer and explanation
Answer: A
A trend over time is a line chart's job; months run along the horizontal axis. Pie charts show shares of one total, and sorting by profit would destroy the time order.
C5 holds =SUM(C1:C5). What is the problem?
- #DIV/0!, because C5 starts empty
- #NAME?, because SUM needs two ranges
- A circular reference: the formula includes its own cell
- Nothing; it totals C1 to C5 correctly
Answer and explanation
Answer: C
The range includes C5, so the formula depends on its own result. Excel warns about a circular reference and Calc shows Err:522. Use =SUM(C1:C4) instead.
You want rows 1 to 3 to stay visible, but no columns. Which cell do you select before freezing?
- A3
- A4
- B4
- D1
Answer and explanation
Answer: B
The rows above the selected cell are frozen, and the columns to its left. A4 has rows 1-3 above it and no columns to its left.
A1 holds 01-01-2024 and B1 holds 31-12-2024. What does =B1-A1 return?
- 366
- 364
- 12
- 365
Answer and explanation
Answer: D
2024 is a leap year with 366 days, but subtracting the first day from the last counts the gaps between them: 366 − 1 = 365.
Marks are in B2:B6 and results (Pass/Fail) in C2:C6. Which formula adds the marks of only the students who passed?
- =COUNTIF(C2:C6,"Pass")
- =SUM(B2:B6,"Pass")
- =SUMIF(C2:C6,"Pass",B2:B6)
- =IF(C2:C6="Pass",SUM(B2:B6))
Answer and explanation
Answer: C
SUMIF checks the condition in C2:C6 and adds the matching cells of B2:B6. COUNTIF would only count how many passed.
F2 holds 59.99. What does =IF(F2>=60,"B",IF(F2>=40,"C","D")) show?
- B
- D
- C
- #VALUE!
Answer and explanation
Answer: C
59.99 is less than 60, so the first test is false; it is at least 40, so the second test is true and the result is C.
A1:A3 hold 12, 0 and 7. What does =ROUND(AVERAGE(A1:A3),1) show?
- 9.5
- 6.3
- 6.4
- 19
Answer and explanation
Answer: B
Zero is a number and is counted: (12 + 0 + 7) ÷ 3 = 19 ÷ 3 = 6.333..., which rounds to 6.3. Leaving out the zero would wrongly give 19 ÷ 2 = 9.5.
Which action leaves a formula's references exactly as they were?
- Cutting it and pasting it in another cell
- Copying it and pasting it in another cell
- Dragging its fill handle down
- Filling it right across a row
Answer and explanation
Answer: A
Moving a formula (cut and paste) keeps it pointing at the same cells. Copying in any form, including the fill handle, shifts its relative references.
B8 holds =SUM(B2:B5)/$B$7. It is copied to E8. What formula is in E8?
- =SUM(B2:B5)/$E$7
- =SUM(E2:E5)/$E$7
- =SUM(B2:B5)/$B$7
- =SUM(E2:E5)/$B$7
Answer and explanation
Answer: D
Copying 3 columns right moves the relative range B2:B5 to E2:E5. $B$7 is absolute in both parts, so it stays $B$7.
B2 holds =A$1*$B2. It is copied to D5. What formula is in D5?
- =A$1*$B5
- =C$1*$B5
- =C$4*$D5
- =C$1*$D2
Answer and explanation
Answer: B
The move is 2 columns right and 3 rows down. In A$1 the row is fixed and the column moves from A to C: C$1. In $B2 the column is fixed and the row moves from 2 to 5: $B5.
A price of 2400 must be increased by 15% GST. Which formula gives 2760?
- =2400*(1+15%)
- =2400+15%
- =2400*15%
- =(2400+15)%
Answer and explanation
Answer: A
Adding 15% means multiplying by 1.15: 2400 × 1.15 = 2760. =2400+15% adds only 0.15, and =2400*15% gives the tax alone, 360.
What does =2+3*2^2 show?
- 20
- 38
- 14
- 100
Answer and explanation
Answer: C
Power first: 2^2 = 4. Then 3 × 4 = 12. Then 2 + 12 = 14.
What does =100-20/4*2 show?
- 40
- 97.5
- 80
- 90
Answer and explanation
Answer: D
Division and multiplication first, left to right: 20 ÷ 4 = 5, 5 × 2 = 10. Then 100 − 10 = 90.
E2 holds 12 and F2 holds the text 15 kg. What does =E2+F2 show?
- #VALUE!
- 27
- 12
- #NAME?
Answer and explanation
Answer: A
15 kg is text, which cannot be added to a number, so the result is #VALUE!. With 15 alone in F2 the sum would be 27.
A2 holds 300 and B2 holds 0. What does =IF(B2=0,"N/A",A2/B2) show?
- #DIV/0!
- 0
- 300
- N/A
Answer and explanation
Answer: D
The test B2=0 is true, so IF returns the value-if-true, N/A, and the division is never done.
In Microsoft Excel, which single key creates a chart of the selected data on a new chart sheet?
- F11
- F5
- F4
- F2
Answer and explanation
Answer: A
F11 creates a chart sheet in Excel (Alt+F1 puts a chart on the current sheet). F4 cycles references, F2 edits a cell and F5 opens Go To.
C2 holds =B2*2. You delete the whole column B. What does the formula now show?
- #NAME?
- 0
- #REF!
- #DIV/0!
Answer and explanation
Answer: C
The cell the formula referred to no longer exists, so the reference is broken and the result is #REF!.
D2 holds =IF(A2>=40,Pass,Fail). What does it show?
- Pass or Fail, depending on A2
- #NAME?
- #VALUE!
- #REF!
Answer and explanation
Answer: B
Text results must be in double quotes. Without them, Pass and Fail are read as names that do not exist, so the cell shows #NAME?. The fix is =IF(A2>=40,"Pass","Fail").
A cell already holds 7. You then apply the Percentage format with no decimals. What does it show?
- 7%
- 0.07%
- 70%
- 700%
Answer and explanation
Answer: D
The percentage format shows the stored value × 100. The stored value is 7, so it shows 700%. To get 7%, type 7% or enter 0.07.