Excel is a powerful tool that is widely used in businesses and organizations of all sizes. As such, it is not surprising that employers frequently test their potential candidates’ proficiency in Excel during the interview process. For advanced-level Excel jobs, in particular, candidates are often required to demonstrate their ability to perform complex tasks and solve intricate problems using the software. In this article, we have compiled a list of the top 30 advanced Excel interview questions and answers that will help you prepare for your upcoming Excel job interview. Whether you are a seasoned Excel user or a beginner, these questions and answers will provide you with a comprehensive understanding of the most important concepts and functions in Excel, and give you the confidence you need to ace your interview.
Take some time to review these typical Excel interview questions if you’re getting ready for your upcoming interview and have listed Excel as one of your skills.
The interviewers who want to evaluate the candidates’ Excel proficiency can also use this tutorial.
This Tutorial Covers:
- What is the What-If Analysis in Excel?
- What filter will you use, if you want more than two conditions or if you want to analyze the list using database function?
- What is the quick way to return to a particular area of a worksheet?
- Which function is used to determine the day of the week for a date?
- What is the benefit of using formula in Excel sheet?
- What is the “What If” condition in Excel formulas?
- How can you disable the automating sorting in pivot tables?
- What is the AND function does in Excel?
- How cell reference is useful in the calculation?
- What is the difference between a function and a subroutine in VBA?
- What is the difference between ThisWorkbook and ActiveWorkbook in VBA?
- How will you pass arguments to VBA Function?
- How do you find the last row and column in VBA?
- How do we check whether a file exists or not in a specified location?
- What is debug in VBA code?
- Explain how to debug a VBA code?
- Write a VBA function to calculate the area of a rectangle?
- Write a VBA function to check if a number is a prime number or not?
- Write a VBA code to create a bar chart with the given data?
- Describe an advantage of using sheet formulas in Excel?
- Explain the order of operations Excel follows when evaluating a formula?
- Name three core modules in VBA?
- What are the advantages of macros?
- How do you run a macro automatically when you open a workbook in VBA?
- How do you save macros?
- How do you hide macros?
- How do you stop recording a macro?
- What is Goal Seek?
- What is a Solver?
- What is Scenario Manager?
- What is the What-If Analysis in Excel?
Excel’s What-If Analysis feature is an effective tool for performing intricate mathematical calculations, playing with data, and testing out various situations.
Think about the following instance:
How much profit can you anticipate if you generate $20,000 in sales during the coming months?
The What-If Analysis can be used to resolve these situations.
Click on the “What-If Analysis” link under “Forecast” on the “Data” tab.
- Comparing several possibilities is done using Scenario Manager.
- The Goal Seek does calculations backward.
- A sensitivity analysis is performed using the Data Table.
2.What filter will you use, if you want more than two conditions or if you want to analyze the list using a database function?
To evaluate the list or determine whether more than two conditions should be tested, you will utilize Advanced Criteria Filter.
3. What is the quick way to return to a particular area of a worksheet?
Use the name box to quickly get back to a certain section of the worksheet. For a worksheet’s name box, type the cell address or range name to go back to a particular location.
4. Which function is used to determine the day of the week for a date?
The weekday for a specific date, counting from Sunday is returned by the WEEKDAY () function.
Example: Make the date 2/28/2023 at cell A2.
Type the below formula in any blank cell.
=WEEKDAY(A2,1)
5. What is the benefit of using formula in Excel sheet?
When you calculate numbers in an Excel sheet, it automatically calculates the number that will be replaced by another number or digit in addition to helping you to provide the final “sum up” of the numbers. Calculations that were formerly difficult, like payroll deduction or averaging student test results, are now simple thanks to Excel sheets.
6. What is the “What If” condition in Excel formulas?
Microsoft Excel formulas can have their data changed to produce various results by using the “What If” condition.
Use the “What If” option, for instance, to determine the precise tax that would be charged on a new bike that you are considering purchasing. There are three cells, A2, B2, and C2, for instance. The first cell contains information about the price, the second cell contains information about the amount of tax (8%) and the third cell computes the precise tax.
7. How can you disable the automating sorting in pivot tables?
To stop pivot tables from automatically sorting,
Go to “More Sort Options” by clicking on your mouse’s left button on “Pivot table” . Then select “Sort” menu > Select “More Options” > Deselect the “Sort automatically when the report is created.”
8. What is the AND function do in Excel?
The AND function performs the logical function, just like the IF function. The AND function will test at least one mathematical expression present in a different spreadsheet cell to determine whether the outcome will be true or false. Using the AND function makes it possible to see the results of multiple cells in a single cell.
Example: There are three subjects and five students in an exam. To pass the exam, a student has to pass all three subjects. The student fails if he/she fails in any of the three subjects.
Explanation:
Student 1, Student 4 and Student 5 have passed all the subjects. Since all the logical conditions for these three students are met, the AND function gives the output “TRUE.”
The rest of the students were unable to pass all three subjects. If any of the subjects is not passed, the AND function returns “FALSE.”
9. How cell reference is useful in the calculation?
Cell references are utilized to prevent repeatedly writing the data for calculation purposes. You must tell Excel the precise location of the data when you create a formula for a particular function. A cell reference is the term used to describe this place. As a result, each time a new value is entered, the cell will compute using the formula for the reference cell.
10. What is the difference between a function and a subroutine in VBA?
| Functions | Subroutines | ||
| A function’s responsibility is to give the result of the action it takes. | Subroutines, on the other hand, don’t return the result of the work they are carrying out. | ||
| They receive a call from a variable. | They come in various types and can be recalled from anyplace in the software. | ||
| As with formulas, functions are employed in spreadsheets. | Spreadsheets do not directly use subroutines as formulas. | ||
| Repetitive tasks are performed by functions, which then produces a value. | Before retrieving the subroutine’s output, users must enter a value in the targeted cell. |
11. What is the difference between ThisWorkbook and ActiveWorkbook in VBA?
| ActiveWorkbook | ThisWorkbook |
| The Workbook in the current window that is the focus of the screen is represented by ActiveWorkbook. | The current workbook in which the active VBA code is running or executing is represented by ThisWorkbook. |
| Workbook window is active and clearly visible. | The Workbook window is dormant or concealed. |
| Get Active Workbook Name: ActiveWorkbook.Name |
Get Current Workbook Name: ThisWorkbook.Name |
| Activate ActiveWorkbook: ActiveWorkbook .Activate |
Activate ThisWorkbook: ThisWorkbook .Activate |
| Example:
‘VBA Get Active Workbook Name |
Example:
‘VBA Get Current Workbook Name |
12. How will you pass arguments to VBA Function?
A VBA function can accept arguments as either a reference or a value.
An example of each usage is shown below.
Dim x As Integer
x = 10
MsgBox Triple(x)
MsgBox x
When the cells are run with the values given as references, 40 is shown twice. The original value is referenced when passing arguments via reference. The function modifies the initial value of x.
We are passing a copy to the function when we pass the arguments by value. The initial value remains unchanged. Consequently, the second MsgBox will show the starting value of 10.
13. How do you find the last row and column in VBA?
Use the code in the following lines of the VBA module to locate the last row:
Sub FindingLastRow()
Dim lastRow As Long
lastRow = ActiveSheet.Cells.SpecialCells(xlLastCell).Row
MsgBox (lastRow)
End Sub
Use the code in the following lines of the VBA module to locate the last column:
Sub FindingLastColumn()
Dim lastRow As Long
lastColumn = ActiveSheet.Cells.SpecialCells(xlLastCell).Column
MsgBox (lastColumn)
End Sub
- How do we check whether a file exists or not in a specified location?
Use the code in the following lines of the VBA module to check whether a file exists or not in a specified location:
Sub CheckFileExists()
Dim strFileName As String
Dim strFileExists As String
strFileName = “File location\file_name.xlsx”
strFileExists = Dir(strFileName)
If strFileExists = “” Then
MsgBox “The selected file doesn’t exist”
Else
MsgBox “The selected file exists”
End If
End Sub
- What is debug in VBA code?
You might wish to test a particular statement to make sure it is functioning properly as you write the program. The chore of debugging code in Excel VBA is crucial while building applications since it aids in code troubleshooting.
Finding and resolving faults, flaws, and other undesired features in the VBA code is the process of “debugging” VBA.
To identify faults and flaws without having to rewrite the code, VBA has several built-in debugging tools available.
Stepping over, stepping out, breakpoints, and debug print are a few of the debugging features offered by the VBA Editor.
- Explain how to debug a VBA code?
The F8 key can be utilized to debug a VBA program line by line. Additionally, you have the option of setting a breakpoint to stop the execution anywhere.
When you press F8, the execution will begin at the beginning of the code and go forward through the lines until it reaches the end. Your present point in executing is indicated by the yellow arrow and the highlighted line.
- Write a VBA function to calculate the area of a rectangle?
Use the code in the following lines of the VBA module to calculate the area of a rectangle:
Function Area(Length As Double, Optional Width As Variant)
If IsMissing(Width) Then
Area = Length * Length
Else
Area = Length * Width
End If
End Function
- Write a VBA function to check if a number is a prime number or not?
Use the code in the following lines of the VBA module to check if a number is a prime number or not:
Sub Prime()
Dim divisors As Integer, number As Long, i As Long
divisors = 0
number = InputBox(“Enter a number”)
For i = 1 To number
If number Mod i = 0 Then
divisors = divisors + 1
End If
Next i
If divisors = 2 Then
MsgBox number & “ is a prime number”
Else
MsgBox number & “ is not a prime number”
End If
End Sub
- Write a VBA code to create a bar chart with the given data?
Consider the data below, which has two characteristics.
The lines of code that follow can be used to make a bar chart.
Sub MakeChart()
Dim myRange As Range
Set myRange = Application.InputBox _
(Prompt:=”Select chart inputs”, Type:=8)
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=myRange, _
PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet
End Sub
The bar chart you will receive after running the aforementioned VBA code lines is shown below.
- Describe an advantage of using sheet formulas in Excel?
Excel formulae have the benefit of making complex data sets easier to calculate quickly and automatically. When carrying out repetitive calculations or evaluating vast amounts of data, this can save time and lower the possibility of errors. The data analysis can be readily changed as needed because to the ease with which formulas may be updated or adjusted. A workbook’s calculations can be consistent and reliable since formulas can be applied to several cells or worksheets.
Candidates may draw attention to the fact that, if a number in a formula cell is changed, sheet formulas can not only do calculations but also update the results of calculations.
- Explain the order of operations Excel follows when evaluating a formula?
When Excel calculates a formula, it does so using the PEMDAS acronym. The following order of operations is what PEMDAS refers to, and promising applicants should be aware of this:
- Parentheses
- Exponentiation
- Multiplication or Division (left to right)
- Addition or Subtraction (left to right)
When resolving mathematical equations, this mnemonic is used to help remember the proper order of operations. A set of guidelines known as the order of operations is used to specify the order in which calculations should be completed in a mathematical problem. PEMDAS aids in ensuring that calculations are performed in the proper order to provide accurate results.
- Name three core modules in VBA?
There are three primary core components in VBA:
- Class modules: Using class modules, users can create new items and improve already-existing objects.
- User forms:A particular kind of module called user forms is used to create an application’s graphical user interface (GUI).
- Code modules:Functions and procedures are typically written in code modules, which are the default modules.
- What are the advantages of macros?
The advantages of macros are written below:
- Automation: Macros allow the for automation of repetitive tasks, saving time and increasing efficiency.
- Customization: Macros can be customized to perform specific tasks, such as data validation, data manipulation, and formatting.
- Error Reduction: Macros can reduce human errors by automating tasks and reducing the need for manual data entry.
- Consistency: Macros can ensure consistency in data processing, reducing the possibility of errors and inconsistencies.
- Efficiency: Macros can improve the speed and efficiency of data processing, allowing for faster data analysis and decision-making.
- Complex Tasks: Macros can automate complex tasks that would otherwise be difficult or time-consuming to perform manually.
- Reusability: Macros can be saved and reused, allowing for easy replication of tasks across different worksheets or workbooks.
- Collaboration: Macros can be shared among team members, allowing for collaboration and teamwork.
- How do you run a macro automatically when you open a workbook in VBA?
Candidates may respond to this query by saying that you ought to begin by pressing the shortcut Alt+F11 to launch the VBA editor. Double-click “ThisWorkbook” located on the left side of the project explorer after that. Enter the code “Private Sub Workbook Open()” after typing it in. Between “Private Sub Workbook Open()” and “End Sub,” type the recorded code. Excel Macro-Enabled Workbook (XLSM) format should be used to save your workbook after you close the VBA editor.
- How do you save macros?
Candidates may outline two methods for saving macros. Your macro could only be necessary for the specific worksheet in which you created it. Simply choose “File” and then “Save” or “Save As” in this situation.
Alternatively, you may need to save a worksheet that contains macros and preserve them when you save the file. When prompted if you wish to continue saving as a macro-free workbook, you must choose “No” in order to accomplish this. Save the workbook in XLSM format after that.
Macros are typically saved as a separate file, with the file extension depending on the program used to create the macro. For example, in Microsoft Excel, macros are saved as .xlsm files, while in Microsoft Word they are saved as .docm files. To save a macro in Excel, you can go to the “Developer” tab, click on “Record Macro”, give the macro a name, and then specify where you want to save it. In Word, you can go to the “Developer” tab, click on “Macros”, give the macro a name and then specify where you want to save it.
- How do you hide macros?
Candidates may mention the fact that users can open the macro they want to hide before hiding it. The first line of code, which contains the “Sub” command, must then be visited. They must write the term “Private” at the beginning of the line, leaving a space between it and the macro’s name. The macro will be hidden after it has been saved.
- How do you stop recording a macro?
Are your candidates aware that they must go to the “View” tab and choose “Macros” in order to cease recording a macro? The next step is to select “Stop Recording.”
- What is Goal Seek?
As the name implies, Excel’s Goal Seek feature assists you in obtaining a value (the goal) by changing a dependent value.
You can use Goal search to determine, for instance, how many monthly installments you should choose when purchasing a vehicle so that your monthly payment doesn’t exceed $200.
- What is a Solver?
With the help of the Excel add-in Solver, you can find the best solution when there are numerous variables and restrictions. It can be thought of as a more sophisticated variation of goal-seeking.
You can define the limitations and the goal you need to accomplish using Solver. To provide you with a potential answer, it performs the calculations in the background.
- What is Scenario Manager?
When you have a lot of variables and want to see how changing them will affect the outcome, Scenario Manager in Excel is a good tool to use. You can develop a one-variable or two-variable data table if just one or two variables are changing. The scenario manager, however, is the best option if you have three or more variables that can vary.
You can utilize scenario manager to build several situations, such as the following if you are a regional sales manager and have four areas reporting to you:
- None of the areas see any sales growth.
- Area A expands by 10% while the other 3 remain static.
- Area A and B grow by 10% while the other two remain static.
You get the point, don’t you?
Excel’s scenario manager makes it simple to build and analyze scenarios, either individually or collectively.
For ready-to-use Dashboard Templates:
