🚀 Excel Interview Questions with Answers — Part 8

71. How do you use FILTER, SORT, and UNIQUE if you have dynamic arrays?
These functions are available in modern versions of Microsoft Excel and automatically spill results into multiple cells.

FILTER
Returns data matching conditions.

Syntax:
=FILTER(array,include,[if_empty])

Example:
=FILTER(A2:C10,B2:B10="East")

📌 Returns only rows where region is “East”.

SORT
Sorts data dynamically.
=SORT(A2:C10,2,1)

Meaning:
Sort by 2nd column
1 = Ascending order

UNIQUE
Extracts unique values.
=UNIQUE(A2:A20)

72. How do you use SUMIFS / COUNTIFS with multiple criteria?
SUMIFS
Adds values based on multiple conditions.

Syntax:
=SUMIFS(sum_range,criteriaᵣange1,criteria1,...)

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

📌 Sums laptop sales in East region.

COUNTIFS
Counts rows matching multiple conditions.
=COUNTIFS(A:A,"East",B:B,"Laptop")

📌 Counts laptop orders in East region.

Benefits:
✅ Multiple conditions
✅ Flexible reporting
✅ Powerful analysis

73. How do you use SUMPRODUCT for 2D-style lookups and weighted averages?
SUMPRODUCT performs array multiplication and summation.

Weighted Average:
Weighted Average = (∑ (xᵢ wᵢ)) / (∑ wᵢ)

Excel Formula:
=SUMPRODUCT(A1:A5,B1:B5)/SUM(B1:B5)

2D Lookup Example:
=SUMPRODUCT((A2:A10="East")(B2:B10="Laptop")(C2:C10))

📌 Returns total laptop sales in East region.

Advantages:
✅ Handles multiple conditions
✅ Works without helper columns
✅ Powerful for advanced analysis

74. How do you build reusable templates with named ranges and formulas?
Reusable templates save time and improve consistency.

Named Ranges:
Assign names to cells/ranges.

Steps:
1. Select range
2. Click Name Box
3. Enter name

Example:
Instead of:
=SUM(A1:A10)

Use:
=SUM(Sales_Data)

Benefits:
✅ Easier formulas
✅ Better readability
✅ Reusable models
✅ Easier maintenance

📌 Common in financial models and dashboards.

75. How do you link multiple sheets and workbooks?
Linking Sheets:
=Sheet2!A1

Linking Workbooks:
='[Sales.xlsx]Jan'!A1

📌 Useful for:
• Consolidated reports
• Multi-department analysis
• Financial models

Important:
⚠️ Broken links can occur if source files move or rename.

76. How do you avoid and fix circular references?
A circular reference happens when a formula refers back to itself.

Example:
=A1+1
entered inside A1.

Problems:
❌ Infinite calculation loop
❌ Incorrect results

Fix Methods:
✅ Check formula references
✅ Use: Formulas → Error Checking → Circular References
✅ Break dependency chain

📌 Always design formulas carefully in financial models.

77. How do you optimize large Excel files for performance?
Large files can become slow.

Optimization Techniques:
✅ Use Excel Tables
✅ Avoid volatile functions NOW, OFFSET, INDIRECT
✅ Reduce unnecessary formatting
✅ Remove unused formulas
✅ Use PivotTables instead of heavy formulas
✅ Convert formulas to values where possible

Additional Tips:
✅ Disable automatic calculation temporarily
✅ Use binary format .xlsb for very large files

📌 Important for analyst-level reporting.

78. How do you use Data Validation to create dropdown lists?
Data Validation restricts input values.

Steps:
1. Select cells
2. Go to: Data → Data Validation
3. Choose:
– List
– Number
– Date
– Custom

Dropdown Example:
Allowed values:
• Yes
• No
• Pending

📌 Helps maintain clean and consistent data.

79. How do you use “What-If Analysis” Goal Seek, Data Tables, Scenario Manager?
“What-If Analysis” tests different scenarios.

Goal Seek:
Finds required input for desired output.

Example: 
Determine required sales to achieve ₹1,00,000 profit. 

Path: 
Data → What-If Analysis → Goal Seek 

Data Tables: 
Shows multiple possible outputs based on varying inputs. 

📌 Useful for sensitivity analysis. 

Scenario Manager: 
Stores multiple business scenarios.