50 advanced Excel interview questions

Do you need to hire an Excel expert for your organization?

Finding the right talent isn’t always simple, and it can be challenging to evaluate their skills. However, there are a couple of useful ways to hire a professional with exceptional Excel skills. 

When you source candidates, you can give them an Advanced Excel skills test to instantly identify those with advanced Excel skills.

Then, when you have a shortlist of candidates, you can invite them to an interview and ask them advanced Excel interview questions to further gauge their skills.

If you’re wondering which questions you should ask during the interview, we’ve made it easier for you.

Here are 50 advanced Excel interview questions you can ask your candidates.

18 general advanced Excel interview questions

Let’s start with some general Excel interview questions. Asking these questions will help you assess a range of advanced Excel skills and learn if your candidates have the right skill set for the role you’re hiring for.

1. What does a nested “If” statement do?

2. How is “VLOOKUP” different from “LOOKUP”?

3. How do you disable the automatic sorting function in pivot tables?

4. How do you view specific results in a pivot table?

5. What is meant by “data validation” in Excel?

6. Describe what conditional formatting is in Excel.

7. Describe how to use conditional formatting in Excel.

8. How do you preserve the formatting of pivot tables when refreshing them?

9. Which approach would you use to measure percentages in Excel?

10. How do you create a feature shortcut in Excel?

11. How do you apply an identical format to all workbook sheets in Excel?

12. How do you return to a certain cell in a worksheet?

13. Explain what a “relative cell address” is.

14. Explain the order of operations Excel follows when evaluating a formula.

15. Describe an advantage of using sheet formulas in Excel.

16. How do you build named ranges?

17. Describe how you would protect workbooks, sheets, and cells in Excel.

18. Explain what a variant data type is.

5 critical general advanced Excel interview questions and some potential answers

When you are interviewing candidates for your vacancy, look for these answers in response to 5 of the most important general advanced Excel interview questions.

1. Describe how you would protect workbooks, sheets, and cells in Excel.

Candidates may mention that it’s possible to protect a workbook by setting a password, which the user must enter to open the workbook.

They may say that it’s possible to protect sheets by navigating to the “Review” tab and selecting “Protect Sheet”. This prevents users from adding or removing sheets. Candidates might also explain that you must set a password for the worksheet and choose the permissions that you wish to give to users.

Candidates may also tell you that since cells are locked by default in Excel, they are all protected when you protect the worksheet. However, it’s possible to choose cells that you don’t want to protect by right-clicking them and selecting “Format Cells”. From there, navigate to the “Protection” tab and deselect the “Locked” option. Finally, click “OK”.

2. How do you disable the automatic sorting function in pivot tables?

Do your candidates know how to disable the automatic sorting function? This is done by right-clicking on the pivot table, selecting “More Sort Options” to open the “Sort” dialog box, clicking “More Options”, and deselecting the “Sort automatically” option.

3. What is conditional formatting in Excel?

Candidates should know that the conditional formatting feature enables users to format cells that meet certain criteria. For example, they may note that it’s possible to format a cell to have a bold font if the value the cell contains is greater than 1,000.

4. Describe an advantage of using sheet formulas in Excel.

Candidates may point out that sheet formulas can not only make calculations but also update the result of a calculation if you replace a number in a cell that is part of the formula.

5. Explain the order of operations Excel follows when evaluating a formula.

Excel follows the PEMDAS acronym when it calculates a formula. Promising candidates should know that PEMDAS refers to the following order of operations: 

1. Parentheses

2. Exponentiation

3. Multiplication or Division (left to right)

4. Addition or Subtraction (left to right)

23 advanced Excel interview questions related to Visual Basic for Applications

The following 23 advanced Excel interview questions will help you learn whether your candidates have advanced skills in and knowledge of Visual Basic for Applications (VBA) in Excel. 

1. What does “Option Explicit” do in VBA?

2. Name three core modules in VBA.

3. How do you pass arguments in VBA?

4. How is “ThisWorkbook” different from “ActiveWorkbook” in VBA?

5. How are subroutines different from functions in VBA?

6. Explain what user forms are in VBA.

7. Explain what ADO is.

8. Describe some of the ADO objects.

9. Which functions are commonly used in VBA?

10. What does “ByVal” do in VBA?

11. What does “ByRef” do in VBA?

12. How are “ByVal” and “ByRef” different in VBA?

13. Explain how to assign a value to an array.

14. What does it mean to format expressions in VBA?

15. What is the purpose of comments in VBA, and how do you write a comment?

16. Name three ways to correct code in VBA.

17. Explain what type of language VBA is.

18. What is the code window in VBA, and where is it located?

19. Explain the different types of VBA procedures.

20. Explain how variables are declared in VBA for Excel.

21. Explain what loops are in VBA.

22. Explain how to test conditions in VBA.

23. Which shortcut will take you to the VBA editor?

5 critical advanced Excel interview questions related to Visual Basic for Applications and some potential answers

To learn whether your candidates have advanced VBA skills, compare their responses with these potential answers to the 5 crucial Excel interview questions related to VBA.

1. What is the code window in VBA, and where is it located?

The VBA code window is a space in which users write code for VBA projects. Do your candidates know that the VBA code window is located on the right-hand side of the VBA pane? Candidates may mention that the shortcut key used to view the code window is F7.

2. Explain what loops are in VBA.

Candidates should know that loops can be used in VBA to repeat code blocks until certain conditions are met. They can also be used to highlight empty dataset rows or strings of text. There are different types of loops in VBA, including the following:

• Do Until

• Do While

• For Each

• For Next

3. Name three core modules in VBA.

Three of the main core modules in VBA are:

