EXCEL CRASH COURSE EXAM FROM WALL STREET PREP – EXAM QUESTIONS
AND CORRECT ANSWERS (VERIFIED ANSWERS) PLUS RATIONALE | 2027 Q&A |
INSTANT DOWNLOAD PDF
1. Which Excel feature is most useful for keeping column and row headings visible
while scrolling through a large worksheet?
A. Conditional Formatting
B. Data Validation
C. Freeze Panes
D. Flash Fill
Rationale: Freeze Panes keeps selected rows or columns visible as you navigate through a
worksheet. The other features serve different purposes, such as formatting, restricting entries, or
recognizing data patterns.
2. A financial analyst wants to quickly move to cell XFD1048576 in a worksheet.
Which Excel feature provides the most direct method?
A. Name Box
B. Format Painter
C. Status Bar
D. Quick Access Toolbar
Rationale: The Name Box, located beside the formula bar, can be used to enter a cell reference
and jump directly to that location. The other features do not provide direct cell-navigation
functionality.
3. A cell contains the formula =B5*C5. When the formula is copied one row
downward, what will Excel normally change the formula to?
A. =B5*C5
B. =$B$5*$C$5
C. =B6*C5
D. =B6*C6
,Rationale: B5 and C5 are relative references, so both row references increase by one when the
formula is copied down one row.
4. What is the primary purpose of an absolute cell reference such as $B$5?
A. To convert the value into text
B. To keep both the column and row reference fixed when copying a formula
C. To prevent the cell from being edited
D. To automatically format the cell as currency
Rationale: Dollar signs make the referenced column and row absolute. Therefore, $B$5 remains
unchanged when a formula containing it is copied.
5. An analyst needs to display a percentage with one decimal place. Which Excel
formatting approach is most appropriate?
A. General formatting
B. Date formatting
C. Percentage formatting with one decimal place
D. Text formatting
Rationale: Percentage formatting displays the underlying value as a percentage and allows the
analyst to control the number of displayed decimal places.
6. A workbook contains formulas that should reference a fixed tax-rate assumption
stored in cell B2. Which reference is most appropriate when copying the formula
across multiple cells?
A. B2
B. B$2
C. $B2
D. $B$2
Rationale: $B$2 locks both the column and row, ensuring that every copied formula continues to
reference the same tax-rate assumption.
7. Which function returns the largest numeric value in a specified range?
,A. MAX
B. LARGECOUNT
C. HIGH
D. TOP
Rationale: MAX returns the largest value in a range. LARGE can also identify high-ranking
values but requires a specified rank, while the other choices are not standard Excel functions.
8. An analyst wants to determine how many cells in a range contain numbers.
Which function should be used?
A. COUNTA
B. SUM
C. COUNT
D. NUMBERS
Rationale: COUNT counts cells containing numeric values. COUNTA counts non-empty cells
regardless of whether they contain numbers or text.
9. Which function counts cells that meet a specified condition?
A. COUNT
B. COUNTIF
C. COUNTA
D. SUM
Rationale: COUNTIF counts cells within a range that satisfy one specified criterion, making it
useful for conditional counting.
10. A sales analyst wants to calculate total revenue only for transactions associated
with the region "West." Which function is most directly suited to this task?
A. COUNTIF
B. AVERAGE
C. SUM
, D. SUMIF
Rationale: SUMIF adds values that meet a specified condition. In this case, it can sum revenue
associated with the West region.
11. What does the IF function fundamentally allow an Excel model to do?
A. Sort a worksheet alphabetically
B. Create a PivotTable
C. Return different results depending on whether a logical condition is met
D. Convert formulas into values
Rationale: IF evaluates a logical test and returns one result when the condition is TRUE and
another when it is FALSE.
12. An analyst wants a formula to return "Review" whenever a calculated value is
greater than 100, and "OK" otherwise. Which function structure is appropriate?
A. SUM
B. MATCH
C. IF
D. INDEX
Rationale: IF is designed for conditional logic and can return different text depending on
whether the calculated value exceeds 100.
13. A model uses several conditions that must all be satisfied before a transaction is
classified as eligible. Which logical function is most appropriate for testing
whether every condition is TRUE?
A. OR
B. IFERROR
C. NOT
D. AND
Rationale: AND returns TRUE only when all supplied logical conditions evaluate to TRUE. OR
instead requires only one condition to be TRUE.
AND CORRECT ANSWERS (VERIFIED ANSWERS) PLUS RATIONALE | 2027 Q&A |
INSTANT DOWNLOAD PDF
1. Which Excel feature is most useful for keeping column and row headings visible
while scrolling through a large worksheet?
A. Conditional Formatting
B. Data Validation
C. Freeze Panes
D. Flash Fill
Rationale: Freeze Panes keeps selected rows or columns visible as you navigate through a
worksheet. The other features serve different purposes, such as formatting, restricting entries, or
recognizing data patterns.
2. A financial analyst wants to quickly move to cell XFD1048576 in a worksheet.
Which Excel feature provides the most direct method?
A. Name Box
B. Format Painter
C. Status Bar
D. Quick Access Toolbar
Rationale: The Name Box, located beside the formula bar, can be used to enter a cell reference
and jump directly to that location. The other features do not provide direct cell-navigation
functionality.
3. A cell contains the formula =B5*C5. When the formula is copied one row
downward, what will Excel normally change the formula to?
A. =B5*C5
B. =$B$5*$C$5
C. =B6*C5
D. =B6*C6
,Rationale: B5 and C5 are relative references, so both row references increase by one when the
formula is copied down one row.
4. What is the primary purpose of an absolute cell reference such as $B$5?
A. To convert the value into text
B. To keep both the column and row reference fixed when copying a formula
C. To prevent the cell from being edited
D. To automatically format the cell as currency
Rationale: Dollar signs make the referenced column and row absolute. Therefore, $B$5 remains
unchanged when a formula containing it is copied.
5. An analyst needs to display a percentage with one decimal place. Which Excel
formatting approach is most appropriate?
A. General formatting
B. Date formatting
C. Percentage formatting with one decimal place
D. Text formatting
Rationale: Percentage formatting displays the underlying value as a percentage and allows the
analyst to control the number of displayed decimal places.
6. A workbook contains formulas that should reference a fixed tax-rate assumption
stored in cell B2. Which reference is most appropriate when copying the formula
across multiple cells?
A. B2
B. B$2
C. $B2
D. $B$2
Rationale: $B$2 locks both the column and row, ensuring that every copied formula continues to
reference the same tax-rate assumption.
7. Which function returns the largest numeric value in a specified range?
,A. MAX
B. LARGECOUNT
C. HIGH
D. TOP
Rationale: MAX returns the largest value in a range. LARGE can also identify high-ranking
values but requires a specified rank, while the other choices are not standard Excel functions.
8. An analyst wants to determine how many cells in a range contain numbers.
Which function should be used?
A. COUNTA
B. SUM
C. COUNT
D. NUMBERS
Rationale: COUNT counts cells containing numeric values. COUNTA counts non-empty cells
regardless of whether they contain numbers or text.
9. Which function counts cells that meet a specified condition?
A. COUNT
B. COUNTIF
C. COUNTA
D. SUM
Rationale: COUNTIF counts cells within a range that satisfy one specified criterion, making it
useful for conditional counting.
10. A sales analyst wants to calculate total revenue only for transactions associated
with the region "West." Which function is most directly suited to this task?
A. COUNTIF
B. AVERAGE
C. SUM
, D. SUMIF
Rationale: SUMIF adds values that meet a specified condition. In this case, it can sum revenue
associated with the West region.
11. What does the IF function fundamentally allow an Excel model to do?
A. Sort a worksheet alphabetically
B. Create a PivotTable
C. Return different results depending on whether a logical condition is met
D. Convert formulas into values
Rationale: IF evaluates a logical test and returns one result when the condition is TRUE and
another when it is FALSE.
12. An analyst wants a formula to return "Review" whenever a calculated value is
greater than 100, and "OK" otherwise. Which function structure is appropriate?
A. SUM
B. MATCH
C. IF
D. INDEX
Rationale: IF is designed for conditional logic and can return different text depending on
whether the calculated value exceeds 100.
13. A model uses several conditions that must all be satisfied before a transaction is
classified as eligible. Which logical function is most appropriate for testing
whether every condition is TRUE?
A. OR
B. IFERROR
C. NOT
D. AND
Rationale: AND returns TRUE only when all supplied logical conditions evaluate to TRUE. OR
instead requires only one condition to be TRUE.
