📊 150 Most Asked Excel Interview Questions & Answers (2026 Edition) 🚀 From Beginner to Excel Expert | Formulas, Pivot Tables, Dashboards, Data Analysis & 20+ Real-World Scenarios | FreeLearning365

 

🚀 150 Most Asked Excel Interview Questions & Answers (2026 Edition)

From Complete Beginner to Expert with Real-Life Scenarios, Hands-On Solutions & Pro Tips

Welcome to Tech Book & FreeLearning365 – your free, practical learning community!
At @FreeLearning365 we believe that every expert was once a beginner who never stopped learning. The secret to mastering Excel isn’t memorising functions – it’s working on real projects, solving messy data, and building tools that actually help people. This guide is built exactly for that: 150 of the most asked Excel interview questions, each answered simply and practically, with over 20 realistic hands-on scenarios you’ll actually face on the job.
Share this with your network, save it for your next interview, and keep practising – that’s how real growth happens. 🚀

---

🔰 Beginner Level (1–30)

1. What is Excel?
A spreadsheet program by Microsoft used for data entry, calculations, charts, automation and analysis.

2. What is a cell?
The intersection of a row and column, e.g., A1. It holds a value, text or formula.

3. Worksheet vs workbook?
A worksheet is a single sheet; a workbook is the entire file (multiple worksheets).

4. What is a cell reference?
The address of a cell used in formulas, like B2.

5. Relative, Absolute, Mixed reference?

• Relative: A1 – changes when copied.

• Absolute: $A$1 – stays fixed.

• Mixed: $A1 or A$1 – fixes column or row.

6. How to quickly sum a column?
AutoSum (Σ button) or Alt + =. It inserts =SUM().

7. What is the SUM function?
=SUM(range) adds all numbers in the range.

8. How does IF function work?
=IF(condition, value_if_true, value_if_false).
Example: =IF(A1>100,"High","Low").

9. What is VLOOKUP?
Vertical lookup. Searches a value in the first column of a table and returns a value from a specified column.
=VLOOKUP(lookup_value, table, col_index, FALSE).

10. Why FALSE in VLOOKUP?
Forces exact match. Without it, approximate match may return wrong results.

11. What is HLOOKUP?
Horizontal lookup – like VLOOKUP but searches first row.

12. XLOOKUP advantages?
Searches anywhere, can return multiple values, handles errors natively, no column-index fuss.

13. What is a PivotTable?
Interactive tool to summarise, group, filter, and analyse large data without formulas.

14. How to create a PivotTable?
Select data → Insert → PivotTable → drag fields to Rows, Columns, Values.

15. What is a PivotChart?
A chart linked to a PivotTable that updates when the PivotTable changes.

16. What is conditional formatting?
Applies colours, icons, bars based on cell values to reveal patterns.

17. What is data validation?
Restricts cell input (dropdown, number range, date).

18. How to create a dropdown list?
Data → Data Validation → List → enter values or refer to a range.

19. What is TRIM function?
Removes extra spaces from text. =TRIM(A1).

20. CONCATENATE vs CONCAT vs TEXTJOIN?

• CONCATENATE: old, joins strings.

• CONCAT: newer, handles ranges.

• TEXTJOIN: adds a delimiter and can skip empties.

21. LEFT, RIGHT, MID functions?
Extract parts of text: LEFT(text, n), RIGHT(text, n), MID(text, start, n).

22. What is COUNTIF?
Counts cells that meet one condition. =COUNTIF(A1:A10,">100").

23. COUNTIFS vs COUNTIF?
COUNTIFS handles multiple criteria across ranges.

24. SUMIF and SUMIFS?
SUMIF(range, criteria, [sum_range]) – one condition.
SUMIFS(sum_range, criteria_range1, criteria1, ...) – multiple conditions.

25. What is INDEX function?
Returns a value at a given row and column inside a range. =INDEX(A1:C10,2,3).

26. What is MATCH function?
Returns position of a lookup value. =MATCH(value, range, 0).

27. INDEX+MATCH vs VLOOKUP?
More flexible: can look left, handle column changes, two-way lookups.

28. What is INDIRECT?
Converts text to a reference. =INDIRECT("A1") reads cell A1.

29. What is an array formula?
Performs multiple calculations on arrays. New Excel uses dynamic arrays; older needs Ctrl+Shift+Enter.

30. What is a dynamic array?
A formula that automatically spills results into multiple cells. E.g., =SORT(A1:A100).

---

🟡 Intermediate Level (31–70)

