Here’s a curated list of **25 Excel interview FAQs for data analysts**, complete
with clear and concise answers:
---
### **1. What is a Pivot Table, and how is it used?**
**Answer**:
Pivot Tables summarize and analyze large datasets by grouping, aggregating, and
filtering data.
**Steps**: Select data > Insert > Pivot Table > Drag fields to Rows/Columns/Values.
---
### **2. Explain the VLOOKUP function.**
**Answer**:
Searches for a value in the first column of a range and returns a value from
another column.
**Syntax**: `=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])`.
**Example**: `=VLOOKUP(A2, B2:D10, 3, FALSE)` finds an exact match in column B and
returns column D.
---
### **3. What’s the difference between VLOOKUP and HLOOKUP?**
**Answer**:
- **VLOOKUP**: Searches vertically (columns).
- **HLOOKUP**: Searches horizontally (rows).
---
### **4. How do you handle duplicate data in Excel?**
**Answer**:
Use **Data > Remove Duplicates** or **Conditional Formatting > Highlight
Duplicates**.
---
### **5. What are Excel Tables, and why use them?**
**Answer**:
Created via **Ctrl+T**, Excel Tables offer:
- Automatic expansion.
- Structured references (e.g., `Table1[Sales]`).
- Built-in filters and formatting.
---
### **6. How does conditional formatting work?**
**Answer**:
Applies formatting rules based on cell values.
**Example**: Highlight cells > **Home > Conditional Formatting > Highlight Cells
Rules > Greater Than**.
---
### **7. Write an example of the IF function.**
**Answer**:
`=IF(A1 > 50, "Pass", "Fail")` returns "Pass" if A1 exceeds 50, else "Fail".
---
### **8. How do you merge text from multiple cells?**
**Answer**:
Use `&` or `CONCAT`/`TEXTJOIN` (Excel 2019+).
**Example**: `=A1 & " " & B1` or `=TEXTJOIN(" ", TRUE, A1, B1)`.
---
### **9. Why use INDEX-MATCH over VLOOKUP?**
**Answer**:
- **Flexibility**: Searches left-to-right or right-to-left.
- **Syntax**: `=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))`.
---
### **10. How to create a drop-down list?**
**Answer**:
1. Select cells > **Data > Data Validation**.
2. Choose **List** and specify source (e.g., `A1:A5`).
---
### **11. Explain SUMIFS.**
**Answer**:
Sums cells meeting multiple criteria.
**Syntax**: `=SUMIFS(sum_range, criteria_range1, criteria1, ...)`.
**Example**: `=SUMIFS(Sales, Region, "East", Month, "Jan")`.
---
### **12. Relative vs. Absolute References?**
**Answer**:
- **Relative**: `A1` changes when copied.
- **Absolute**: `$A$1` remains fixed.
---
### **13. How to handle #N/A errors?**
**Answer**:
Use `IFERROR` to return custom messages.
**Example**: `=IFERROR(VLOOKUP(...), "Not Found")`.
---
### **14. What does the TRIM function do?**
**Answer**:
Removes extra spaces: `=TRIM(" Data ")` → "Data".
---
### **15. How to format dates with TEXT?**
**Answer**:
`=TEXT(A1, "mm/dd/yyyy")` converts a date serial number to text.
---
### **16. Use of COUNTIF?**
**Answer**:
Counts cells meeting a condition.
**Example**: `=COUNTIF(A1:A10, ">20")`.
---
### **17. What is Power Query?**
**Answer**:
A data transformation tool (**Data > Get & Transform**) to import, clean, and merge
data.
---
### **18. How to create a histogram?**
**Answer**:
1. **Data Analysis ToolPak > Histogram** (classic).
2. **Insert > Histogram Chart** (Excel 2016+).
---
### **19. Explain the OFFSET function.**
**Answer**:
Returns a dynamic range.
**Syntax**: `=OFFSET(start_cell, rows_down, cols_right, height, width)`.
---
### **20. How to protect a worksheet?**
**Answer**:
**Review > Protect Sheet** > Set password and permissions.
---
### **21. What is conditional aggregation?**
**Answer**:
Using `SUMIF`, `COUNTIFS`, or `AVERAGEIF` to aggregate data based on criteria.
---
### **22. Use of the RANK function?**
**Answer**:
`=RANK(number, ref, [order])` ranks numbers in ascending/descending order.
---
### **23. What are array formulas?**
**Answer**:
Formulas performing multiple calculations (use **Ctrl+Shift+Enter**).
**Example**: `{=SUM(A1:A10*B1:B10)}`.
---
### **24. How to calculate loan payments with PMT?**
**Answer**:
`=PMT(rate, nper, pv)`
**Example**: `=PMT(5%/12, 60, 100000)` calculates monthly payments for a ₹1L loan
at 5% APR over 5 years.
---
### **25. XLOOKUP vs. VLOOKUP?**
**Answer**:
**XLOOKUP**:
- Searches in any direction.
- Defaults to exact match.
- Syntax: `=XLOOKUP(lookup_value, lookup_array, return_array)`.
---
### **Key Tips for Interviews**
- **Practice Formulas**: Master `VLOOKUP`, `SUMIFS`, and `INDEX-MATCH`.
- **Know Shortcuts**: E.g., **Alt + N + V** for Pivot Tables.
- **Highlight Projects**: Mention real-world Excel use cases.
Here’s a **dedicated list of 25 Excel interview questions** for data analysts,
focusing on formulas, functions, and advanced features:
---
### **1. What is the difference between `COUNT`, `COUNTA`, and `COUNTBLANK`?**
**Answer**:
- `COUNT`: Counts numeric cells.
- `COUNTA`: Counts non-empty cells (text, numbers, errors).
- `COUNTBLANK`: Counts empty cells.
---
### **2. How do you use the `XLOOKUP` function?**
**Answer**:
Replaces `VLOOKUP`/`HLOOKUP` with flexible syntax:
```excel
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode])
```
**Example**:
`=XLOOKUP("Apple", A2:A10, B2:B10)`
---
### **3. What are dynamic array functions? Name three.**
**Answer**:
Functions that spill results into multiple cells:
- `FILTER`: Filters data based on criteria.
- `SORT`: Sorts a range dynamically.
- `UNIQUE`: Extracts unique values.
---
### **4. How do you combine data from multiple sheets?**
**Answer**:
Use **Power Query** (Data > Get Data > From File > From Workbook) to merge tables
or append queries.
---
### **5. How to create a waterfall chart?**
**Answer**:
1. Select data.
2. Go to **Insert > Waterfall Chart** (Excel 2016+).
---
### **6. What is the `AGGREGATE` function?**
**Answer**:
Performs calculations while ignoring errors/hidden rows:
```excel
=AGGREGATE(function_num, options, range)
```
**Example**:
`=AGGREGATE(9, 5, A1:A10)` calculates SUM (9) while ignoring hidden rows (5).
---
### **7. How to use `INDEX-MATCH-MATCH` for two-way lookup?**
**Answer**:
```excel
=INDEX(data_range, MATCH(row_value, row_header_range, 0), MATCH(col_value,
col_header_range, 0))
```
---
### **8. What is the `LET` function?**
**Answer**:
Assigns names to calculation results for readability:
```excel
=LET(name, value, calculation)
```
**Example**:
`=LET(x, A1+A2, x*10)`
---
### **9. How to use `TEXTJOIN` to concatenate with a delimiter?**
**Answer**:
```excel
=TEXTJOIN(", ", TRUE, A1:A10)
```
- `TRUE` ignores empty cells.
---
### **10. How to transpose data without formulas?**
**Answer**:
1. Copy the data.
2. Right-click > **Paste Special > Transpose**.
---
### **11. What is the `SWITCH` function?**
**Answer**:
Evaluates multiple conditions without nested `IF` statements:
```excel
=SWITCH(expression, value1, result1, [default])
```
**Example**:
`=SWITCH(A1, "Red", 1, "Blue", 2, 0)`
---
### **12. How to find the last non-empty cell in a column?**
**Answer**:
Use `LOOKUP`:
```excel
=LOOKUP(2,1/(A:A<>""), A:A)
```
---
### **13. What is the `IFS` function?**
**Answer**:
Simplifies multiple `IF` conditions:
```excel
=IFS(condition1, result1, condition2, result2, ...)
```
---
### **14. How to calculate running totals?**
**Answer**:
Use a formula like `=SUM($A$1:A1)` and drag down.
---
### **15. How to use `SUMPRODUCT` for conditional sums?**
**Answer**:
```excel
=SUMPRODUCT((A1:A10="East")*(B1:B10))
```
---
### **16. How to highlight weekends in a date range?**
**Answer**:
1. Select dates.
2. **Conditional Formatting > New Rule > Use Formula**:
```excel
=WEEKDAY(A1,2)>5
```
---
### **17. How to create a dynamic named range?**
**Answer**:
Use `OFFSET` or `INDEX`:
```excel
=OFFSET($A$1,0,0,COUNTA($A:$A),1)
```
---
### **18. How to remove spaces between words?**
**Answer**:
Use `TRIM` for leading/trailing spaces and `SUBSTITUTE` for all spaces:
```excel
=SUBSTITUTE(A1, " ", "")
```
---
### **19. What is the `EDATE` function?**
**Answer**:
Adds months to a date:
```excel
=EDATE(start_date, months)
```
---
### **20. How to calculate the median with criteria?**
**Answer**:
Use `MEDIAN` with `IF` in an array formula (**Ctrl+Shift+Enter**):
```excel
=MEDIAN(IF(A1:A10="East", B1:B10))
```
---
### **21. How to use `FORMULATEXT`?**
**Answer**:
Displays the formula from a referenced cell:
```excel
=FORMULATEXT(A1)
```
---
### **22. How to create a data entry form?**
**Answer**:
1. Add the **Form** button via **Quick Access Toolbar > More Commands**.
2. Use it to input data into tables.
---
### **23. How to use `CELL` function?**
**Answer**:
Returns cell metadata:
```excel
=CELL("filename", A1) // Returns workbook name
```
---
### **24. How to prevent duplicate entries?**
**Answer**:
Use **Data Validation** with a custom formula:
```excel
=COUNTIF($A$1:$A$10, A1)=1
```
---
### **25. What is the `LAMBDA` function?**
**Answer**:
Creates custom reusable functions without VBA:
```excel
=LAMBDA(x, y, x+y)(A1, B1) // Adds two values
```
---
### **Key Areas to Master**
- **Formulas**: `XLOOKUP`, `FILTER`, `LAMBDA`.
- **Data Tools**: Power Query, PivotTables, Data Validation.
- **Dynamic Arrays**: `SORT`, `UNIQUE`, `SEQUENCE`.
Let me know if you need examples or practice exercises! 🚀
