Advanced Excel Interview Questions

Basic & Intermediate

What is Excel and what are its main uses?
A spreadsheet program used for data entry, analysis, visualization, and reporting.

What is a cell in Excel?
The intersection of a row and a column (e.g., A1).

What is a range in Excel?
A selection of two or more cells (e.g., A1:A10).

What is the difference between a workbook and a worksheet?
A workbook is the entire Excel file; worksheets are individual sheets inside it.

How do you freeze panes?
View > Freeze Panes — keeps rows/columns visible while scrolling.

How do you filter data in Excel?
Use Data > Filter — drop-down arrows appear to filter rows.

Explain the difference between absolute and relative cell references.

• Relative (A1): changes when copied.

• Absolute ($A$1): stays fixed.

• Mixed ($A1 or A$1): fixes row or column.

What is Conditional Formatting?
Used to highlight cells based on rules (e.g., highlight top 10 values).

How do you create a chart in Excel?
Select data → Insert > Charts → choose chart type.

What is the difference between a bar chart and a column chart?

• Bar: horizontal bars.

• Column: vertical bars.

✅ Formulas & Functions

What is the SUMIF function?
Adds values based on a single condition: =SUMIF(range, criteria, sum_range)

What is the SUMIFS function?
Adds values based on multiple conditions: =SUMIFS(sum_range, criteria_range1, criteria1, ...)

What does the CONCATENATE function do?
Combines text strings: =CONCATENATE(A1, " ", B1).
Modern Excel: =CONCAT or =TEXTJOIN.

How does the IF function work?
Tests a condition and returns one value if TRUE, another if FALSE.
=IF(A1>50, "Pass", "Fail")

What is the purpose of nested IFs?
Handles multiple conditions.
Example: =IF(A1>90,"A", IF(A1>80,"B","C"))

How do you find the largest/smallest number in a range?
Use MAX(range) and MIN(range).

What is the AND function?
Returns TRUE if all conditions are true:
=AND(A1>10, B1<5)

What is the OR function?
Returns TRUE if any condition is true:
=OR(A1>10, B1<5)

How does the LOOKUP function work?
Looks up a value in a single row/column and returns a value from the same position.

Difference between VLOOKUP and HLOOKUP?

• VLOOKUP: searches vertically.

• HLOOKUP: searches horizontally.

✅ Advanced Lookups & Data Tools

Explain the MATCH function.
Returns the position of a value in a range: =MATCH("Apple", A1:A5, 0)

Explain the INDEX function.
Returns a cell value by row/column index: =INDEX(A1:C5, 2, 3)

How do you combine INDEX and MATCH?
Powerful alternative to VLOOKUP:
=INDEX(B2:B10, MATCH("John", A2:A10, 0))

What is XLOOKUP?
Modern replacement for VLOOKUP/HLOOKUP, more flexible:
=XLOOKUP(lookup_value, lookup_array, return_array)

What is the INDIRECT function?
Returns a cell reference specified by text: =INDIRECT("A"&1) → returns value in A1.

What does the OFFSET function do?
Returns a cell/range offset from a reference:
=OFFSET(A1, 2, 3) → 2 rows down, 3 columns right.

What is the TRANSPOSE function?
Switches rows and columns: =TRANSPOSE(A1:B3).

How to remove duplicate values?
Data > Remove Duplicates or Advanced Filter.

What is Power Pivot?
An Excel add-in for creating data models, relationships, and advanced calculations.

What is Power Query?
A tool for importing, cleaning, and transforming data from multiple sources.

✅ Data Analysis & Automation

What are Pivot Charts?
Charts linked to Pivot Tables — update dynamically when Pivot data changes.

How do you create a dynamic chart?
Use named ranges or tables so charts auto-expand.

How do you validate data entry?
Data > Data Validation → set rules (e.g., allow only numbers, lists).

What is a macro?
A recorded sequence of actions to automate tasks. Stored as VBA code.

How do you run a macro?
View > Macros > View Macros → select and run.

What is VBA in Excel?
Visual Basic for Applications — the programming language to write macros.

What is a dashboard in Excel?
An interactive summary combining charts, tables, and KPIs.

How do you protect a cell?
Lock cells → Review > Protect Sheet.

How do you share an Excel file for collaboration?

• Use OneDrive/SharePoint.

• Allow multiple users to edit.

How do you link data from other workbooks?
Use external references: '[WorkbookName.xlsx]Sheet1'!A1.

✅ Tips to Crack the Interview

• Be ready to explain real-life examples.

• Know shortcuts (e.g., Ctrl + T for tables).

• Practice using Power Query & Pivot Tables.

• Prepare to solve small tasks live.