31. How does FILTER function work?
=FILTER(array, include, [if_empty]) returns rows matching a condition, dynamically.

32. UNIQUE function?
Returns unique values from a range. =UNIQUE(A1:A100).

33. SORT vs SORTBY?
SORT orders a range directly; SORTBY sorts based on another array without including it.

34. What is an Excel Table?
Structured data range (Ctrl+T) with auto-formatting, filtering, and column-named formulas.

35. Benefits of Excel Tables?
Auto-expanding ranges, structured references, easy filtering, dynamic charts.

36. What are Named Ranges?
A name you give a cell or range (e.g., “Sales”) to use in formulas.

37. IFERROR function?
=IFERROR(formula, value_if_error) hides errors gracefully.

38. IFERROR vs IFNA?
IFERROR catches all errors; IFNA only catches #N/A.

39. What is a macro?
A recorded/written automation of tasks using VBA or macro recorder.

40. How to enable Developer tab?
Right-click ribbon → Customize Ribbon → check Developer.

41. What is VBA?
Visual Basic for Applications – Excel’s programming language for macros and user forms.

42. How to create a simple macro?
Developer → Record Macro → perform actions → Stop. Then run it.

43. Personal Macro Workbook?
A hidden workbook (PERSONAL.XLSB) that stores macros for all files.

44. PivotTable calculated field?
A new field in a PivotTable created with a formula using existing fields.

45. PivotTable slicer?
Visual button for filtering PivotTables/PivotCharts interactively.

46. Timeline slicer?
A date-based slicer to filter by months, quarters, years.

47. What is Power Query?
A data transformation tool (Get & Transform) to import, clean, reshape data.

48. Where is Power Query?
Data tab → Get & Transform Data (Excel 2016+).

49. Power Query vs formulas?
Power Query is for ETL (extract, transform, load); formulas work on already-loaded data.

50. What is Power Pivot?
An in-memory data modelling tool handling large datasets, relationships, and DAX.

51. How to enable Power Pivot?
COM Add-ins → Microsoft Power Pivot for Excel.

52. What is DAX?
Data Analysis Expressions – formula language for Power Pivot measures and calculated columns.

53. Relationship in Power Pivot?
A link between tables using a common column – replaces VLOOKUP across tables.

54. Measure vs calculated column in Power Pivot?
Calculated column is row‑by‑row static; measure is dynamic based on filter context.

55. GETPIVOTDATA function?
Extracts data from a PivotTable using field names. Automatically generated.

56. How to stop auto‑GETPIVOTDATA?
PivotTable Analyze → Options → uncheck “Generate GetPivotData”.

57. How to protect a worksheet?
Review → Protect Sheet → set password & permissions.

58. Protect workbook vs worksheet?
Worksheet restricts cell changes; workbook prevents structural changes (add/delete sheets).

59. What is cell locking?
Cells are locked by default but only take effect after sheet protection.

60. Remove duplicates?
Data → Remove Duplicates → choose columns.

61. Text to Columns?
Splits a column by delimiter or fixed width (Data tab).

62. Flash Fill?
Detects pattern and auto‑fills (Ctrl+E). Great for splitting names, formatting.

63. What is a sparkline?
Tiny chart inside a cell showing trend (line, column, win/loss).

64. Print headers on every page?
Page Layout → Print Titles → set rows to repeat.

65. Watch Window?
Monitors cell values in a floating window (Formulas tab).

66. Goal Seek?
What‑if tool to find the input that achieves a target (Data → What‑If Analysis).

67. Solver vs Goal Seek?
Goal Seek changes one variable; Solver handles multiple variables and constraints.

68. Scenario Manager?
Saves different input sets and switches between them to view outcomes.

69. EDATE function?
Returns date x months from a start date. =EDATE(start, months).

70. NETWORKDAYS?
Working days between two dates, excluding weekends/holidays.

---

🟠 Advanced Level (71–110)

71. Custom Number Formats?
Define display format without changing value. Use codes like #,##0 and "K".

72. Show thousands as “K”?
Format: #,##0, "K" – the comma after 0 divides by 1000.

73. INDIRECT for dependent dropdowns?
Create named ranges matching first dropdown entries; use =INDIRECT(first_cell) in second validation.

74. Find circular references?
Formulas → Error Checking → Circular References.

75. AGGREGATE function?
Like SUBTOTAL but can ignore errors, hidden rows, and nested SUBTOTAL. =AGGREGATE(9,6,range) sums ignoring errors.

76. SUMPRODUCT?
Multiplies arrays and sums products. Used for conditional sums without array formulas.

