EXCEL INTERVIEW PREP
(From basics to advanced)
BASIC EXCEL INTERVIEW QUESTIONS
1. What is Excel used for?
Excel is used to store data, perform calculations, analyze information, and create reports
and dashboards for business decision-making.
Use case: Sales reports, MIS reporting, budgeting.
2. What is a cell and a range in Excel?
A cell is a single data entry point formed by a row and column, and a range is a group of
cells used together for calculations or analysis.
Use case: Applying formulas to multiple records like A1:A100.
3. What is a formula in Excel?
A formula is used to perform calculations in Excel and always starts with an equals sign.
Use case: Calculating totals, differences, percentages.
Example:
=A1+B1
4. What is the SUM function?
The SUM function adds multiple numbers or a range of cells.
Use case: Calculating total sales or expenses.
Syntax:
=SUM(number1, [number2], …)
Example:
=SUM(A1:A10)
Jnana Pravallika
5. What is the AVERAGE function?
The AVERAGE function calculates the mean of selected values.
Use case: Finding average salary or performance score.
Syntax:
=AVERAGE(number1, [number2], …)
Example:
=AVERAGE(B1:B10)
6. What is the COUNT function?
COUNT counts only numeric values in a given range.
Use case: Counting number of transactions.
Syntax:
=COUNT(value1, [value2], …)
Example:
=COUNT(A1:A10)
7. What is the COUNTA function?
COUNTA counts all non-empty cells, including text and numbers.
Use case: Checking whether data is missing.
Syntax:
=COUNTA(value1, [value2], …)
Example:
=COUNTA(A1:A10)
Jnana Pravallika
8. What is the COUNTBLANK function?
COUNTBLANK counts the number of empty cells in a range.
Use case: Identifying missing data.
Syntax:
=COUNTBLANK(range)
Example:
=COUNTBLANK(A1:A10)
9. What are MAX and MIN functions?
MAX returns the highest value and MIN returns the lowest value in a range.
Use case: Identifying top and bottom performance.
Syntax:
=MAX(range)
=MIN(range)
Example:
=MAX(A1:A10)
=MIN(A1:A10)
10. What is relative cell reference?
A relative reference changes automatically when a formula is copied to another cell.
Use case: Row-wise calculations like totals.
Example:
=A1+B1
11. What is absolute cell reference?
Jnana Pravallika
An absolute reference remains fixed using the $ symbol even when copied.
Use case: Applying fixed tax or commission rate.
Example:
=A1*$B$1
12. What is mixed cell reference?
A mixed reference fixes either the row or column while the other part changes.
Use case: Calculation tables or rate matrices.
Example:
=$A1
=A$1
13. What is sorting in Excel?
Sorting arranges data in ascending or descending order to make comparison easier.
Use case: Ranking employees or products.
14. What is filtering in Excel?
Filtering displays only rows that meet specific criteria.
Use case: Viewing region-wise or category-wise data.
15. What is conditional formatting?
Conditional formatting highlights cells automatically based on rules.
Use case: Highlighting low sales, duplicates, or overdue tasks.
16. What is Freeze Panes?
Freeze Panes keeps selected rows or columns visible while scrolling.
Use case: Large datasets with headers.
Jnana Pravallika
17. What is Wrap Text?
Wrap Text displays long text within a cell on multiple lines.
Use case: Description or remarks columns.
18. What is Find and Replace?
Find and Replace searches for specific values and replaces them across the worksheet.
Use case: Cleaning inconsistent data.
19. What is Paste Special?
Paste Special allows pasting only selected components like values or formats.
Use case: Removing formulas and keeping final results.
20. What is AutoFill?
AutoFill automatically fills sequences such as numbers, dates, or formulas.
Use case: Serial numbers and copying formulas.
INTERMEDIATE EXCEL INTERVIEW QUESTIONS
21. What is the IF function?
The IF function is used to apply a logical condition and return different results based on
whether the condition is true or false.
Use case: Pass/Fail status, bonus eligibility, approval logic.
Syntax:
=IF(logical_test, value_if_true, value_if_false)
Example:
=IF(A2>=50,"Pass","Fail")
Jnana Pravallika
22. What is Nested IF?
Nested IF is used when multiple conditions need to be evaluated in a single formula.
Use case: Grading systems or performance bands.
Syntax:
=IF(condition1, result1, IF(condition2, result2, result3))
Example:
=IF(A2>=80,"A",IF(A2>=60,"B","C"))
23. What is the AND function?
The AND function checks multiple conditions and returns TRUE only if all conditions are
satisfied.
Use case: Eligibility rules with multiple criteria.
Syntax:
=AND(logical1, logical2, ...)
Example:
=IF(AND(A2>=50,B2="Yes"),"Eligible","Not Eligible")
24. What is the OR function?
The OR function returns TRUE if at least one of the given conditions is true.
Use case: Flexible rule checks.
Syntax:
=OR(logical1, logical2, ...)
Example:
=IF(OR(A2>90,B2>90),"Top Performer","Normal")
Jnana Pravallika
25. What is the VLOOKUP function?
VLOOKUP searches for a value in the first column of a table and returns a corresponding
value from another column.
Use case: Fetching employee details, prices, or department names.
Syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Example:
=VLOOKUP(A2,Employees!A:C,3,FALSE)
26. What are the limitations of VLOOKUP?
VLOOKUP cannot look to the left, breaks if columns are moved, and is slower on large
datasets.
Use case: Reason for preferring XLOOKUP or INDEX-MATCH.
27. What is the XLOOKUP function?
XLOOKUP is a modern lookup function that replaces VLOOKUP and HLOOKUP and works
in any direction.
Use case: Safer and more flexible lookups.
Syntax:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])
Example:
=XLOOKUP(A2,A:A,B:B,"Not Found")
28. What is the HLOOKUP function?
HLOOKUP searches horizontally across the first row and returns data from a specified row.
Use case: Month-wise data stored in rows.
Syntax:
Jnana Pravallika
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Example:
=HLOOKUP("Jan",A1:D5,2,FALSE)
29. What is the SUMIF function?
SUMIF adds values that meet a single condition.
Use case: Total sales for a specific region or category.
Syntax:
=SUMIF(range, criteria, [sum_range])
Example:
=SUMIF(A:A,"East",B:B)
30. What is the COUNTIF function?
COUNTIF counts the number of cells that meet a specified condition.
Use case: Counting completed tasks or orders.
Syntax:
=COUNTIF(range, criteria)
Example:
=COUNTIF(A:A,"Completed")
31. What is the AVERAGEIF function?
AVERAGEIF calculates the average of values that meet a condition.
Use case: Average salary of a specific department.
Syntax:
=AVERAGEIF(range, criteria, [average_range])
Jnana Pravallika
Example:
=AVERAGEIF(A:A,"HR",B:B)
32. What is the SUMIFS function?
SUMIFS adds values based on multiple conditions.
Use case: Sales by region and month.
Syntax:
=SUMIFS(sum_range, criteria_range1, criteria1, ...)
Example:
=SUMIFS(C:C,A:A,"East",B:B,"Jan")
33. What is the COUNTIFS function?
COUNTIFS counts cells that meet multiple conditions.
Use case: Counting completed orders from a region.
Syntax:
=COUNTIFS(range1, criteria1, range2, criteria2)
Example:
=COUNTIFS(A:A,"East",B:B,"Completed")
34. What is the TEXT function?
The TEXT function formats numbers into readable text.
Use case: Formatting dates or currency.
Syntax:
=TEXT(value, format_text)
Jnana Pravallika
Example:
=TEXT(A1,"dd-mm-yyyy")
35. What is CONCAT / TEXTJOIN?
These functions combine multiple text values into one.
Use case: Creating full names or IDs.
Syntax:
=TEXTJOIN(delimiter, ignore_empty, text1, text2, ...)
Example:
=TEXTJOIN(" ",TRUE,A2,B2)
36. What is the LEFT function?
LEFT extracts characters from the beginning of text.
Use case: Extracting codes or prefixes.
Syntax:
=LEFT(text, [num_chars])
Example:
=LEFT(A2,4)
37. What is the MID function?
MID extracts characters from the middle of a text string.
Use case: Extracting IDs or substrings.
Syntax:
=MID(text, start_num, num_chars)
Example:
Jnana Pravallika
=MID(A2,2,3)
38. What is the TRIM function?
TRIM removes extra spaces from text except single spaces between words.
Use case: Cleaning imported data.
Syntax:
=TRIM(text)
Example:
=TRIM(A2)
39. What is Data Validation?
Data Validation restricts the type of data users can enter into a cell.
Use case: Dropdown lists, preventing invalid input.
40. What is the IFERROR function?
IFERROR replaces Excel errors with a custom value to keep reports clean.
Use case: Avoiding #DIV/0 and #N/A errors in dashboards.
Syntax:
=IFERROR(value, value_if_error)
Example:
=IFERROR(A1/B1,0)
ADVANCED EXCEL INTERVIEW QUESTIONS
Jnana Pravallika
41. What is a Pivot Table?
A Pivot Table is used to quickly summarize, analyze, and aggregate large datasets without
writing formulas.
Use case: Sales summary by region, month, or product for management reports.
42. What are Rows, Columns, Values, and Filters in a Pivot Table?
Rows and Columns define how data is grouped, Values perform calculations, and Filters
control what data is displayed.
Use case: Revenue by region (Rows) and month (Columns) with total sales (Values).
43. What is a Calculated Field in a Pivot Table?
A Calculated Field allows you to create custom calculations inside a Pivot Table using
existing fields.
Use case: Calculating Profit inside Pivot (Sales − Cost).
44. What is a Slicer in Excel?
A Slicer is a visual filter that allows interactive filtering of Pivot Tables and dashboards.
Use case: Filtering dashboard data by region or year with one click.
45. What is the INDEX function?
INDEX returns a value from a specific position in a range based on row and column
numbers.
Use case: Advanced lookups and flexible data retrieval.
Syntax:
=INDEX(array, row_num, [column_num])
Example:
=INDEX(B:B,5)
46. What is the MATCH function?
Jnana Pravallika
MATCH finds the position of a value in a range.
Use case: Locating the row number of a lookup value.
Syntax:
=MATCH(lookup_value, lookup_array, match_type)
Example:
=MATCH(A2,A:A,0)
47. How does INDEX + MATCH work together?
INDEX returns the value and MATCH provides the position, together acting as a flexible
alternative to VLOOKUP.
Use case: Lookups that work even when columns move.
Syntax:
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
Example:
=INDEX(B:B,MATCH(A2,A:A,0))
48. What is the IFERROR function used for in advanced reports?
IFERROR handles errors gracefully and keeps dashboards clean and professional.
Use case: Avoiding visible errors in KPI reports.
Syntax:
=IFERROR(value, value_if_error)
Example:
=IFERROR(A1/B1,0)
49. What is the FILTER function?
Jnana Pravallika
FILTER returns only the rows that meet specified conditions dynamically.
Use case: Creating dynamic reports without Pivot Tables.
Syntax:
=FILTER(array, include, [if_empty])
Example:
=FILTER(A2:C100,C2:C100>5000)
50. What is the SORT function?
SORT dynamically sorts data based on one or more columns.
Use case: Ranking sales or employees dynamically.
Syntax:
=SORT(array, [sort_index], [sort_order])
Example:
=SORT(A2:B10,2,-1)
51. What is the UNIQUE function?
UNIQUE returns a list of distinct values from a range.
Use case: Getting a unique customer or product list.
Syntax:
=UNIQUE(array)
Example:
=UNIQUE(A2:A20)
52. What are Dynamic Arrays in Excel?
Jnana Pravallika
Dynamic Arrays automatically spill results into adjacent cells.
Use case: Creating live, auto-updating reports.
53. What is the TODAY function?
TODAY returns the current date automatically.
Use case: Daily reports and tracking deadlines.
Syntax:
=TODAY()
Example:
=TODAY()
54. What is the NOW function?
NOW returns the current date and time.
Use case: Time-based tracking and logs.
Syntax:
=NOW()
Example:
=NOW()
55. What are volatile functions in Excel?
Volatile functions recalculate whenever the worksheet changes, which can impact
performance.
Use case: Knowing when to avoid them in large files.
56. What is Power Query?
Jnana Pravallika
Power Query is used to clean, transform, and combine data from multiple sources
automatically.
Use case: Monthly data refresh and ETL tasks.
57. What is Power Pivot?
Power Pivot is used for data modeling and creating relationships between large tables.
Use case: Working with millions of rows across multiple tables.
58. What is a Dashboard in Excel?
A dashboard is a visual summary of key metrics using charts, KPIs, and slicers.
Use case: Management and stakeholder reporting.
59. What is a Macro in Excel?
A Macro automates repetitive tasks using recorded or written VBA code.
Use case: Automating report formatting or data cleanup.
60. How do you optimize large Excel files?
Large Excel files are optimized by using tables, Power Query, minimizing volatile formulas,
and avoiding unnecessary formatting.
Use case: Improving performance and reducing file size.
SCENARIO-BASED EXCEL INTERVIEW QUESTIONS
61. How do you find duplicate values in Excel?
Duplicate values are identified by checking if a value appears more than once in a range.
Use case: Data cleaning, customer list validation.
Syntax:
=COUNTIF(range, criteria)
Jnana Pravallika
Example:
=COUNTIF(A:A,A2)>1
62. How do you remove duplicate records?
Duplicates can be removed using Excel’s built-in Remove Duplicates feature.
Use case: Cleaning customer or transaction data.
63. How do you compare two lists in Excel?
Lists are compared by checking whether a value exists in another list.
Use case: Comparing old vs new customer lists.
Syntax:
=COUNTIF(range, criteria)
Example:
=IF(COUNTIF(B:B,A2)=0,"Missing","Found")
64. How do you calculate Year-over-Year (YoY) growth?
YoY growth is calculated by comparing the current year value with the previous year value.
Use case: Financial and sales growth analysis.
Syntax / Example:
=(B2-B1)/B1
65. How do you find the last non-blank cell in a column?
The LOOKUP function is used to return the last non-empty value.
Use case: Dynamic ranges and reporting.
Syntax:
=LOOKUP(lookup_value, lookup_vector, result_vector)
Jnana Pravallika
Example:
=LOOKUP(2,1/(A:A<>""),A:A)
66. How do you create a dropdown list in Excel?
Dropdown lists are created using Data Validation.
Use case: Restricting user input to valid values.
67. How do you highlight top or bottom values?
Conditional Formatting is used to automatically highlight top or bottom values.
Use case: Identifying top performers or low sales.
68. How do you split a full name into first and last name?
Text functions are used to extract parts of the string.
Use case: Preparing clean customer data.
Syntax:
=LEFT(text, FIND(" ",text)-1)
Example:
=LEFT(A2,FIND(" ",A2)-1)
69. How do you handle errors like #DIV/0 or #N/A?
Errors are handled using IFERROR to replace them with a meaningful value.
Use case: Clean dashboards and reports.
Syntax:
=IFERROR(value, value_if_error)
Example:
=IFERROR(A1/B1,0)
Jnana Pravallika
70. How do you lock specific cells in Excel?
Cells are locked by protecting the sheet after unlocking editable cells.
Use case: Preventing accidental changes to formulas.
71. How do you convert text to date format?
DATEVALUE converts text-formatted dates into actual date values.
Use case: Cleaning imported date data.
Syntax:
=DATEVALUE(text)
Example:
=DATEVALUE(A1)
72. How do you combine data from multiple sheets?
Data is combined efficiently using Power Query.
Use case: Monthly or departmental data consolidation.
73. How do you summarize data by month or category?
Pivot Tables are used to aggregate and summarize data quickly.
Use case: Sales summary by month or region.
74. How do you count unique values in Excel?
UNIQUE is used to extract distinct values and COUNTA counts them.
Use case: Counting unique customers.
Syntax:
=COUNTA(UNIQUE(range))
Jnana Pravallika
Example:
=COUNTA(UNIQUE(A1:A20))
75. How do you track KPIs in Excel?
KPIs are tracked using calculated fields, charts, and conditional formatting.
Use case: Performance dashboards.
76. How do you create a dynamic report?
Dynamic reports are created using Tables, Pivot Tables, and slicers.
Use case: Auto-updating management reports.
77. How do you reduce Excel file size?
File size is reduced by removing unnecessary formatting, images, and saving as binary
format.
Use case: Improving performance.
78. How do you prepare Excel data for Power BI?
Data is prepared by cleaning it using Power Query, ensuring proper headers, and avoiding
merged cells.
Use case: Smooth Power BI data refresh.
79. How do you find Top-N values dynamically?
SORT and FILTER functions are used together to return top values.
Use case: Top 5 sales or products.
Syntax:
=SORT(array, sort_index, sort_order)
Example:
=SORT(A2:B10,2,-1)
Jnana Pravallika
80. Why is Excel still widely used in companies?
Excel remains popular because it is flexible, easy to use, widely available, and powerful for
quick analysis and reporting.
Use case: Day-to-day business analysis and decision-making.
Jnana Pravallika