50 Tricky MS Excel Objective Questions and Answers PDF

Download the Practice Workbook

Problem Overview

The Excel and PDF files each contain 50 MS Excel objective questions and answers in PDF format. The questions are provided in the “Problem” sheet. The answers are highlighted in the “Solution” sheet. Here’s a snapshot of the sample dataset for this article. The dataset represents the sales achieved by 20 employees for a company.

Let’s go through the objective questions.

• Q1. Which Function can be used to Find the Number of Females?

1. • COUNTIFS

1. • COUNTIF

1. • COUNT

1. • Both a & b

• Q2. The function to find the highest sales value is –

1. • MAX

1. • MAXIMUM

1. • AGGREGATE

1. • Both a & c

• Q3. The total value of sales for the male employees can be calculated by using –

1. • SUMIF

1. • IFS

1. • MAX

1. • INDEX-MATCH

• Q4. To find the employee who generated the most sales, you can use the following formula –

1. • =INDEX(B5:B24,MATCH(MAX(F5:F24),F5:F24,0))

1. • =INDEX(B5:B24,MATCH(MAX(F5:F24),F5:F24,1))

1. • =INDEX(B5:B24,MAX(F5:F24),0)

1. • =INDEX(B5:B24,MATCH(MAX(F5:F24),F5:F24,-1))

• Q5. To find the distinct job position names, you can use –

1. • AGGREGATE

1. • UNIQUE

1. • IFERROR, INDEX, and MATCH

1. • Both b & c

• Q6. To determine the total number of characters in the employee names, you can use the function named –

1. • LENGTH

1. • LEN

1. • LENB

1. • STRINGLENGTH

• Q7. To extract the day value from the Date Joined column, which of the following features can be used?

1. • DAY Function

1. • Insert an adjacent helper column and use Flash Fill

1. • LEFT Function

1. • a, b & c

• Q8. Which function can be used to determine the number of empty cells in the dataset?

1. • COUNT

1. • COUNTA

1. • COUNTEMPTY

1. • COUNTBLANK

• Q9. Which function from the list can return a random name?

1. • =INDEX(B5:B24,MATCH(RANDBETWEEN(1,20),B5:B24,0))

1. • =INDEX(B5:B24,RANDBETWEEN(1,20))

1. • =INDEX(B6:B25,RAND())

1. • =INDEX(B6:B25,RAND(20))

• Q10. To create a Pie Chart using the Position column, you can –

1. • Select the cell range D5:D24 and select Pie Chart from the Insert tab.

1. • Select the cell range D5:D24 and select Recommended Chart from the Insert tab, and then select the Pie Chart.

1. • Find the unique values and their instances and then create a Pie Chart.

1. • None of these.

• Q11. To determine the number of sales greater than $100,000 AND dates joined after 30th April, the following formula needs to be used –

1. • =COUNTIFS(F5:F24,”>100000″,E5:E24,”>44681″)

1. • =COUNTIF(F5:F24,”>100000″)+COUNTIF(E5:E24,”>44681″)

1. • Both a & b

1. • None of these

• Q12. To determine the number of sales greater than $100,000 OR dates joined after 30th April, the following formula needs to be used –

1. • =COUNTIFS(F5:F24,”>100000″,E5:E24,”>44681″)

1. • =COUNTIF(F5:F24,”>100000″)+COUNTIF(E5:E24,”>44681″)

1. • Both a & b

1. • None of these

• Q13. To calculate the average sales by the male employees, the following formula need to be used –

1. • =AVERAGEIF(C5:C24,”Male”,F5:F24)

1. • =AVERAGEIFS(C5:C24,”Male”,F5:F24)

1. • =IF(C5:C24=”Male”,AVERAGE(F5:F24),””)

1. • =MEANIF(C5:C24,”Male”,F5:F24)

• Q14. To find the arithmetic mean, you can use the following function –

1. • MEAN

1. • AVERAGE

1. • GEOMEAN

1. • MIDPOINT

• Q15. The shortcut to find the total of some numbers is –

1. • Alt + =

1. • Ctrl + =

1. • Shift + =

1. • Ctrl + Alt + =

• Q16. To insert a chart from a data selection, you can press –

1. • F5

1. • F7

1. • F11

1. • F2

• Q17. To open the Spelling dialog box, you need to press –

1. • F5

1. • F7

1. • F11

1. • F2

• Q18. To bring up Custom Cell Formatting, press –

1. • Ctrl + 1

1. • Ctrl + 2

1. • Ctrl + 3

1. • Ctrl + 4

• Q19. To repeat the previous action, you will need to press –

1. • F5

1. • F4

1. • F7

1. • F3

• Q20. The latest version of Microsoft Office is called –

1. • Excel 366

1. • Microsoft 365

1. • Excel 2023

1. • Office 365

• Q21. Which of the following shortcuts will open the Find and Replace dialog box and activate the Replace tab?

1. • Ctrl + H

1. • Ctrl + R

1. • Ctrl + F

1. • Alt + F

• Q22. To return value from the left side of the matched value, we can use –

1. • VLOOKUP Function

1. • VLOOKUP and IF Functions

1. • HLOOKUP Function

1. • ZLOOKUP Function