• Class modules. Users can make new objects and enhance existing objects using class modules.

• User forms. User forms are a type of module used for building a graphical user interface (GUI) for an application.

• Code modules. Code modules are usually the default modules in which functions and procedures are written.

4. Explain what ADO is.

ADO stands for ActiveX Data Objects. Candidates should know that ADO can help you access data from several sources. You can retrieve and gather data from all data sources so that it’s located on one platform. Without ADO, you would have to write the code for every required data source.

5. What is the purpose of comments in VGA, and how do you write a comment?

Do your candidates know that they can write comments in VBA code that describe the code’s logic? Can they explain that these comments help other users work with the code? Are they aware that VBA comments are created by placing an apostrophe at the beginning of a line of code?

9 advanced Excel interview questions related to macros

Ask your candidates these 9 advanced interview questions to find out whether they have the comprehensive and advanced Excel skill set required for your open role.

1. What are the advantages of macros?

2. How do you delete a macro from a workbook?

3. How do you run a macro automatically when you open a workbook in VBA?

4. How do you save macros? 

5. How do you hide macros?

6. Describe four ways to run macros.

7. Is it possible to pull data from other workbooks with macros? How do you do this?

8. How do you assign a macro to a particular button?

9. How do you stop recording a macro?

5 critical advanced Excel interview questions related to macros and some potential answers

Find out whether your candidates have advanced skills with Excel macros by comparing their responses with the potential answers listed below.

1. What are the advantages of macros?

Your candidates may say that macros offer two main advantages:

• They save time by automating repetitive tasks

• They enhance productivity and accuracy by reducing the need to input commands manually

2. How do you run a macro automatically when you open a workbook in VBA?

In response to this question, candidates may state that you should start by opening the VBA editor with the shortcut Alt+F11. Then, on the left side of the project explorer, double-click “ThisWorkbook”. Input the code “Private Sub Workbook_Open()” and hit the enter key. Next, write the recorded code between “Private Sub Workbook_Open()” and “End Sub”. After you close the VBA editor, save your workbook in Excel Macro-Enabled Workbook (XLSM) format.

3. How do you save macros?

Candidates may describe two ways to save macros. You may only need your macro for the particular workbook in which you made it. In this case, just select “File” and then “Save” or “Save As”. 

Alternatively, you may need to save a workbook that contains macros and preserve them after you save the workbook. This requires you to select “No” when asked if you want to continue saving as a macro-free workbook. Then, save the workbook in XLSM format.

4. How do you hide macros?

Candidates may mention that users can hide macros by first opening the macro that they want to hide. They must then go to the first line of code, which contains the “Sub” command. At the start of the line, they need to write the word “Private”, leaving a space between it and the macro’s name. Once the macro is saved, it will be hidden.

5. How do you stop recording a macro?

Are your candidates aware that to stop recording a macro, users must navigate to the “View” tab and select “Macros”? They must then click on the “Stop Recording” option.

8 tips to help you use advanced Excel interview questions

Here are best practices you can use before and during interviews to get the most out of our list of advanced Excel interview questions and make a well-informed hiring decision.

1. Give candidates an Advanced Excel skills test before the interview

As mentioned in the introduction, since pre-employment skills tests help you instantly spot candidates who don’t have the right Excel knowledge and skills, you should use an Advanced Excel skills test to ensure that you only spend time interviewing the most promising candidates for the role. 

2. Choose a set of questions that you will ask all candidates

Consistency is essential when conducting an interview. When you use advanced Excel interview questions, ensure that you choose a set of questions that will apply to every candidate. You can ask follow-up questions to get the information you need from a candidate who fails to provide enough detail, but be consistent with the questions you ask your candidates.

3. Describe the responsibilities of the position before asking any questions

Always explain the responsibilities of the position to the candidate in more detail than the job ad before you ask them technical questions related to the role.

For example, if you mentioned that VBA is an essential part of the role in your job description, explain how this advanced Excel skill will help with the role. Or, if conditional formatting is a regular part of the role, explain why it is important. 

4. Use advanced Excel interview questions and skills tests for senior positions

It’s important that you use advanced Excel interview questions and skills tests for senior positions as opposed to General Excel skills tests or basic Excel interview questions. Always ensure that you match the assessments you use to the level of the role you’re hiring for.

5. Ask open-ended questions

Answers to open-ended questions will generally be more detailed than those to closed-ended questions. Sticking to open-ended questions such as “What do you find most challenging about using Excel?” will help you learn more about your candidates than questions like “Do you find using Excel challenging?”

6. Start the interview with general interview questions

Before you dive into a bunch of technical and advanced Excel interview questions, try to ask candidates questions about their backgrounds and previous roles. You can also ask them questions related to how they think they fit your role and organization.

7. Allow your candidates to ask you questions

Toward the end of the interview, allow your candidates to ask you questions about the role. Be prepared to answer technical questions that might come up. Ensure that you’re also prepared to give information about the team your candidates will be working with. 

8. Give candidates an approximation of how long the final selection will take

To help boost the candidate experience and close the interview, inform your candidates about your hiring decision timeline, and give them an estimate of when you will make your final selection. 

Select the right advanced Excel interview questions to evaluate your candidates

Since it can be a challenge to hire talent that fits your organization and has the right advanced Excel skills for your team, remember that skills testing can make the process easier.

Although you may have hundreds of applicants, you can narrow them down with our Advanced Excel skills test. You should make skills testing your priority and complete it before reviewing resumes.

When you have decided on a shortlist, select the advanced Excel interview questions that suit your interview process and evaluate your candidates with ease.

Hire the best talent using skills assessment tools and the right interview questions. Try TestGorilla for free.