In this article, you’ll find a collection of the top 50 MCQs related to Excel formulas, along with their corresponding answers. These questions cover fundamental concepts and are suitable for both job-related assessments and academic examinations. If you have a basic understanding of Excel, you should be able to tackle these questions.
Before we dive into the MCQs, make sure you’re familiar with the following:
- Basics of an Excel spreadsheet
- Usage of the INDEX-MATCH formula
- Application of functions like AGGREGATE, AVERAGE, AVERAGEIF, CEILING.MATH, COUNT, COUNTBLANK, IF, IFS, COUNTIF, COUNTA, COUNTIFS, DATEDIF, DAY, FIND, IFERROR, LEN, MROUND, RAND, RANDBETWEEN, ROW, ROWS, SUM, SUMIF, TODAY, VLOOKUP, and XLOOKUP.
The Excel file contains fifty MCQ on Excel formulas and their corresponding answers. The questions are provided in the “Problem” sheet and the answers are highlighted in the “Solution” sheet. Below you can see a snapshot of the sample dataset for this article below. This dataset represents the data for twenty employees of a particular company.
Now, let’s go through the MCQ on Excel formulas.
- Q1. To find the number of empty cells, which formula would you use?
- (a) =COUNT(B5:F24)
- (b) =COUNTA(B5:F24)
- (c) =COUNTBLANK(B5:F24)
- (d) =DCOUNT(B5:F24)
- Q2. How can you display the applied formula for a cell?
- (a) =FORMULATEXT(Cell_Reference)
- (b) =TEXTFORMULA(Cell_Reference)
- (c) =FORMULASTEXT(Cell_Reference)
- (d) =SHOWFORMULA(Cell_Reference)
- Q3. Which of the following functions will you use to count the numbers in the dataset?
- (a) =COUNT(B5:F24)
- (b) =COUNTA(B5:F24)
- (c) =COUNTNUM(B5:F24)
- (d) =DCOUNT(B5:F24)
- Q4. To find the maximum salary, which formula(s) can you use?
- (a) =MAX(F5:F24)
- (b) =LARGE(F5:F24,1)
- (c) =AGGREGATE(4,0,F5:F24)
- (d) All of the above
- Q5. How do you calculate the mean of salaries?
- (a) =AGGREGATE(2,0,F5:F24)
- (b) =AVERAGE(F5:F24)
- (c) =MEAN(F5:F24)
- (d) All of the above
- Q6. Which formula returns 0?
- (a) =COUNTA(C5:C24)
- (b) =COUNT(C5:C24)
- (c) =COUNTBLANK(C5:C24)
- (d) None of these
- Q7. How many functions are included in the AGGREGATE function?
- (a) 17
- (b) 18
- (c) 19
- (d) 20
- Q8. To find the string size (number of characters) for the name column, which formula should you use?
- (a) =LEN(B5:B24)
- (b) =SIZE(B5:B24)
- (c) =STRINGLENGTH(B5:B24)
- (d) =LENGTH(B5:B24)
- Q9. To count the number of employees whose names begin with “R,” which formula is appropriate?
- (a) =COUNTIF(B5:B24,R*)
- (b) =COUNTIF(B5:B24,”R*”)
- (c) =COUNTIF(B5:B24,”R”)
- (d) =COUNTIF(B5:B24,”*R”)
- Q10. How can you calculate the space position in the name column?
- (a) =FIND(” “,B5:B24,1)
- (b) =SEARCH(” “,B5:B24,1)
- (c) =AGGREGATE(” “,B5:B25,1)
- (d) Both a & b
- Q11. What’s the difference between the SEARCH and FIND functions?
- (a) The FIND function is case sensitive, and the SEARCH function is not.
- (b) The SEARCH function is case sensitive, and the FIND function is not.
- (c) There is no difference between them, only for compatibility, both are listed.
- (d) None of these.
- Q12. Which function can be used to find the number of females?
- (a) COUNTIFS
- (b) COUNTIF
- (c) COUNT
- (d) Both a & b
- Q13. Which function finds the highest salary value?
- (a) MAX
- (b) MAXIMUM
- (c) AGGREGATE
- (d) Both a & c
- Q14. How can you calculate the total value of salaries for male employees?
- (a) SUMIF
- (b) IFS
- (c) MAX
- (d) INDEX-MATCH
- Q15. To find the employee who received the highest salary, which formula should you use?
- (a) =INDEX(B5:B24,MATCH(MAX(F5:F24),F5:F24,0))
- (b) =INDEX(B5:B24,MATCH(MAX(F5:F24),F5:F24,1))
- (c) =INDEX(B5:B24,MAX(F5:F24),0)
- (d) =INDEX(B5:B24,MATCH(MAX(F5:F24),F5:F24,-1))
- Q16. How can you find distinct job department names?
- (a) AGGREGATE
- (b) UNIQUE
- (c) Combination of IFERROR, INDEX, MATCH
- (d) Both b & c
- Q17. Which feature(s) can be used to extract the day value from the “Date Joined” column?
- (a) DAY Function
- (b) Insert an adjacent helper column and use Flash Fill
- (c) LEFT Function
- (d) Both a, b & c
- Q18. Which function determines the number of empty cells in the dataset?
- (a) COUNT
- (b) COUNTA
- (c) COUNTBLANT
- (d) COUNTBLANK
- Q19. Which function returns a random name from the list?
- (a) =INDEX(B5:B24,MATCH(RANDBETWEEN(1,20),B5:B24,0))
- (b) =INDEX(B5:B24,RANDBETWEEN(1,20))
- (c) =INDEX(B6:B25,RAND())
- (d) =INDEX(B6:B25,RAND(20))
- Q20. To determine the number of salaries greater than $100,000 AND dates joined after 30th April, which formula should you use?
- (a) =COUNTIFS(F5:F24,”>100000″,E5:E24,”>44681″)
- (b) =COUNTIF(F5:F24,”>100000″)+COUNTIF(E5:E24,”>44681″)
- (c) Both a & b
- (d) None of these
- Q21. To determine the number of salaries greater than $100,000 OR dates joined after 30th April, which formula should you use?
- (a) =COUNTIFS(F5:F24,”>100000″,E5:E24,”>44681″)
- (b) =COUNTIF(F5:F24,”>100000″)+COUNTIF(E5:E24,”>44681″)
- (c) Both a & b
- (d) None of these
- Q22. How can you calculate the average salary for male employees?
- (a) =AVERAGEIF(C5:C24,”Male”,F5:F24)
- (b) =AVERAGEIFS(C5:C24,”Male”,F5:F24)
- (c) =IF(C5:C24=”Male”,AVERAGE(F5:F24),””)
- (d) =MEANIF(C5:C24,”Male”,F5:F24)
- Q23. Which function calculates the arithmetic mean?
- (a) MEAN
- (b) AVERAGE
- (c) GEOMEAN
- (d) MIDPOINT
- Q24. What shortcut applies the SUM function?
- (a) Alt+=
- (b) Ctrl+=
- (c) Shift+=
- (d) Ctrl+Alt+=
- Q25. To retrieve a value from the left side of a matched value, which function can you use?
- (a) VLOOKUP Function
- (b) Combination of VLOOKUP and IF Functions
- (c) HLOOKUP Function
- (d) ZLOOKUP Function
- Q26. Which formula correctly returns the name of the employee with a salary of $25,010?
- (a) =VLOOKUP(F5,IF({1,0},F5:F24,B5:B24),2,0)
- (b) =ZLOOKUP(F5,F5:F24,B5:B24)
- (c) =XLOOKUP(F6,F5:F24,B5:B24)
- (d) Both a&c
- Q27. If cell C15 is empty and F15 contains $135,430, what is the result of C15*F15?
- (a) $135,430
- (b) 0
- (c) #VALUE!
- (d) #DIV/0
- Q28. Which function determines the number of values in the Salary column?
- (a) NUM
- (b) NUMBER
- (c) COUNT
- (d) None of these
- Q29. To display the current date along with the time, which function(s) can you use?
- (a) =NOW()
- (b) =TODAY()
- (c) Both
- (d) None of these
- Q30. Which formula rounds up the salary figure from cell F17 to the nearest thousand?
- (a) =MROUND(F17,1000)
- (b) =FLOOR.MATH(F17,1000)
- (c) =CEILING.MATH(F17,1000)
- (d) =ROUNDUP(F17,1000)
- Q31. How can you assign sequential serial numbers (1, 2, 3, etc.) to the rows using a formula and AutoFill?
- (a) =ROWS($B$5:B5)
- (b) =ROWS(B5)
- (c) =ROW(B5)-3
- (d) Both a&c
- Q32. Which of the following are not valid Excel functions?
- (a) NUM
- (b) MEANS
- (c) TRUE
- (d) Both a&b
- Q33. Which function is available but not shown in Excel Tooltip?
- (a) DATEVALUE
- (b) DATEDIF
- (c) KLOOKUP
- (d) DCOUNT
- Q34. To fix a cell reference, which symbol do you use?
- (a) $
- (b) !
- (c) *
- (d) %
- Q35. What is the Not Equal operator in Excel?
- (a) =!
- (b) <>
- (c) !=
- (d) ||
- Q36. What is a circular reference in an Excel formula?
- (a) A reference that relies on itself
- (b) A type of the absolute cell reference
- (c) A reference that Speeds up calculation
- (d) None of these
- Q37. Which shortcut fills down a formula?
- (a) Ctrl+D
- (b) Alt+D
- (c) Shift+D
- (d) Ctrl+Alt+D
- Q38. Which shortcut is used to activate the Flash Fill feature?
- (a) Ctrl+F
- (b) Ctrl+E
- (c) Alt+E
- (d) Alt+F
- Q39. To display the remainder after dividing 100 by 3, which function should you use?
- (a) =MOD(100,3)
- (b) =DIV(3,100)
- (c) =MODE(100,3)
- (d) =REMAINDER(100,3)
- Q40. How do you concatenate values in a formula?
- (a) Semicolon (;)
- (b) Comma (,)
- (c) Ampersand (&)
- (d) Pipe (|)
- Q41. Which is the latest lookup function?
- (a) KLOOKUP
- (b) XLOOKUP
- (c) VLOOKUP
- (d) LOOKUP
- Q42. A formula must begin with:
- (a) =
- (b) +
- (c) –
- (d) (
- Q43. Which of the following formulas contains an error?
- (a) =F7+F8
- (b) =F9+F11
- (c) (F9+F11)
- (d) No error
- Q44. o find the output of a formula, you need to select the full formula or a portion of it and press X to show the output. What does X represent?
- (a) F7
- (b) F8
- (c) F9
- (d) F10
- Q45. How can you refer to a cell reference from another worksheet?
- (a) navigate to the sheet and click on that cell
- (b) type the sheet name, add !, and include the cell address
- (c) both of these
- (d) It is not possible in Excel
- Q46. Which of the following functions was introduced in Excel 2019?
- (a) UNIQUE
- (b) IFS
- (c) FLOOR.MATH
- (d) XLOOKUP
- Q47. Which function can handle all kinds of errors?
- (a) IFNA
- (b) IFERROR
- (c) ISERROR
- (d) ALLERROR
- Q48. How can you insert formulas?
- (a) Typing the formula
- (b) Using Insert Function feature from the Functions tab
- (c) Any of the above two
- (d) None of these
- Q49. Which function removes extra spaces?
- (a) TRIM
- (b) TRUNC
- (c) CODE
- (d) DELETE
- Q50. Which of the following functions is a Statistical function?
- (a) GESTEP
- (b) DEVSQ
- (c) BITXOR
- (d) IMSUB
The image below depicts the first five solutions to the MCQ on Excel formulas. The solutions to the questions have been highlighted.
Download Practice Workbook
You can download the Excel file from the following link.
