Advanced Excel Interview Questions
and Answers
1. What is the difference between VLOOKUP, HLOOKUP, INDEX & MATCH?
When would you use INDEX-MATCH over VLOOKUP?
VLOOKUP searches for a value in the first column of a range and returns a value in the same
row from a specified column. HLOOKUP works similarly but searches in the first row. INDEX
returns the value of a cell at a given row and column number, and MATCH returns the
relative position of a value in a row or column. INDEX-MATCH is more flexible and faster for
large datasets, especially when searching left of the key column, which VLOOKUP can't do.
2. Explain the use of IFERROR() and give an example where it’s useful.
IFERROR() is used to catch and manage errors in formulas. For example, =IFERROR(A1/B1,
"Error") returns 'Error' instead of #DIV/0! if B1 is zero.
3. What does the ARRAYFORMULA or array formula (e.g., {=A1:A5*B1:B5})
mean in Excel?
Array formulas perform multiple calculations on one or more items in an array. The formula
{=A1:A5*B1:B5} multiplies each element of the ranges and returns an array of the results.
4. How do you use SUMIFS() and COUNTIFS()? Provide examples.
SUMIFS sums values that meet multiple criteria. Example: =SUMIFS(C2:C10, A2:A10,
"Apples", B2:B10, ">10") sums values in C2:C10 where A2:A10 = Apples and B2:B10 > 10.
COUNTIFS counts with multiple criteria.
5. How does the OFFSET() function work and when would you use it?
OFFSET returns a reference to a range that is offset from a starting cell. Example:
=OFFSET(A1,2,1) returns the cell that is two rows down and one column right from A1.
6. What is a Pivot Table? How do you handle calculated fields and slicers in it?
A Pivot Table summarizes data dynamically. Calculated fields are custom formulas added to
Pivot Tables. Slicers are visual filters used to filter data interactively.
7. How do you remove duplicates from a large dataset while keeping the first
occurrence?
Use the 'Remove Duplicates' option under the Data tab. Select the column(s), and Excel
keeps the first occurrence by default.
8. Explain the purpose and use of the Data Validation feature in Excel.
Data Validation restricts the type of data or values that users can enter. Example: restrict
entries to a list of items or to a number range.
9. How do you use Conditional Formatting dynamically based on formulas?
Use 'New Rule' → 'Use a formula to determine which cells to format'. Example: =A1>100 to
highlight values greater than 100.
10. What is Goal Seek and how is it different from Solver? Give real-world
examples.
Goal Seek finds the input needed to reach a desired result from a formula. Solver can handle
multiple variables and constraints. Goal Seek is simpler, Solver is more powerful.
11. What is Power Query? How is it different from regular Excel functions like
Text to Columns?
Power Query automates data import, transformation, and cleanup. Unlike static functions
like Text to Columns, Power Query allows repeatable and dynamic transformations.
12. Explain relationships in Power Pivot and how they differ from VLOOKUPs.
Power Pivot allows creating relationships between tables like in a database. It's more
efficient than VLOOKUP as it doesn't duplicate data.
13. What are DAX formulas? Give examples of commonly used ones like
CALCULATE() and RELATED().
DAX (Data Analysis Expressions) is used in Power Pivot and Power BI. CALCULATE()
modifies context filters. RELATED() pulls data from related tables.
14. What is a macro? How do you record and edit a macro?
A macro is a recorded set of actions in Excel. Use the Developer tab to record. Macros are
written in VBA and can be edited in the Visual Basic Editor.
15. What is the difference between ThisWorkbook, ActiveWorkbook, and
Workbooks("Name") in VBA?
ThisWorkbook refers to the workbook containing the VBA code. ActiveWorkbook is the one
currently in focus. Workbooks("Name") references a workbook by name.
16. Write a simple VBA script to loop through a column and highlight all values
above 100.
Sub HighlightAbove100()
For Each cell In Range("A1:A100")
If cell.Value > 100 Then
cell.Interior.Color = RGB(255, 255, 0)
End If
Next cell
End Sub
17. How do you protect a sheet or workbook but allow certain cells to be
editable?
Select editable cells → Format Cells → Protection → Uncheck 'Locked'. Then protect the
sheet (Review tab → Protect Sheet). Only locked cells will be protected.
18. How can you restrict a user from entering invalid email format using Excel
tools?
Use Data Validation with custom formula like:
=AND(ISNUMBER(FIND("@",A1)),ISNUMBER(FIND(".",A1))) to ensure '@' and '.' are
present.
19. What are named ranges? How can they improve formulas and navigation?
Named ranges are user-defined names for cells/ranges. They make formulas easier to
understand and navigate (e.g., =SUM(SalesData) instead of =SUM(A1:A10)).
20. How do you create interactive dashboards in Excel using formulas, slicers,
and charts?
Use PivotTables and PivotCharts with slicers, dynamic named ranges, and formulas like
OFFSET or INDIRECT to update visual elements interactively.
