In this article, you will find advanced Excel questions and answers to test your skills. You should know the following:
- introduction to Excel VBA,
- enable the Developer tab,
- the basics of an Excel spreadsheet,
- use of the PivotTable,
- application of Data Validation,
- use of the Slicers,
- utilization of the PivotTable Timeline feature,
- use of the Excel Filter feature,
- use of the Excel Chart options,
- utilization of the Trendline feature,
- the Name Range,
- custom format cells,
- understanding of the Sparkline,
- application of cell border,
- AGGREGATE, AVERAGE, FIND, IFERROR, SUM, SUMIFS, VLOOKUP, LOOKUP, and HLOOKUP functions.
You should use at least Excel 2019 to solve the problems without any issues.
Here is the overview of the top 50 advanced Excel exam questions and answers.
Download the Question Workbook
Problem Overview
The Excel file contains fifty advanced Excel exam questions and their corresponding answers.
- The questions are provided in the “Problem” sheet.
- The answers are highlighted in the “Solution” sheet.
- Provide your answer (by typing or selecting from the dropdown list) in the “I” column.
- If it is correct, you will get a green checkmark in the “J” column.
Let’s go through the Excel multiple-choice questions. You can provide your answers either by typing or using the dropdown list in the Excel file containing the advanced exam questions and answers.
- Q1. In Excel, what shortcut key is used to add a new worksheet?
- (a) Ctrl + F11
- (b) Alt + F11
- (c) Shift + F11
- (d) Shift + Alt + F11
- Q2. In Excel, what is the function for adding a group of cells?
- (a) ADD
- (b) SUM
- (c) SUMA
- (d) SUMS
- Q3. Which of the following options is NOT a kind 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 an Excel filtering method?
- (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. In Excel, how do you add a trendline to a chart?
- (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 can you use Excel to filter a table 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. 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. A Function to look up a value in a table in Excel is –
- (a) =VLOOKUP(value, table, column)
- (b) =HLOOKUP(value, table, row)
- (c) =LOOKUP(value, table)
- (d) =FIND(value, table)
- Q12. In VBA, a Subroutine is –
- (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. The function of the IFERROR function is –
- (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. The VLOOKUP function in Excel is –
- (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. To create a named range in a worksheet, you should –
- (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. In VBA, how do you make a new subroutine?
- (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 > Subroutine
- (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. In Excel, how do you quickly eliminate duplicates from a list of data?
- (a) Use the Remove Duplicates button on the Data tab
- (b) Use the Unique function
- (c) Use the Dedupe 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 does NOT fall under the category of an Excel formula?
- (a) Logical formula
- (b) Text formula
- (c) Array formula
- (d) Graph formula
- Q29. How do you make a pivot table from a set 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 chart types CANNOT 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. What is the best way to freeze worksheet panes?
- (a) View > Freeze Panes
- (b) Page Layout > Freeze Panes
- (c) Formulas > Freeze Panes
- (d) Data > Freeze Panes
- Q37. What does a method in VBA mean?
- (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 does an Excel named range mean?
- (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 does “event” mean 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. In Excel, how do you choose a group of cells that are adjacent to one another?
- (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 can a custom number format be applied to an Excel cell?
- (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. In Excel, how do you get to the VBA editor?
- (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 purpose does an Excel pivot table serve?
- (a) Creating formulas
- (b) Sorting and filtering data
- (c) Analyzing and summarizing large amounts of data
- (d) All of the above
- Q47. What purposes does VBA serve in Excel?
- (a) Creating formulas
- (b) Automating tasks
- (c) Creating charts
- (d) All of the above
- Q48. What does Excel’s SUMIFS function do?
- (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 meets 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 should a sparkline be inserted into a cell?
- (a) Insert > Sparkline
- (b) Data > Sparkline
- (c) Formulas > Sparkline
- (d) Sparklines are not available in Excel
- Q50. In Excel, how do you add a border to a cell?
- (a) Pick out the desired cell, then click the Border button on the toolbar
- (b) After choosing the cell, select Add Border from the Format menu
- (c) Choose the cell and then, from the Format menu, select the Cell Borders command
- (d) Pick the cell and then select the Borders option from the Format menu
Here’s an overview of how to input the answer in the “Problem” sheet.
Pls share answer
Dear Anjli Gupta,
Answers are given in the Solution sheet of Excel File which is in Download Practice Workbook section.
Regards
ExcelDemy
Thank you!!!!
Can’t find answers to.the questions
Thank you again
Hello Deborah Yassen,
You are most welcome.
Regards
ExcelDemy
I cannot find answers to the questions
Dear Ayebusiwa Oyesanmi Emmanuel,
Answers are given in the Solution sheet of Excel File which is in Download Practice Workbook section.
Regards
ExcelDemy