Are you thinking of joining an Advanced Excel level position in any company?
Well, you need to be prepared for all the ups and downs while preparing yourself. Try to go through all the possible questions that are asked in an advanced excel interview.
This article will definitely help you to at least to answer the questions that revolve around them. For your convenience, we have compiled a list of 50+ advanced excel interview questions as well as their answers that will help you in getting a job.
Advanced Excel Interview Questions
Q1. Explain the functions of COUNT and COUNTA?
Ans: COUNT is helpful in counting the number of cells that contain only numbers except for blank cells whereas COUNTA counts the number of cells that contain alpha-numeric except blank cells.
Q2. What is the default data type in VBA?
Ans: A variant is the default data type in VBA.
Q3. What is the use of Option Explicit in VBA?
Ans: Option Explicit will force the user to declare variables. The use of undeclared variables results in an error while compiling the codes.
Q4. How will you fix/lock the cell/range reference?
Ans: Using $ symbol.
Example: $O $8 – Here Locked Column O and row 8.
Q5. How will you inspect a function/formula in Excel?
Ans: Using F9 key.
Q6. What is the difference between VLOOKUP and INDEX-MATCH?
Ans: VLOOKUP – With the help of VLOOKUP, you can retrieve the data from left to right in the range/Table.
INDEX-MATCH – With the help of a combination of INDEX and MATCH, you can retrieve the data from left to right/right to left in a range/table.
Q7. Difference between FUNCTION and FORMULA in excel?
Ans: FORMULA – is a statement which is written by the user for calculations. Ex: =1+2+3
FUNCTION – is a built-in formula by Excel. Ex: =SUM(1+2+3).
Q8. What is Microsoft Excel ?
Ans: Microsoft Excel is an electronic spreadsheet program, created by Microsoft Corporation. It helps you to organize, format, and calculate data with formulas using a spreadsheet system broken up by rows and columns. Microsoft Excel is also used for storing, organizing and manipulating the data with formulas using a spreadsheet system broken up by rows and columns.
Q9. How can you add a new Excel worksheet?
Ans: To add a new Excel worksheet, you should insert a worksheet tab at the bottom of the screen.
Q10. To move to the previous worksheet and next sheet, what keys will you press?
Ans: To move to the previous worksheet, you will use the keys Ctrl + PgUp, and to move to the next sheet you will use keys Ctrl + PgDown.
Q11. What does the IF function in Excel?
Ans: IF function is used in Excel to check whether certain conditions are true or false. True conditions will display the desired results while false will display other results.
Q12. How can you wrap the text within a cell?
Ans: You need to select the desired text to be wrapped and then select wrap text from home tab.
Q13. How can you sum up the Rows and Column number quickly in the Excel sheet?
Ans: In this scenario, SUM function, you can get the total sum of the rows and columns, in an Excel worksheet.
Q14. What is the use of NameBox in MS-Excel?
Ans: In order to return to a particular area of the worksheet, NameBox is used by typing the range name or cell address in the name box.
Q15. What are three report formats that are available in Excel?
Ans: Following are the types of report formats
- Compact
- Report
- Tabular
Q16. How can you create shortcuts to Excel functions?
Ans: ‘Quick Access Toolbar found’ above the home button can be used to customized to display most frequently used shortcuts.
Q17. Define Format Painter?
Ans: If you want to copy the format of a cell, text, image etc and apply on another text, the Format painter is used.
Q18. In excel, what is the function used to get the length of a string?
Ans: Use the function ‘LEN’ to find the text string length.
Q19. Mention the event used to check the status of a Pivot Table modification
Ans: Use the event ‘PivotTableUpdate’ to check the status of a Pivot Table modification in a worksheet.
Q20. What is the shortcut key to minimize the workbook?
Ans: The keys ‘Ctrl+F9’ is the shortcut key to minimize the workbook.
Q21. Is it possible to close all the open excel files at a time?
Ans: Yes, it is possible to close all the open excel files at a time.
Q22. What does it mean by a red triangle on the top right of a cell?
Ans: The triangle indicates that there is some comments associated with that particular cell. You can view or read the comment by placing the mouse on it.
Q23. What are the New Enhancements to Excel?
Answer: The following are some of the New Enhancements to Excel:
- Slicers
- Tables
- Sparklines
- Powerpivot
- IFERROR
Q24. How will you cancel an entry using the shortcut key?
Ans: ‘Esc’ key is used to cancel the entry in Excel.
Q25. What are the different types of data filters available in Excel?
Ans: Date filter, Text Filter and Number Filter are the different types of data filters available in Excel.
Q26. Will we be able to change the font and color of the multiple sheet tabs?
Ans: Yes, we can easily change the font and color of the sheet tabs in Excel.
Q27. What is the shortcut for spell check?
Ans: F7 – This opens the spell-check dialog box
Q28. What is VBA?
Ans: VBA stands for Visual Basic for Applications. It is one of the programming languages that you can use to automate tasks in Excel.
Q29. What are some of the useful loops in VBA?
Ans: There are the following loops in Excel VBA:
- For Next Loop
- Do While Loop
- Do Until Loop
- For Each Next Loop.
Q30. Can you use VLOOKUP for multiple tables?
Ans: Yes, VLOOKUP can also be used for multiple tables as well. If you have two lookup tables, you just need to create named ranges for each table, and then use the IF function to select between each table based on some given condition.
Q31. What are the ways to create a dynamic range?
Ans: The ways to create a dynamic range are:
- Creating a Table
- Using OFFSET and COUNTA functions.
Q32. What is Excel object Model?
Ans: Application – Workbooks – Worksheets – Range.
Q33. What are ADO, ODBC and OLEDB?
Ans: ADO –ActiveX Data Objects is a data access framework and is useful to get the data from the databases.
ODBC – Open Database Connectivity is useful to get the data from the external database.
OLEDB – Object Linking and Embedding, Database.
Q34. What is a Slicer?
Ans: Slicer is used to filter the Table, Pivot Table data. Instead of using Filters section in a Pivot Table, we can use Slicer.
Q35. Can you name some Text Functions?
Ans: CONCATENATE( ) – To join several text strings to one string.
- PROPER ( ) – Arranges the characters in a proper way.
- TEXT( ) – Converts a value into text formatting
- LEFT ( ) – Returns the specified number of characters from the starting character.
Q36. How will you find duplicate values in a column?
Ans: To highlight duplicate values – use Conditional Formatting
To get a number of duplicate values – use COUNTIF function.
Q37. What are charts in MS-Excel?
Ans: To enable graphical representation of the data in Excel, charts are provided. You can use any chart type, including column, bar, line, pie, scatter, etc. by clicking Insert and then selecting the type of chart.
Q38. Which event do you use to check whether the Pivot Table is modified or not?
Ans: To check whether the pivot table is modified or not we use “PivotTableUpdate” in worksheet containing the pivot table.
Q39. What is the use of LOOKUP function in Excel?
Ans: The LOOKUP function returns a value from a range or an array in an Excel.
Q40. How can you disable automatic sorting in pivot tables?
Ans: To disable automatic sorting in pivot tables:
You need to click on Go To, then More Sort Options, then Right Click ‘Pivot tables’, then Select ‘sort menu’, then select ‘More Options’ and finally deselect ‘Sort automatically’.
Q41. What is the latest version of Microsoft Excel?
Ans: Microsoft Excel with an Office 365 subscription is the latest version of Microsoft Excel.
Q42. What is the Name Manager in Excel?
Ans: Names which we give for a cell/Range, Table will be managed by the Name Manager.
Q43. What are the ways to run a macro?
Ans: The different ways to run a macro are:
- Assigning a macro to a shape.
- Assigning a macro to a button
- Run a macro from the ribbon
- Run a macro using a keyboard short cut key.
Q44. How do you remove duplicate spaces from a cell?
Ans: Using TRIM () function, we can delete duplicate spaces and gives unique/single space between words.
Q45. Can you add cells?
Ans: Yes, you can insert new cells into a sheet. To add a new cell, simply select the cell where you want to insert it and then select the Insert option.
Q46. How would you add a new column to an existing pivot table for calculations?
Ans: Using Calculated Field.
Q47. What are volatile functions in Excel?
Ans: Volatile functions recalculate the formula, again and again, so Excel workbook performance will be slow down. Volatile functions recalculate the formulas whenever any changes occur in the worksheet.
Ex: NOW (), RAND ()
Q48. Is it possible to make a single Pivot Table for multiple data sources?
Ans: Yes, it is possible. Using Pivot Table Data Modeling technique.
Q49. Define cells in Excel?
Ans: The place where we store the data is called a cell.
Q50. Provide the two macro languages available in MS Excel
Ans: Visual Basic Applications (VBA) and XLM are the two (2) macro languages available in MS Excel.
Q51. Can you give us the different sections in a Pivot Table?
Ans: Filter Area, Columns Area, Values Area, and Rows Area are the sections available in Pivot Table.
Wrap Up
We hope that these interview questions on Advanced Excel will definitely help you in forming an idea about questions that are generally asked in an advanced excel interview.
For more details on interview-related questions, you must remain connected with our website.
