// Excel Interview Questions //
Basic Excel Questions
1) What are the differences between a workbook and a worksheet in Excel?
A workbook is the entire Excel file, which can contain multiple worksheets (individual
tabs within the workbook). Think of the workbook as a book and the worksheets as
pages.
2) How do you freeze panes?
A: Go to View → Freeze Panes → Freeze Top Row/First Column/Custom Pane. This
keeps selected rows or columns visible while scrolling.
3) What is the difference between relative, absolute, and mixed references?
i) Relative (A1): Changes when copied to another cell.
ii) Absolute ($A$1): Remains constant when copied.
iii) Mixed (A$1 or $A1): Partially fixed.
4) How do you apply conditional formatting?
Select the cells → Home → Conditional Formatting → choose a rule type (like
“Greater Than”, “Top 10%”, or a custom formula) → set formatting → OK.
5) Difference between COUNT, COUNTA, COUNTIF, COUNTIFS
i) COUNT → counts numeric cells only.
ii) COUNTA → counts all non-empty cells.
iii) COUNTIF (range, criteria) → counts cells matching a single condition.
iv) COUNTIFS (range1, criteria1, range2, criteria2, …) → counts cells matching
multiple conditions.
2. Formulas and Functions
6) How does VLOOKUP work?
Looks for a value in the first column of a range and returns a value in the same row from
another column.
=VLOOKUP (lookup value, table array, col_index, [range lookup])
TRUE → approximate match
FALSE → exact match
7) Difference between VLOOKUP and INDEX-MATCH
VLOOKUP can only search the first column.
INDEX-MATCH can search any column, is more flexible, and handles insertions better.
Example: =INDEX (B2:B10, MATCH (1001, A2:A10, 0))
8) Explain IF and nested IF statements
IF: =IF (condition, value_if_true, value_if_false)
Nested IF: Multiple IFs inside each other for multiple conditions.
Example: =IF(A1>90,"A”, IF(A1>80,"B","C"))
9) Difference between SUMIF and SUMIFS
SUMIF → sum based on one condition
SUMIFS → sum based on multiple conditions
Example: =SUMIFS (C2:C10, A2:A10, "Apple", B2:B10, ">100")
10) How do you combine text from multiple cells?
Using &: =A1 & " " & B1
Using CONCAT (Excel 2016+): =CONCAT (A1, B1)
Data Analysis & Pivot Tables
11) How do you create a Pivot Table?
Select your data
Insert → Pivot Table
Drag fields into Rows, Columns, Values, Filters
12) Difference between Pivot Table and regular table
Pivot Table → Summarizes and aggregates data dynamically
Regular table → Stores raw data
13) What is a slicer in Pivot Tables?
A visual filter that allows you to filter Pivot Table data interactively.
14) How do you refresh Pivot Table data?
Right-click the Pivot Table → Refresh
Charts & Visualization
15) How do you add a secondary axis?
Click chart → Select data series → Format → Plot on Secondary Axis
16) Difference between bar chart and column chart
Column → vertical bars
Bar → horizontal bars
17) What are sparklines?
Miniature charts within a cell showing trends, useful for dashboards.
Advanced Excel Questions
18) Explain Index and Match
INDEX (array, row_num, [col_num]) → Returns value at a specific row/column
MATCH (lookup value, lookup array, match type) → Returns the position of a value
Combined: Flexible lookup solution: =INDEX (B2:B10, MATCH (1001, A2:A10, 0))
19) What are dynamic arrays?
Functions like FILTER, SORT, UNIQUE automatically spill results into multiple cells.
Example: =UNIQUE (A2:A20) → returns a list of unique values.
20) How do you create a drop-down list?
Data → Data Validation → Allow: List → Enter values or reference a range
21) How do you protect a worksheet?
Review → Protect Sheet → Set password and select permissions
22) How do you handle large datasets?
Use tables, PivotTables, filters, and avoid volatile formulas like OFFSET.
Consider splitting data or using Power Query.
Scenario-Based Questions
23) How to remove duplicates?
Data → Remove Duplicates → Select columns → OK
24)How to combine data from multiple sheets?
Use Consolidate (Data → Consolidate)
Or Power Query for complex merges
25How to calculate running totals?
Formula: =SUM ($B$2: B2) and drag down
[Link]