Office Module

Advance Excel MCQ

Q1. What is the shortcut key to insert a new worksheet in Excel?

(a) Ctrl+F11

(b) Alt+F11

(c) Shift+F11.

(d) Shift+Alt+F11

Q2. What is the function to sum a range of cells in Excel?

(a) ADD

(b) SUM.

(c) SUMA

(d) SUMS

Q3. Which of the following is NOT a type of pivot table?

(a) Standard pivot table

(b) Pivot chart.

(c) Timeline pivot table

(d) Slicer pivot table

Q4. Which of the following is NOT a way to filter data in Excel?

(a) AutoFilter

(b) Advanced Filter

(c) Slicer

(d) Data validation.

Q5. Which of the following is NOT a type of data connection in Excel?

(a) OLE DB connection

(b) ODBC connection

(c) HTTPS connection

(d) Text file connection.

Q6. Which of the following is NOT a type of reference in Excel?

(a) Absolute reference

(b) Unique reference.

(c) Relative reference

(d) External reference

Q7. Which of the following is NOT a type of chart in Excel?

(a) Line chart

(b) Column chart

(c) Pie chart

(d) Open Matrix chart.

Q8. How do you add a trendline to a chart in Excel?

(a) Right-click the chart and select "Add Trendline".

(b) Click the "Chart Tools" tab and click the "Trendline" button

(c) Click the "Insert" tab and click the "Trendline" button

(d) Click the "Data" tab and click the "Trendline" button

Q9. How do you filter a table in Excel based on cell values?

(a) Click the "Data" tab and click the "Filter" button.

(b) Click the "Home" tab and click the "Filter" button

(c) Right-click the table and select "Filter"

(d) Press Ctrl + Shift + L

Q10. What is the keyboard shortcut to merge and center a selection of cells in Excel?

(a) Ctrl+M

(b) Ctrl+Shift+M

(c) Ctrl+C

(d) Alt+H+M+C.

Q11. What is the function to look up a value in a table in Excel?

(a) =VLOOKUP(value, table, column).

(b) =HLOOKUP(value, table, row)

(c) =LOOKUP(value, table)

(d) =FIND(value, table)

Q12. What is a subroutine in VBA?

(a) A set of instructions that can be used multiple times in a program.

(b) A loop that continues until a condition is met

(c) A conditional statement

(d) None of the above

Q13. How do you find the average of a range of cells in Excel?

(a) =AVG(range)

(b) =MEAN(range)

(c) =SUM(range)/COUNT(range)

(d) =AVERAGE(range).

Q14. How can you add a comment to a cell in Excel?

(a) Right-click on the cell and select "Insert Comment"

(b) Go to the "Review" tab and select "New Comment"

(c) Both A and B.

(d) None of the above

Q15. What is the function of the IFERROR function in Excel?

(a) It returns a value if a cell contains an error

(b) It hides errors in a cell

(c) It replaces errors in a cell with a specified value.

(d) It displays a custom error message in a cell

Q16. What is the function of the VLOOKUP function in Excel?

(a) It searches for a value in the leftmost column of a table and returns a value from the same row in a specified column.

(b) It searches for a value in a specified column of a table and returns a value from the same row in the leftmost column

(c) It searches for a value in a specified row of a table and returns a value from the same column in the topmost row

(d) It searches for a value in the topmost row of a table and returns a value from the same column in a specified row

Q17. What is the keyboard shortcut to insert a new row in Excel?

(a) Ctrl+Shift++.

(b) Ctrl+Shift -

(c) Ctrl++

(d) Ctrl+-

Q18. How do you create a named range in a worksheet?

(a) Select the cells and go to Insert > Name > Define.

(b) Select the cells and go to Formulas > Name > Define

(c) Select the cells and go to Data > Name > Define

(d) Select the cells and go to Home > Name > Define

Q19. Which of the following is NOT a type of data validation rule?

(a) Whole number

(b) Date

(c) Custom

(d) None of the above.

Q20. Which of the following is NOT a way to import data into Excel?

(a) Import data from a text file

(b) Import data from a database

(c) Import data from a web page

(d) Import data from a scanner.

Q21. How do you insert a hyperlink in Excel?

(a) Right-click the cell and select "Insert Hyperlink"

(b) Click the "Insert" tab and click the "Hyperlink" button

(c) Press Ctrl+K.

(d) Press Ctrl+Shift+K

Q22. How do you create a new subroutine in VBA?

(a) Go to the Insert tab and click on the Subroutine button

(b) Go to the Developer tab and click on the Subroutine button

(c) Right-click on the VBA editor and select Insert > Procedure….

(d) None of the above

Q23. There x number of functions inside the AGGREGATE Function, here the value of x is -

(a) 17

(b) 18

(c) 19.

(d) 21

Q24. The last column in Excel is -

(a) XFA

(b) XFB

(c) XFC

(d) XFD.

Q25. How can you quickly remove duplicates from a list of data in Excel?

(a) Use the Remove Duplicates button on the Data tab.

(b) Use the Unique function

(c) Use the DEDUP function

(d) Use the Remove Duplicates function

Q26. What is an object in VBA?

(a) A set of instructions that can be used multiple times in a program

(b) A loop that continues until a condition is met

(c) A piece of data that can be manipulated in a program

(d) A physical or abstract entity that represents something in the real world.

Q27. How do you insert a function in Excel?

(a) Click the "Formulas" tab and click the "Function Library" button

(b) Press F3

(c) Press Shift+F3.

(d) Press Ctrl+Shift+F3

Q28. Which of the following is NOT a type of formula in Excel?

(a) Logical formula

(b) Text formula

(c) Array formula

(d) Graph formula.

Q29. How can you create a pivot table from a range of data in a worksheet?

(a) Data > Pivot Table

(b) Insert > Pivot Table.

(c) Home > Pivot Table

(d) Formulas > Pivot Table

Q30. What is a property in VBA?

(a) A characteristic of an object.

(b) A method of an object

(c) An event of an object

(d) All of the above

Q31. Which of the following is NOT a logical operator in Excel?

(a) =.

(b) <

(c) AND

(d) >

Q32. Which of the following is NOT a type of chart that can be created in Excel?

(a) Line chart

(b) Bar chart

(c) Pie chart

(d) Word cloud.

Q33. How can you create a drop-down list in Excel?

(a) Use the Data Validation feature.

(b) Use the List feature

(c) Use the Drop-Down List feature

(d) Use the Combo Box feature

Q34. How do you insert a hyperlink in a cell?

(a) Right-click on the cell and select "Insert Hyperlink"

(b) Insert > Hyperlink.

(c) Data > Hyperlink

(d) Formulas > Hyperlink

Q35. Which of the following is NOT a way to protect data in Excel?

(a) Password protection

(b) Data validation

(c) Data filters.

(d) Conditional formatting

Q36. How do you freeze panes in a worksheet?

(a) View > Freeze Panes.

(b) Page Layout > Freeze Panes

(c) Formulas > Freeze Panes

(d) Data > Freeze Panes

Q37. What is a method in VBA?

(a) A characteristic of an object

(b) An action that can be performed on an object.

(c) An event of an object

(d) All of the above

Q38. In which of the following cases would the formula =SUM(F5:F24) return an error?

(a) F5:F24 contains text values.

(b) F5:F24 contains a mix of text and numeric values

(c) F5:F24 contains only numeric values

(d) F5:F24 is an empty range

Q39. How do you declare a variable in VBA?

(a) Dim variableName As dataType.

(b) Var variableName As dataType

(c) Variable variableName As dataType

(d) Set variableName As dataType

Q40. What is a named range in Excel?

(a) A range of cells that has a specific name.

(b) A range of cells that has a specific formula

(c) A range of cells that has a specific data validation rule

(d) All of the above

Q41. What is an event in VBA?

(a) A characteristic of an object

