Prepare for your Excel interview with these 30 essential questions and answers. Covering basic, intermediate, and advanced topics, this guide helps freshers, candidates with 1-3 years experience, and professionals with 3-6 years gain confidence in Excel concepts, functions, and practical scenarios.
Basic Excel Interview Questions (1-10)
1. What is a cell address in Excel?
A cell address identifies the location of a cell using its column letter and row number, such as A1 for column A, row 1.
2. What are the common data formats in Excel?
Common formats include General, Number, Currency, Date, Time, Percentage, Text, and Custom for specific display needs.
3. Explain the order of operations in Excel formulas.
Excel follows PEMDAS: Parentheses, Exponents, Multiplication and Division (left to right), Addition and Subtraction (left to right).
4. What is the SUM function and how do you use it?
The SUM function adds values in a range. Example: =SUM(A1:A10) totals cells from A1 to A10.
5. How do you create a simple formula in Excel?
Start with = sign, then use operators like +, -, *, /. Example: =A1+B1 adds values in A1 and B1.
6. What is relative cell referencing?
Relative referencing adjusts cell addresses when copying formulas. Example: Copying =A1+1 from B1 to B2 becomes =A2+1.
7. What is absolute cell referencing?
Absolute referencing locks a cell with $. Example: =$A$1+1 always refers to A1 when copied.
8. How do you freeze panes in Excel?
Select the cell below and right of the panes to freeze, then go to View > Freeze Panes > Freeze Panes.
9. What is the purpose of data validation in Excel?
Data validation restricts cell entries, like creating drop-down lists. Go to Data > Data Validation > List.
10. How do you sort data in Excel?
Select data range, go to Data > Sort, choose column and order (A to Z or smallest to largest).
Intermediate Excel Interview Questions (11-20)
11. What is the IF function?
IF performs logical tests. Example: =IF(A1>10, "Pass", "Fail") returns Pass if A1 exceeds 10.
12. Explain VLOOKUP function.
VLOOKUP searches vertically. Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]).
13. How does SUMIF work?
SUMIF sums based on criteria. Example: =SUMIF(A1:A10, "Sales", B1:B10) sums B values where A is “Sales”.
14. What is a Pivot Table?
Pivot Table summarizes large datasets dynamically. Insert via Insert > PivotTable, drag fields to areas.
15. How do you apply conditional formatting?
Select range, Home > Conditional Formatting. Example: Highlight cells > Greater Than > value.
16. Explain INDEX-MATCH combination.
INDEX returns value at position, MATCH finds position. Example: =INDEX(B:B, MATCH("Item", A:A, 0)).
17. How do you create a drop-down list?
Data > Data Validation > Settings > List > Source (e.g., =$D$1:$D$5 for list in D1:D5).
18. What is the COUNTIF function?
COUNTIF counts cells meeting criteria. Example: =COUNTIF(A1:A10, ">50") counts numbers over 50.
19. How do you use filters in Excel?
Select data, Data > Filter. Click dropdown arrows in headers to filter by values or conditions.
20. In a Zoho sales report scenario, how would you calculate total sales for a specific product using SUMIF?
Use =SUMIF(ProductColumn, "Laptop", SalesColumn) to sum sales where product matches “Laptop”.
Advanced Excel Interview Questions (21-30)
21. What are Macros in Excel?
Macros automate tasks via VBA. Record via Developer > Record Macro, or write VBA code.
22. Explain XLOOKUP function.
XLOOKUP is modern VLOOKUP alternative. Syntax: =XLOOKUP(lookup_value, lookup_array, return_array), handles errors better.
23. How do you perform What-If Analysis with Goal Seek?
Data > What-If Analysis > Goal Seek. Set cell value to target by changing another cell.
24. What is Scenario Manager?
Compares scenarios. Data > What-If Analysis > Scenario Manager > Add scenarios with changing cells.
25. How do you calculate correlation using CORREL?
=CORREL(A1:A10, B1:B10) returns coefficient between -1 and 1 measuring relationship strength.
26. Explain Pivot Table slicers.
Slicers filter Pivot Tables visually. Insert > Slicer, connect to Pivot Table for interactive filtering.
27. In an Atlassian project tracking scenario, how would you use Solver for resource optimization?
Data > Solver. Set objective cell, variables, constraints like total hours <= budget, solve for allocation.
28. How do you normalize data in Excel?
Use =(Value - AVERAGE(range)) / STDEV(range) to standardize for comparison.
29. What is Power Query in Excel?
Power Query cleans and transforms data. Data > Get Data > transform without formulas.
30. In a Salesforce dataset with duplicates, how would you identify and remove them while preserving YoY calculations?
Data > Remove Duplicates on key columns. For YoY, use Pivot Table with =((Current - Previous)/Previous)*100.
