Excel Interview Questions & Answers
Table of Contents
Beginner Excel Interview Questions
1. What is Microsoft Excel?
Answer: Microsoft Excel is a spreadsheet application used to store, organize, calculate, analyze, and visualize data using worksheets, formulas, functions, tables, charts, and other tools.
2. What is a workbook?
Answer: A workbook is an Excel file that can contain one or more worksheets.
3. What is a worksheet?
Answer: A worksheet is a spreadsheet page inside an Excel workbook consisting of rows and columns.
4. What is a cell?
Answer: A cell is the intersection of a row and a column. For example, A1 is a cell.
5. What is a cell reference?
Answer: A cell reference identifies the location of a cell, such as A1, B5, or C10.
6. What are rows and columns?
Answer: Rows run horizontally and are numbered 1, 2, 3, etc. Columns run vertically and are identified
by letters A, B, C, etc.
7. What is the difference between a workbook and worksheet?
Answer: A workbook is the complete Excel file, while a worksheet is an individual sheet inside that workbook.
8. What is a formula in Excel?
Answer: A formula is an expression used to perform calculations. It normally starts with =.
Example:
=A1+B1
9. What is an Excel function?
Answer: A function is a predefined formula that performs a specific calculation.
Example:
=SUM(A1:A10)
10. What is AutoSum?
Answer: AutoSum automatically creates a SUM formula to add a range of numbers.
Shortcut:
Alt + =
Basic Excel Functions
11. What does SUM do?
=SUM(A1:A10)
It adds all numeric values in the selected range.
12. What does AVERAGE do?
=AVERAGE(A1:A10)
It calculates the arithmetic average.
13. What does COUNT do?
=COUNT(A1:A10)
It counts cells containing numbers.
14. What does COUNTA do?
=COUNTA(A1:A10)
It counts non-empty cells.
15. What does MAX do?
=MAX(A1:A10)
It returns the largest value.
16. What does MIN do?
=MIN(A1:A10)
It returns the smallest value.
17. What is the IF function?
=IF(A1>=50,”Pass”,”Fail”)
It returns one result when a condition is TRUE and another when it is FALSE.
18. What is COUNTIF?
=COUNTIF(A1:A10,”>50″)
It counts cells that meet a specified condition.
19. What is SUMIF?
=SUMIF(A1:A10,”>50″,B1:B10)
It adds values based on a condition.
20. What is IFERROR?
=IFERROR(A1/B1,0)
It allows you to return an alternative result when a formula produces an error.
Intermediate Excel Interview Questions
21. What is VLOOKUP?
Answer: VLOOKUP searches for a value in the first column of a table and returns a corresponding value from another column.
Example:
=VLOOKUP(A2,$E$2:$G$100,3,FALSE)
22. What is XLOOKUP?
Answer: XLOOKUP is a modern lookup function that can search vertically or horizontally and provides more flexibility than VLOOKUP.
Example:
=XLOOKUP(A2,E:E,G:G,”Not Found”)
23. What is the difference between VLOOKUP and XLOOKUP?
Answer:
| VLOOKUP | XLOOKUP |
| Searches from first column | Can search in any direction |
| Uses column number | Uses a return range |
| Less flexible | More flexible |
| Older function | Modern Excel function |
| Approximate/exact matching | Exact match is the default |
24. What is HLOOKUP?
Answer: HLOOKUP searches for a value horizontally across the first row and returns a corresponding value from another row.
25. What is INDEX?
Answer: INDEX returns a value from a specified position within a range.
=INDEX(B2:B10,5)
26. What is MATCH?
Answer: MATCH returns the position of a value within a range.
=MATCH(“Ali”,A2:A20,0)
27. Why are INDEX and MATCH used together?
Answer: INDEX + MATCH provides a flexible lookup method that can search in different directions and is not restricted by a fixed return-column number.
28. What is an absolute cell reference?
Answer: An absolute reference does not change when a formula is copied.
Example:
$A$1
29. What is a relative reference?
Answer: A relative reference changes when a formula is copied.
Example:
A1
30. What is a mixed reference?
Answer: A mixed reference locks either the row or column.
Examples:
$A1
A$1
31. What does F4 do in a formula?
Answer: F4 cycles through relative, absolute, and mixed references.
Example:
A1
$A$1
A$1
$A1
32. What is conditional formatting?
Answer: Conditional formatting automatically changes the appearance of cells based on specified conditions.
Example: Highlight sales greater than 100,000.
33. What is Data Validation?
Answer: Data Validation controls what users can enter into a cell.
For example, you can create a drop-down list containing:
Pending
Approved
Rejected
34. What is an Excel Table?
Answer: An Excel Table is a structured data range with features such as automatic filtering, sorting, structured references, and automatic expansion.
Shortcut:
Ctrl + T
35. What is a PivotTable?
Answer: A PivotTable is an Excel tool used to summarize, analyze, group, and report large datasets quickly.
36. What is a PivotChart?
Answer: A PivotChart is a chart connected to a PivotTable that visually represents summarized data.
37. What is sorting?
Answer: Sorting rearranges data according to a specific order, such as:
- A → Z
- Z → A
- Smallest → Largest
- Largest → Smallest
38. What is filtering?
Answer: Filtering displays only records that meet specified conditions while hiding other records temporarily.
Shortcut:
Ctrl + Shift + L
39. What is Freeze Panes?
Answer: Freeze Panes keeps selected rows or columns visible while scrolling through a worksheet.
It is especially useful for large datasets.
40. What is Flash Fill?
Answer: Flash Fill automatically recognizes a pattern and fills remaining data accordingly.
Shortcut:
Ctrl + E
Example:
Ali Khan
Ahmed Raza
Usman Ali
Flash Fill can extract first names automatically.
Advanced Excel Interview Questions
41. What is SUMIFS?
Answer: SUMIFS adds values based on multiple criteria.
=SUMIFS(C:C,A:A,”Lahore”,B:B,”Paid”)
42. What is COUNTIFS?
Answer: COUNTIFS counts cells/records that satisfy multiple conditions.
=COUNTIFS(A:A,”Lahore”,B:B,”Paid”)
43. What is AVERAGEIFS?
Answer: It calculates an average based on multiple criteria.
44. What is the FILTER function?
Answer: FILTER returns records that meet specified criteria.
=FILTER(A2:D100,C2:C100=”Paid”)
45. What is the UNIQUE function?
Answer: UNIQUE returns distinct values from a range.
=UNIQUE(A2:A100)
46. What is the SORT function?
Answer: SORT dynamically sorts a range.
=SORT(A2:C100,2,1)
47. What is a dynamic array?
Answer: A dynamic array allows a formula to return multiple results that automatically spill into neighboring cells.
Examples include:
FILTER()
SORT()
UNIQUE()
48. What is a named range?
Answer: A named range gives a meaningful name to a cell or range.
Instead of:
=A1:A100
you could use:
=Sales
49. What is Power Query?
Answer: Power Query is an Excel data transformation and connection tool used to import, clean, combine, transform, and load data from different sources.
50. What is Power Pivot?
Answer: Power Pivot is an Excel data modeling tool that allows users to work with large datasets, relationships, and DAX calculations.
51. What is DAX?
Answer: DAX stands for Data Analysis Expressions. It is a formula language used primarily in Power Pivot and Power BI for calculations and data analysis.
52. What is a slicer?
Answer: A slicer is a visual filtering control used with Tables and PivotTables.
53. What is a dashboard?
Answer: An Excel dashboard is a visual summary of important data using charts, KPIs, PivotTables, slicers, and other analytical elements.
54. How do you remove duplicate data?
Answer: Select the dataset and use:
Data → Remove Duplicates
55. How do you find duplicate values?
Answer: You can use Conditional Formatting or a formula such as:
=COUNTIF(A:A,A2)>1
