Excel Interview Questions

A workbook is the entire Excel file (the .xlsx file), while a worksheet (or sheet) is a single tabbed page within that workbook. A workbook can contain many worksheets, and you can reference data across them.

A cell reference points to a cell so a formula can use its value.

• Relative (A1): adjusts when copied to another cell.
• Absolute ($A$1): stays fixed when copied.
• Mixed ($A1 or A$1): locks only the column or only the row.

Press F4 while editing a reference to cycle through these types.

• COUNT counts cells that contain numbers.
• COUNTA counts cells that are not empty (numbers, text, errors).
• COUNTBLANK counts cells that are empty.

Go to View → Freeze Panes. You can freeze the top row, the first column, or freeze panes at the current selection so headers stay visible while you scroll through large datasets.

• SUM adds all values in a range: =SUM(B2:B100).
• SUMIF adds values that meet one condition: =SUMIF(A2:A100,"East",B2:B100).
• SUMIFS adds values that meet multiple conditions: =SUMIFS(B2:B100, A2:A100,"East", C2:C100,"2026").

In SUMIFS the sum range comes first; in SUMIF it comes last — a common source of mistakes.

Both join text from several cells. The & operator is the quickest: =A2&" "&B2 joins first and last names with a space. In Excel 365 the modern function is TEXTJOIN: =TEXTJOIN(", ", TRUE, A2:A5) joins a list and skips blanks.

Conditional formatting changes a cell's appearance based on its value or a formula — for example highlighting values above a threshold, colour scales, data bars or icon sets. Select the range, go to Home → Conditional Formatting, and choose a rule. It is widely used in dashboards to flag exceptions visually.

• ROUND(number, digits) rounds to the nearest value: =ROUND(3.146, 2) → 3.15.
• ROUNDUP always rounds away from zero: =ROUNDUP(3.141, 2) → 3.15.
• ROUNDDOWN always rounds toward zero: =ROUNDDOWN(3.149, 2) → 3.14.

Select the cell, go to Data → Data Validation, choose List under "Allow", and either type values separated by commas or point to a range such as =$F$2:$F$10. This restricts entries to the allowed options and reduces data-entry errors.

Clear Contents (Delete key) removes the values but keeps the empty cells and surrounding layout in place. Delete (right-click → Delete) removes the cells entirely and shifts the remaining cells up or left, which can break references and layout.

A formula is any expression you type starting with = (e.g. =A1+A2). A function is a built-in, named operation used inside formulas (e.g. SUM, AVERAGE). So =SUM(A1:A2) is a formula that uses a function.

Enter the formula in the first cell, then double-click the fill handle (the small square at the bottom-right of the cell) to copy it down to the end of the adjacent data. In an Excel Table, formulas auto-fill the whole column automatically.

##### means the column is too narrow to display the value (or a negative date/time) — widen the column. A #SPILL! error means a dynamic array cannot spill because something blocks the output range — clear the cells in the spill area.

Save updates the current file in place. Save As creates a new copy — letting you change the name, location or file format (e.g. .xlsx to .csv or .pdf) while keeping the original.

Both look up a value and return a related value from a table.

• VLOOKUP searches vertically down the first column and returns a value from a column to the right.
• HLOOKUP searches horizontally across the first row and returns a value from a row below.

Example: =VLOOKUP("Apple", A2:C100, 3, FALSE) finds "Apple" in column A and returns the value from the 3rd column, using an exact match.

XLOOKUP is the modern replacement for VLOOKUP. Its advantages:

• It can look left or right (VLOOKUP only looks right).
• It defaults to an exact match.
• It has a built-in "if not found" argument.
• It returns a value without a hard-coded column index, so inserting columns does not break it.

Example: =XLOOKUP("Apple", A2:A100, C2:C100, "Not found").

A PivotTable summarises large datasets by grouping and aggregating data (sum, count, average, etc.) without formulas. You drag fields into Rows, Columns, Values and Filters to reshape the data instantly.

Use it when you need quick summaries like "total sales by region and month" from thousands of rows.

• IF tests one condition and returns one of two results.
• Nested IF places IFs inside each other to test multiple conditions, which can get hard to read.
• IFS tests several conditions in order and returns the first match, making multi-condition logic much cleaner.

Example: =IFS(A1>=90,"A", A1>=75,"B", A1>=50,"C", TRUE,"Fail").

To delete duplicates, select the data and use Data → Remove Duplicates. To return a list of unique values with a formula (Excel 365), use =UNIQUE(range). You can combine it with SORT: =SORT(UNIQUE(A2:A100)).

INDEX/MATCH is a flexible lookup alternative to VLOOKUP. MATCH finds the position of a value, and INDEX returns the value at that position.

Example: =INDEX(C2:C100, MATCH("Apple", A2:A100, 0)). It can look up in any direction and does not break when columns are inserted.

ISERROR returns TRUE/FALSE depending on whether a formula errors, so it is used inside an IF. IFERROR is a shortcut that returns a value you specify when a formula errors: =IFERROR(A2/B2, 0) returns 0 instead of #DIV/0!. IFERROR is cleaner and evaluates the formula only once.

When you copy a VLOOKUP down a column, the lookup table should stay fixed while the lookup value moves. Lock the table with absolute references: =VLOOKUP(A2, $E$2:$F$100, 2, FALSE). Here A2 is relative (moves per row) and $E$2:$F$100 is absolute (always points to the same table).

A named range gives a friendly name to a cell or range (e.g. TaxRate for $B$1). Create one via Formulas → Define Name or the Name Box. Benefits: formulas become readable (=Price*TaxRate), references are absolute by default, and they are easier to maintain across a workbook.

• TEXT(value, format) formats a number as text: =TEXT(TODAY(),"dd-mmm-yyyy").
• LEFT(text, n) returns the first n characters.
• RIGHT(text, n) returns the last n characters.
• MID(text, start, n) returns n characters from a start position.

These are commonly combined with FIND/SEARCH to split codes like "INV-2026-001".

A PivotTable summarises data in a tabular grid. A PivotChart is a graphical view of that same summary that stays linked to the PivotTable — changing the table or its filters updates the chart automatically. Use the chart for presentations and the table for detailed numbers.

To flag duplicates, use =COUNTIF($A$2:$A$100, A2)>1 in a helper column — it returns TRUE for any value that appears more than once. You can also use this inside conditional formatting to highlight duplicates, or combine with IF to label the first vs repeat occurrences.

When recording a macro, absolute mode records the exact cell addresses you click, so the macro always acts on those same cells. Use Relative References mode records movements relative to the active cell, so the macro works wherever you start it. Choose relative when the task should repeat across different ranges.

Options include: Data → Consolidate (sum/average across sheets with the same layout), 3D references like =SUM(Jan:Dec!B2), or Power Query to append/merge many sheets or files into one table that refreshes automatically. Power Query is best for recurring, large consolidations.

COUNTIF counts cells meeting one condition: =COUNTIF(A:A,"East"). COUNTIFS counts cells meeting multiple conditions across ranges: =COUNTIFS(A:A,"East", B:B,">1000").

Use a lookup to bring columns from the second table into the first keyed on a common ID — XLOOKUP/VLOOKUP for a few columns, or Power Query Merge for many columns or large data. Power Query is cleaner and refreshable.

IFERROR(value, alt) returns alt if the formula throws any error. IFNA(value, alt) catches only the #N/A error (common with lookups) and lets other errors surface — useful when you want to see genuine mistakes.

Use a mixed reference that expands: =SUM($B$2:B2) in the first row, then copy down. Each row sums from the fixed start to the current row. In a PivotTable, use "Show Values As → Running Total In".

An array formula performs calculations on multiple values at once and can return multiple results. In Excel 365, dynamic arrays automatically "spill" results into neighbouring cells.

Functions like FILTER, SORT, UNIQUE and SEQUENCE are dynamic array functions. Example: =FILTER(A2:C100, B2:B100>1000) returns all rows where the value in column B exceeds 1000.

Power Query (Get & Transform) is Excel's ETL tool for importing, cleaning and reshaping data from many sources — files, folders, databases and the web. The steps you apply are recorded and repeatable, so refreshing re-runs the whole transformation automatically.

It is ideal for recurring reports where raw data arrives in a messy format each period.

In the context of the Data Model / Power Pivot:

• A calculated column is computed row-by-row and stored in the table, consuming memory.
• A calculated field / measure is computed at query time based on the current filter context and is not stored, making it more efficient for aggregations.

