Excel interview questions, answered the way you’d say them.

XLOOKUP, where your Excel has it: it matches exactly by default, can return a column to the left of the one it searches, and takes an if_not_found value. VLOOKUP searches only the first column of its range, picks the result by column number, which points at the wrong column once someone inserts one, and defaults to approximate match unless you pass FALSE. INDEX/MATCH works in every version and can also look left; XLOOKUP isn’t available in Excel 2016 or 2019.

=XLOOKUP(A2, Products[SKU], Products[Price], "Not found")
=INDEX(Products[Price], MATCH(A2, Products[SKU], 0))
=VLOOKUP(A2, Products, 3, FALSE)

When VLOOKUP returns a wrong but plausible price, check the fourth argument first: without FALSE it does an approximate match.

Microsoft Support: XLOOKUP functionMicrosoft Support: VLOOKUP functionMicrosoft Support: Look up values with VLOOKUP, INDEX, or MATCH

Practise it with Prism

A relative reference such as A2 shifts when you copy the formula, an absolute one such as $A$2 stays fixed, and a mixed one locks only the column ($A2) or only the row (A$2). Lock whatever must not move: to apply one commission rate in F1 to every order, write =B2*$F$1 and fill down. In the formula bar, F4 cycles through the forms.

A favourite follow-up is a price-by-quantity grid from one formula, =$A3*B$2, filled right and down.

Microsoft Support: Switch between relative, absolute, and mixed references

Practise it with Prism

SUMIFS adds the values where every condition holds, and COUNTIFS counts the rows where every condition holds. For a month, give a date range with comparison operators joined to DATE: on or after 1 October and before 1 November, which also catches times recorded during 31 October. Mind the argument order: SUMIFS takes the range to add first, while the older SUMIF takes it last.

=SUMIFS(Orders[Amount], Orders[City], "Pune", Orders[Mode], "UPI",
    Orders[Date], ">="&DATE(2026,10,1), Orders[Date], "<"&DATE(2026,11,1))
=COUNTIFS(Orders[City], "Pune", Orders[Mode], "UPI",
    Orders[Date], ">="&DATE(2026,10,1), Orders[Date], "<"&DATE(2026,11,1))

Put the city and the dates in cells and refer to them, as in ">="&H1, so one formula serves every month.

Microsoft Support: SUMIFS functionMicrosoft Support: COUNTIFS function

Practise it with Prism

Pick the method by how permanent it should be. Data, Remove Duplicates deletes repeated rows for good, comparing only the columns you tick and keeping the first occurrence, so copy the data first. UNIQUE returns a de-duplicated list as a formula and leaves the source untouched, and a COUNTIFS helper column flags repeats so you can review them before deleting anything.

=UNIQUE(Customers[Email])
=COUNTIFS(Customers[Email], [@Email])>1

Agree what counts as a duplicate before you start: the same email, or the same email and the same order date?

Microsoft Support: Filter for unique values or remove duplicate valuesMicrosoft Support: UNIQUE functionMicrosoft Support: COUNTIFS function

Practise it with Prism

IFERROR replaces every kind of error, including #REF!, #VALUE! and #NAME?, so wrapped round a lookup it also hides a deleted column or a misspelt function, and the sheet quietly shows your fallback. IFNA catches only #N/A, the “not found” case, so genuine mistakes still surface. XLOOKUP’s if_not_found argument does the same job without a wrapper.

=IFNA(VLOOKUP(A2, Products, 3, FALSE), "Not found")
=XLOOKUP(A2, Products[SKU], Products[Price], "Not found")

Use IFERROR only where you know which error to expect and why, such as a division by an empty cell.

Microsoft Support: IFERROR functionMicrosoft Support: IFNA functionMicrosoft Support: XLOOKUP function

Practise it with Prism

Use Data Validation: allow only a whole number from 1 to 10 for quantity, a date range, a text length, or a List so payment mode comes from a drop-down of UPI, Card and COD. Point the list at a column of an Excel table and the drop-down grows as items are added. Validation checks only what people type, not pasted or filled values or data already there, so run Circle Invalid Data to find those.

Add an input message so people see the rule before they type, not just an error afterwards.

Microsoft Support: Apply data validation to cellsMicrosoft Support: Create a drop-down listMicrosoft Support: More on data validation

Practise it with Prism

Match the chart to the question. A line shows a trend over equal intervals such as months; bar or column charts compare categories such as cities, sorted by value; a scatter shows how two numbers relate; a histogram shows how values such as order amounts are distributed. Keep a pie for parts of a whole with a few categories and no negatives, and use a combo chart with a secondary axis when two measures differ in scale.

Avoid 3-D charts: they make values harder to read and compare.

Microsoft Support: Available chart types in OfficeMicrosoft Learn: Tips for designing a great Power BI dashboard

Practise it with Prism

A PivotTable summarises thousands of rows by dragging fields, such as cities as rows, months as columns and the sum of revenue as values, with no formulas to break. It doesn’t update by itself: after the source changes, right-click it and Refresh, or use Refresh All. If new rows still don’t appear, it was built on a fixed range; build it on an Excel table instead, and rows added to the table are included when you refresh.