• Q23. Which formula is correct for returning the name of the salesperson with $25,010 in sales?

1. •  =VLOOKUP(F5,IF({1,0},F5:F24,B5:B24),2,0)

1. • =ZLOOKUP(F5,F5:F24,B5:B24)

1. • =XLOOKUP(F6,F5:F24,B5:B24)

1. • Both a & c

• Q24. The cell C15 is empty and F15 is $135,430. So, the output of =C15*F15 is –

1. • $135,430

1. • 0

1. • #VALUE!

1. • #DIV/0

• Q25. Different types of Font Formatting are available in the –

1. • Home Tab

1. • Insert Tab

1. • Data Tab

1. • Format Tab

• Q26. There is a total of x functions inside the AGGREGATE Function. The value of x is –

1. • 17

1. • 18

1. • 19

1. • 21

• Q27. The maximum number of rows and columns in Excel are –

1. • 1,048,576 columns and 16,384 rows

1. • 1,048,576 rows and 16,384 columns

1. • 256 columns and 65,536 rows

1. • None of these

• Q28. The last column in Excel is –

1. • XFA

1. • XFB

1. • XFC

1. • XFD

• Q29. The first version of Excel was released in –

1. • 1982

1. • 1985

1. • 1987

1. • 1989

• Q30. Which of the following functions can you use to determine the number of values in the Sales column?

1. • NUM

1. • NUMBER

1. • COUNT

1. • None of these

• Q31. Microsoft Excel is the most popular spreadsheet program in the world. Which of the following is the first spreadsheet program?

1. • Microsoft Excel

1. • Lotus 1-2-3

1. • VisiCalc

1. • SuperCalc

• Q32. If you want to show the current date with time, you can use –

1. • =NOW()

1. • =TODAY()

1. • Both

1. • None of these

• Q33. The difference between the SEARCH and FIND function is –

1. • The FIND function is case sensitive and the SEARCH function is not.

1. • The SEARCH function is case sensitive and the FIND function is not.

1. • There is no difference between them, only for compatibility, both are listed.

1. • None of these.

• Q34. Which of the following functions is not available in the VBA WorksheetFunction method?

1. • IF

1. • COUNTIF

1. • SUM

1. • COUNTA

• Q35. If there is a red triangle in the top-right corner of the cell, it signifies –

1. • There is a note in that cell.

1. • The cell is formatted as text.

1. • There is an error on that cell.

1. • There is a circular reference.

• Q36. If there is a green triangle in the top-left corner of the cell, it signifies –

1. • There is a note in that cell.

1. • The cell is formatted as a number.

1. • There is an error on that cell.

1. • There is a circular reference.

• Q37. Which of the following shortcuts brings up the VBA window?

1. • Alt + F8

1. • Alt + F9

1. • Alt + F11

1. • Alt + F7

• Q38. Which of the formulas rounds up the sales figure from cell F17 to the nearest thousand?

1. • =MROUND(F17,1000)

1. • =FLOOR.MATH(F17,1000)

1. • =CEILING.MATH(F17,1000)

1. • =ROUNDUP(F17,1000)

• Q39. You can assign sequential serial number (1,2,3, etc.) to the rows by using the formula and AutoFill –

1. • =ROWS($B$5:B5)

1. • =ROWS(B5)

1. • =ROW(B5)-3

1. • Both a & c

• Q40. Which of the following are not valid Excel functions?

1. • NUM

1. • MEAN

1. • TRUE

1. • Both a&b

• Q41. In the VBA Cells function, the argument Cells (x, y) means –

1. • x = row number, y = column number

1. • x = column number, y = row number

1. • x = row index y = column index

1. • x = column index, y = row index

• Q42. The maximum height of a row is –

1. • 409.5 Units

1. • 403.5 Units

1. • 410 Units

1. • 412 Units

• Q43. You can copy a cell by using the shortcut –

1. • Ctrl + P

1. • Ctrl + V

1. • Ctrl + C

1. • Ctrl + X

• Q44. Which of the following functions is available but not shown in the Excel Tooltip?

1. • DATEVALUE

1. • DATEDIF

1. • KLOOKUP

1. • MEAN

• Q45. Which of the following is an absolute cell reference?

1. • F15

1. • F$15

1. • $F$15

1. • $F15

• Q46. Which of the following is a mixed cell reference?

1. • F15

1. • $F$16

1. • $F$15

1. • $F15

• Q47. When you move your cursor to the bottom right corner of a cell, it turns into a plus (+) sign. This icon is known as the –

1. • AutoFill Tool

1. • Fill Handle

1. • Fill Tool

1. • Drag Handle

• Q48. If you select a range of cells and type something, pressing a specific shortcut will copy the value to the selected range. That shortcut key is –

1. • Shift + Enter

1. • Ctrl + Enter

1. • Alt + Enter

1. • None of these

• Q49. If there is a VBA code in your Excel file, then you should save the file in ____ format –

1. • .xlsx

1. • .xls

1. • .xlsm

1. • .csv

• Q50. Google has developed their own spreadsheet program, which is known as –

1. • Sheets

1. • Docs

1. • Spreadsheet

1. • Slides

The image below depicts the first five solutions to the objective questions.

Download the Practice Workbook