77. INDIRECT for dynamic sheet references?
=SUM(INDIRECT("'"&A1&"'!B:B")) – sum column B from sheet named in A1.

78. Array constants?
Values in braces {1,2,3} used in formulas.

79. FORMULATEXT function?
Shows formula as text. Useful for auditing.

80. Trace Precedents/Dependents?
Shows arrows between cells (Formulas tab) to visualise relationships.

81. Database functions (DSUM)?
=DSUM(database, field, criteria) – performs aggregate on a list with criteria range.

82. HYPERLINK function?
=HYPERLINK(url, friendly_name) creates clickable links.

83. How to transpose data?
Copy → Paste Special → Transpose. Or use TRANSPOSE() array function.

84. Clear vs Delete?
Clear removes content/formatting; Delete removes cells and shifts others.

85. Combo chart?
Combines two chart types (e.g., column + line) with dual axes.

86. Dynamic chart range?
Use Excel Table as source, or named range with INDEX/OFFSET.

87. OFFSET function?
=OFFSET(reference, rows, cols, [height], [width]) returns a dynamic reference.

88. OFFSET vs INDEX for dynamic ranges?
INDEX is non‑volatile (better performance) vs OFFSET which recalculates constantly.

89. What is a volatile function?
Recalculates with every worksheet change even if inputs haven’t changed. Examples: NOW, TODAY, RAND, OFFSET, INDIRECT.

90. Combine text with line break?
=TEXTJOIN(CHAR(10), TRUE, range) + Wrap Text.

91. LAMBDA function?
Create custom functions using formulas, no VBA. Define a name and reuse.

92. LET function benefits?
Assigns names to intermediate calculations – cleaner, faster formulas.

93. Heat map?
Conditional formatting with Color Scales (Home → Conditional Formatting).

94. Forecast Sheet?
Data tab → Forecast Sheet – uses exponential smoothing to predict values.

95. TEXTSPLIT, TEXTBEFORE, TEXTAFTER?
New text functions: split text into rows/columns, extract before/after a delimiter.

96. Normal chart vs PivotChart?
PivotChart is interactive and linked to PivotTable; normal chart uses static data.

97. Protect formula from being seen/edited?
Lock cell, check “Hidden” in Format Cells, then protect sheet.

98. Document Inspector?
File → Info → Check for Issues → Inspect Document. Removes hidden data and personal info.

99. Fix #VALUE! error?
Check for wrong data types (text where number expected). Use ISTEXT, ISNUMBER to diagnose.

100. Best practice for scalable Excel models?
Separate inputs/calculations/outputs, use Tables, avoid hard-coding, named ranges, consistent formulas, Power Query for data loads.

101. What is a calculated item in a PivotTable?
A custom item within a PivotTable field created by a formula (different from calculated field).

102. How to join tables without Power Pivot?
Use VLOOKUP, INDEX+MATCH, XLOOKUP, or Power Query merge.

103. Advantages of Power Query over manual cleaning?
Repeatable, step‑by‑step, handles millions of rows, no formula dragging, easy updates.

104. What is the M language?
The formula language behind Power Query steps.

105. How to handle data from a folder?
Power Query → From File → From Folder → combine and transform all files automatically.

106. What is a parameter in Power Query?
A named input that makes queries dynamic (e.g., file path, filter value).

107. How to deal with changing column names in Power Query?
Use column positions or “Demote Headers” and rename programmatically.

108. What is the performance difference between SUMIFS and SUMPRODUCT for large data?
SUMIFS is generally faster because it is optimised; SUMPRODUCT evaluates arrays and can be slower.

109. How to create a custom sort list?
File → Options → Advanced → Edit Custom Lists → import or type order.

110. What is a dynamic named range using INDEX?
=Sheet1!$A$1:INDEX(Sheet1!$A:$A, COUNTA(Sheet1!$A:$A)) – auto‑expanding range.

---

🧩 Real-Life Scenario-Based Questions (111–135)

Practical, hands-on problems you’ll face at work – with answers that show you can deliver.

111. Scenario: You receive a dataset with duplicate transactions. Manager wants only the latest entry per order ID. How do you do it?
Sort by Order ID and Date descending, then use Remove Duplicates on Order ID column. For dynamic approach, use =UNIQUE(FILTER(...)) or Power Query: sort by date descending, remove duplicates.

112. Scenario: Two tables – Orders and Customers – need a combined report without Power Pivot. How?
Use XLOOKUP or INDEX+MATCH to bring customer name into Orders sheet. Or use Power Query Merge to join tables and load.

