WorksheetsSPJ-Q1EX
Total questions: 50
Worksheet time: 25mins
Which Excel function looks up values vertically in a table?
HLOOKUP
VLOOKUP
XLOOKUP
FIND
What does the IF function check?
Cell color
Cell format
A condition
File size
Which tab contains Conditional Formatting?
Insert
Data
Home
Formulas
What does =IF(A1>50,"Pass","Fail") return when A1=60?
Pass
Fail
60
Error
How does Data Validation help users?
By coloring cells
By restricting input
By creating charts
By sorting data
To calculate the average of B1:B10, you would use:
=TOTAL(B1:B10)
=AVG(B1:B10)
=AVERAGE(B1:B10)
=MEAN(B1:B10)
Which formula correctly joins text from A1 and B1?
=A1+B1
=JOIN(A1,B1)
=CONCAT(A1,B1)
=MERGE(A1,B1)
To round 89.456 to one decimal place, use:
=ROUND(89.456,0)
=ROUND(89.456,1)
=ROUNDUP(89.456,1)
=TRUNC(89.456,1)
What does =IFERROR(VLOOKUP(...),"Not Found") do?
Always returns "Not Found"
Returns the lookup result or "Not Found" if error
Checks for blank cells
Validates data entry
Which function would you use to count non-empty cells?
COUNT
SUM
COUNTA
COUNTIF
Which formula properly handles division with error checking?
=A1/B1
=IF(B1=0,"Error",A1/B1)
=DIVIDE(A1,B1)
Both B and C
To validate that cells only contain "Yes" or "No", you would use:
Conditional Formatting
Data Validation list
IF statements
VLOOKUP
Which approach best calculates final grades from raw scores?
Manual entry
VLOOKUP with Transmutation table
AVERAGE function
RAND function
To highlight scores below 60 in red, you would:
Use Data Validation
Create Conditional Formatting rule
Manually color cells
Use VLOOKUP
To prevent invalid dates in a column, you would:
Use Data Validation date criteria
Add Conditional Formatting
Create an IF formula
Hide the column
To calculate weighted grades (20% Written, 60% Performance, 20% Quarterly), you would:
Use =AVERAGE
Use =SUM with multiplied weights
Manually calculate each
Use Conditional Formatting
What does the formula =IF(A1="", "Missing", A1) do?
Returns A1 if it is empty
Returns 'Missing' if A1 is empty
Returns TRUE if A1 is not empty
Returns FALSE if A1 is empty
Which feature allows you to change the cell color based on its value?
Data Validation
Conditional Formatting
Cell Linking
Pivot Table
What is the purpose of Data Validation in Excel?
To format cells
To restrict data entry
To calculate values
To link cells
Which Excel feature allows you to link data from one sheet to another?
Cell Linking
Conditional Formatting
Data Validation
Sorting
Which symbol is used to begin a formula in Excel?
#
=
$
*
The default separator for function arguments in Excel (English version) is:
, (comma)
; (semicolon)
: (colon)
- (dash)
What does the `IF` function return if the condition is FALSE and no value is specified?
0
#N/A
FALSE
#VALUE!
Which of the following best describes Data Validation?
Formatting numbers
Restricting input values in a cell
Calculating totals
Merging cells
What happens if you delete the column used in a formula?
The formula updates automatically
The formula shows #REF! error
The formula ignores the deletion
The formula changes to text
Which of the following is an absolute reference?
A1
$A$1
A$1
$A1
If conditional formatting is applied to highlight duplicate values, what happens to "25" if it appears three times?
All '25' cells are highlighted
Only the first '25' is highlighted
Only the last '25' is highlighted
None is highlighted
The formula =IF(A1="","Blank",A1) will return "Blank" when A1 is empty.
True
False
VLOOKUP can search to the left of the lookup column.
True
False
Data Validation can restrict cells to whole numbers between 1-100.
True
False
Conditional Formatting rules can reference other worksheets.
True
False
=COUNTA(A1:A10) counts only cells with numbers.
True
False
The Transmutation Table converts percentage scores to letter grades.
True
False
=NOW() updates automatically when the worksheet recalculates.
True
False
Data Validation input messages appear when cells are selected.
True
False
=VLOOKUP requires the lookup column to be sorted when using TRUE as the last argument.
True
False
The formula =IFERROR(A1/B1,"Error") will return "Error" if B1 is 0.
True
False
The function that retrieves values from a table based on a lookup value is (a) .
To return "Empty" when A1 is blank, use =IF( (a) ,"Empty",A1).
The (a) feature automatically changes cell appearance based on rules.
To restrict cell entries to specific options, use (a) .
= (a) ("Excel") returns the number of characters in the text.
The (a) function checks if a cell contains a number.
To get the current date and time, use the (a) function.
To round 89.456 to two decimal places, use = (a) (89.456,2).
The (a) table converts raw scores to final grades.
To count cells meeting specific criteria, use (a) .
To extract the first 3 characters from A1, use = (a) (A1,3).
The (a) function returns TRUE if all arguments are true.
To calculate the sum of A1:A10 only if all values are numbers, use = (a) .
