Excel Crash Course Exam from Wall Street
Prep - Wall Street Prep Questions And
Answers Updated 2025
Course
Excel Crash Course from Wall Street Prep
1. Relative vs. Absolute References
Question: What is the main difference between a relative cell reference and an absolute cell
reference in Excel?
A. Relative references change when copied; absolute references remain constant
B. Relative references remain fixed; absolute references change when copied
C. Both change when copied, but only absolute references update formulas automatically
D. There is no difference
Answer: A. Relative references change when copied; absolute references remain constant
Explanation: Relative references (e.g., A1) update based on the position where they are copied,
whereas absolute references (e.g., $A$1) remain fixed no matter where they are copied.
2. Denoting Absolute References
Question: How do you denote an absolute cell reference in Excel?
A. A1
B. $A1
C. A$1
D. $A$1
Answer: D. $A$1
Explanation: Adding a dollar sign before both the column letter and row number (e.g., $A$1)
makes the reference absolute, ensuring it remains unchanged during copy‐and‐paste
operations.
3. The VLOOKUP Function
Question: What is the primary purpose of the VLOOKUP function in Excel?
A. To search for a value horizontally
B. To search for a value vertically and return a corresponding value from a specified column
C. To perform a mathematical sum
D. To create a pivot table
,Answer: B. To search for a value vertically and return a corresponding value from a specified
column
Explanation: VLOOKUP (Vertical Lookup) searches for a value in the first column of a range
and returns a value in the same row from a column you specify.
4. The IF Function
Question: What does the IF function do in Excel?
A. It sums a range of cells
B. It calculates averages
C. It tests a condition and returns one value if true and another if false
D. It counts the number of cells that meet a criterion
Answer: C. It tests a condition and returns one value if true and another if false
Explanation: The IF function uses logical tests to determine which value to return based on
whether a condition is met.
5. Calculating Averages
Question: Which function would you use to calculate the average of a range of cells?
A. SUM
B. COUNT
C. AVERAGE
D. MEDIAN
Answer: C. AVERAGE
Explanation: The AVERAGE function adds all the numbers in a range and then divides by the
count of numbers, giving you the mean.
6. Counting with Criteria
Question: Which function is used to count the number of cells that meet a specific criterion?
A. COUNT
B. COUNTIF
C. SUMIF
D. AVERAGEIF
Answer: B. COUNTIF
Explanation: COUNTIF counts the number of cells in a range that meet a given condition,
making it ideal for conditional counts.
,7. Creating Pivot Tables
Question: Which menu option in Excel allows you to create a pivot table?
A. Data > Sort
B. Insert > PivotTable
C. Formulas > AutoSum
D. Review > Protect Sheet
Answer: B. Insert > PivotTable
Explanation: The Insert tab contains the PivotTable option, which helps you summarize and
analyze large data sets quickly.
8. Named Ranges
Question: What is a named range in Excel?
A. A cell that contains text
B. A specific area of a worksheet given a name for easier reference in formulas
C. A range that is automatically updated by Excel
D. A feature that locks cells from editing
Answer: B. A specific area of a worksheet given a name for easier reference in formulas
Explanation: Named ranges allow you to assign a name to a cell or range, making formulas
easier to understand and manage.
9. The CONCATENATE Function
Question: What does the CONCATENATE function do?
A. It splits text into separate cells
B. It joins two or more text strings into one
C. It calculates the sum of numeric values
D. It sorts text alphabetically
Answer: B. It joins two or more text strings into one
Explanation: CONCATENATE (or the ampersand operator “&”) is used to combine text from
multiple cells or literal strings into a single string.
10. Auto-Fitting Columns
, Question: Which action best auto-fits a column’s width to its content?
A. Dragging the column border manually
B. Double-clicking the right border of the column header
C. Using the Format Painter
D. Pressing Ctrl+F
Answer: B. Double-clicking the right border of the column header
Explanation: Double-clicking the column’s right border automatically resizes the column to
match the widest cell content.
11. Freezing Panes
Question: How do you freeze the top row in an Excel worksheet?
A. Home > Format > Freeze Top Row
B. Data > Sort > Freeze
C. View > Freeze Panes > Freeze Top Row
D. Insert > PivotTable > Freeze Top Row
Answer: C. View > Freeze Panes > Freeze Top Row
Explanation: The Freeze Panes option under the View tab allows you to lock the top row so it
remains visible as you scroll down.
12. Financial Functions – PV
Question: Which function calculates the present value of an investment in Excel?
A. FV
B. NPV
C. PV
D. IRR
Answer: C. PV
Explanation: The PV function calculates the present value of an investment based on a series of
future payments and a discount rate.
13. Starting a Formula
Question: How do you initiate a formula in Excel?
A. With a plus sign (+)
B. With an asterisk (*)
Prep - Wall Street Prep Questions And
Answers Updated 2025
Course
Excel Crash Course from Wall Street Prep
1. Relative vs. Absolute References
Question: What is the main difference between a relative cell reference and an absolute cell
reference in Excel?
A. Relative references change when copied; absolute references remain constant
B. Relative references remain fixed; absolute references change when copied
C. Both change when copied, but only absolute references update formulas automatically
D. There is no difference
Answer: A. Relative references change when copied; absolute references remain constant
Explanation: Relative references (e.g., A1) update based on the position where they are copied,
whereas absolute references (e.g., $A$1) remain fixed no matter where they are copied.
2. Denoting Absolute References
Question: How do you denote an absolute cell reference in Excel?
A. A1
B. $A1
C. A$1
D. $A$1
Answer: D. $A$1
Explanation: Adding a dollar sign before both the column letter and row number (e.g., $A$1)
makes the reference absolute, ensuring it remains unchanged during copy‐and‐paste
operations.
3. The VLOOKUP Function
Question: What is the primary purpose of the VLOOKUP function in Excel?
A. To search for a value horizontally
B. To search for a value vertically and return a corresponding value from a specified column
C. To perform a mathematical sum
D. To create a pivot table
,Answer: B. To search for a value vertically and return a corresponding value from a specified
column
Explanation: VLOOKUP (Vertical Lookup) searches for a value in the first column of a range
and returns a value in the same row from a column you specify.
4. The IF Function
Question: What does the IF function do in Excel?
A. It sums a range of cells
B. It calculates averages
C. It tests a condition and returns one value if true and another if false
D. It counts the number of cells that meet a criterion
Answer: C. It tests a condition and returns one value if true and another if false
Explanation: The IF function uses logical tests to determine which value to return based on
whether a condition is met.
5. Calculating Averages
Question: Which function would you use to calculate the average of a range of cells?
A. SUM
B. COUNT
C. AVERAGE
D. MEDIAN
Answer: C. AVERAGE
Explanation: The AVERAGE function adds all the numbers in a range and then divides by the
count of numbers, giving you the mean.
6. Counting with Criteria
Question: Which function is used to count the number of cells that meet a specific criterion?
A. COUNT
B. COUNTIF
C. SUMIF
D. AVERAGEIF
Answer: B. COUNTIF
Explanation: COUNTIF counts the number of cells in a range that meet a given condition,
making it ideal for conditional counts.
,7. Creating Pivot Tables
Question: Which menu option in Excel allows you to create a pivot table?
A. Data > Sort
B. Insert > PivotTable
C. Formulas > AutoSum
D. Review > Protect Sheet
Answer: B. Insert > PivotTable
Explanation: The Insert tab contains the PivotTable option, which helps you summarize and
analyze large data sets quickly.
8. Named Ranges
Question: What is a named range in Excel?
A. A cell that contains text
B. A specific area of a worksheet given a name for easier reference in formulas
C. A range that is automatically updated by Excel
D. A feature that locks cells from editing
Answer: B. A specific area of a worksheet given a name for easier reference in formulas
Explanation: Named ranges allow you to assign a name to a cell or range, making formulas
easier to understand and manage.
9. The CONCATENATE Function
Question: What does the CONCATENATE function do?
A. It splits text into separate cells
B. It joins two or more text strings into one
C. It calculates the sum of numeric values
D. It sorts text alphabetically
Answer: B. It joins two or more text strings into one
Explanation: CONCATENATE (or the ampersand operator “&”) is used to combine text from
multiple cells or literal strings into a single string.
10. Auto-Fitting Columns
, Question: Which action best auto-fits a column’s width to its content?
A. Dragging the column border manually
B. Double-clicking the right border of the column header
C. Using the Format Painter
D. Pressing Ctrl+F
Answer: B. Double-clicking the right border of the column header
Explanation: Double-clicking the column’s right border automatically resizes the column to
match the widest cell content.
11. Freezing Panes
Question: How do you freeze the top row in an Excel worksheet?
A. Home > Format > Freeze Top Row
B. Data > Sort > Freeze
C. View > Freeze Panes > Freeze Top Row
D. Insert > PivotTable > Freeze Top Row
Answer: C. View > Freeze Panes > Freeze Top Row
Explanation: The Freeze Panes option under the View tab allows you to lock the top row so it
remains visible as you scroll down.
12. Financial Functions – PV
Question: Which function calculates the present value of an investment in Excel?
A. FV
B. NPV
C. PV
D. IRR
Answer: C. PV
Explanation: The PV function calculates the present value of an investment based on a series of
future payments and a discount rate.
13. Starting a Formula
Question: How do you initiate a formula in Excel?
A. With a plus sign (+)
B. With an asterisk (*)
