Top 50 Excel Interview Questions

Related Excel Core Functions , Intermediate and Advanced

Excel Related Question & Answer

Part - 1 Basic Excel

Basic / Fundamental Questions

Want to feel confident in your Excel interview? We’ve broken down the first 15 questions for you.

Part -2 Intermediate

Intermediate Questions

Ready to take your Excel skills to the next level? Questions 16 to 35 cover the tools employers love most!

Part - 3 Advance

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

Intermediate Questions​

Advance Questions​

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.

  • Relative Reference (A1): Adjusts automatically when copied to another cell.

  • Absolute Reference ($A$1): Stays fixed on cell A1 regardless of where the formula is copied.

  • Mixed Reference ($A1 or A$1): Locks either the column or the row exclusively.

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).

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).

Select the dataset => go to the Data tab => click Remove Duplicates => select the columns to check => click OK.

  • Entire Row: Shift + Spacebar

  • Entire Column: Ctrl + Spacebar

  • Current Date (Static): Ctrl + ; (Semicolon)

  • Current Time (Static): Ctrl + Shift + ;

  • Dynamic Date/Time: Use formulas =TODAY() or =NOW().

  • =COUNT(): Counts cells containing only numbers.

  • =COUNTA(): Counts all non-empty cells (text, numbers, errors).

  • =COUNTBLANK(): Counts empty cells only.

Freeze Panes keeps row headers or column headers visible while scrolling through large datasets (View tab => Freeze Panes).

Select the column => Data Tab => Text to Columns => choose Delimited (by space or comma) => click Finish.

Flash Fill automatically detects patterns in adjacent columns and fills data accordingly.

  • Shortcut: Ctrl + E.

Copy cells (Ctrl + C) => Right-click target => Paste Special (Ctrl + Alt + V) => select Values.

  • Formula: A user-written expression (e.g., =A1 + B1).

  • Function: A built-in predefined calculation in Excel (e.g., =SUM(A1:B1)).

Select target cell => Data Tab => Data Validation => Allow: List => Enter comma-separated items or select a cell range.

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.

  • 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_found argument).

  • VLOOKUP: Searches vertically down columns.

  • HLOOKUP: Searches horizontally across rows.

  • SUMIF(range, criteria, [sum_range]): Sums based on one single condition.

  • SUMIFS(sum_range, criteria_range1, criteria1, ...): Sums based on multiple conditions.

  • 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.

An interactive data summarization tool used to organize, aggregate, group, and analyze large datasets without writing complex formulas.

An interactive visual filter button for Pivot Tables, Pivot Charts, or Data Tables.

A custom field created inside a Pivot Table that performs calculations using existing data fields (e.g., Revenue - Cost to create a Profit field).

Placing an IF function inside another IF function to test multiple conditions: =IF(A1>90, "A", IF(A1>75, "B", "C")).

Simplifies nested IFs by evaluating multiple conditions in order: =IFS(A1>90, "A", A1>75, "B", TRUE, "C").

Suppresses error messages (#N/A, #VALUE!, #DIV/0!) and replaces them with custom text or values: =IFERROR(VLOOKUP(...), "Not Found").

  • COUNTIF: Counts cells matching one condition.

  • COUNTIFS: Counts cells matching multiple criteria simultaneously.

  • =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.

  • 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)).

Excel stores dates as sequential serial numbers starting from January 1, 1900 (Day 1). Adding 1 to a date adds one day.

Calculates the last day of a month n months in the past or future (=EOMONTH(TODAY(), 0) returns the end of the current month).

Calculates the difference between two dates in years ("Y"), months ("M"), or days ("D"): =DATEDIF(Start_Date, End_Date, "Y").

It restricts user inputs in cells (e.g., allowing only numbers between 1 and 100, or limiting input to dates in the current year).

A formula that calculates multiple results and automatically “spills” them into neighboring cells (introduced in Excel 365).

  • =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.

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.

A formula language used in Power Pivot and Power BI to create custom measures and calculated columns across related tables.

  • Implicit: Drag-and-drop aggregation created automatically by Excel.

  • Explicit: Formally written DAX measure code (e.g., Total Sales := SUM(Sales[Amount])).

  • Macro: A recorded set of actions to automate repetitive tasks.

  • VBA: The underlying programming language used to write custom macros and scripts.

Go to Developer Tab => Record Macro => perform actions => click Stop Recording.

 

  • 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).

An advanced optimization tool that finds maximum, minimum, or target values for a formula subject to constraints.

Legacy array formulas requiring Ctrl + Shift + Enter to perform calculations across array ranges. Modern Excel handle these automatically via Dynamic Arrays.

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.

  • #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.

  • 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).

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.

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])).

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.

--

Q...
Q...

Related Posts

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top