📚 Top 10 Excel Interview Questions (2025) Part – 2 ✅

Introduction

If you’ve already mastered the first 10 Excel Interview Questions Part – 1, it’s time to dive deeper into Part 2. These next questions 11–20 focus on more advanced Excel concepts that employers often ask to test your problem-solving and automation skills. Let’s break them down one by one.

11. What is Data Validation in Excel?

Data validation helps control what type of data can be entered into a cell. It ensures data accuracy and consistency.

• Limit input to whole numbers between 1 and 100, for instance.

• Uses: Dropdown lists, restricting dates, limiting text length.

👉 This prevents users from entering incorrect or invalid data.

12. Explain VLOOKUP and HLOOKUP Functions.

Data in a table can be searched using both functions.

• VLOOKUP (Vertical Lookup) – Vertical Lookup, or VLOOKUP, retrieves information from a different column by searching for a value in the first column.
  Example: =VLOOKUP(101, A2:D10, 3, FALSE)
• HLOOKUP (Horizontal Lookup) – Horizontal Lookup, or HLOOKUP, retrieves information from a different row by searching for a value in the first row.
  Example: =HLOOKUP(“Q1”, A1:F5, 3, FALSE)

👉 In modern Excel, XLOOKUP is preferred as it replaces both functions with more flexibility.

13. What is the Difference Between Excel Tables and Ranges?

• Range – A simple selection of cells.
• Table – A structured format with special features like auto-filtering, easy formatting, and automatic formula extension.

👉 Tables make data dynamic, while ranges are static.

14. How Do You Create Charts and Graphs?

Charts and graphs turn raw data into visual insights.

Steps to create:

1. Select your data.
2. Go to Insert → Charts.
3. Choose chart type (bar, line, pie, etc.).
4. Customize using titles, legends, and formatting.

👉 A bar chart for comparing monthly sales is one example.

15. What Are Macros and How Do You Create Them?

A macro is a series of instructions that automates repetitive actions in Excel.

• Visual Basic for Applications, or VBA, is used to write macros.

• You can record them using the Macro Recorder without coding.

👉 Example: Automating report formatting or copying data between sheets.

16. How Do You Record and Run a Macro?

• Record: To begin recording, select View → Macros → Record Macro, take action, and then end the recording.

• Run: Choose the macro by pressing Alt + F8, then press run.

• For ease of use, macros can also be linked to a button or keyboard shortcut.

17. What is the Purpose of the IF Function?

Depending on whether a condition is TRUE or FALSE, the IF function checks it and returns a different value.

• Syntax: =IF(condition, value_if_true, value_if_false)
• Example: =IF(A1>50, “Pass”, “Fail”)

👉 Useful in decision-making formulas.

18. Explain Nested Functions with an Example.

One function used inside another is called a nested function.

• Example: =IF(AND(A1>50, B1>50), “Pass”, “Fail”)
  Here, AND is nested inside IF.

👉 Nesting allows complex conditions and calculations.

19. How Do You Use INDEX and MATCH Functions Together?

• INDEX: Provides a cell’s value inside a range.

• MATCH: Determines a value’s location within a range.

When combined:
=INDEX(B2:B10, MATCH(“ProductA”, A2:A10, 0))
👉 This formula finds ProductA in column A and returns its corresponding value from column B.

This is more powerful and flexible than VLOOKUP.

20. What Are Array Formulas?

An array formula performs multiple calculations at once and returns either a single value or multiple values.

• Example: {=SUM(A1:A5*B1:B5)}
  This multiplies each value in column A with column B and sums the results.

👉 Array formulas are powerful but require careful handling (in modern Excel, dynamic arrays simplify this).

Conclusion

These advanced Excel questions (11–20) cover automation, data validation, lookups, macros, and formulas. By preparing for them, you’ll show employers that you can not only handle data but also optimize workflows using Excel’s advanced features.

FAQs

1. What’s the difference between XLOOKUP and VLOOKUP?
   XLOOKUP is more flexible, works left or right, and doesn’t require column numbers.
2. Can macros run automatically?
   Yes, you can assign macros to run on workbook open, close, or specific events.
3. Are array formulas still used in 2025?
   Yes, but dynamic arrays have simplified most use cases.
4. Which kind of chart works best for time-series data?
   For displaying patterns over time, a line chart works best.
5. Do I need VBA knowledge for Excel interviews?
   Not always, but basic macro knowledge is a plus in data-heavy roles.