(b) An action that can be performed on an object

(c) A trigger that causes a certain action to occur.

(d) All of the above

Q42. How do you select a range of cells in a contiguous block in Excel?

(a) Click the first cell in the range, then hold down the Shift key and click the last cell in the range.

(b) Click the first cell in the range, then hold down the Alt key and click the last cell in the range

(c) Click the first cell in the range, then hold down the Ctrl key and click the last cell in the range

(d) Click the first cell in the range, then hold down the Tab key and click the last cell in the range

Q43. What is the keyboard shortcut to insert a new row in an Excel worksheet?

(a) Alt+I

(b) Alt+R

(c) Ctrl+N

(d) Ctrl+Shift+N

Q44. How do you apply a custom number format to a cell in Excel?

(a) Select the cell, then click the Custom Format button on the toolbar

(b) Select the cell, then click the Format Cells button on the toolbar.

(c) Select the cell, then click the Number Format command in the Format menu

(d) Select the cell, then click the Number command in the Format menu

Q45. How do you access the VBA editor in Excel?

(a) Press F11

(b) Go to the Insert tab and click on the VBA button.

(c) Go to the Developer tab and click on the Visual Basic button

(d) All of the above

Q46. What is a pivot table used for in Excel?

(a) Creating formulas

(b) Sorting and filtering data

(c) Analyzing and summarizing large amounts of data.

(d) All of the above

Q47. What is VBA used for in Excel?

(a) Creating formulas

(b) Automating tasks.

(c) Creating charts

(d) All of the above

Q48. What is the function of the SUMIFS function in Excel?

(a) It sums the values in a specified range that meet multiple criteria.

(b) It calculates the average of the values in a specified range that meet multiple criteria

(c) It counts the number of cells in a specified range that meet multiple criteria

(d) It returns the maximum value in a specified range that meets multiple criteria

Q49. How do you insert a sparkline in a cell?

(a) Insert > Sparkline.

(b) Data > Sparkline

(c) Formulas > Sparkline

(d) Sparklines are not available in Excel

Q50. How do you add a border to a cell in Excel?

(a) Select the cell, then click the Border button on the toolbar

(b) Select the cell, then click the Add Border command in the Format menu

(c) Select the cell, then click the Cell Borders command in the Format menu.

(d) Select the cell, then click the Borders command in the Format menu ****** ADVANCE EXCEL QUESTIONS 1)       Explain MS Excel in brief. 2)      What is the diffrence between workbook and worksheet? 3)     What is the shortcut key to insert a new worksheet in Excel? 4)     What is the file format in MS-Excel for smallest file size? 5)     How many cells in excel? 6)      What is a cell reference? 7)      What do you understand about the Wildcards in excel? 8)     What the mathematical operators is in excel? 9)      What is the logical operators is in excel? 10)   What is an Excel array formula? 11)     How many types of Array? 12)   What is a Named Range? 13)   What is the difference between filter and advance? 14)   What is the difference between AutoFilter and advanced filter? 15)   How many number format in Excel? 16)   What is the purpose of pivot tables? 17)   What is conditional formatting in Excel? 18)   What is meant by “data validation” in Excel? 19)   What is a Macro? How do you create a macro in Excel? 20) What is an Excel Dashboard? 21)   What is the difference between Goal Seek and Solver? 22)  How can one create a dropdown list in Excel? 23) How do you get rid of duplicates in Excel? 24) What is the use of Slicer and Timeline in Excel? 25) What is the difference between if and ifs? 26)  What is the difference between countif and countifs? 27)  What is the difference between sumif and sumifs? 28) How is “VLOOKUP” different from “LOOKUP”? 29)  How is “VLOOKUP” different from “XLOOKUP”? 30) What is the difference between VLOOKUP and INDEX-MATCH? 31)   Explain any 10 error name in Excel.  32) What is Power Query used for? 33) How do you protect cells or sheets in Excel? 34)  What is Custom List in Excel? and How do I create a custom list?