Excel Crash Course Exam from Wall Street Prep - Wall Street Prep
Questions And Answers Updated 2025
Question 1
Which of the following formulas would correctly sum the values in cells A1, A2, A3, and B1?
A) =SUM(A1:A3,B1)
B) =SUM(A1,A2,A3,B1)
C) =A1+A2+A3+B1
D) All of the above
E) None of the above
Correct Answer: D) All of the above
Rationale: All provided formulas correctly calculate the sum of the specified
cells. =SUM(A1:A3,B1) uses a range and a single cell, =SUM(A1,A2,A3,B1) lists individual cells,
and =A1+A2+A3+B1 uses direct addition.
Question 2
To fix the column reference in a formula when dragging it across rows (e.g., to keep referring
to column B), which type of cell reference should be used?
A) Absolute reference (e.g., $B$4)
B) Relative reference (e.g., B4)
C) Mixed reference with dollar sign before the column (e.g., $B4)
D) Mixed reference with dollar sign before the row (e.g., B$4)
E) 3D reference
Correct Answer: C) Mixed reference with dollar sign before the column (e.g., $B4)
Rationale: Placing a dollar sign before the column letter (e.g., $B4) creates a mixed reference
that locks the column but allows the row to change when the formula is dragged.
Question 3
Which function is best suited for looking up a value in the first column of a table and returning a
value from a specified column in the same row?
A) HLOOKUP
B) INDEX
C) MATCH
D) VLOOKUP
E) XLOOKUP
Correct Answer: D) VLOOKUP
Rationale: VLOOKUP (Vertical Lookup) is designed to search for a value in the leftmost
column of a table and return a corresponding value from a designated column in that row.
, Question 4
You have a dataset where column A contains employee names and column B contains their
department. You want to count how many employees are in the "Sales" department. Which
formula should you use?
A) =SUMIF(B:B,"Sales",A:A)
B) =COUNTIF(B:B,"Sales")
C) =COUNT(A:A,"Sales")
D) =SUM(B:B="Sales")
E) =IF(B:B="Sales",COUNT(A:A),0)
Correct Answer: B) =COUNTIF(B:B,"Sales")
Rationale: COUNTIF counts the number of cells within a range that meet a single specified
criterion. Here, it counts cells in column B that equal "Sales".
Question 5
What does the IFERROR function do in Excel?
A) It highlights cells that contain errors.
B) It attempts to fix errors in a formula.
C) It allows you to specify a value to return if a formula evaluates to an error; otherwise, it
returns the result of the formula.
D) It converts all errors into a text string.
E) It pauses formula execution until the error is corrected.
Correct Answer: C) It allows you to specify a value to return if a formula evaluates
to an error; otherwise, it returns the result of the formula.
Rationale: IFERROR(value, value_if_error) is used to gracefully handle errors, returning a
specified alternative value instead of a standard error message (like #N/A, #DIV/0!, etc.).
Question 6
You have a table of sales data (A1:C100) and want to find the sales amount for a specific
product ID (in cell D1) and region (in cell E1). The product IDs are in column A, regions in
column B, and sales amounts in column C. Which formula combination is most efficient for this
two-criteria lookup?
A) =VLOOKUP(D1,A1:C100,3,FALSE)
B) =INDEX(C1:C100,MATCH(D1&E1,A1:A100&B1:B100,0)) (entered as array formula)
C) =SUMIF(A:A,D1,C:C) + SUMIF(B:B,E1,C:C)
D) =HLOOKUP(E1,A1:C100,3,FALSE)
E) =VLOOKUP(D1,A:C,MATCH(E1,B:B,0),FALSE)
Questions And Answers Updated 2025
Question 1
Which of the following formulas would correctly sum the values in cells A1, A2, A3, and B1?
A) =SUM(A1:A3,B1)
B) =SUM(A1,A2,A3,B1)
C) =A1+A2+A3+B1
D) All of the above
E) None of the above
Correct Answer: D) All of the above
Rationale: All provided formulas correctly calculate the sum of the specified
cells. =SUM(A1:A3,B1) uses a range and a single cell, =SUM(A1,A2,A3,B1) lists individual cells,
and =A1+A2+A3+B1 uses direct addition.
Question 2
To fix the column reference in a formula when dragging it across rows (e.g., to keep referring
to column B), which type of cell reference should be used?
A) Absolute reference (e.g., $B$4)
B) Relative reference (e.g., B4)
C) Mixed reference with dollar sign before the column (e.g., $B4)
D) Mixed reference with dollar sign before the row (e.g., B$4)
E) 3D reference
Correct Answer: C) Mixed reference with dollar sign before the column (e.g., $B4)
Rationale: Placing a dollar sign before the column letter (e.g., $B4) creates a mixed reference
that locks the column but allows the row to change when the formula is dragged.
Question 3
Which function is best suited for looking up a value in the first column of a table and returning a
value from a specified column in the same row?
A) HLOOKUP
B) INDEX
C) MATCH
D) VLOOKUP
E) XLOOKUP
Correct Answer: D) VLOOKUP
Rationale: VLOOKUP (Vertical Lookup) is designed to search for a value in the leftmost
column of a table and return a corresponding value from a designated column in that row.
, Question 4
You have a dataset where column A contains employee names and column B contains their
department. You want to count how many employees are in the "Sales" department. Which
formula should you use?
A) =SUMIF(B:B,"Sales",A:A)
B) =COUNTIF(B:B,"Sales")
C) =COUNT(A:A,"Sales")
D) =SUM(B:B="Sales")
E) =IF(B:B="Sales",COUNT(A:A),0)
Correct Answer: B) =COUNTIF(B:B,"Sales")
Rationale: COUNTIF counts the number of cells within a range that meet a single specified
criterion. Here, it counts cells in column B that equal "Sales".
Question 5
What does the IFERROR function do in Excel?
A) It highlights cells that contain errors.
B) It attempts to fix errors in a formula.
C) It allows you to specify a value to return if a formula evaluates to an error; otherwise, it
returns the result of the formula.
D) It converts all errors into a text string.
E) It pauses formula execution until the error is corrected.
Correct Answer: C) It allows you to specify a value to return if a formula evaluates
to an error; otherwise, it returns the result of the formula.
Rationale: IFERROR(value, value_if_error) is used to gracefully handle errors, returning a
specified alternative value instead of a standard error message (like #N/A, #DIV/0!, etc.).
Question 6
You have a table of sales data (A1:C100) and want to find the sales amount for a specific
product ID (in cell D1) and region (in cell E1). The product IDs are in column A, regions in
column B, and sales amounts in column C. Which formula combination is most efficient for this
two-criteria lookup?
A) =VLOOKUP(D1,A1:C100,3,FALSE)
B) =INDEX(C1:C100,MATCH(D1&E1,A1:A100&B1:B100,0)) (entered as array formula)
C) =SUMIF(A:A,D1,C:C) + SUMIF(B:B,E1,C:C)
D) =HLOOKUP(E1,A1:C100,3,FALSE)
E) =VLOOKUP(D1,A:C,MATCH(E1,B:B,0),FALSE)