Expect a follow-up on preparing the source: one header row, no blank rows or columns, and one record per row.

Microsoft Support: Create a PivotTable to analyze worksheet data

Practise it with Prism

Select the data rows, then Conditional Formatting, New Rule, “Use a formula to determine which cells to format”, and write the formula for the first selected row. The formula must return TRUE or FALSE; lock the columns with $ and leave the row relative, so every cell in a row tests that row’s order date in column C and delivery date in column F.

=AND($F2="", TODAY()-$C2>7)

If the colour lands in the wrong cells, check the dollar signs: the rule needs $C2, not C2.

Microsoft Support: Use conditional formatting to highlight information in ExcelMicrosoft Support: Switch between relative, absolute, and mixed references

Practise it with Prism

In Microsoft 365 or Excel 2024, TEXTBEFORE and TEXTAFTER split on a delimiter, and a negative instance number counts from the end; for PUN-2026-00125 the first three formulas return PUN, 2026 and 00125. Clean the text first: TRIM removes extra spaces but not the non-breaking space (character 160) that text copied from web pages often carries, so substitute it out. In older versions, use LEFT, MID and FIND.

=TEXTBEFORE(A2, "-")
=TEXTBEFORE(TEXTAFTER(A2, "-"), "-")
=TEXTAFTER(A2, "-", -1)
=TRIM(SUBSTITUTE(A2, CHAR(160), " "))

Keep IDs as text: turning 00125 into a number drops the leading zeros.

Microsoft Support: TEXTBEFORE functionMicrosoft Support: TEXTAFTER functionMicrosoft Support: TRIM function

Practise it with Prism

Add 1 to the calendar year when the month is April or later: YEAR(A2)+(MONTH(A2)>=4) is the year in which the financial year ends, because TRUE counts as 1. Excel stores dates as serial numbers, so group by real dates, such as the month’s first day from EOMONTH(A2,-1)+1, and make text labels only for display, because text can’t be used in later date calculations.

=LET(fy, YEAR(A2)+(MONTH(A2)>=4), "FY "&(fy-1)&"-"&RIGHT(fy, 2))
=EOMONTH(A2, -1)+1

Test the boundaries: 31 March 2026 should read FY 2025-26, and 1 April 2026 should read FY 2026-27.

Microsoft Support: LET functionMicrosoft Support: EOMONTH functionMicrosoft Support: TEXT function

Practise it with Prism

Nest the dynamic array functions, which spill their results into the cells below: FILTER keeps the rows whose condition is TRUE (multiply conditions for AND, add them for OR), UNIQUE removes repeats and SORT orders the result. The list updates whenever the data does. Anything sitting in the spill range causes #SPILL!, and FILTER with no matches returns #CALC! unless you give its third argument.

=SORT(UNIQUE(FILTER(Orders[City], Orders[Mode]="UPI", "None")))
=FILTER(Orders, (Orders[Mode]="UPI")*(Orders[Amount]>=5000), "None")

Point at whole table columns, as here, so the list picks up new rows without editing the formula.

Microsoft Support: FILTER functionMicrosoft Support: UNIQUE functionMicrosoft Support: SORT functionMicrosoft Support: Dynamic array formulas and spilled array behavior

Practise it with Prism

Use an approximate-match lookup on a slab table that lists each slab’s lower limit. XLOOKUP with match mode -1 returns an exact match or the next smaller value, so a ₹7,450 order falls in the ₹5,000 slab; VLOOKUP’s TRUE mode does the same only when the first column is sorted ascending. This beats nested IFs: changing a rate or adding a slab means editing the table, not every formula.

Slabs table   From     Rate
              0        0%
              1000     5%
              5000     10%
              10000    15%
=XLOOKUP(B2, Slabs[From], Slabs[Rate], 0, -1)
=VLOOKUP(B2, Slabs, 2, TRUE)

Test the edges: with this table, ₹5,000 should get 10% and ₹4,999 should get 5%.

Microsoft Support: XLOOKUP functionMicrosoft Support: VLOOKUP function

Practise it with Prism

All three sit under Data, What-If Analysis. Goal Seek works backwards from a target to one input, such as the conversion rate that takes monthly revenue to ₹10 lakh. A Data Table shows the result for many values of one or two inputs at once, and Scenario Manager saves named sets of inputs such as best, base and worst case, each holding up to 32 values. For more inputs with constraints, use the Solver add-in.

Build the model as formulas first: these tools only work when the result cell is calculated from the input cells.

Microsoft Support: Introduction to What-If AnalysisMicrosoft Support: Use Goal Seek to find the result you want by adjusting an input value

Practise it with Prism

When the same cleaning has to be repeated. Power Query, called Get & Transform in Excel, connects to local files and folders or to cloud sources and services, records each cleaning step in the Power Query Editor and replays them all on Refresh. For twelve monthly CSV files with the same columns, combine the folder into one table once, and later months come in with a refresh. Use formulas for live calculation in the sheet, and Power Query for shaping data before it arrives.

Keep only the files you want in that folder: every file in it, and in any subfolders you select, gets combined.

Microsoft Support: About Power Query in ExcelMicrosoft Support: Import data from a folder with multiple files (Power Query)

Practise it with Prism