In this article, you will find the top 50 MCQ on Excel formulas, along with answers. Most of the questions are very basic. As a result, anyone with a basic understanding of Excel will be able to answer the questions. These questions will be helpful for job and academic examinations. Moreover, you should know the following: the basics of an Excel spreadsheet, use of the INDEX-MATCH formula, application of the 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 functions to solve them. You should use at least Excel 2019 to solve the problems without any issues.
Download Practice Workbook
You can download the Excel file from the following link.
Problem Overview
The Excel file contains fifty MCQ on Excel formulas and their corresponding answers. The questions are provided in the “Problem” sheet. Additionally, the answers are highlighted in the “Solution” sheet. We 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, you will use –
- (a) =COUNT(B5:F24)
- (b) =COUNTA(B5:F24)
- (c) =COUNTBLANK(B5:F24)
- (d) =DCOUNT(B5:F24)
- Q2. To display the applied formula to a cell, you should use –
- (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, you can use –
- (a) =MAX(F5:F24)
- (b) =LARGE(F5:F24,1)
- (c) =AGGREGATE(4,0,F5:F24)
- (d) All of the above
- Q5. You can find the mean of the salaries using –
- (a) =AGGREGATE(2,0,F5:F24)
- (b) =AVERAGE(F5:F24)
- (c) =MEAN(F5:F24)
- (d) All of the above
- Q6. Which formula will return 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, you will use –
- (a) =LEN(B5:B24)
- (b) =SIZE(B5:B24)
- (c) =STRINGLENGTH(B5:B24)
- (d) =LENGTH(B5:B24)
- Q9. If you want to count the number of employees whose name begins with R, you can use –
- (a) =COUNTIF(B5:B24,R*)
- (b) =COUNTIF(B5:B24,”R*”)
- (c) =COUNTIF(B5:B24,”R”)
- (d) =COUNTIF(B5:B24,”*R”)
- Q10. To calculate the space position in the name column, you can use –
- (a) =FIND(” “,B5:B24,1)
- (b) =SEARCH(” “,B5:B24,1)
- (c) =AGGREGATE(” “,B5:B25,1)
- (d) Both a&b
- Q11. The difference between the SEARCH and FIND function is –
- (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. The function to find the highest salary value is –
- (a) MAX
- (b) MAXIMUM
- (c) AGGREGATE
- (d) Both a & c
- Q14. The total value of salary for the male employees can be calculated by using –
- (a) SUMIF
- (b) IFS
- (c) MAX
- (d) INDEX-MATCH
- Q15. To find the employee who received the most salary, you will use the following formula –
- (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. To find the distinct job department names, you can use –
- (a) AGGREGATE
- (b) UNIQUE
- (c) Combination of IFERROR, INDEX, MATCH
- (d) Both b & c
- Q17. To extract the day value from the Date Joined column, which of the following features can be used?
- (a) DAY Function
- (b) Insert an adjacent helper column and use Flash Fill
- (c) LEFT Function
- (d) Both a, b & c
- Q18. Which function can be used to determine the number of empty cells in the dataset?
- (a) COUNT
- (b) COUNTA
- (c) COUNTBLANT
- (d) COUNTBLANK
- Q19. Using which function from the list you can return a random name –
- (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 salary greater than $100,000 AND dates joined after 30th April, the following formula needs to be used –
- (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 salary greater than $100,000 OR dates joined after 30th April, the following formula needs to be used –
- (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. To calculate the average salary by the male employees, the following formula need to be used –
- (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. To find the arithmetic mean, you can use the following function –
- (a) MEAN
- (b) AVERAGE
- (c) GEOMEAN
- (d) MIDPOINT
- Q24. Shortcut to apply the SUM function is –
- (a) Alt+=
- (b) Ctrl+=
- (c) Shift+=
- (d) Ctrl+Alt+=
- Q25. To return value from the left side of the matched value, we can use –
- (a) VLOOKUP Function
- (b) Combination of VLOOKUP and IF Functions
- (c) HLOOKUP Function
- (d) ZLOOKUP Function
- Q26. Which formula is correct for returning the name of the employee for the $25,010 salary?
- (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. The cell C15 is empty and F15 is $135,430. So, the output of =C15*F15 is –
- (a) $135,430
- (b) 0
- (c) #VALUE!
- (d) #DIV/0
- Q28. Which of the following functions will you use to determine the number of values in the Salary column?
- (a) NUM
- (b) NUMBER
- (c) COUNT
- (d) None of these
- Q29. If you want show the current date with time, you can use –
- (a) =NOW()
- (b) =TODAY()
- (c) Both
- (d) None of these
- Q30. Which of the formulas will you use to round 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. You can assign sequential serial number (1,2,3, etc.) to the rows by using the 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 of the following functions is available but not shown in Excel Tooltip?
- (a) DATEVALUE
- (b) DATEDIF
- (c) KLOOKUP
- (d) DCOUNT
- Q34. If you want to fix a cell reference, you will use –
- (a) $
- (b) !
- (c) *
- (d) %
- Q35. The Not Equal operator in Excel is –
- (a) =!
- (b) <>
- (c) !=
- (d) ||
- Q36. Circular reference in Excel formula is –
- (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. To fill down a formula, you need to use the following shortcut –
- (a) Ctrl+D
- (b) Alt+D
- (c) Shift+D
- (d) Ctrl+Alt+D
- Q38. Which of the following shortcuts can be used to use the Flash Fill feature?
- (a) Ctrl+F
- (b) Ctrl+E
- (c) Alt+E
- (d) Alt+F
- Q39. If you want to display the remainder after you divide 100 by 3, then you should use –
- (a) =MOD(100,3)
- (b) =DIV(3,100)
- (c) =MODE(100,3)
- (d) =REMAINDER(100,3)
- Q40. To concatenate values in the formula, you need to use –
- (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 formula contains an error?
- (a) =F7+F8
- (b) =F9+F11
- (c) (F9+F11)
- (d) No error
- Q44. To find the output of the formula, you need to select the full formula or a portion of it and need to press X to show the output. Here X is –
- (a) F7
- (b) F8
- (c) F9
- (d) F10
- Q45. To refer to a cell reference from another worksheet, you can –
- (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 of the following functions can handle all kinds of errors?
- (a) IFNA
- (b) IFERROR
- (c) ISERROR
- (d) ALLERROR
- Q48. You can insert formulas by –
- (a) Typing the formula
- (b) Using Insert Function feature from the Functions tab
- (c) Any of the above two
- (d) None of these
- Q49. To remove extra spaces, you can use the function –
- (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
Now, the image below depicts the first five solutions to the MCQ on Excel formulas. We have highlighted the solutions to the questions.
Conclusion
Thank you for reading this article. By completing this article, we hope that you have gained knowledge about the top 50 MCQ on Excel formulas with answers. You can find more articles similar to this on the ExcelDemy website. If you have any questions or suggestions, feel free to comment below. However, remember that our website implements comment moderation. Therefore, your comment may not be instantly visible. So, have a little bit of patience, and we will solve your query as soon as possible. Keep excelling!
Get FREE Advanced Excel Exercises with Solutions!