Measures are written in DAX, for example Total Sales = SUM(Sales[Amount]).

Common techniques:

• Replace volatile functions (OFFSET, INDIRECT, NOW, TODAY) where possible.
• Avoid full-column references like A:A; limit ranges.
• Use helper columns instead of huge nested array formulas.
• Convert ranges to Tables and use the Data Model for large data.
• Set calculation to Manual while building, and remove unused formatting and defined names.

A macro is a recorded or coded set of actions that automates repetitive tasks. VBA (Visual Basic for Applications) is the programming language behind macros.

Use VBA when you need custom automation beyond formulas — for example, looping through sheets, generating formatted reports, building custom functions (UDFs), or interacting with other Office apps. Simple example:

Sub SayHello()\n    MsgBox "Hello, Excel!"\nEnd Sub

• FILTER(array, include) returns rows that meet a condition: =FILTER(A2:C100, C2:C100>1000).
• SORT(array, [col], [order]) sorts a range dynamically.
• UNIQUE(array) returns distinct values.

They "spill" results automatically and recalculate as the source changes. You can nest them: =SORT(UNIQUE(FILTER(...))).

A two-way lookup finds a value at the intersection of a row and a column. With INDEX/MATCH: =INDEX(data, MATCH(rowKey, rowHeaders, 0), MATCH(colKey, colHeaders, 0)). With XLOOKUP you nest two lookups: =XLOOKUP(rowKey, rowHeaders, XLOOKUP(colKey, colHeaders, data)). INDEX/MATCH is still the most universal and works in all Excel versions.

The Data Model (Power Pivot) lets you load multiple tables and define relationships between them using key columns, instead of flattening everything with VLOOKUP. A single PivotTable can then analyse several related tables at once. This supports star-schema designs and large datasets that exceed a worksheet's row limit.

Frequently used DAX functions include CALCULATE (change filter context), SUMX/AVERAGEX (row-by-row iterators), RELATED (pull a value across a relationship), FILTER, and time-intelligence functions like TOTALYTD and SAMEPERIODLASTYEAR. Example: Sales YTD = TOTALYTD(SUM(Sales[Amount]), Calendar[Date]).

Base the chart on a Table (Ctrl+T) so new rows are included automatically, or use dynamic named ranges with OFFSET/INDEX. In Excel 365 you can chart a spilled dynamic array (e.g. a FILTER result) by referencing the spill range with #, such as =Sheet1!$H$2#, so the chart grows and shrinks with the data.

A built-in function ships with Excel (SUM, VLOOKUP, etc.). A UDF (User Defined Function) is a custom function you write in VBA to extend Excel. Example:

Function GSTAmount(amount As Double, rate As Double) As Double\n    GSTAmount = amount * rate / 100\nEnd Function

You then call it on a sheet like a normal formula: =GSTAmount(B2, 18). UDFs can be slower than native functions, so use them only where no built-in exists.

By default all cells are "locked", but locking only takes effect after you protect the sheet. To allow input in some cells: unlock those cells (Format Cells → Protection, uncheck Locked), then apply Review → Protect Sheet. Use Protect Workbook to prevent adding/deleting sheets, and set a file-open password via File → Info → Protect Workbook → Encrypt with Password.

Goal Seek (Data → What-If Analysis) finds the input value needed to reach a target result — e.g. what price gives a profit of ₹1,00,000. A Data Table shows how one or two inputs change a formula's output across many scenarios in a grid, which is ideal for sensitivity analysis in financial models.

LET lets you name intermediate calculations inside a formula, improving readability and speed (a value is computed once and reused). Example: =LET(rev, A2*B2, tax, rev*0.18, rev+tax) defines rev and tax then returns the total.

LAMBDA lets you build your own reusable function in pure Excel (no VBA). You define parameters and a formula, name it via Name Manager, then call it like a built-in function. It enables custom, shareable logic and even recursion.

SUMPRODUCT multiplies corresponding elements of arrays and sums the results. It also performs conditional maths without array-entry: =SUMPRODUCT((Region="East")*(Sales)) sums sales for the East region. It is powerful for multi-condition weighted calculations.

An Excel Table (Ctrl+T) auto-expands with new rows, gives structured references (Table1[Sales]), auto-fills formulas, and keeps headers visible. A normal range does none of this. Tables make formulas and charts dynamic and far easier to maintain.