Top 20 Excel Interview Questions: Test Excel skills of your candidates

As an HR manager, you need to test the Excel skills of the candidates for a job vacancy, but you do not know which questions to ask? Then, we have gathered top 20 Excel Interview Questions for you to understand the real level of Microsoft Excel of any candidate.

According to stats, more than 80% of the recent jobs require Excel skills. Because, Excel is not just a software to use, it also needs analytical and data analysis skills.

If any candidate is comfortable for the majority of the below questions, then you can believe that this candidate is a powerful Excel user.

Top 20 Excel Interview Questions

We’ll start with basic questions to comfort the candidate. But the whole list does not mean fully in order from basic to advance. We make a mixed coverage to catch a real interview atmosphere.

Also, you’ll find very basic and simple answers under each question.

Let’s start!

Question 1: What is the difference between absolute and relative cells references? Can you give us the shortcut key?

Relative references change when a formula is copied to another cell, while absolute references remain constant regardless of where they are copied. The shortcut key for toggling between absolute and relative references is F4.

Question 2: How can you highlight the top three biggest numbers in a range in Excel?

We’ll use conditional formatting to highlight the top 10 values. We’ll select the range, go to Conditional Formatting, and then from the Top/Bottom Rules I’ll click Top 10 Items and set it to 3.

Questions 3: What’s the difference between weighted Average and Weighted Arithmetic Average? Explain with a simple example.

These terms are often used interchangeably to mean the same thing: an average where each data point contributes proportionally to the total average, based on its weight or importance.

For example, let’s assume I get 40, 60 and 80 points from my Maths, Science, and English exams. And weekly hours for these there subjects are 6, 4, 2 hours respectively.

The average will be calculated independent from the weekly hours:

The weighted Average, on the other hand will be calculated based on the weekly hours:

Question 4: You have two columns and how do you check if these two columns have the same data or not?

We can use the ‘IF’ function to compare each cell in the columns. For example: =IF(A1=B1, “Match”, “No Match”). Then I can highlight the Match values with conditional formatting.

Question 5: What does it mean an array formula? When and how we use array formulas?

An array formula performs multiple calculations on one or more sets of values and returns either a single result or multiple results. It’s used for complex calculations like matrix operations or simultaneous calculations on multiple values.

Question 6: What’s VBA? When do you use macros?

VBA (Visual Basic for Applications) is a programming language for Excel. Macros are automated sequences of actions in Excel. Macros are often created using VBA to perform repetitive tasks.

Question 7: What are the differences Between Excel and Google Sheets?

Microsoft Excel is better in terms of advanced features, a wider array of formulas, and more comprehensive data analysis tools.

But Google Sheets has also advantages on simultaneous file editing, access from all devices, automatically recording. And also, Google Sheets is free where as Microsoft Excel not.

Question 8: You have a very big data, and your file is extremely slow. Whenever you change any data, your calculations take too much of time to update. How can you solve this?

We can try to reduce file size by removing unused cells, columns, and rows. We should also optimize complex formulas. Finally, we can use Excel’s Manual Calculation Mode. This will switch of auto-calculation and will prevent slowing down in each update in return.

Question 9: How do you create a drop-down menu from a dynamic list? Imagine that the number of items on the list can change, there might be blank rows on the list. And I do not want to see blank option in my drop-down menu.

We will use a combination of the Data Validation feature and an OFFSET formula to create a drop-down that excludes blank cells and adjusts automatically as the list changes.

Question 10: Why do you use Power Pivot?

Power Pivot is used for handling large data sets, creating complex models, and performing powerful data analysis within Excel. If I have two different sets of data, then I can combine them with Power Pivot functions.

Question 11: What is the WHAT IF Analysis?

It’s a process of changing values in cells to see how those changes affect the outcome of formulas on the sheet. Examples include Goal Seek and Scenario Manager.

We can test different scenarios with What If Analysis tool in Excel.

Question 12: What’s Excel Solver? What are the main parameters in Solver?

Solver is an Excel add-in used for optimizing a goal or objective, subject to constraints. Key parameters include the objective cell, variable cells, and constraints. We can use Solver add-in for both linear and non-linear problems.

Question 13: How do you prevent #REF or #N/A or #VALUE!, or something like that on my report. What do you do to prevent these errors?

We can use error-handling formulas like IFERROR or IFNA to manage and prevent common error values. This will create clear reporting.

Question 14: I will share my Excel with a collegues, but I want that certain important cells will be unchangeable. What do you suggest?

We should first lock the cells that we don’t want to change. And then we’ll protect the worksheet. Locked cells cannot be edited unless the sheet is unprotected.

Question 15: What’s the difference between COUNT, COUNTA and COUNTBLANK functions?

• COUNT function counts only numeric values.
• COUNTA function counts all non-empty cells.
• COUNTBLANK function counts empty cells.

Question 16: How do you remove duplicates in a range?

We can use the Remove Duplicates feature under the Data tab.

Question 17: What could you do to stop the pivot table from losing the column width upon refreshing?

We should change the PivotTable Option. With a Right-click  we can open PivotTable Options dialog box, then we’ll uncheck AutoFit column widths on update under the Layout & Format tab.

Question 18: Which chart types are more suitable for a trend analysis, comparison and relationship between data sets?

For trend analysis we can use line graphs or dual-axis charts. If we will compare two or more data sets, then bar charts and columns charts might be a suitable solution. Lastly, in order to visualize relationships between data values, we can use treemap charts or scatter plots.

Question 19: I have 12 monthly sheets having the same layout and calculations. But I realize that one of the cells is not as it should be. Do I have to go each tab one-by-one and change them?

No, we do not need to do them one-by-one. We can group the sheets by selecting them while holding down SHIFT or CTRL. Then we can make the change once, and it will apply to all grouped sheets.

Question 20: What are the downsides of merging cells? What can you use instead?

Merged cells can cause issues with sorting, filtering, and applying certain Excel functions. Instead, we can use Center Across Selection under the Alignment tab of Format Cells dialog box.

FINAL WORDS

If you’re a recruiter searching for the best candidate for your business, you’ll need to test the skills of the applicants. And Excel is one of the most demanded skills for all jobs.

We’ve tried to give you a complete list of top Excel Interview Questions that you can ask. But if you want a real testing tool, meet our Excel Formulas Trainer, which will give you real insight about the Excel power of any candidate:

This tool, designed in Excel itself, consists of 30 exercises in three levels.

Each exercise has instructions and the candidate will not be able to go the next exercise until finds the correct solution:

– This is an example from one of the exercises in Someka’s Excel Formulas Trainer –

You should try this unique tool for both your candidates and for your exisiting employees!

Recommended Readings:

Complete List of Things You Can Do With Excel

20 Most-Asked Excel Job Interview Questions for Managers

20 Most Asked Excel Job Interview Questions for Financial Analysts