113. Scenario: A weekly report requires splitting full names into First and Last Name from a single column. Many have middle names. Flash Fill?
Flash Fill (Ctrl+E) works well for simple patterns. For robust, use:

• First: =LEFT(A2, FIND(" ", A2)-1)

• Last: =TRIM(RIGHT(SUBSTITUTE(A2, " ", REPT(" ", 99)), 99))

114. Scenario: You must create a dashboard that filters by region, product, and month using slicers. Key steps?
Put data in an Excel Table → Insert PivotTables and PivotCharts → add slicers for Region, Product, and a Timeline for Month → connect slicers to all PivotTables (right-click → Report Connections). Arrange on one sheet.

115. Scenario: Sales data has order dates in format “20250131” (YYYYMMDD). How to convert to proper date?
=DATE(LEFT(A1,4), MID(A1,5,2), RIGHT(A1,2)). In Power Query, change type to Date after splitting or using Date.FromText.

116. Scenario: You need to find the top 5 customers by revenue in a huge table. Formula?
=INDEX(SORT(UNIQUE(...))? Actually: Use a PivotTable: Rows = Customer, Values = Sum of Revenue, sort descending, filter Top 5. Or dynamic array: =SORT(UNIQUE(...)) with LARGE if unique.

117. Scenario: Payroll: calculate overtime pay (hours above 40 at 1.5 rate).
=IF(Hours>40, (40*Regular_Rate) + (Hours-40)*Regular_Rate*1.5, Hours*Regular_Rate).

118. Scenario: Clean imported addresses: remove extra spaces, separate City, State ZIP into columns.

• Trim: =TRIM(A2)

• Split: =TEXTBEFORE(TRIM(A2), ",", -1) for City, etc. Or Text to Columns with comma delimiter. Modern: TEXTSPLIT.

119. Scenario: Build a loan amortisation schedule (monthly payment, principal, interest split).
Use PMT(rate, nper, pv) for payment. Then:

• Interest = IPMT(rate, period, nper, pv)

• Principal = PPMT(rate, period, nper, pv)

• Balance = previous balance - principal.

120. Scenario: You have a list of customer IDs and order dates; find the latest order date for each customer.
=MAXIFS(OrderDate_range, CustomerID_range, customer_cell). Or dynamic: =SORT(UNIQUE(... with MAXIFS.

121. Scenario: Create a Gantt chart for project tasks using Excel.
Use stacked bar chart: two series – one for start date (invisible) and one for duration. Format bars, use conditional formatting.

122. Scenario: After deleting rows, your formulas show #REF! How to prevent?
Use structured references (Table columns) instead of direct range references, or use INDIRECT with caution. Index/Match is more robust than direct A1 references.

123. Scenario: Automate a monthly sales report that pulls from CSV, cleans, and creates a summary.
Use Power Query to connect to CSV file, clean (remove blanks, change types), load to workbook. Refresh with one click each month. If needed, add a short VBA script to refresh all and save.

124. Scenario: Commission calculation: 5% for sales up to $10k, 7% for $10k–$20k, 10% above. Tiered formula.
=SUMPRODUCT((Sales>{0,10000,20000})*(Sales-{0,10000,20000}), {0.05,0.02,0.03}) – incremental.

125. Scenario: Data validation: if User selects “Custom” in column A, column B must show a list of custom options; if “Standard”, different list.
Create named ranges “CustomList”, “StandardList”. In B2 validation: =INDIRECT(A2). Ensure A2 matches named range exactly.

126. Scenario: You need to find break-even units: Fixed costs / (Price – Variable cost). What tool to determine input to reach zero profit?
Goal Seek: Set profit cell to 0 by changing units cell.

127. Scenario: Sensitivity analysis: how does net profit change with varying price and cost?
Use Data Table (Data → What-If → Data Table) with two variables (row and column input cells).

128. Scenario: Highlight all overdue invoices (due date < today) in red, and paid ones green.
Conditional Formatting: formula rule =AND(DueDate<TODAY(), Status<>"Paid") red; another rule for Status="Paid" green.

129. Scenario: Protect a form so users only fill yellow cells; all formulas and headers locked.
Unlock yellow input cells (Ctrl+1 → Protection → uncheck Locked). Lock everything else. Hide formulas if needed. Protect Sheet.

130. Scenario: Importing a large CSV with parsing errors, wrong delimiters, and nulls. How to clean efficiently?
Use Power Query: detect delimiter, replace errors/null with appropriate values, set correct data types. Steps are repeatable.

131. Scenario: You need a unique, sorted list of departments from a messy column with blanks and duplicates.
=SORT(UNIQUE(FILTER(DepartmentRange, DepartmentRange<>""))) – dynamic array.

132. Scenario: Real estate price analysis: scatter chart with trendline and equation.
Insert scatter (X = area, Y = price). Add trendline, display equation and R² on chart.

133. Scenario: Consolidate monthly sheet tabs (Jan, Feb, …) with identical layout into one summary.
Use Power Query: From Workbook → combine sheets with same structure. Or 3D sum =SUM(Jan:Dec!B2) if layout identical.

134. Scenario: A user accidentally types dates in inconsistent formats (US vs EU). How to standardise?
Use Power Query: change type to Date using locale. Or formula: =DATEVALUE(MID(...)) but Power Query is robust.

135. Scenario: Create a dynamic print area that adjusts as data grows.
Define a named range using OFFSET or INDEX, then set Print Area to that named range. Or use a Table.

---

⚡ Expert-Level & Strategic Questions (136–150)

136. How to handle a 2‑million‑row dataset that doesn’t fit in a normal sheet?
Use Power Query for transformation, load only to Data Model (Power Pivot) without filling worksheet. Analyse via PivotTables.

137. Explain the difference between row context and filter context in DAX.
Row context is the current row in a calculated column; filter context is the set of filters applied by PivotTable/slicers.

138. What is the performance impact of many volatile functions?
Slows down workbook because they recalculate on every change, even unrelated ones. Minimise OFFSET, INDIRECT, use INDEX instead.

139. How to create a reusable custom function in Excel without VBA?
Define a name with a LAMBDA formula. Example: =LAMBDA(x, IF(x>0, x, 0)) named “PosNum”.

140. How to handle the “list separator” issue when sharing workbooks across locales?
Use semicolon vs comma differently; best to write formulas using universal approach or Power Query which is locale-independent.

141. What is the best way to debug a complex nested formula?
Use Evaluate Formula tool (Formulas tab). Break formula into parts with LET, or separate helper columns.

142. How to ensure a PivotTable includes new rows automatically?
Use Excel Table as source for PivotTable; right-click PivotTable → Refresh.

143. What is the difference between .xlsb and .xlsx?
.xlsb is binary format – faster open/save, smaller size, supports macros. .xlsx is XML-based, no macros.

144. How to reduce Excel file size significantly?
Remove excess formatting, compress images, save as .xlsb, use Power Query instead of formulas for heavy data, delete blank rows/columns.

145. Scenario: Your VLOOKUP is returning #N/A even though the value exists. Why?
Check for hidden spaces (TRIM), numbers stored as text. Use VALUE() or convert with Text to Columns.

146. Scenario: Create a rolling 12‑month chart that updates automatically.
Use a dynamic named range with INDEX and COUNTA for the date and value columns. Chart source = named ranges.

147. How do you approach building a financial model from scratch?
Separate assumptions, calculations, outputs; use consistent timeline; clearly colour code; include error checks and summary dashboard; use scenario manager.

148. What are the key differences between Excel on the desktop and Excel for the web?
Limited VBA, some advanced features (Power Pivot) unavailable online, but co‑authoring is stronger.

149. How to handle sensitive data in Excel before sharing?
Use Document Inspector, password-protect sheet/workbook, mask data with formulas or Dynamic Data Masking, or remove sensitive columns entirely.

150. What separates a true Excel expert from an intermediate user?
The ability to choose the right tool (formula vs Power Query vs Pivot vs VBA), automate smartly, design for the end user, and explain their work clearly. It’s not about knowing every function – it’s about solving real problems with the simplest, most scalable solution.

---

✨ Final Words from @FreeLearning365

Remember – Excel is not a software, it’s a mindset. Every messy dataset, every tight deadline, every “can you just…” request is a chance to build something that saves hours, delights users, and grows your skills. Keep practising, keep sharing your knowledge, and never stop exploring the edges of what Excel can do.
You’re now ready to ace that interview and solve real-world problems like a pro.

If this guide helped you, share it with a friend, tag us @FreeLearning365 and @techbook24, and stay tuned for more hands-on learning resources. 🚀

#Excel #ExcelInterview #DataAnalysis #PowerQuery #ExcelExpert #FreeLearning365 #TechBook

---

For even deeper dives, follow @techbook24 and @FreeLearning365 – where learning is free, practical, and always career‑focused.