50 Excel Multiple Choice Questions: Test Your Skills

Before taking on these questions, it’s helpful to understand the following:

Introduction to Excel VBA, enable the Developer tab
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.

This quiz is compatible with Excel 2019 and later.

Download Practice Workbook

You can download the Excel file from the following link.

Problem Overview

The Excel file contains fifty multiple choice questions 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. You can provide your answer (by typing or selecting from the dropdown list) in the “I” column. If it is correct, you will get a green check mark in the “J” column.

Here are the questions:

Q1. To insert a chart from a data selection, you need to press –
- (a) F5
- (b) F7
- (c) F11
- (d) F12
Q2. To open the Spelling dialog box, you need to press –
- (a) F5
- (b) F7
- (c) F11
- (d) F2
Q3. To bring up Custom Cell Formatting, you press –
- (a) Ctrl+1
- (b) Ctrl+2
- (c) Ctrl+3
- (d) Ctrl+4
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 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 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 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
- (d) None of the above
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 highest salary, you 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. Which of the following features allows you 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 can be used to determine the number of empty cells in the dataset?
- (a) COUNT
- (b) COUNTA
- (c) COUNTBLANT
- (d) COUNTBLANK
Q19. Which of the following functions will 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. Which formula allows you to determine who has salaries greater than $100,000 AND who joined after 30th April?
- (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. Which formula allows you to determine who has salaries greater than $100,000 OR who joined after 30th April?
- (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 for male employees, the following formula needs 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. The 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, you 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 who has a $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 these 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. Which formula can you combine with AutoFill to assign sequential serial number (1,2,3, etc.) to the rows?
- (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 can use –
- (a) $
- (b) !
- (c) *
- (d) %
Q35. The Not Equal operator in Excel is –
- (a) =!
- (b) <>
- (c) !=
- (d) ||
Q36. A circular reference in an 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 the above
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 enables the Flash Fill feature?
- (a) Ctrl+F
- (b) Ctrl+F
- (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 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 the above
- (d) This isn’t 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. In the VBA Cells function, the argument is Cells (x, y), which means –
- (a) x = row number, y = column number
- (b) x = column number, y = row number
- (c) x = row index y = column index
- (d) x = column index, y = row index
Q49. To remove extra spaces, you can use the function –
- (a) TRIM
- (b) TRUNC
- (c) CODE
- (d) DELETE
Q50. If there is a VBA code in your Excel file, then you should save the file as ____ format –
- (a) .xlsx
- (b) .xls
- (c) .xlsm
- (d) .csv