C268: Spreadsheet Applications | Practice Exam
Questions & Answers | 2026 Edition
Instructions
This exam tests your ability to apply Microsoft Excel functions and features to solve
business problems. Assume all actions begin with a properly formatted data set in
Excel 365/Excel 2021 or later, unless otherwise specified.
Time Limit: 90 minutes
Total Questions: 25
Scoring: 4 points per question (100 points total)
Business Context: Unless otherwise noted, questions reference Northwind Traders, a
wholesale food distribution company with sales data organized in tables containing
columns for: OrderID, OrderDate, CustomerID, ProductID, ProductName, Category,
UnitPrice, Quantity, Discount, Region, and SalesRep.
Section 1: Formulas, Functions & Cell Referencing (Questions 1-8)
Q1. In a budget sheet, cell B10 contains a projected expense of $1,200. You want to
reference this exact value in multiple formulas across the sheet, ensuring the reference
does not change if you copy the formulas. How should you reference this cell?
A. B10
B. $B$10 [CORRECT]
C. B
10D.
,B10
Correct Answer: B
Rationale: An absolute reference, created by placing dollar signs before both the column
letter and row number ($B$10), locks the reference completely. This ensures that when
the formula is copied to another cell, it will still point to cell B10. B10 (A) is a relative
reference and will change relative to the new cell position. B$10 (C) locks only the row
(mixed reference), and $B10 (D) locks only the column (mixed reference)—neither
provides complete stability.
Q2. The "Sales Data" table has columns for ProductID (Column A), ProductName
(Column B), and UnitPrice (Column C). You need to find the price of the product with
ID "P-10025". Which formula is correct?
A. =LOOKUP("P-10025", A2:A100, C2:C100)
B. =VLOOKUP("P-10025", A2:C100, 3, FALSE) [CORRECT]
C. =XLOOKUP("P-10025", C2:C100, A2:A100)
D. =HLOOKUP("P-10025", A1:C100, 3, TRUE)
Correct Answer: B
Rationale: VLOOKUP searches for a value ("P-10025") in the first column of a table
range (A2:C100) and returns a value from a specified column number (3, which
corresponds to Column C containing UnitPrice). The FALSE argument (or 0) ensures an
exact match rather than an approximate match. Option A uses the older LOOKUP
function which requires sorted data and is less reliable. Option C reverses the lookup
, and return arrays (it searches Column C for the ID instead of Column A). Option D
incorrectly uses HLOOKUP (designed for horizontal data) on vertical data and uses
TRUE for approximate match, which could return incorrect results.
Q3. You are creating a commission calculator. Cell B2 contains the sales amount
($5,000) and cell C2 contains the commission rate (5%). You need to copy this formula
down for 100 sales reps. The sales amount varies per row (B3, B4, etc.), but the
commission rate in C2 must remain fixed. Which formula should you enter in D2?
A. =B2*C2
B. =B2*$C$2 [CORRECT]
C. =$B2*C2
D. =B$2*C$2
Correct Answer: B
Rationale: This requires a mixed reference strategy: the sales amount (B2) should
change relatively as you copy down (B3, B4, etc.), but the commission rate (C2) must
remain absolute ($C$2) to always reference that specific cell. Option A uses all relative
references, so C2 would become C3, C4, etc. Option C incorrectly locks the column B,
which isn't necessary for vertical copying. Option D locks row 2 for both, preventing the
sales amount from adjusting per row.
Q4. Which formula correctly calculates the monthly payment for a $25,000 car loan at
4.5% annual interest over 5 years?
A. =PMT(4.5%, 5, 25000)
Questions & Answers | 2026 Edition
Instructions
This exam tests your ability to apply Microsoft Excel functions and features to solve
business problems. Assume all actions begin with a properly formatted data set in
Excel 365/Excel 2021 or later, unless otherwise specified.
Time Limit: 90 minutes
Total Questions: 25
Scoring: 4 points per question (100 points total)
Business Context: Unless otherwise noted, questions reference Northwind Traders, a
wholesale food distribution company with sales data organized in tables containing
columns for: OrderID, OrderDate, CustomerID, ProductID, ProductName, Category,
UnitPrice, Quantity, Discount, Region, and SalesRep.
Section 1: Formulas, Functions & Cell Referencing (Questions 1-8)
Q1. In a budget sheet, cell B10 contains a projected expense of $1,200. You want to
reference this exact value in multiple formulas across the sheet, ensuring the reference
does not change if you copy the formulas. How should you reference this cell?
A. B10
B. $B$10 [CORRECT]
C. B
10D.
,B10
Correct Answer: B
Rationale: An absolute reference, created by placing dollar signs before both the column
letter and row number ($B$10), locks the reference completely. This ensures that when
the formula is copied to another cell, it will still point to cell B10. B10 (A) is a relative
reference and will change relative to the new cell position. B$10 (C) locks only the row
(mixed reference), and $B10 (D) locks only the column (mixed reference)—neither
provides complete stability.
Q2. The "Sales Data" table has columns for ProductID (Column A), ProductName
(Column B), and UnitPrice (Column C). You need to find the price of the product with
ID "P-10025". Which formula is correct?
A. =LOOKUP("P-10025", A2:A100, C2:C100)
B. =VLOOKUP("P-10025", A2:C100, 3, FALSE) [CORRECT]
C. =XLOOKUP("P-10025", C2:C100, A2:A100)
D. =HLOOKUP("P-10025", A1:C100, 3, TRUE)
Correct Answer: B
Rationale: VLOOKUP searches for a value ("P-10025") in the first column of a table
range (A2:C100) and returns a value from a specified column number (3, which
corresponds to Column C containing UnitPrice). The FALSE argument (or 0) ensures an
exact match rather than an approximate match. Option A uses the older LOOKUP
function which requires sorted data and is less reliable. Option C reverses the lookup
, and return arrays (it searches Column C for the ID instead of Column A). Option D
incorrectly uses HLOOKUP (designed for horizontal data) on vertical data and uses
TRUE for approximate match, which could return incorrect results.
Q3. You are creating a commission calculator. Cell B2 contains the sales amount
($5,000) and cell C2 contains the commission rate (5%). You need to copy this formula
down for 100 sales reps. The sales amount varies per row (B3, B4, etc.), but the
commission rate in C2 must remain fixed. Which formula should you enter in D2?
A. =B2*C2
B. =B2*$C$2 [CORRECT]
C. =$B2*C2
D. =B$2*C$2
Correct Answer: B
Rationale: This requires a mixed reference strategy: the sales amount (B2) should
change relatively as you copy down (B3, B4, etc.), but the commission rate (C2) must
remain absolute ($C$2) to always reference that specific cell. Option A uses all relative
references, so C2 would become C3, C4, etc. Option C incorrectly locks the column B,
which isn't necessary for vertical copying. Option D locks row 2 for both, preventing the
sales amount from adjusting per row.
Q4. Which formula correctly calculates the monthly payment for a $25,000 car loan at
4.5% annual interest over 5 years?
A. =PMT(4.5%, 5, 25000)
