50 Tricky MS Excel Objective Questions and Answers PDF

In this article, you will find 50 MS Excel objective questions and answers in PDF format. 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 examinations. Moreover, you should know 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 to solve them. You should use at least Excel 2019 to solve the problems without any issues.

Download Practice Workbook

Problem Overview

The Excel and PDF files each contain fifty MS Excel objective questions and answers in PDF format. 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 sales achieved by twenty employees for a particular 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 will 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. • Combination of IFERROR, INDEX, 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. • Both 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. Using which function from the list you 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 will –

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. Shortcut to find the total of some numbers is –

1. • Alt+=

1. • Ctrl+=

1. • Shift+=

1. • Ctrl+Alt+=

• Q16. To insert chart from a data selection, you will need to 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 the 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 on –

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. The following shortcut key 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. • Combination of VLOOKUP and IF Functions

1. • HLOOKUP Function

1. • ZLOOKUP Function

• Q23. Which formula is correct for returning the name of the salesperson for the $25,010 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 x number of functions inside the AGGREGATE Function, here 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 is released in –

1. • 1982

1. • 1985

1. • 1987

1. • 1989

• Q30. Which of the following functions will 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 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. The following function 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, then 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, then 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. You will press the following shortcut to bring up the VBA window –

1. • Alt+F8

1. • Alt+F9

1. • Alt+F11

1. • Alt+F7

• Q38. Which of the formulas will you use to round 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 is Cells (x, y), which 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 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 –

1. • AutoFill Tool

1. • Fill Handle

1. • Fill Tool

1. • Drag Handle

• Q48. If you select a range of cells and type something, then pressing a specific shortcut will copy the value to the selected range. Now, 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 as ____ 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. So, this concludes the article on the topic MS Excel objective questions and answers PDF”.

Download Practice Workbook

Conclusion

Thank you for reading this article. By completing this article, we hope that you have gained knowledge about MS Excel objective questions and answers in PDF format. 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!