Basic Excel Interview Questions with Answer Part 1
Basic Excel Interview Questions with Answers
Looking for Basic Excel Interview Questions to prepare for your next job interview? This complete guide covers essential Microsoft Excel questions and answers for beginners, fresh graduates, students, and professionals. You’ll learn about Excel formulas, functions, worksheets, cells, rows, columns, formatting, sorting, filtering, and other fundamental Excel concepts.
Whether you’re preparing for a data entry, accounting, administration, finance, sales, or office job, these questions will help you strengthen your Excel knowledge and prepare for common interview topics.
Don’t wait until interview day—start practicing these Basic Excel Interview Questions and Answers today. Work through each question, test your Excel knowledge, and build the confidence you need to tackle your next Excel interview successfully.
Table of Contents
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 + =
Part 2 — 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.
Part 3 — 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.
Part 4 — 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
Part 5 — Practical Interview Questions
56. How would you find the highest salary?
=MAX(B2:B100)
57. How would you find the second-highest salary?
A modern Excel approach:
=LARGE(B2:B100,2)
58. How would you find the third-highest salary?
=LARGE(B2:B100,3)
59. How would you calculate an employee’s age?
If the date of birth is in A2:
=DATEDIF(A2,TODAY(),”Y”)
60. How would you calculate total sales?
=SUM(B2:B100)
61. How would you calculate sales only for Lahore?
=SUMIF(A2:A100,”Lahore”,B2:B100)
62. How would you count employees from Lahore?
=COUNTIF(A2:A100,”Lahore”)
63. How would you find employees whose salary is greater than 50,000?
=FILTER(A2:C100,C2:C100>50000)
64. How would you identify duplicate employee IDs?
=COUNTIF(A:A,A2)>1
65. How would you extract the first name from a full name?
For modern Excel:
=TEXTBEFORE(A2,” “)
66. How would you extract the last name?
=TEXTAFTER(A2,” “)
67. How would you combine first and last names?
=A2&” “&B2
or:
=CONCAT(A2,” “,B2)
68. How would you remove extra spaces?
=TRIM(A2)
69. How would you convert text to uppercase?
=UPPER(A2)
70. How would you convert text to lowercase?
=LOWER(A2)
71. How would you capitalize each word?
=PROPER(A2)
Common HR Interviewer Excel Questions
72. What is your Excel skill level?
Sample answer:
“I would describe myself as an intermediate-to-advanced Excel user. I am comfortable with formulas, functions, lookups, PivotTables, sorting, filtering, conditional formatting, data validation, and data cleaning. I can also work with advanced functions such as XLOOKUP, INDEX/MATCH, SUMIFS, FILTER, SORT, and UNIQUE.”
73. Which Excel functions do you know?
Sample answer:
“I have worked with SUM, AVERAGE, COUNT, IF, IFERROR, SUMIF, SUMIFS, COUNTIF, COUNTIFS, VLOOKUP, XLOOKUP, INDEX, MATCH, FILTER, SORT, UNIQUE, TEXT functions, and date functions.”
74. Have you worked with PivotTables?
Sample answer:
“Yes. I can create PivotTables to summarize large datasets, group information, apply filters, calculate totals, and create PivotCharts.”
75. Can you clean Excel data?
Sample answer:
“Yes. I can remove duplicates, handle blank cells, trim unnecessary spaces, standardize text, split columns, combine columns, correct formatting, and use Power Query for more complex data-cleaning tasks.”
76. Can you create Excel reports?
Sample answer:
“Yes. I can create structured reports using formulas, Tables, PivotTables, charts, conditional formatting, and dashboards.”
77. How do you protect an Excel worksheet?
Answer:
Use:
Review → Protect Sheet
You can optionally set a password and control which actions users can perform.
78. What is workbook protection?
Answer:
Workbook protection can restrict structural changes such as adding, deleting, moving, or renaming worksheets.
79. What is the difference between Delete and Clear Contents?
Answer:
Delete can remove cells/rows/columns and shift surrounding cells. Clear Contents removes the data while leaving the cell structure in place.
80. What would you do if an Excel formula returns #N/A?
Answer:
First, I would check whether the lookup value exists and whether the lookup ranges are correct. I could also use IFERROR() when an alternative result is appropriate.
Example:
=IFERROR(XLOOKUP(A2,E:E,F:F),”Not Found”)
Top 20 Questions to Prepare Before an Excel Interview
- What is Excel?
- Workbook vs worksheet?
- What is a cell reference?
- Formula vs function?
- What is SUM?
- What is IF?
- What is VLOOKUP?
- What is XLOOKUP?
- VLOOKUP vs XLOOKUP?
- What is INDEX/MATCH?
- Relative vs absolute reference?
- What does F4 do?
- What is Conditional Formatting?
- What is Data Validation?
- What is an Excel Table?
- What is a PivotTable?
- What is Power Query?
- How do you remove duplicates?
- How do you find duplicates?
- How would you create an Excel report/dashboard?
I can also turn this into a professional 100+ Excel Interview Questions & Answers PDF, including MCQs, practical Excel tests, formula-based interview tasks, and answers for Fresher → Advanced candidates.
