Top 50 Excel Interview Questions
Related Excel Core Functions , Intermediate and Advanced
Excel Related Question & Answer
Basic / Fundamental Questions
Want to feel confident in your Excel interview? We’ve broken down the first 15 questions for you.
Intermediate Questions
Ready to take your Excel skills to the next level? Questions 16 to 35 cover the tools employers love most!
Advanced Questions
Ready to stand out as an Excel expert? Questions 36 to 50 cover the high-level tools top companies pay big money for!
Basic / Fundamental Questions (Q1 to 15)
Intermediate Questions (Q16 to 35)
Advance Questions (Q36 to 50)
1. What is the difference between a Cell, Worksheet, and Workbook?
Cell: The smallest individual unit where data is entered (e.g.,
A1).Worksheet: A single page containing rows and columns (e.g.,
Sheet1).Workbook: An entire Excel file that contains one or more worksheets.
2. What is the difference between relative and absolute cell references?
Relative Reference (
A1): Adjusts automatically when copied to another cell.Absolute Reference (
$A$1): Stays fixed on cellA1regardless of where the formula is copied.Mixed Reference (
$A1orA$1): Locks either the column or the row exclusively.
3. How do you lock a cell reference quickly in Excel?
Press the F4 key while highlighting a cell reference in the formula bar to cycle through absolute and mixed references (($A$1 => A$1 => $A1 => A1).
4. What is Conditional Formatting?
A feature that automatically applies formatting (colors, data bars, icon sets) to cells based on specified conditions or rules (e.g., highlighting numbers $> 1000$ in green).
5. How do you remove duplicate rows in a dataset?
Select the dataset => go to the Data tab => click Remove Duplicates => select the columns to check => click OK.
6. What is the shortcut to select an entire row or column?
Entire Row:
Shift + SpacebarEntire Column:
Ctrl + Spacebar
7. How do you insert the current date and current time in Excel?
Current Date (Static):
Ctrl + ;(Semicolon)Current Time (Static):
Ctrl + Shift + ;Dynamic Date/Time: Use formulas
=TODAY()or=NOW().
8. What is the difference between COUNT, COUNTA, and COUNTBLANK?
=COUNT(): Counts cells containing only numbers.=COUNTA(): Counts all non-empty cells (text, numbers, errors).=COUNTBLANK(): Counts empty cells only.
9. What is Freeze Panes and why is it used?
Freeze Panes keeps row headers or column headers visible while scrolling through large datasets (View tab => Freeze Panes).
10. How do you split text into multiple columns (e.g., First Name and Last Name)?
Select the column => Data Tab => Text to Columns => choose Delimited (by space or comma) => click Finish.
11. What is Flash Fill and what is its keyboard shortcut?
Flash Fill automatically detects patterns in adjacent columns and fills data accordingly.
Shortcut:
Ctrl + E.
12. How do you convert formulas to static values?
Copy cells (Ctrl + C) => Right-click target => Paste Special (Ctrl + Alt + V) => select Values.
13. What is the difference between Formulas and Functions?
Formula: A user-written expression (e.g.,
=A1 + B1).Function: A built-in predefined calculation in Excel (e.g.,
=SUM(A1:B1)).
14. How do you create a Dropdown List in Excel?
Select target cell => Data Tab => Data Validation => Allow: List => Enter comma-separated items or select a cell range.
15. What are Named Ranges and why are they helpful?
A Named Range gives a descriptive name to a cell or cell range (e.g., naming B2:B100 as Sales). It makes formulas easier to read (=SUM(Sales)).
16. What is VLOOKUP and what are its limitations?
Limitations: Can only look from left to right; breaks if columns are inserted/deleted.
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])searches for a value in the first column of a table and returns a value in the same row from a specified column.
17. How does XLOOKUP improve upon VLOOKUP?
Can look left, right, up, or down.
Defaults to exact match.
Doesn’t break when columns/rows are inserted.
Built-in error handling (
if_not_foundargument).
18. What is the difference between VLOOKUP and HLOOKUP?
VLOOKUP: Searches vertically down columns.HLOOKUP: Searches horizontally across rows.
19. What is the difference between SUMIF and SUMIFS?
SUMIF(range, criteria, [sum_range]): Sums based on one single condition.SUMIFS(sum_range, criteria_range1, criteria1, ...): Sums based on multiple conditions.
20. How do INDEX and MATCH work together?
MATCH: Returns the relative position/row number of an item.INDEX: Returns the value at a specific row/column index.Combining them (
=INDEX(Return_Range, MATCH(Value, Lookup_Range, 0))) allows dynamic left-and-right lookups.
21. What is a Pivot Table and why is it used?
An interactive data summarization tool used to organize, aggregate, group, and analyze large datasets without writing complex formulas.
22. What is a Slicer in Excel?
An interactive visual filter button for Pivot Tables, Pivot Charts, or Data Tables.
23. What is a Calculated Field in a Pivot Table?
A custom field created inside a Pivot Table that performs calculations using existing data fields (e.g., Revenue - Cost to create a Profit field).
24. What are Nested IF statements?
Placing an IF function inside another IF function to test multiple conditions: =IF(A1>90, "A", IF(A1>75, "B", "C")).
25. What is the IFS function (Excel 2019+)?
Simplifies nested IFs by evaluating multiple conditions in order: =IFS(A1>90, "A", A1>75, "B", TRUE, "C").
26. What is IFERROR and how do you use it?
Suppresses error messages (#N/A, #VALUE!, #DIV/0!) and replaces them with custom text or values: =IFERROR(VLOOKUP(...), "Not Found").
27. What is the difference between COUNTIF and COUNTIFS?
COUNTIF: Counts cells matching one condition.COUNTIFS: Counts cells matching multiple criteria simultaneously.
28. How do you extract text using LEFT, RIGHT, and MID?
=LEFT(text, num_chars): Extracts characters from the start.=RIGHT(text, num_chars): Extracts characters from the end.=MID(text, start_num, num_chars): Extracts characters from the middle.
29. What is TEXTJOIN and how does it differ from CONCAT?
CONCAT: Combines text strings without automatic delimiters.TEXTJOIN: Combines strings using a specified delimiter (e.g., comma, space) and can ignore empty cells (=TEXTJOIN(", ", TRUE, A1:A5)).
30. How do date calculations work in Excel?
Excel stores dates as sequential serial numbers starting from January 1, 1900 (Day 1). Adding 1 to a date adds one day.
31. What is EOMONTH?
Calculates the last day of a month n months in the past or future (=EOMONTH(TODAY(), 0) returns the end of the current month).
32. What is DATEDIF?
Calculates the difference between two dates in years ("Y"), months ("M"), or days ("D"): =DATEDIF(Start_Date, End_Date, "Y").
33. What is Data Validation and how does it prevent bad data entry?
It restricts user inputs in cells (e.g., allowing only numbers between 1 and 100, or limiting input to dates in the current year).
34. What is a Dynamic Array Formula?
A formula that calculates multiple results and automatically “spills” them into neighboring cells (introduced in Excel 365).
35. Explain UNIQUE, SORT, and FILTER functions.
=UNIQUE(range): Returns distinct list of items.=SORT(range): Sorts a range dynamically.=FILTER(range, criteria): Extracts rows matching specific criteria.
36. What is Power Query and when should you use it?
An ETL (Extract, Transform, Load) data preparation tool built into Excel. Use it to import, clean, merge, unpivot, and automate repeatable data pipeline tasks.
37. What is Power Pivot and Data Modeling?
An advanced data analysis tool used to create relationships between multiple tables, build star schemas, and analyze millions of rows beyond Excel’s 1,048,576 row limit.
38. What is DAX (Data Analysis Expressions)?
A formula language used in Power Pivot and Power BI to create custom measures and calculated columns across related tables.
39. What is the difference between implicit and explicit DAX measures?
Implicit: Drag-and-drop aggregation created automatically by Excel.
Explicit: Formally written DAX measure code (e.g.,
Total Sales := SUM(Sales[Amount])).
40. What is VBA (Visual Basic for Applications) and Macros?
Macro: A recorded set of actions to automate repetitive tasks.
VBA: The underlying programming language used to write custom macros and scripts.
41. How do you record a Macro?
Go to Developer Tab => Record Macro => perform actions => click Stop Recording.
42. What is Goal Seek and Scenario Manager?
Goal Seek: What-If Analysis tool that calculates the input required to reach a desired target output.
Scenario Manager: Allows you to define and switch between multiple input value sets (e.g., Best Case, Base Case, Worst Case).
43. What is Solver in Excel?
An advanced optimization tool that finds maximum, minimum, or target values for a formula subject to constraints.
44. What are Array Formulas / CSE Formulas?
Legacy array formulas requiring Ctrl + Shift + Enter to perform calculations across array ranges. Modern Excel handle these automatically via Dynamic Arrays.
45. What does the #SPILL! error mean?
Occurs when a Dynamic Array formula tries to output results, but a cell in the spill range is blocked by text, data, or merged cells.
46. What causes #N/A, #VALUE!, #REF!, #NAME?, and #DIV/0! errors?
#N/A: Lookup value not found.#VALUE!: Incorrect data type used in formula (e.g., text added to number).#REF!: Invalid cell reference (e.g., deleted column).#NAME?: Misspelled function name.#DIV/0!: Division by zero or empty cell.
47. How do you optimize Excel performance for large workbooks?
Change Calculation Mode to Manual.
Avoid volatile functions (
INDIRECT,OFFSET,NOW(),TODAY()).Use Power Query instead of complex nested array formulas.
Convert raw ranges into Excel Tables (
Ctrl + T).
48. What is INDIRECT and why is it considered a volatile function?
INDIRECT converts a text string into a valid cell reference (=INDIRECT("A1")). It is volatile because it recalculates every time any change occurs in the entire workbook, slowing down performance.
49. What is OFFSET?
Returns a reference to a range that is a specified number of rows and columns away from a starting cell (=OFFSET(reference, rows, cols, [height], [width])).
50. What is the difference between SUMPRODUCT and standard array formulas?
SUMPRODUCT multiplies corresponding components in two or more arrays and returns the sum of those products (=SUMPRODUCT(Qty_Range, Price_Range)). It handles array math natively without needing array enter shortcuts.
Leave a reply