🚀 Bonus Excel Interview Questions with Answers — Part 11

101. What is the difference between COUNT, COUNTA, and COUNTBLANK?

Function : Purpose

COUNT : Counts numeric cells only

COUNTA : Counts non-empty cells

COUNTBLANK : Counts empty cells

Examples:

=COUNT(A1:A10)

=COUNTA(A1:A10)

=COUNTBLANK(A1:A10)

📌 Very useful in data quality analysis.

102. What is the difference between SUMIF and SUMIFS?

Function : Criteria Support

SUMIF : Single condition

SUMIFS : Multiple conditions

Example — SUMIF

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

Example — SUMIFS

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

📌 SUMIFS is more powerful for business reporting.

103. What are volatile functions in Excel?

Volatile functions recalculate whenever any change occurs in the workbook.

Common Volatile Functions:

• NOW()

• TODAY()

• RAND()

• RANDBETWEEN()

• OFFSET()

• INDIRECT()

Example:

=NOW()

⚠️ Too many volatile functions can slow large workbooks.

104. What is the difference between MATCH and XMATCH?

Feature : MATCH : XMATCH

Exact match default : No : Yes

Reverse search : No : Yes

Wildcards : Limited : Better support

Search modes : Basic : Advanced

Example:

=XMATCH(A1,B1:B10)

📌 XMATCH is the modern improved version of MATCH.

105. What is a spill range in dynamic arrays?

Dynamic array formulas automatically return multiple results into neighboring cells.

Example:

=UNIQUE(A1:A20)

The output “spills” into nearby cells automatically.

Spill Error:

SPILL!

Occurs when cells blocking the spill range are not empty.

106. What is the difference between .xlsx, .xls, .csv, and .xlsb?

Format : Description

.xlsx : Standard Excel workbook

.xls : Older Excel format

.csv : Plain text comma-separated data

.xlsb : Binary workbook smaller/faster

Important:

• CSV does not save formulas or formatting

• XLSB improves performance for large files

📌 Analysts often use .csv for data transfer.

107. How do you audit formulas in Excel?

Formula Auditing Tools:

Path:

Formulas → Formula Auditing

Useful Features:

Feature : Purpose

Trace Precedents : Show source cells

Trace Dependents : Show affected cells

Evaluate Formula : Step-by-step calculation

Error Checking : Detect formula issues

📌 Essential for debugging complex models.

108. What is the difference between manual and automatic calculation mode?

Automatic Calculation:

Excel recalculates formulas instantly.

Manual Calculation:

Formulas recalculate only when triggered manually.

Change Mode:

Formulas → Calculation Options

Shortcut to Recalculate:

F9

📌 Manual mode helps improve performance in very large workbooks.

109. What are named ranges and why are they useful?

Named ranges assign meaningful names to cells or ranges.

Example:

Instead of:

=SUM(A1:A100)

Use:

=SUM(Sales_Data)

Benefits:

✅ Easier formulas

✅ Better readability

✅ Reusable templates

✅ Easier maintenance

📌 Very common in financial and analyst models.

**110.

What are common Excel errors and how do you fix them?

Error : Meaning

#DIV/0! : Division by zero

#N/A : Value not found

#VALUE! : Wrong data type

#REF! : Invalid cell reference

#NAME? : Unknown formula/name

#SPILL!** : Spill range blocked

Example Fix:

=IFERROR(A1/B1,"Error")

📌 Error handling improves report reliability and professionalism.

Double Tap ❤️ For More