Advanced Excel Interview Questions: 25 Practical Tasks with Answers

Advanced Excel Interview Questions: 25 Practical Tasks with Answers

If you are preparing for an Excel-based job interview, simply memorizing formulas will not be enough.

Most employers want to know whether you can actually use Excel to solve everyday business problems.

You may be given a sales sheet and asked to calculate totals, clean messy data, find an employee record,

prepare a PivotTable, or create a small dashboard. In many interviews, the recruiter is less interested

in how many formulas you know and more interested in how confidently you can use the right Excel feature

for the task.

The interviewer might also be interested in knowing how you overcome challenges. If there are two

different ways to accomplish a task, they may evaluate your capacity to select the most effective

and dependable approach.

25 Useful Excel Skills to Practice Before Your Interview

In an Excel interview, the evaluators usually test your ability to work with real-world data.

You might need to use formulas, lookup functions, PivotTables, conditional formatting, charts,

data validation, Power Query or logical functions.

The interviewer may also want to know how you deal with obstacles. For example, if there are two ways

to do a job, they might assess your ability to choose the best and most reliable method.

Here are 25 practical Excel tasks worth practicing before your interview.

1. What is the best way to calculate the total sales of one product?

Assume that column A is the product names and column B is the sales.

You have to find out the total sales of Laptop.

You can use:

=SUMIF(A:A,"Laptop",B:B)

SUMIF adds values only when a specified condition is met.

Useful if you want to know the total for each product, city, employee, department or category.

2. What is the formula for calculating sales when there are several conditions?

Assume that you have a table with Product, Region, and Sales columns.

You want to calculate the sales of Laptop only in Delhi.

Use:

=SUMIFS(C:C,A:A,"Laptop",B:B,"Delhi")

If you have multiple criteria calculations, SUMIFS is a good option.

It is widely used in sales reports, financial reports, performance reports and

Management Information System (MIS) dashboards.

3. How can you determine an employee's department using an Employee ID?

You have a list of employee IDs and their departments. You need Excel to obtain

the department associated with an employee ID.

The simple modern solution is:

=XLOOKUP(E2,A:A,B:B,"Not Found")

The employee ID you are looking for is in E2.

It is definitely worth practicing XLOOKUP because it is a very common interview

question in Advanced Excel.

4. What is VLOOKUP and what is it used for?

VLOOKUP searches a table for a value in the first column and returns the value

from another column.

For example:

=VLOOKUP(E2,A2:D100,3,FALSE)

This returns the value in the 3rd column based on the value in E2.

In an interview, they might ask you about the limitations of VLOOKUP and how

it is different from XLOOKUP.

5. How would you find the highest and lowest sales?

If sales are in B2:B20, use:

=MAX(B2:B20)

to determine the greatest value.

Use:

=MIN(B2:B20)

to find the minimum value.

These are simple formulas, but interviewers may combine them with other functions

to see how well you understand them.

6. How do you find the third-highest salary?

If employee salaries are in B2:B100, you can use:

=LARGE(B2:B100,3)

The 3 tells Excel to return the third-largest value.

Similarly:

=SMALL(B2:B100,3)

returns the third-smallest value.

This is a more efficient way to do this than sorting the data manually each time.

7. How do you count employees in a specific department?

You can count the number of Sales employees with:

=COUNTIF(B:B,"Sales")

Use COUNTIFS if you have to use more than one condition.

For example:

=COUNTIFS(B:B,"Sales",C:C,">50000")

This can be used to count Sales employees having a salary of more than ₹50,000.

8. How would you delete duplicate records?

Assume you have duplicate email addresses in your client list.

Select your data set, go to the Data tab and click

Remove Duplicates.

But don't delete duplicates unnecessarily.

For instance, two clients may have the same name but different email addresses.

It is important to identify which columns should be used when determining an

actual duplicate.

Paying attention to these details is important in practical Excel interviews.

9. How can you split text into separate columns?

If your data is separated by spaces, you can separate it into individual columns.

If your data is more complicated, you might want to use text formulas or Power Query.

10. What is the correct way to combine a first name and a last name?

Suppose the person's name is Rahul Sharma. The first name is in cell A2 and

the last name is in cell B2.

Use:

=A2&" "&B2

You can also use:

=CONCAT(A2," ",B2)

The space surrounded by quotation marks separates the first and last name properly.

11. How do you eliminate extra spaces from data?

Often, data from websites, applications or other documents includes unnecessary spaces.

Use:

=TRIM(A2)

TRIM removes unnecessary spaces while preserving normal spaces between words.

This is a basic function, but it can save a lot of time when cleaning messy data.

12. What are the ways to change the case of text?

To change text to uppercase, use:

=UPPER(A2)

For lowercase:

=LOWER(A2)

For proper case:

=PROPER(A2)

PROPER is especially helpful for cleaning names, locations, text fields and other information.

13. How do you extract a portion of a product code?

Suppose A2 contains:

DEL-2026-105

and you want to extract DEL.

Use:

=LEFT(A2,3)

If you want to get the last 3 characters:

=RIGHT(A2,3)

If the information is in the middle, MID can be used.

These text functions can be helpful for product codes, employee IDs, invoice numbers,

and location codes.

14. How would you calculate an employee's age?

If B2 contains the employee's date of birth, use:

=DATEDIF(B2,TODAY(),"Y")

This returns the person's age in full years.

Date calculations are frequently used in HR, payroll, employee databases and

administrative reports.

15. How do you find the number of days between two dates?

Assume that the start date is in A2 and the end date is in B2.

A simple calculation is:

=B2-A2

Excel will return the number of days between the two dates.

Other date functions can also be used to calculate working days, months or years,

depending on the requirement.

16. How would you use IF to display Pass or Fail?

Assume that students require 40 marks or more to pass.

Use:

=IF(B2>=40,"Pass","Fail")

The IF function returns one value if a condition is true and another value if it is false.

It is one of the most commonly used Excel logical functions.

17. How can you check two conditions at the same time?

Suppose an employee receives a bonus only if sales are more than ₹1,00,000

and attendance is more than 90%.

Use:

=IF(AND(B2>100000,C2>90%),"Eligible","Not Eligible")

AND is a function that tests whether both conditions are true.

When only one of several conditions needs to be true, interviewers might also

ask you to use OR.

18. What would you do if you encountered a formula error?

If a value is not found, a VLOOKUP formula may return the #N/A error.

The IFERROR function can be used:

=IFERROR(VLOOKUP(E2,A:D,3,FALSE),"Not Found")

If the lookup generates an error, Excel will display "Not Found" instead.

This helps make reports more readable and understandable for other users.

19. How would you find the top five products in terms of sales?

One practical way to do this is to create a PivotTable containing product names

and total sales.

Then sort the sales and display the top five products.

Depending on the dataset, formulas could also be used to complete the task.

The key is to select a method that is accurate and easily adaptable moving forward.

20. How do you highlight sales that are below the target?

Assume the monthly target is ₹50,000 and you want all values below this target

to be highlighted.

Select the sales figures and apply Conditional Formatting.

Set the rule to highlight values below ₹50,000.

This makes it easier to identify poor-performing areas without checking every

figure manually.

Conditional formatting is a useful way to track deadlines, identify duplicates,

highlight top performers, manage overdue payments and flag unusual values.

21. How do you create a drop-down list in Excel?

Imagine a status column containing predefined options such as:

• Finalise

Instead of typing the status every time, create a drop-down list using

Data Validation.

Select the cells where you want to apply data validation, go to

Data Validation, choose List, and type or

reference the allowed values.

This improves data consistency and reduces typing errors.

22. What techniques would you use to clean a large dataset?

Suppose you receive a document containing many records. It has empty rows,

redundant columns, inconsistent formatting and incorrect data types.

It would be too time-consuming to clean everything manually.

Power Query allows you to import, clean, transform, merge and restructure data.

For example, you can remove unnecessary columns, change date formats, substitute

values, split columns and remove duplicates.

The main advantage is that the same transformation steps can be applied again

when new data arrives.

This is where Power Query becomes incredibly useful for periodic reporting.

23. How would you create an Excel dashboard?

An interviewer may give you raw sales data and ask you to create a simple dashboard.

The first step should not be creating charts.

Start by understanding the business question.

You may need to show KPIs such as total sales, total orders, average order value,

top product, best-performing region and monthly sales trend.

You can use PivotTables to summarize the data and PivotCharts to visualize it.

Slicers can make the dashboard interactive.

A good dashboard should make important information easy to understand.

Filling the page with too many charts usually makes it harder, not better.

How Should You Prepare for Complex Excel Interview Questions?

Practice with real data is one of the best ways to prepare for an Excel interview.

Collect or generate a basic dataset related to sales, staff, inventory, finance

or customers.

Start working on real problems.

Practice data cleaning and learn to use SUMIFS, COUNTIFS, IF, XLOOKUP, VLOOKUP,

text functions, date formulas, PivotTables, charts, conditional formatting

and Power Query.

Do not only practice writing formulas.

Practice explaining what you are doing and why you selected a particular function.

For example, an interviewer may ask:

Why did you choose XLOOKUP over VLOOKUP?

XLOOKUP is newer and more flexible than VLOOKUP, but it is not available in

some older versions of Excel.

Common Excel Interview Mistakes Candidates Make

One of the most common mistakes is trying to impress the interviewer with

unnecessarily complex formulas.

In practice, it is often better to use a simple formula that gives the correct

result than an overly complicated solution.

Another mistake is neglecting the quality of the data.

Before performing any analysis, look for blank cells, duplicates, bad formats,

text that looks like numbers, formula errors and inconsistent values.

The way information is presented is also important.

Even accurate calculations can make a report confusing if the titles are vague,

dates are inconsistent or figures are poorly formatted.

Think about the person who will eventually use your Excel file.

Final Thoughts

Advanced Excel interview questions are not really about showing how many formulas

you remember.

They are designed to determine whether you can take real data, understand the problem,

choose the right Excel tool and produce a useful result.

If you are preparing for an interview, focus first on the skills that appear most

often in real jobs: data cleaning, lookup formulas, IF statements, SUMIFS, COUNTIFS,

PivotTables, conditional formatting, Power Query, charts and basic dashboards.

Once you are comfortable with these areas, take random datasets and create your

own questions.

Ask yourself questions such as:

• Which product generated the highest revenue?
• Which employee achieved the target?
• Which region had the lowest sales?
• How many orders were completed this month?
• Which customers appear more than once?

That type of practice will help you become faster and more confident than simply

reading formulas from a list.

Want to Improve Your Advanced Excel Skills?

If you are preparing for Excel interviews or want to improve your reporting, MIS,

data analysis, finance or business skills, regular hands-on practice is the most

important step.

SoftCrayons provides practical Advanced Excel training covering formulas, lookup

functions, PivotTables, dashboards, data cleaning, Power Query, reporting and

interview-oriented exercises.

You can book a free demo class to get an idea about the mode of teaching and see

the utilisation of Excel in practical professional scenarios.