Excel Functions & Formulas Quiz | Spreadsheet Modeling | MCQs
Q. What is the starting date for Excel's date system?
a) 1 January, 1800
b) 1 January, 1900
c) 1 January, 2000
d) 1 January, 1970
Answer: b) 1 January, 1900
Q. What does DATEVALUE("01/01/1900") return?
a) "01/01/1900"
b) 1
c) 0
d) #VALUE!
Answer: b) 1
Q. What will TODAY() return in Excel?
a) Current system time
b) Current date without time
c) Current date and time
d) A fixed date
Answer: b) Current date without time
Q. What is the correct formula to get the year from a date in Excel?
a) YEAR(A1)
b) EXTRACTYEAR(A1)
c) GETYEAR(A1)
d) YEAROF(A1)
Answer: a) YEAR(A1)
Q. What fraction of 24 hours does Excel use to represent 6 AM?
a) 0.25
b) 0.50
c) 0.75
d) 1.00
Answer: a) 0.25
Q. Which function returns the current date and time?
a) CURRENTDATE()
b) NOW()
c) TODAY()
d) TIMESTAMP()
Answer: b) NOW()
Q. What does IFERROR(A1/B1, "Error") do?
a) Shows an error if B1 is zero
b) Displays "Error" if A1/B1 results in an error
c) Returns 0 when there’s an error
d) Only works with IF function
Answer: b) Displays "Error" if A1/B1 results in an error
Q. Which function allows multiple IF conditions in Excel?
a) IFMULTI()
b) IFERROR()
c) IFS()
d) IFX()
Answer: c) IFS()
Q. What does COUNT(A1:A10) do?
a) Counts all values including text
b) Counts only numeric values
c) Counts blank cells
d) Returns an error
Answer: b) Counts only numeric values
Q. Which function counts the number of non-empty cells?
a) COUNTIF()
b) COUNTBLANK()
c) COUNTA()
d) COUNTEMPTY()
Answer: c) COUNTA()
Q. What does SUMIF(A1:A10, ">50") do?
a) Adds all values in A1:A10 greater than 50
b) Counts all values in A1:A10 greater than 50
c) Returns the number 50
d) Returns an error
Answer: a) Adds all values in A1:A10 greater than 50
Q. What does AVERAGEIF(A1:A10, ">100", B1:B10) do?
a) Averages all values in B1:B10 where A1:A10 is greater than 100
b) Counts values greater than 100
c) Sums values greater than 100
d) Returns an error
Answer: a) Averages all values in B1:B10 where A1:A10 is greater than 100
Q. In VLOOKUP(10, A2:B10, 2, FALSE), what does FALSE signify?
a) Approximate match
b) Exact match
c) No match
d) Case-sensitive match
Answer: b) Exact match
Q. What function returns the position of a value in a row or column?
a) MATCH()
b) INDEX()
c) VLOOKUP()
d) HLOOKUP()
Answer: a) MATCH()
Q. What does Data Validation do in Excel?
a) Allows only specific data to be entered in a cell
b) Sorts data automatically
c) Converts text to numbers
d) Filters data
Answer: a) Allows only specific data to be entered in a cell
Q. Which data validation option allows selecting values from a list?
a) Dropdown()
b) LIST
c) VALIDATE
d) SELECTLIST()
Answer: b) LIST
Q. What does OFFSET(A1, 2, 1) return?
a) The value in the cell 2 rows down and 1 column to the right of A1
b) The value in A1
c) An error
d) Moves A1 to a new location
Answer: a) The value in the cell 2 rows down and 1 column to the right of A1
Q. Which function returns the highest value in a range?
a) LARGE()
b) MAX()
c) TOP()
d) BIGGEST()
Answer: b) MAX()
Q. How do you find the second highest value in a range?
a) MAX(A1:A10, 2)
b) LARGE(A1:A10, 2)
c) HIGHEST(A1:A10, 2)
d) TOP(A1:A10, 2)
Answer: b) LARGE(A1:A10, 2)
Q. What does SUMPRODUCT(A1:A5, B1:B5) do?
a) Adds values in A1:A5 and B1:B5
b) Multiplies corresponding elements and sums them
c) Counts non-blank values
d) Finds the maximum product
Answer: b) Multiplies corresponding elements and sums them
Q.
If A1:A3 contains {2, 3, 4} and B1:B3 contains {5, 6, 7}, what is the result of
SUMPRODUCT(A1:A3, B1:B3)?
a) 56
b) 38
c) 32
d) 74
Answer: b) 56 (2×5 + 3×6 + 4×7 = 56)
Q. What is the primary difference between HLOOKUP() and VLOOKUP()?
a) HLOOKUP() searches vertically, VLOOKUP() searches horizontally
b) HLOOKUP() searches horizontally, VLOOKUP() searches vertically
c) They function the same way
d) HLOOKUP() requires sorted data
Answer: b) HLOOKUP() searches horizontally, VLOOKUP() searches vertically
Q. In HLOOKUP(20, A1:D3, 2, FALSE), what does 2 represent?
a) The lookup value
b) The row number to return data from
c) The number of columns to search
d) The table array
Answer: b) The row number to return data from
Q. What does MATCH(50, A1:A10, 0) return?
a) The value 50
b) The row number where 50 appears
c) The sum of values greater than 50
d) The total count of 50s
Answer: b) The row number where 50 appears
Q. What function would you use to return a value from a specific row and column in an array?
a) MATCH()
b) INDEX()
c) VLOOKUP()
d) HLOOKUP()
Answer: b) INDEX()
Q. What does LARGE(A1:A10, 3) return?
a) The third largest value in A1:A10
b) The average of the three largest values
c) The largest value
d) The third smallest value
Answer: a) The third largest value in A1:A10
Q. What does SMALL(A1:A10, 2) return?
a) The smallest value
b) The second smallest value
c) The sum of all small values
d) The average of the two smallest values
Answer: b) The second smallest value
Q. What does ROW(A5) return?
a) 5
b) A5
c) The total number of rows
d) The column number of A5
Answer: a) 5
Q. What does COLUMN(D4) return?
a) 4
b) D
c) 3
d) 2
Answer: c) 4 (D is the fourth column)
Q. What does COUNTIFS(A1:A10, ">50", B1:B10, "<100") do?
a) Counts values in A1:A10 greater than 50 and in B1:B10 less than 100
b) Counts values only in A1:A10 greater than 50
c) Counts values only in B1:B10 less than 100
d) Returns an error
Answer: a) Counts values in A1:A10 greater than 50 and in B1:B10 less than 100
Q. What will IF(5>3, "Yes", "No") return?
a) Yes
b) No
c) 5
d) Error
Answer: a) Yes
Q. What does OFFSET(A1, 2, 3) return?
a) A cell 2 rows down and 3 columns right from A1
b) A1
c) A cell 3 rows down and 2 columns right
d) An error
Answer: a) A cell 2 rows down and 3 columns right from A1
Q. How do you apply a list for data validation?
a) Data > Data Validation > List
b) Format > List
c) Insert > List
d) Home > Data Validation
Answer: a) Data > Data Validation > List
Q. What is the purpose of Data Validation in Excel?
a) To restrict the type of data entered
b) To format cells
c) To create pivot tables
d) To generate random numbers
Answer: a) To restrict the type of data entered
Q. If a lookup value is not found in VLOOKUP(), what is returned?
a) 0
b) #N/A
c) The first value in the column
d) The last value in the column
Answer: b) #N/A
Q. In VLOOKUP(), what should be true for an approximate match?
a) The lookup column must be sorted in ascending order
b) The lookup column can be in any order
c) The table should have no blank cells
d) The lookup column should be sorted in descending order
Answer: a) The lookup column must be sorted in ascending order
Q. What is the primary purpose of spreadsheet modeling?
a) To store images
b) To analyze data using formulas and functions
c) To design websites
d) To write programming code
Answer: b) To analyze data using formulas and functions
Q. What is an advantage of using functions like SUMIF() and COUNTIF() in modeling?
a) They allow conditional calculations
b) They store images
c) They increase file size unnecessarily
d) They replace pivot tables
Answer: a) They allow conditional calculations
Q. What does LEFT("Excel", 2) return?
a) Ex
b) cel
c) Exc
d) El
Answer: a) Ex
Q. What does RIGHT("Spreadsheet", 5) return?
a) Sheet
b) Spread
c) Read
d) Spr
Answer: a) Sheet
Q. What does LEN("Excel") return?
a) 5
b) 4
c) 6
d) Error
Answer: a) 5
Q. What does MID("Spreadsheet", 3, 4) return?
a) Read
b) Spre
c) Shee
d) Ads
Answer: a) Read
Q. What does TRIM(" Excel ") return?
a) "Excel"
b) " Excel "
c) "Excel "
d) " Excel"
Answer: a) "Excel"
Q. What is the result of =CONCATENATE("Good", " ", "Morning")?
a) GoodMorning
b) Good Morning
c) Good, Morning
d) Good_Morning
Answer: b) Good Morning
Q. What does SUBSTITUTE("10-10-2023", "-", "/") return?
a) 10-10-2023
b) 10/10/2023
c) 10102023
d) Error
Answer: b) 10/10/2023
Q. What does AND(5>3, 10>8) return?
a) TRUE
b) FALSE
c) 1
d) Error
Answer: a) TRUE
Q. What does OR(5>10, 3<8) return?
a) TRUE
b) FALSE
c) Error
d) #VALUE!
Answer: a) TRUE
Q. What does NOT(TRUE) return?
a) FALSE
b) TRUE
c) 0
d) Error
Answer: a) FALSE
Q. What does ROUND(3.456, 2) return?
a) 3.45
b) 3.46
c) 3.5
d) 3.4
Answer: b) 3.46
Q. What does ROUNDUP(4.123, 1) return?
a) 4.2
b) 4.1
c) 4.0
d) 5
Answer: a) 4.2
Q. What does ROUNDDOWN(9.876, 1) return?
a) 9.9
b) 9.8
c) 9.87
d) 9.7
Answer: b) 9.8
Q. What does IFERROR(10/0, "Error") return?
a) #DIV/0!
b) Error
c) 0
d) 10
Answer: b) Error
Q. What error does =VLOOKUP(50, A1:B10, 3, FALSE) return if there are only 2 columns in A1:B10?
a) #N/A
b) #VALUE!
c) #REF!
d) #DIV/0!
Answer: c) #REF!
Q. Which chart type is best for showing trends over time?
a) Line Chart
b) Pie Chart
c) Scatter Plot
d) Bar Chart
Answer: a) Line Chart
Q. Which chart type is best for showing parts of a whole?
a) Pie Chart
b) Column Chart
c) Histogram
d) Scatter Chart
Answer: a) Pie Chart
Q. What is the primary purpose of a Pivot Table?
a) To summarize and analyze large data sets
b) To insert images
c) To create animated charts
d) To store text data
Answer: a) To summarize and analyze large data sets
Q. What does TEXT(1234.56, "$#,##0.00") return?
a) $1,234.56
b) 1234.56
c) 1234
d) Error
Answer: a) $1,234.56
Q. What function extracts the current system date?
a) TODAY()
b) DATE()
c) NOW()
d) TIME()
Answer: a) TODAY()
Q. What does EOMONTH("01-Jan-2023", 1) return?
a) 28-Feb-2023
b) 31-Jan-2023
c) 1-Feb-2023
d) 30-Apr-2023
Answer: a) 28-Feb-2023
Q. What does WORKDAY("01-Mar-2023", 5) return (assuming no holidays)?
a) 08-Mar-2023
b) 07-Mar-2023
c) 06-Mar-2023
d) 10-Mar-2023
Answer: b) 07-Mar-2023
Q. Which function returns the highest value in a range?
a) MAX()
b) MIN()
c) LARGE()
d) INDEX()
Answer: a) MAX()
Q. What function returns the number of characters in a text string?
a) LEN()
b) TRIM()
c) COUNT()
d) MID()
Answer: a) LEN()
Q. What does RAND() return?
a) A random number between 0 and 1
b) A random integer between 1 and 100
c) A fixed random value
d) An error
Answer: a) A random number between 0 and 1
Q. Which function would you use to generate a random number between 10 and 100?
a) RANDBETWEEN(10,100)
b) RAND(10,100)
c) RANDOM(10,100)
d) INT(RAND()*100)
Answer: a) RANDBETWEEN(10,100)
