Excel Interview Questions and Answers

Answer

VLOOKUP takes a hard coded column number counted from the left edge of the table array, so the formula has no idea which column it is really pointing at. The moment somebody inserts a column inside that range, position 4 now holds what used to be position 3, and the formula keeps returning values with no error at all. That silence is the whole problem, a wrong number that looks correct travels much further than a #REF! ever does.

INDEX with MATCH separates the two jobs: MATCH finds the row by looking up the value, INDEX pulls from a column you point at directly, so inserting a column shifts the reference along with the data. XLOOKUP does the same thing with one function, takes a lookup array and a return array rather than an offset, defaults to exact match, and lets you supply the not found text inline instead of wrapping the whole thing in IFERROR. Two more VLOOKUP limits worth naming: it cannot look to the left of the key, and on very large sheets it forces Excel to scan a wide table array rather than two thin columns.

The interviewer is probing whether you understand references or only memorised syntax. A weak answer says 'VLOOKUP searches vertically and HLOOKUP searches horizontally' and stops there, which tells the panel you have never maintained somebody else's workbook.

Fragile, breaks silently when a column is inserted into A:D
=VLOOKUP($A2,Master!$A:$D,4,FALSE)

Survives inserts, works leftwards too
=INDEX(Master!$D:$D,MATCH($A2,Master!$A:$A,0))

Modern form, exact match by default, own error message
=XLOOKUP($A2,Master!$A:$A,Master!$D:$D,"Not in master")

Return several columns in one go (spills across)
=XLOOKUP($A2,Master!$A:$A,Master!$C:$E,"Not in master")

Answer

The fourth argument, range_lookup, chooses between approximate match (TRUE or 1) and exact match (FALSE or 0). If you omit it, Excel assumes TRUE, which is the single most expensive default in the product. Approximate match assumes the first column is sorted ascending and, when it cannot find the value, it returns the largest value that is less than what you asked for.

On unsorted data that produces confidently wrong numbers: employee code 10452 that does not exist in the master returns whatever row 10449 happened to hold, and your salary sheet is now wrong for one person out of nine hundred. Always type FALSE, or better, use 0 which is faster to type and identical in meaning. The legitimate uses of TRUE are grade banding and slab calculations: income tax slabs, commission tiers, delivery charge bands, shipping weight brackets.

There you build a sorted lower bound table and let approximate match find the band, which is far cleaner than nested IFs. Interviewers ask this specifically to see whether you can name a correct use of TRUE rather than just calling it dangerous. A weak answer is 'FALSE means exact match' with no mention of the default or the sorting requirement, which is a recited definition rather than experience.

Unsorted data, missing key: this returns a WRONG value, not an error
=VLOOKUP($A2,Master!$A:$D,4)

Always state it
=VLOOKUP($A2,Master!$A:$D,4,0)

Legitimate approximate match, slab lookup on a sorted lower bound table
Slabs sheet:  0 -> 0%,  300000 -> 5%,  700000 -> 10%,  1000000 -> 15%
=VLOOKUP($B2,Slabs!$A$2:$B$5,2,TRUE)

Same slab logic with XLOOKUP, match_mode -1 means next smaller item
=XLOOKUP($B2,Slabs!$A$2:$A$5,Slabs!$B$2:$B$5,0,-1)

Answer

XLOOKUP takes lookup_value, lookup_array, return_array, then three optional arguments. if_not_found replaces the IFERROR wrapper, so you can return a blank, a zero or a message without hiding genuine errors elsewhere in the formula. match_mode is 0 for exact (the default), -1 for exact or next smaller, 1 for exact or next larger, and 2 for wildcard matching with asterisk and question mark. search_mode is 1 to search first to last, -1 to search last to first, and 2 or -2 for binary search on sorted data. The reverse search is the argument people forget and it is genuinely useful: search_mode of -1 gives you the most recent transaction for a customer without sorting or a helper column, because it finds the last matching row rather than the first. Two further points that separate a good answer.

XLOOKUP can return an entire row or column, so one formula spills several fields instead of four separate lookups, and the return array can be a different shape from the lookup array as long as the dimension being matched lines up. And because it is a dynamic array function, the result spills, which means it will throw #SPILL! if something sits in the way. The interviewer wants to hear that you use if_not_found deliberately. A weak answer treats XLOOKUP as 'VLOOKUP without the column number' and never mentions the last three arguments.

Full signature
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Exact match with a clean fallback
=XLOOKUP($A2,Cust!$A:$A,Cust!$C:$C,"New customer",0,1)

Most recent order for a customer, reverse search, no sorting needed
=XLOOKUP($A2,Orders!$B:$B,Orders!$F:$F,"No order yet",0,-1)

Wildcard match_mode 2, finds the first vendor starting with the typed text
=XLOOKUP($A2&"*",Vendors!$A:$A,Vendors!$D:$D,"",2,1)

Return three columns at once, the result spills into E2:G2
=XLOOKUP($A2,Cust!$A:$A,Cust!$C:$E,"",0,1)

Answer

A reference has two halves and each can be locked independently. A2 is fully relative, both column and row move when you copy. $A$2 is fully absolute, neither moves. $A2 locks the column and lets the row move, which is what you want when you drag rightwards across a report but each row should still read its own key. A$2 locks the row and lets the column move, which is what you want when every row multiplies by a rate sitting in one header row.

Mixed references are the whole exam here, because anybody can press F4 once. The canonical test is building a multiplication table or a rate matrix from a single formula typed once in the top left cell and dragged across the whole block. The rule that survives every version of this question: look at the direction the formula will travel, and lock whatever must not travel with it.

Lookup ranges and rate tables get fully absolute or column locked, the key cell usually gets the column locked only, and the growing part stays relative. F4 cycles through the four states while the cursor is on the reference, and on many Indian laptops you need Fn plus F4. Interviewers use this to check whether you build one formula and fill, or twelve formulas by hand. A weak answer says 'dollar means fixed' and cannot produce a mixed reference on demand.

Rate matrix: months across C1:H1, quantities down B2:B50, one formula filled everywhere
=$B2*C$1

Lookup where the table must never move but the key follows the row
=VLOOKUP($A2,Master!$A$2:$D$5000,4,0)

Running total that grows as it fills down (anchor the start only)
=SUM($C$2:C2)

Percent of grand total, denominator pinned to one cell
=C2/$C$999

F4 cycle on a selected reference:  A2  ->  $A$2  ->  A$2  ->  $A2  ->  A2

Answer

There are four usual causes and you should be able to test each in under a minute. First, trailing or leading spaces from a system export, tested with =LEN(A2) against =LEN(D2) or by wrapping both sides in TRIM. Second, a number stored as text on one side, which shows as left aligned with a small green triangle in the corner; =ISNUMBER(A2) versus =ISNUMBER(D2) settles it, and the fix is either multiplying by 1, Text to Columns with Finish, or coercing inside the formula.

Third, invisible characters that TRIM does not touch, most often the non breaking space CHAR(160) that arrives with anything copied from a web page or a PDF, removed with SUBSTITUTE on CHAR(160) before TRIM. Fourth, a genuine mismatch in the master, which is the answer nobody wants but is often correct, so confirm with COUNTIF before you start cleaning. The diagnostic formula worth memorising is a single cell that compares the exact strings and reports the lengths, because it answers all four at once.

The interviewer is checking your debugging instinct, not your function list. A weak answer is 'I wrap it in IFERROR', which hides a data quality problem that will surface later in a reconciliation, and panels at audit firms treat that answer as a red flag.

Is it really there at all
=COUNTIF(Master!$A:$A,$A2)

Length check exposes spaces and hidden characters
=LEN($A2)&" vs "&LEN($D2)

Exact comparison, EXACT is case sensitive too
=EXACT(TRIM($A2),TRIM($D2))

Text versus number mismatch
=ISNUMBER($A2)&" / "&ISNUMBER($D2)

Clean both sides inside the lookup, handles CHAR(160) from web copies
=XLOOKUP(TRIM(SUBSTITUTE($A2,CHAR(160)," ")),Master!$A:$A,Master!$D:$D,"Not found")

Force a text key to number when the master stores real numbers
=XLOOKUP($A2*1,Master!$A:$A,Master!$D:$D,"Not found")

Answer

The classic method uses named ranges plus INDIRECT. Create one named range per state, where the name exactly matches the state text, so a list called Maharashtra holds the Maharashtra cities. The first dropdown is a normal Data Validation list pointing at the list of states.

The second dropdown is a list whose source is =INDIRECT($A2), so it resolves the text in A2 into the matching named range. The gotchas are the whole point of the question. Names cannot contain spaces, so Tamil Nadu has to become Tamil_Nadu and you need SUBSTITUTE inside the INDIRECT.

INDIRECT is volatile, so a thousand dependent cells slow the file down. And if the user changes the state after choosing a city, the stale city stays in the cell, because Data Validation only checks on entry, so you add a conditional formatting rule or a clearing macro. The modern alternative avoids INDIRECT entirely: a helper column driven by FILTER produces the valid city list as a spill range, and the validation source points at the spill using the hash operator.

The interviewer is checking whether you have actually built one of these for a form. A weak answer describes Data Validation generally and never mentions the stale selection problem, which is the bug the business always reports.

Sheet Lists: A1 header States, then B, C, D each headed with a state name
Name each city column with Formulas, Create from Selection, Top row

Dropdown 1, cell A2, Data Validation, List
=Lists!$A$2:$A$40

Dropdown 2, cell B2, Data Validation, List (handles spaces in the state name)
=INDIRECT(SUBSTITUTE($A2," ","_"))

Modern route, no INDIRECT. Put this in a helper cell, say Z2
=FILTER(Lists!$C$2:$C$500,Lists!$B$2:$B$500=$A2,"")

Then point Data Validation at the spill range
=$Z$2#

Flag a city that no longer belongs to the chosen state
=COUNTIFS(Lists!$B:$B,$A2,Lists!$C:$C,$B2)=0

Answer

SUMIF takes range, criteria, sum_range in that order, which is the opposite order to SUMIFS, and that inconsistency is exactly what the interviewer is testing. SUMIFS takes the sum range first and then pairs of criteria range and criteria, and it accepts up to 127 pairs. Since SUMIFS handles one condition perfectly well, the practical advice is to stop using SUMIF entirely so you never have to remember two argument orders.

Criteria are text strings, which trips people up: a comparison is written as a quoted operator joined to the cell with an ampersand, so greater than or equal joined to a date cell, not a bare cell reference. For dates, use two criteria on the same column, one for the start and one for the end, and reference cells rather than typing dates, because a typed date inside a criteria string is interpreted using the machine locale and an Indian DD/MM entry can be read as MM/DD. Wildcards work in the criteria: asterisk for any run of characters, question mark for a single character, and a tilde to escape a literal asterisk or question mark.

COUNTIFS and AVERAGEIFS follow the SUMIFS pattern exactly. A weak answer recites the syntax without mentioning the argument order flip or the ampersand, and then fumbles when asked to add a date filter live.

Old form, note the odd argument order
=SUMIF($B:$B,"Mumbai",$D:$D)

Preferred, sum range first
=SUMIFS($D:$D,$B:$B,"Mumbai")

Two conditions plus a date window from cells G1 and G2
=SUMIFS($D:$D,$B:$B,$F2,$A:$A,">="&$G$1,$A:$A,"<="&$G$2)

Wildcard, every product code starting with SKU and ending in X
=SUMIFS($D:$D,$C:$C,"SKU*X")

Count of open tickets that are not closed
=COUNTIFS($E:$E,"<>Closed",$A:$A,">="&$G$1)

Average order value above 500 only
=AVERAGEIFS($D:$D,$D:$D,">500",$B:$B,$F2)

Blank versus not blank
=COUNTIFS($C:$C,"")     counts truly empty
=COUNTIFS($C:$C,"<>")   counts anything present

Answer

SUMPRODUCT multiplies arrays element by element and sums the result, and it evaluates arrays natively without needing the old Ctrl plus Shift plus Enter array entry. That second property is why it became the tool for every condition SUMIFS could not express. Its everyday job is weighted calculation: quantity times rate summed in one cell, or a weighted average when you divide by the sum of the weights.

Its interview job is conditional logic, because a comparison produces TRUE and FALSE, multiplying two comparison arrays gives 1 only where both are true, and multiplying that by the value array gives a conditional sum. You will often see the double unary trick, two minus signs written before a comparison, to coerce TRUE into 1. Multiplying the conditions together does the same coercion and reads more clearly, so prefer it.

Where SUMPRODUCT still beats SUMIFS in 2026: OR logic across different columns, criteria computed on the fly such as YEAR of a date column or LEFT of a code column, counting distinct values, and comparing two ranges of unequal shape. Where it loses: it does not use the same optimisations SUMIFS does, so full column references inside it are genuinely slow. A weak answer calls it 'an array formula' with no example, and cannot explain why a comparison behaves as a number.

Weighted revenue, quantity times rate, no helper column
=SUMPRODUCT($C$2:$C$5000,$D$2:$D$5000)

Weighted average price
=SUMPRODUCT($C$2:$C$5000,$D$2:$D$5000)/SUM($C$2:$C$5000)

Conditional sum, both conditions true gives 1
=SUMPRODUCT(($B$2:$B$5000="Mumbai")*($E$2:$E$5000="Delivered")*$D$2:$D$5000)

Criteria computed on the fly, SUMIFS cannot do this directly
=SUMPRODUCT((YEAR($A$2:$A$5000)=2026)*(LEFT($C$2:$C$5000,3)="SKU")*$D$2:$D$5000)

OR across two different columns
=SUMPRODUCT((($B$2:$B$5000="Mumbai")+($B$2:$B$5000="Pune")>0)*$D$2:$D$5000)

Count distinct invoice numbers in a range
=SUMPRODUCT(1/COUNTIF($F$2:$F$500,$F$2:$F$500))

Answer

IFERROR catches every error type: #N/A, #VALUE!, #REF!, #DIV/0!, #NAME?, #NUM! and #NULL!. IFNA catches only #N/A. In a lookup, #N/A means the key was not found, which is often expected and safe to replace with a blank or a zero.

Every other error means something structurally wrong: #REF! means a referenced range was deleted, #VALUE! usually means text where a number belongs, #NAME? means a misspelled function or a missing named range. Wrapping the lookup in IFERROR hides all of those behind the same clean blank cell, so a broken workbook looks fine and the total is quietly short by a few lakh. The correct habit is IFNA on lookups, IFERROR only where you genuinely expect a division by zero or a parse failure, and if_not_found inside XLOOKUP where available because it is scoped to the lookup itself and leaves the rest of the formula honest.

Two practical additions for the interview: IFERROR evaluates the expression twice in older engines, so a heavy formula wrapped in it costs double, and there is no need to write IFERROR around a division when a simple check on the denominator is clearer. A weak answer is 'both do the same thing, IFNA is newer', which tells the panel you have never had to debug a broken reference in a live report.

Hides everything, including a genuinely broken reference
=IFERROR(VLOOKUP($A2,Master!$A:$D,4,0),0)

Hides only the not found case, lets real breakage surface
=IFNA(VLOOKUP($A2,Master!$A:$D,4,0),0)

Best, the fallback is scoped to this lookup only
=XLOOKUP($A2,Master!$A:$A,Master!$D:$D,0)

Legitimate IFERROR, the denominator can genuinely be zero
=IFERROR($C2/$B2,0)

Clearer than wrapping, and cheaper to evaluate
=IF($B2=0,0,$C2/$B2)

Audit the sheet for what is actually failing before you suppress anything
=COUNTIF($D$2:$D$5000,"#N/A")   or use Home, Find and Select, Go To Special, Formulas, Errors

Answer

Since dynamic arrays shipped, a formula in one cell can return a whole block of results that spills into the neighbouring cells. You type it once in the top left cell, the range is outlined in blue, and the results resize automatically when the source data changes. FILTER returns rows matching a condition and takes an if_empty argument.

UNIQUE returns distinct values and takes a by_column flag and an exactly_once flag. SORT sorts by a column index and direction, SORTBY sorts one array using values from another array that need not be adjacent to it, which is the one people forget. SEQUENCE generates a run of numbers or dates and is what you use to build a month header row or a serial column that stays in step.

The spill range is referenced with the hash operator, so E2# means the whole spilled block, and chart series, Data Validation sources and downstream formulas can point at it and resize with it. #SPILL! means Excel cannot write the results: something occupies a cell in the spill zone, including a stray space, or the formula sits inside an Excel Table, which cannot host a spill, or the result would exceed the sheet. The fix is Home, Find and Select, Go To Special, or clicking the warning menu which offers Select Obstructing Cells. A weak answer describes FILTER and stops, missing that the real gain is a report that resizes itself with no maintenance.

Distinct customer list, sorted, in one formula
=SORT(UNIQUE($B$2:$B$5000))

All delivered orders above 5000 for the selected city
=FILTER($A$2:$F$5000,($B$2:$B$5000=$H$1)*($D$2:$D$5000>5000),"No rows")

Top 10 by value, SORTBY sorts one range using another
=TAKE(SORTBY($B$2:$B$5000,$D$2:$D$5000,-1),10)

Twelve month headers starting from a date in A1
=EDATE($A$1,SEQUENCE(1,12,0,1))

Values that appear exactly once (exactly_once flag)
=UNIQUE($B$2:$B$5000,FALSE,TRUE)

Reference the whole spill downstream, it resizes automatically
=COUNTA($E$2#)
=SUMIFS($D:$D,$B:$B,$E2#)

#SPILL! causes: an occupied cell in the way, the formula placed inside a Table,
or a merged cell in the spill zone

Answer

The @ is the implicit intersection operator. Before dynamic arrays, if a formula received a range where a single value was expected, Excel silently picked the value from the row or column intersecting the formula cell, a behaviour called implicit intersection. When dynamic arrays arrived, that silence became a problem, because the same formula would now spill instead.

To keep old workbooks producing identical results, Excel inserts an explicit @ wherever the legacy behaviour was being relied on, so a legacy =SUM(A:A*B:B) or a lookup written against a whole column may reopen as =@A2:A100 style references. It is not an error and it is not new logic, it is Excel telling you where the old file depended on a behaviour that no longer happens automatically. What matters in practice is what to do next: if you want the modern spilling behaviour, delete the @ and let the formula return an array, then make sure nothing sits in the spill zone.

If the formula is intentionally scalar, leave it. The same symbol has a second unrelated meaning inside Excel Tables, where @ in structured references means this row, as in [@Amount]. Interviewers ask this to separate people who have migrated real workbooks from people who only know the current version. A weak answer guesses that @ is a typo or a Table thing without knowing about implicit intersection.

Legacy formula written when implicit intersection was the norm
=VLOOKUP(A:A,Master!A:D,4,0)

Reopened in a dynamic array Excel it becomes
=VLOOKUP(@A:A,Master!A:D,4,0)

Remove the @ to get array behaviour, the result now spills down
=VLOOKUP(A2:A500,Master!A:D,4,0)

Force a single value on purpose from a spilling function
=@FILTER($D:$D,$B:$B=$H$1)

Completely different meaning inside a Table, @ means this row
=[@Qty]*[@Rate]

Answer

LET lets you name intermediate results inside a formula and reuse them, taking pairs of name and value and then a final calculation. Two benefits, and interviewers want both. Readability: a formula that repeats the same XLOOKUP three times, once to check for an error, once to test whether it is blank, once to return it, becomes a named value computed once.

Performance: without LET, Excel evaluates that repeated subexpression every single time it appears, so a heavy lookup or FILTER repeated four times costs four times as much on every recalculation. Name it once with LET and the engine computes it once. On a sheet of thirty thousand rows with a repeated FILTER that difference is measured in seconds per keystroke, not milliseconds.

Naming rules are simple: names must start with a letter, cannot look like a cell reference, and later names can refer to earlier ones, which lets you build a small pipeline inside one cell. The natural pairing is with LAMBDA, where LET holds the working values and LAMBDA makes the whole thing reusable. The interviewer is checking whether you write formulas anyone can maintain. A weak answer treats LET as cosmetic and misses the single evaluation point, which is the reason it exists.

Before, the same lookup runs three times per cell
=IF(XLOOKUP($A2,C!$A:$A,C!$D:$D,"")="","New",IF(XLOOKUP($A2,C!$A:$A,C!$D:$D,"")>100000,"Key",XLOOKUP($A2,C!$A:$A,C!$D:$D,"")))

After, one evaluation and readable
=LET(
  val, XLOOKUP($A2,C!$A:$A,C!$D:$D,""),
  IF(val="","New",IF(val>100000,"Key",val))
)

A small pipeline, later names can use earlier ones
=LET(
  rows,   FILTER($A$2:$F$9999,$B$2:$B$9999=$H$1),
  amts,   INDEX(rows,0,4),
  total,  SUM(amts),
  IF(total=0,"No sales",TEXT(total,"#,##,##0"))
)

Answer

LAMBDA turns a formula into a reusable function without any code. You write LAMBDA with parameter names followed by the calculation, then save it through Formulas, Name Manager, or the Advanced Formula Environment, giving it a name and a description. From then on the workbook has a custom function that any user can type.

The everyday wins are business rules that are otherwise copy pasted: a GST split, a slab based incentive, an ageing bucket, a fiscal year that starts in April. Change the rule once in Name Manager and every sheet updates, instead of hunting for a nested IF pasted into forty columns. LAMBDA also enables recursion by calling its own name, and the helper functions MAP, REDUCE, SCAN, BYROW and BYCOL apply a LAMBDA across an array without a helper column.

Against VBA the comparison is concrete. LAMBDA needs no macro enabled file, survives a corporate policy that blocks macros, recalculates natively with the engine, and works in Excel for the web. VBA still wins where you must touch the file system, drive Outlook, format or move sheets, or interact with anything outside the grid, because LAMBDA can only return a value.

The interviewer is testing whether you know the modern route exists. A weak answer jumps straight to VBA for every reuse problem.

Define in Name Manager, name it FISCALYEAR
=LAMBDA(d, "FY"&TEXT(YEAR(d)-(MONTH(d)<4),"00")&"-"&TEXT(YEAR(d)+(MONTH(d)>=4),"00"))

Use it anywhere in the workbook
=FISCALYEAR($A2)

Name it AGEBUCKET, days overdue into a bucket
=LAMBDA(days, IFS(days<=30,"0-30", days<=60,"31-60", days<=90,"61-90", TRUE,"90+"))

Apply a LAMBDA down an array with no helper column
=BYROW($C$2:$D$500, LAMBDA(r, INDEX(r,1)*INDEX(r,2)))

Running total across an array
=SCAN(0,$D$2:$D$500,LAMBDA(acc,v,acc+v))

Test before naming: call it inline with the argument appended
=LAMBDA(d, YEAR(d))($A2)

Answer

DATEDIF is the undocumented function that HR sheets run on. It takes start, end and a unit code: Y for whole years, M for whole months, D for days, YM for months remaining after the whole years, and MD for days remaining after whole months. It does not appear in the function autocomplete, which is exactly why interviewers ask about it, and it errors if the start date is later than the end date, which is the bug you hit when somebody enters a future joining date.

EOMONTH returns the last day of a month offset from a date, so zero gives this month end, minus one gives previous month end, and adding one day to EOMONTH with minus one gives the first of the current month, which is how you build a clean period column. NETWORKDAYS counts working days excluding Saturday and Sunday and an optional holiday range. NETWORKDAYS.INTL is the one to name in an Indian context because it takes a weekend argument, so a six day working week with only Sunday off is weekend code 11, and a Saturday and Sunday plant shutdown is code 1.

Both accept a holiday list, which for India means a company specific list, since public holidays vary state by state. WORKDAY.INTL is the inverse and gives you an SLA due date. A weak answer computes tenure by dividing days by 365, which drifts on leap years and looks careless on a payroll sheet.

Tenure as years and months (DATEDIF is hidden from autocomplete)
=DATEDIF($B2,TODAY(),"Y")&" y "&DATEDIF($B2,TODAY(),"YM")&" m"

Period columns for a monthly report
=EOMONTH($A2,0)          month end
=EOMONTH($A2,-1)+1       first day of the month
=EOMONTH($A2,-1)         previous month end

Working days, Sunday only off (weekend code 11), with a holiday list
=NETWORKDAYS.INTL($B2,$C2,11,Holidays!$A$2:$A$30)

Standard five day week
=NETWORKDAYS.INTL($B2,$C2,1,Holidays!$A$2:$A$30)

SLA due date, 3 working days from raising, Sunday only off
=WORKDAY.INTL($B2,3,11,Holidays!$A$2:$A$30)

Age on a given date, correct across leap years
=DATEDIF($B2,$C2,"Y")

Answer

Excel stores a date as a serial number of days from 1 January 1900, with times as the fractional part, so any real date is right aligned and can be formatted at will. Text that looks like a date is left aligned, ignored by SUMIFS date criteria, and sorts alphabetically, which is why a report suddenly starts in April. The diagnosis is ISNUMBER, and there are three fixes depending on the source: Text to Columns with the correct DMY option in step three, DATEVALUE where the format matches your locale, or splitting the string with LEFT, MID and RIGHT and rebuilding with DATE, which is the only reliable route when the text is ambiguous.

Ambiguity is the second half of the problem. A CSV exported from a US configured system writes 03/04/2026 meaning 4 March, while an Indian user reads 4 March as 04/03/2026. Every value where the day is 12 or less converts silently to the wrong date, and the ones above 12 stay as text, which is the classic symptom: a column that is half converted.

Always import through Power Query with an explicit locale, or use Text to Columns and choose DMY. The 1900 leap year story is that Excel deliberately treats 1900 as a leap year and accepts a non existent 29 February 1900, inherited for compatibility with Lotus 1-2-3, so date arithmetic before 1 March 1900 is off by one day. A weak answer just says 'format the cells as date', which does nothing to text.

Diagnose first
=ISNUMBER($A2)          TRUE means a real date
=$A2*1                  errors if the cell is text

Explicit rebuild from an ambiguous DD/MM/YYYY text string
=DATE(RIGHT($A2,4)*1, MID($A2,4,2)*1, LEFT($A2,2)*1)

When the string is MM/DD/YYYY from a US export, swap the parts
=DATE(RIGHT($A2,4)*1, LEFT($A2,2)*1, MID($A2,4,2)*1)

Bulk fix: select column, Data, Text to Columns, Next, Next, Date, DMY, Finish

Display without changing the value
=TEXT($A2,"dd-mmm-yyyy")

The historical quirk
=DATE(1900,2,29)     Excel accepts this non existent date, serial 60

Answer

A circular reference means a formula depends, directly or through a chain, on its own result. The classic accident is a SUM that includes its own cell, usually created by dragging a total row into the range. Excel shows a warning, sets the offending cell to zero, and displays the address in the status bar, and Formulas, Error Checking, Circular References lists them.

The dangerous case is that once you have dismissed the warning in a session, further circular references can be introduced without a fresh prompt, so the trace tool is your friend. Deliberate circularity exists in real finance models: interest on an average balance where the balance depends on the interest, a bonus calculated on profit after the bonus, or a revolver in a cash flow model. For those you enable File, Options, Formulas, Enable iterative calculation, and set maximum iterations and maximum change, so Excel loops until the result stops moving by more than the tolerance.

The cost is real: the model becomes order dependent, values can differ slightly between recalculations, and anybody who opens the file without iterative calculation enabled sees zeros. The safer alternative used by most modelling teams is to break the loop with a copy paste circuit breaker cell, or to solve algebraically, or to use Goal Seek. A weak answer just says 'a cell refers to itself, so delete it', with no idea that iterative calculation exists.

The accidental one, D10 sits inside its own range
D10: =SUM(D2:D10)

Find them: Formulas, Error Checking, Circular References
The status bar shows Circular References: D10

Deliberate, interest on average balance (needs iterative calculation on)
B5 (interest):  =AVERAGE(B4,B6)*$B$1
B6 (closing):   =B4+B5-B7

Enable it: File, Options, Formulas, Enable iterative calculation
Maximum Iterations 100, Maximum Change 0.001

Safer alternative, a circuit breaker switch
B5: =IF($B$9=0, 0, AVERAGE(B4,B6)*$B$1)

Or solve it without a loop: Data, What-If Analysis, Goal Seek

Answer

TRIM removes leading and trailing spaces and collapses runs of internal spaces to a single space, which is exactly right for names typed by humans. CLEAN removes the non printable characters below code 32, which is what you get from a mainframe or a fixed width export with embedded line feeds. Neither touches the character that actually causes most of the pain: CHAR(160), the non breaking space, which arrives with anything copied from a web page, a PDF or an HTML email.

It looks identical to a normal space, has code 160 rather than 32, and survives TRIM completely. You remove it with SUBSTITUTE first and then TRIM. The other regular offenders are the zero width space and a stray tab, and the general purpose diagnostic is CODE on the first character or LEN against LEN of the trimmed value.

In a real cleaning pass I would build one helper column that substitutes CHAR(160) and CHAR(9), applies CLEAN, then TRIM, then coerces case with PROPER or UPPER so the key is consistent, and I would use that helper column as the join key everywhere rather than nesting the same cleaning inside every lookup. Interviewers ask this because it is the difference between a candidate who says 'the data is bad' and one who fixes it in ninety seconds. A weak answer names TRIM and assumes it handles everything.

The full cleaning stack for a join key
=TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE($A2,CHAR(160)," "),CHAR(9)," ")))

See what is actually in there
=LEN($A2)-LEN(TRIM($A2))        how many spaces TRIM would remove
=CODE(LEFT($A2,1))              32 is a space, 160 is non breaking
=UNICODE(MID($A2,5,1))          check any position

Standardise case and punctuation for names
=PROPER(TRIM($A2))
=UPPER(SUBSTITUTE(TRIM($A2),".",""))

Strip everything except digits from a phone number
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM($A2),"+91",""),"-","")," ","")

Bulk route without formulas: Ctrl+H, paste a copied CHAR(160) into Find, leave Replace blank

Answer

The classic route is LEFT, RIGHT, MID with FIND or SEARCH locating the delimiter. FIND is case sensitive and SEARCH is not, and SEARCH accepts wildcards, which is the difference interviewers ask about. LEFT with FIND of a space minus one gives the first name.

The last name is the harder half because middle names exist, so the standard trick is to substitute the last space with a rare character and find that, using SUBSTITUTE with an instance number computed from the space count. On Microsoft 365 the modern answer is far shorter: TEXTBEFORE and TEXTAFTER take a delimiter and an instance number, including negative instance numbers that count from the end, so the last word is TEXTAFTER with minus one. TEXTSPLIT splits into columns, rows, or both at once, and takes an ignore_empty flag plus a pad_with value, which handles the ragged address strings you actually get.

For the pincode, the reliable rule in Indian addresses is the six digit run, which you find by testing positions or by splitting on spaces and picking the token whose length is six and which is numeric. TEXTJOIN is the inverse, joining a range with a delimiter and optionally skipping blanks, which single cell CONCATENATE could never do. A weak answer only offers Text to Columns, which is a manual step that does not refresh when the data changes.

Classic route
=LEFT($A2,FIND(" ",$A2)-1)                             first name
=MID($A2,FIND(" ",$A2)+1,LEN($A2))                     everything after the first space
=TRIM(RIGHT(SUBSTITUTE($A2," ",REPT(" ",99)),99))      last word, any number of middles

Modern route on Microsoft 365
=TEXTBEFORE($A2," ")            first name
=TEXTAFTER($A2," ",-1)          last name, negative instance counts from the end
=TEXTSPLIT($A2," ")             spills each word into its own column
=TEXTSPLIT($A2,",",,TRUE)       split an address on commas, ignore empties

Pincode: the six digit token in a free text address
=TEXTAFTER($A2," ",-1)*1        when the pincode is genuinely last
=LOOKUP(9^9,1*MID($A2,SEQUENCE(LEN($A2)),6))

Rejoin, skipping blanks (CONCATENATE cannot skip)
=TEXTJOIN(", ",TRUE,$B2:$F2)

Answer

Left alignment plus a green triangle in the corner means the values are text, and SUM ignores text silently rather than erroring, which is why the total quietly reads zero or is short. Three routes, and you should be able to name the tradeoff of each. One, select the column and use Data, Text to Columns, Next, Next, Finish, which forces a reparse of every cell and is the fastest bulk fix on a one time file.

Two, the paste special multiply trick: type 1 in a blank cell, copy it, select the range, Paste Special, Multiply, which coerces the whole block in place with no helper column. Three, a formula: multiply by 1, or use VALUE, or NUMBERVALUE when the decimal and thousands separators differ from your locale, which matters when a file arrives from a European system where the comma is the decimal mark. Two extra cases show real experience.

Numbers with a trailing minus sign, common in SAP exports, need SUBSTITUTE to move the sign before conversion. And amounts carrying the Indian digit grouping as text, or a currency symbol, need those characters stripped first. Finally, if the column is genuinely an identifier such as a GSTIN or a sixteen digit reference, leaving it as text is correct, and the real bug would be Excel converting it into scientific notation. A weak answer just reformats the cells as Number, which changes nothing at all.

Diagnose
=ISTEXT($A2)
=SUMPRODUCT(1*ISTEXT($A$2:$A$5000))    how many cells are text

Route 1, bulk reparse
Select column, Data, Text to Columns, Next, Next, Finish

Route 2, in place with no helper column
Type 1 in a blank cell, Ctrl+C, select the range, Ctrl+Alt+V, Multiply, OK

Route 3, formulas
=$A2*1
=VALUE($A2)
=NUMBERVALUE($A2,",",".")     when the source uses comma as decimal mark

SAP style trailing minus, 1250.00 written with the sign at the end
=IF(RIGHT($A2,1)="-", -1*LEFT($A2,LEN($A2)-1)*1, $A2*1)

Strip a currency symbol and grouping first
=SUBSTITUTE(SUBSTITUTE($A2,"₹",""),",","")*1

Answer

Remove Duplicates is destructive. It deletes rows in place based on the columns you tick, it keeps the first occurrence and discards the rest with no record of what went, and if you tick only the email column it will throw away rows whose other columns held different and possibly newer information. That is the answer to the trap in the question.

It is also case insensitive and treats leading spaces as a difference, so it under deletes on dirty data and over deletes on the wrong column selection. UNIQUE is non destructive: it returns a distinct list into a new range, leaves the source untouched, updates when the source changes, and its exactly_once flag returns only values with no duplicate at all, which is a different and useful thing. A COUNTIFS or COUNTIF helper column is the option that actually answers the business question, because it labels every row with its occurrence number, so you can inspect the duplicates before deciding, keep the most recent rather than the first, and count how many you are about to remove.

My working method on a customer master: normalise the key first with TRIM and UPPER on phone or email, add an occurrence number partitioned by that key and ordered by a date column, review the rows where the count is above one, then keep the latest and archive the rest on another sheet. A weak answer runs Remove Duplicates immediately and cannot say what was lost.

Occurrence number per key, the safe way to see duplicates first
=COUNTIF($B$2:$B2,$B2)                 1 for the first, 2 for the second
=COUNTIFS($B$2:$B$5000,$B2)            total occurrences of this key

Normalise the key before any of this
=UPPER(TRIM(SUBSTITUTE($B2,CHAR(160),"")))

Keep the latest row rather than the first
=IF($C2=MAXIFS($C$2:$C$5000,$B$2:$B$5000,$B2),"KEEP","ARCHIVE")

Non destructive distinct list, updates with the source
=UNIQUE($B$2:$B$5000)
=SORT(UNIQUE(FILTER($B$2:$B$5000,$B$2:$B$5000<>"")))

Values that occur exactly once
=UNIQUE($B$2:$B$5000,FALSE,TRUE)

Highlight before deleting: Home, Conditional Formatting, Highlight Cells, Duplicate Values

Answer

Built in rules only colour the cell you evaluated, so for a whole row you need Use a formula to determine which cells to format. Two things decide whether it works. First, select the full data range starting from the top left data cell before you create the rule, because the formula is written relative to the active cell of that selection and Excel then offsets it for every other cell.

Second, get the dollar signs right: lock the column of the tested field and leave the row relative, so $E2 tested across a row keeps reading column E while walking down the rows. Getting that one wrong is the reason most people's row highlighting shifts diagonally, and it is precisely what the interviewer is checking. Beyond that, the useful rules to name are duplicate detection through Highlight Cells Rules or a COUNTIF formula, data bars and colour scales for at a glance magnitude inside a table, and icon sets for status.

Practical cautions worth mentioning: conditional formatting on entire columns is a real performance drain, rules multiply when you copy and paste rows so Manage Rules regularly shows the same rule split into dozens of ranges, and the Applies To range is easy to break. A weak answer describes the Highlight Cells menu and cannot write a formula rule when asked to colour a row rather than a cell.

Select A2:H5000 first, then Home, Conditional Formatting, New Rule, Use a formula

Entire row red when overdue by more than 45 days
=AND($E2<>"", TODAY()-$E2>45, $F2<>"Paid")

Alternate row shading that survives sorting
=MOD(ROW(),2)=0

Highlight duplicate keys across the whole table
=COUNTIF($B$2:$B$5000,$B2)>1

Flag a row missing any mandatory field
=COUNTBLANK($A2:$F2)>0

Highlight the top 10 by value inside the row rule
=$D2>=LARGE($D$2:$D$5000,10)

Row matching the search box in H1
=AND($H$1<>"", ISNUMBER(SEARCH($H$1,$B2)))

Answer

Because the pivot's source is a fixed range such as Sheet1!$A$1:$H$5000, and refresh re-reads that same fixed range. New rows below it are simply not in scope. People patch it by editing the data source each month, or by pointing the pivot at whole columns, which bloats the cache and produces a blank item in every field.

The correct fix is to select the source and press Ctrl+T to convert it into an Excel Table, then base the pivot on the Table name. Tables auto expand when rows are added, so refresh picks up everything, and formulas that reference the Table expand with it too. That leads into the second half of the answer, structured references.

Inside a Table you write TableName[Amount] rather than D2:D5000, and [@Amount] for the current row, which means formulas read like sentences, survive sorting and filtering, and do not need dollar signs. Tables also give you a banded format, a totals row with a dropdown of aggregations, and a stable name that Power Query, charts and Data Validation can all point at. Two cautions to mention: dynamic array formulas cannot spill inside a Table, and merged cells are not allowed in one, which is usually a good thing. A weak answer says 'right click and refresh', which is the action that already failed.

Convert first: select any cell in the data, Ctrl+T, tick My table has headers
Rename it in Table Design, Table Name: tblSales

Structured references, no dollar signs needed
=SUM(tblSales[Amount])
=SUMIFS(tblSales[Amount],tblSales[City],$H$1)
=[@Qty]*[@Rate]                        current row inside the Table
=SUM(tblSales[[Jan]:[Mar]])            a span of columns

Point the pivot at the Table
Insert, PivotTable, Table/Range: tblSales

If you cannot use a Table, an old style dynamic named range
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),8)     works but is volatile

Refresh everything including queries
Data, Refresh All, or Ctrl+Alt+F5

Answer

Drop the date field into Rows, right click any date, choose Group, and pick Months and Quarters and Years together, which produces a proper hierarchy rather than a flat list. Selecting Years alongside Months matters: group by Months only and January 2025 merges with January 2026 into one row, which is a genuinely common reporting error. On recent versions Excel auto groups dates when you drop them in and adds the Years and Quarters fields automatically, which you can undo by ungrouping.

Days can be grouped by an arbitrary number of days, so 7 gives you weekly buckets, though the weeks start from the first date in the data and not from a Monday, which is worth flagging. Numbers group the same way: right click a value field placed in Rows, choose Group, and set starting at, ending at, and by, so by 5000 gives you order value bands without a single helper formula. Two failure cases the interviewer is likely fishing for.

Grouping refuses to work when the column contains any text or blank cell, and the error message says 'Cannot group that selection', which almost always means one text date hiding in the column. And grouping is stored in the pivot cache shared by pivots built from the same source, so grouping in one pivot changes the other. A weak answer builds a helper column with MONTH and never mentions the built in grouping.

Date grouping: right click a date in the pivot, Group, tick Years, Quarters, Months

Weekly buckets: Group, tick Days only, Number of days: 7

Value bands: right click a number in Rows, Group
Starting at 0, Ending at 50000, By 5000

If grouping is refused, find the offending cell
=COUNTIF($A$2:$A$5000,"*")            counts text entries in a date column
=SUMPRODUCT(1*ISTEXT($A$2:$A$5000))

Helper column route, useful when you need a custom fiscal grouping
=TEXT($A2,"yyyy-mm")                   sortable period key
="Q"&ROUNDUP(MONTH(EDATE($A2,-3))/3,0)  Indian fiscal quarter, April start

Indian fiscal year label
="FY"&TEXT(YEAR($A2)-(MONTH($A2)<4),"0000")

Answer

A calculated field creates a new field from other fields, evaluated across the whole pivot, for example margin as revenue minus cost. A calculated item creates a new item inside an existing field, for example an item called North that adds Delhi, Chandigarh and Lucknow within the City field. The trap, and the reason this question is asked, is that calculated fields operate on the sums, not on each row.

A calculated field defined as revenue divided by quantity gives total revenue divided by total quantity, which is the weighted average and usually what you want, but a calculated field defined as price times quantity gives the sum of price times the sum of quantity, which is meaningless. If you need a row level product, create it in the source data or the data model, not as a calculated field. Calculated items have their own problems: they double count if the source rows already roll up, they disable grouping on that field, and they slow large pivots noticeably because they are computed per cell.

Both also ignore the aggregation you selected, always calculating on the sum. The modern answer is to add the column in Power Query or write a DAX measure in the data model, where the evaluation order is explicit. A weak answer explains the menu path and cannot say what happens to a multiplication.

Calculated field: PivotTable Analyze, Fields Items and Sets, Calculated Field
Name: Margin
Formula: =Revenue - Cost                       correct, sums subtract cleanly

Name: AvgPrice
Formula: =Revenue / Quantity                   correct, this is a weighted average

Name: LineTotal
Formula: =Price * Quantity                     WRONG, gives SUM(Price)*SUM(Quantity)
Fix it in the source or the Table instead:  =[@Price]*[@Qty]

Calculated item: click an item in the field first, then Calculated Item
Name: North Zone
Formula: =Delhi + Chandigarh + Lucknow         careful, can double count the total

Better route, a DAX measure in the data model
Margin % := DIVIDE(SUM(Sales[Revenue]) - SUM(Sales[Cost]), SUM(Sales[Revenue]))

Answer

Drag the value field into Values a second time, right click it, choose Show Values As, and pick the calculation. The options that matter in reporting are % of Grand Total, % of Column Total and % of Row Total, % of Parent Row Total for a hierarchy such as zone then city, Running Total In, % Running Total In, Difference From and % Difference From, and Rank Largest to Smallest. Difference From takes a base field and a base item, so setting the base field to Month and the base item to previous gives month on month change directly inside the pivot, which answers the growth question without a single formula, and setting the base item to a specific month gives an index against that baseline.

Rename the second copy of the field, because Excel will call it Sum of Amount2 and that reaches the deck. Two details that show experience. % of Parent Row Total is the one people never find and it is exactly right for zone and city hierarchies, since it shows each city's share of its own zone rather than of the national total. And Running Total In requires you to name the field the running is ordered by, so on a grouped date field you point it at Month, and it resets per year only if Years is also in the layout. A weak answer builds helper formulas next to the pivot, which break the moment anyone filters.

Drag Amount into Values twice
Right click the second one, Show Values As

% of Grand Total          city share of national sales
% of Parent Row Total     city share within its own zone
Running Total In: Month   cumulative sales through the year
% Running Total In        cumulative share, useful for ABC analysis
Difference From           Base field: Month, Base item: (previous)
% Difference From         Base field: Month, Base item: (previous)  -> MoM growth
Rank Largest to Smallest  Base field: City

Rename the header, Excel defaults to Sum of Amount2
Double click the header, type: Share of Total

Pull one pivot number into a summary cell
=GETPIVOTDATA("Amount",$A$3,"City","Mumbai","Month","Apr")

Answer

By default, clicking a pivot value while writing a formula inserts GETPIVOTDATA rather than a cell reference. That is not a bug, it is Excel protecting you: a pivot's layout moves when filters, slicers or field order change, so a plain reference like C7 points at whatever happens to be sitting there afterwards, while GETPIVOTDATA fetches the value by its field and item names and keeps returning the right number. It also fails loudly with #REF! when the item genuinely disappears, which is far safer than silently reading a neighbouring row.

The catch that makes people hate it is that arguments are inserted as literal text, so you cannot drag the formula across a month row. The fix is not to switch it off but to replace the hard coded strings with cell references, so the field and item come from your header row and the formula fills normally. When you truly need positional references, for instance building a fixed dashboard grid that will not change shape, turn it off through PivotTable Analyze, Options, the dropdown next to Options, Generate GetPivotData.

My practical position: keep it on for summary cells feeding a dashboard, turn it off when you are laying out a grid, and never leave plain references into a pivot that a user can slice. A weak answer says 'I always turn it off because it is annoying' with no discussion of the risk.

What Excel writes for you
=GETPIVOTDATA("Amount",$A$3,"City","Mumbai","Month","Apr")

Make it fillable, arguments from cells
=GETPIVOTDATA("Amount",$A$3,"City",$A7,"Month",B$6)

Survive a missing combination
=IFERROR(GETPIVOTDATA("Amount",$A$3,"City",$A7,"Month",B$6),0)

Grand total of the whole pivot, no field arguments
=GETPIVOTDATA("Amount",$A$3)

Turn it off: PivotTable Analyze, Options dropdown, untick Generate GetPivotData

The alternative for dashboards that must not depend on layout at all
=SUMIFS(tblSales[Amount],tblSales[City],$A7,tblSales[Month],B$6)

Answer

That layout is a cross tab, readable for humans and useless for analysis, because every new month adds a column and every formula and pivot has to be rebuilt. The answer is Power Query unpivot. Load the range with Data, From Table/Range, select the columns that identify the row such as branch and product, right click and choose Unpivot Other Columns rather than Unpivot Columns.

That distinction is the whole point of the question: Unpivot Other Columns keeps working when finance adds April next month, while Unpivot Columns names the specific month columns and breaks. You then rename Attribute to Month and Value to Amount, set the data types explicitly, and Close and Load to a Table or straight to the data model. Once the data is in that tall shape, a pivot rebuilds the original cross tab in ten seconds and every SUMIFS becomes trivial.

Worth mentioning: Unpivot Only Selected Columns is a third variant, Fill Down handles the merged looking blanks that come from a formatted report, and Remove Top Rows plus Use First Row as Headers handles the title and logo rows above the data. The whole thing is recorded as applied steps, so next month you replace the file and press Refresh All. The interviewer wants to hear the word unpivot and hear you choose the version that survives new columns. A weak answer describes copying and transposing by hand.

Data, From Table/Range, then in the editor:
Select Branch and Product, right click, Unpivot Other Columns
Rename Attribute to Month, Value to Amount
Set types: Month as Text, Amount as Decimal Number
Home, Close and Load To, Table or Only Create Connection plus Add to Data Model

The generated M for the key step
= Table.UnpivotOtherColumns(#"Changed Type", {"Branch", "Product"}, "Month", "Amount")

Handle a formatted report with title rows and gaps
= Table.Skip(Source, 3)
= Table.PromoteHeaders(#"Removed Top Rows")
= Table.FillDown(#"Promoted Headers", {"Branch"})

Go back the other way when a human needs the cross tab
Select Month, Transform, Pivot Column, Values: Amount, Aggregate: Sum

Answer

Append stacks tables vertically, adding rows, and is what you use for twelve monthly files with the same columns. It matches by column name, so a header spelled Amount in one file and Amount in another with a trailing space creates two separate columns with nulls in each, which is the failure people actually hit. Merge joins tables horizontally, adding columns, and is the Power Query equivalent of a VLOOKUP but done once at load time rather than in fifty thousand live formulas.

The join kinds are Left Outer, which keeps everything from the first table and is the default and the right choice most of the time, Right Outer, Full Outer, Inner, plus the two that Excel formulas have no clean equivalent for: Left Anti and Right Anti, which return only the rows with no match. Anti joins are the correct answer to a reconciliation question, because Left Anti immediately gives you every invoice in the ledger that is missing from the bank statement, with no helper column and no COUNTIF. After a merge you expand the resulting column and pick only the fields you need, and you should always check the row count before and after, because a merge on a non unique key fans out and silently multiplies your rows exactly the way a SQL join does. A weak answer treats Merge as 'the VLOOKUP one' and never mentions anti joins or fan out.

Append: Home, Append Queries, Three or more tables
Column names must match exactly, trailing spaces create ghost columns
= Table.Combine({Jan, Feb, Mar})

Merge: Home, Merge Queries, pick the key column in both, choose Join Kind
= Table.NestedJoin(Sales, {"CustID"}, Master, {"CustID"}, "Master", JoinKind.LeftOuter)
= Table.ExpandTableColumn(#"Merged Queries", "Master", {"Segment","City"}, {"Segment","City"})

Reconciliation, rows present in the ledger but missing from the bank file
Join Kind: Left Anti (rows only in first)
= Table.NestedJoin(Ledger, {"UTR"}, Bank, {"UTR"}, "Bank", JoinKind.LeftAnti)

Always sanity check the fan out
Home, Keep Rows, Count Rows before and after the merge

Answer

Use Data, Get Data, From File, From Folder, point it at the folder, and click Combine and Transform. Power Query builds a sample file query, an auto generated transform function, and a main query that applies that function to every file, so any cleaning you do on the sample propagates to all thirty. Filter on Extension and on the file name pattern first so temporary files starting with a tilde do not break the refresh, and keep the Source.Name column because that is your branch identifier and it is the single most useful column in the whole pipeline.

Then set types, remove the junk columns, load to the data model, and next month you drop new files into the folder and press Refresh All. The applied steps list on the right is a readable, reorderable audit trail, which is the reason this beats a macro for anyone who has to maintain it. Query folding is what happens when Power Query can translate your steps back into the source system's own language, typically SQL, so filtering and grouping run on the server and only the result travels.

Folding survives filtering, removing columns, renaming, grouping and joins, and breaks the moment you add an index column, use certain custom M functions, or merge with a different source type. Once it breaks it never resumes for later steps, which is why you push filters and column removal to the very top of the query. On files from a folder there is no server, so folding does not apply, but the discipline of filtering early still cuts memory. A weak answer describes the folder import and has never heard of folding.

Data, Get Data, From File, From Folder, Combine and Transform Data

Protect the refresh from stray files
= Table.SelectRows(Source, each [Extension] = ".xlsx" and not Text.StartsWith([Name], "~"))

Keep the file name as the branch key
= Table.RenameColumns(#"Removed Other Columns", {{"Source.Name", "Branch"}})
= Table.TransformColumns(#"Renamed", {{"Branch", each Text.BeforeDelimiter(_, "."), type text}})

Whole query in one M block
let
    Source   = Folder.Files("D:\Reports\Branches"),
    OnlyXlsx = Table.SelectRows(Source, each [Extension] = ".xlsx"),
    Combined = Table.Combine(List.Transform(OnlyXlsx[Content], each Excel.Workbook(_, true){[Item="Data"]}[Data])),
    Typed    = Table.TransformColumnTypes(Combined, {{"Amount", type number}, {"Date", type date}})
in
    Typed

Check folding on a SQL source: right click the last step, View Native Query
If it is greyed out, folding has already stopped at that step

Answer

Loading to the data model puts the tables into an in memory columnar engine rather than the worksheet grid. Four concrete gains. You are no longer bound by the roughly one million row sheet limit, because the model holds far more, compressed.

You define relationships between tables rather than flattening everything with lookups, so a fact table joins to a calendar and a customer master without a single VLOOKUP and the workbook shrinks dramatically. You write measures in DAX, which are evaluated in the filter context of whatever cell they land in, so one measure serves every slicer combination instead of a different formula per report. And you get distinct count as a native aggregation, which a classic pivot cannot do at all.

A correct time intelligence answer needs a proper date table marked as a date table, otherwise DATEADD and SAMEPERIODLASTYEAR misbehave on gaps. The measure itself uses CALCULATE to shift the filter context and DIVIDE rather than a slash, because DIVIDE handles the divide by zero case and returns blank instead of an error. Say clearly when you would move: the moment the workbook has more than a few hundred thousand rows, or needs several tables related together, or needs distinct counts and period comparisons, the model earns its keep. A weak answer treats Power Pivot as a bigger pivot table and cannot explain filter context.

Load queries with Only Create Connection plus Add this data to the Data Model
Relationships: Sales[CustID] to Customer[CustID], Sales[Date] to Calendar[Date]
Mark the calendar table: Power Pivot, Design, Mark as Date Table

Base measures
Total Sales := SUM(Sales[Amount])
Orders := DISTINCTCOUNT(Sales[OrderID])
Avg Order Value := DIVIDE([Total Sales], [Orders])

Prior month and growth
Prev Month Sales := CALCULATE([Total Sales], DATEADD(Calendar[Date], -1, MONTH))
MoM Growth % := DIVIDE([Total Sales] - [Prev Month Sales], [Prev Month Sales])

Same period last year
LY Sales := CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Calendar[Date]))

Row level product handled correctly, unlike a pivot calculated field
Line Revenue := SUMX(Sales, Sales[Qty] * Sales[Rate])

Ignore a slicer on purpose, for a share of total
Share of All Cities := DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Sales[City])))

Answer

Decide on three axes: who maintains it, what the task touches, and how it fails. Formulas are right when the calculation must be visible and auditable inside the sheet, when a business user needs to see how a number was derived, and when the transformation is small. They are wrong when they multiply into hundreds of thousands of live cells that recalculate on every keystroke.

Power Query is right for anything that is import, reshape, clean and combine: fixed steps applied to data whose shape is stable but whose contents change monthly. It is the default answer for repeated clean up because the steps are visible and reorderable, it needs no macro permissions, it refreshes on demand, and a colleague can read the applied steps list without knowing any code. It is wrong for anything outside the data itself.

VBA earns its place when you must act on the environment: save thirty files, email a report through Outlook, create and format sheets, drive another application, respond to a button, or apply logic that genuinely requires loops and conditionals over workbook objects. The organisational constraint decides more of this than elegance does: many Indian enterprises block macro enabled files by policy or strip them at the mail gateway, so a query based solution actually ships. My default order is Power Query first, then a small set of formulas or measures on top, then VBA only for the last mile. A weak answer picks the tool the candidate happens to know.

Decision shortcuts
Import, reshape, clean, combine, dedupe, unpivot, join   -> Power Query
Business logic a user must see and audit in the sheet    -> formulas or a DAX measure
Repeat over files, email, format, create sheets, buttons -> VBA
Over a few hundred thousand rows, or multiple tables     -> Data Model plus DAX

The same job three ways, dedupe by customer id
Formula   =IF(COUNTIF($B$2:$B2,$B2)=1,"KEEP","DROP")
Power Query  Home, Remove Rows, Remove Duplicates on CustID (after sorting by date)
VBA          ActiveSheet.Range("A1:H5000").RemoveDuplicates Columns:=2, Header:=xlYes

What a maintainer sees afterwards
Power Query: a named list of applied steps
VBA: a module somebody has to open and read
Formulas: the logic in the grid, but repeated in every row

Answer

Match the chart to the question. Trend over time is a line chart, or a column chart when the periods are few and discrete and you want each period compared rather than the shape of the movement. Comparison across categories is a bar chart, horizontal when the labels are long, which is almost always true for Indian city and branch names, and sorted by value rather than alphabetically unless the order carries meaning.

Composition at a point in time is a stacked bar or a treemap, and composition over time is a stacked area or a 100 percent stacked column. Correlation is a scatter, with a trendline only if you are ready to defend it. Distribution is a histogram or a box plot.

The pushbacks that show judgment: pie charts beyond three or four slices, because people cannot compare angles, and never two pies side by side to show change. 3D anything, which distorts the very lengths the reader is trying to compare. A truncated value axis on a column chart, which exaggerates differences, though it is legitimate on a line chart tracking small movements. Dual axis charts where the two scales are chosen to make lines cross.

And a chart at all where a single well formatted number and a delta would communicate faster. A weak answer lists chart types with no opinion, which reads as decoration rather than communication.

Question to chart
How did sales move this year          -> line, months on the x axis
Which branch sold the most            -> horizontal bar, sorted descending
What is the product mix now           -> stacked bar or treemap
How did the mix change over the year  -> 100 percent stacked column
Does spend relate to revenue          -> scatter, one point per campaign
How are order values distributed      -> histogram

Sorted bars from a formula so the chart reorders on refresh
=SORTBY(UNIQUE(tblSales[Branch]), SUMIFS(tblSales[Amount],tblSales[Branch],UNIQUE(tblSales[Branch])), -1)

A KPI cell that beats a chart
=TEXT($C$2,"₹#,##,##0")&" ("&TEXT($C$2/$C$1-1,"+0.0%;-0.0%")&" MoM)"

Indian digit grouping for lakhs and crores
Format code: [>=10000000]₹0.00,,," Cr";[>=100000]₹0.00,," L";₹#,##0

Answer

Slicers are visual filters attached to a Table or a pivot, added from PivotTable Analyze, Insert Slicer, and the point that gets marks is Report Connections, which links one slicer to several pivots so the whole dashboard moves together. Timelines are the date specific version with a level selector for years, quarters, months and days, and they only work on a real date field, which is another reason the date column must be numeric. Everything must be built on the same pivot cache or the same data model, otherwise the slicer cannot reach the other pivots.

On layout, freeze panes matters more than people think: select the cell below and to the right of what should stay visible, then View, Freeze Panes, so a report with a header block and a key column stays readable at row 400. Split view is the alternative when you need to compare two distant parts of the same sheet. For print, set the print area, choose Page Layout, Print Titles and set rows to repeat at top so headers appear on every page, use Fit to 1 page wide by many tall rather than shrinking to one page, set landscape for wide reports, and add page numbers in the footer.

The habit that separates people who send reports for real is checking Print Preview before mailing, because a report that spills a single column onto page four is the thing your manager will notice. A weak answer knows slicers but not Report Connections.

Slicers
PivotTable Analyze, Insert Slicer, pick City and Category
Right click the slicer, Report Connections, tick every pivot it should drive
Slicer Settings, tick Hide items with no data

Timeline
PivotTable Analyze, Insert Timeline, choose the date field, set level to Months

Freeze the header block and the key column
Click B7, then View, Freeze Panes, Freeze Panes

Print setup
Page Layout, Print Area, Set Print Area
Page Layout, Print Titles, Rows to repeat at top: $1:$6
Page Layout, Orientation Landscape, Scale to Fit: 1 page wide, Automatic tall
Insert, Header and Footer, add Page X of Y
Ctrl+P and check the preview before mailing

A slicer aware total in a KPI cell
=SUBTOTAL(109,tblSales[Amount])     109 ignores hidden and filtered rows

Answer

Use a combo chart: revenue as clustered columns on the primary axis, margin percent as a line on a secondary axis, set through Chart Design, Change Chart Type, Combo, ticking Secondary Axis for the percentage series. The judgment part, which is what is being tested, is that secondary axes are easy to abuse. Both axis ranges must be set deliberately rather than left automatic, because auto scaling can make an improving margin look flat or a flat one look dramatic, and axis ranges shift the moment the data refreshes.

Label the axes with their units, keep the percentage line visually distinct rather than the same colour family as the columns, and if the two series have no real relationship, use two separate charts stacked instead. For in cell visuals: sparklines, added from Insert, Sparklines, give a twelve month shape inside a single cell next to each branch, with the high and low points marked and the axis set to the same minimum across all rows so the rows are comparable, which is the setting everyone forgets. Data bars from conditional formatting work well inside a table column, and you should set the minimum and maximum manually to a fixed value so bars are comparable between refreshes, and tick Show Bar Only to keep the number readable elsewhere.

Icon sets suit status columns and should use three states at most. A weak answer adds a secondary axis with no mention of scaling honesty.

Combo chart
Select the data, Insert, Combo Chart
Revenue: Clustered Column, primary axis
Margin %: Line with Markers, tick Secondary Axis
Right click each axis, Format Axis, set Minimum and Maximum manually

Sparklines
Select the target cells, Insert, Sparklines, Line, data range = the 12 month block
Sparkline tab, tick High Point and Low Point
Axis, Vertical Axis Minimum Value, Same for All Sparklines

Data bars in a table column
Home, Conditional Formatting, Data Bars, More Rules
Minimum: Number 0, Maximum: Number 5000000, tick Show Bar Only

An arrow indicator that reads at a glance
=IF($D2>$C2,"▲ ","▼ ")&TEXT($D2/$C2-1,"0.0%")

One cell headline for the top of the dashboard
="Top branch: "&INDEX(tblSales[Branch],MATCH(MAX(tblSales[Amount]),tblSales[Amount],0))

Answer

Month on month growth is current minus previous, divided by previous. Three implementation routes and you should know all three. In a plain sheet, reference the row above and guard the denominator, because the first row has nothing above it and a zero base gives #DIV/0!.

In a pivot, use Show Values As, % Difference From, with base field Month and base item previous, which is faster and survives slicing. In the data model, use CALCULATE with DATEADD or PARALLELPERIOD, which is the only route that handles missing months correctly. That last point is the substance of the question.

If March had no sales and is simply absent from the data, a row above reference compares April against February and labels it as month on month, which is wrong. The fix in a sheet is to build a complete month spine with SEQUENCE and EDATE and look values up against it, so absent months appear as zero rather than vanishing. Then decide the business rule for a zero base: growth from zero is undefined, so show a dash or the text 'new', not 999 percent, because that number will end up on a slide.

Year on year needs the same date offset by twelve months and is the honest comparison for any business with festival seasonality, which describes most Indian retail, since a Diwali month compared to the month before tells you nothing useful. A weak answer writes B3/B2-1 and stops.

Simple in sheet, guarded
=IF(N($C2)=0,"",$C3/$C2-1)

Safer with a lookup against a complete month spine, immune to missing months
=LET(
  cur,  SUMIFS(tblSales[Amount],tblSales[Month],$A3),
  prev, SUMIFS(tblSales[Amount],tblSales[Month],EDATE($A3,-1)),
  IF(prev=0,"new",cur/prev-1)
)

Build the month spine so no month can go missing
=EDATE(DATE(2026,4,1),SEQUENCE(12,1,0,1))

Year on year, the right comparison under festival seasonality
=LET(
  cur, SUMIFS(tblSales[Amount],tblSales[Month],$A3),
  ly,  SUMIFS(tblSales[Amount],tblSales[Month],EDATE($A3,-12)),
  IF(ly=0,"n/a",cur/ly-1)
)

Display format that shows sign and colour
0.0%;[Red]-0.0%;"flat"

In the pivot instead
Show Values As, % Difference From, Base field Month, Base item (previous)

Answer

Record through View, Macros, Record Macro, or the record button on the status bar, and note the Use Relative References toggle before you start, because absolute recording hard codes the exact cells you clicked and the macro then only ever works on that one range. Save as .xlsm, since a normal .xlsx silently discards macros on save, which is the mistake everyone makes once. The recorder produces working but poor code, and knowing what to fix is the real question.

Recorded code selects and activates everything, because that is literally what you did with the mouse, and every Select plus Selection pair can be collapsed into one direct statement on the range, which is both faster and immune to the wrong sheet being active. It hard codes ranges, so replace them with the used range or a last row found from the bottom up. It records settings you did not intend to change, such as every attribute of a format dialog you touched.

It has no error handling and no protection against being run on the wrong sheet. And it usually deserves ScreenUpdating switched off around the body for anything that loops. Store personal utilities in the Personal Macro Workbook so they are available in every file, and put shared ones in the workbook itself or an add in. A weak answer says the recorder is fine as is, which shows no experience of a macro breaking in month two.

What the recorder writes
Sub Macro1()
    Range("A1:H1").Select
    Selection.Font.Bold = True
    Selection.Interior.Color = 15917529
    Range("A2").Select
    ActiveWindow.FreezePanes = True
End Sub

What you should keep
Sub FormatHeader(ws As Worksheet)
    With ws.Range("A1:H1")
        .Font.Bold = True
        .Interior.Color = RGB(217, 225, 242)
    End With
    ws.Rows(2).Select
    ActiveWindow.FreezePanes = True
End Sub

Find the real last row instead of a hard coded one
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

Speed wrapper for anything that loops
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
' body
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

Answer

The pattern is: get a distinct list of branches, loop it, filter the master with AutoFilter, copy the visible rows into a new workbook, save with a name built from the branch and the date, close it. Wrap the body with ScreenUpdating and DisplayAlerts off so it does not flicker or prompt on overwrite, and turn them back on in an error handler so a failure does not leave Excel in a silent state. Use fully qualified references and declare your variables with Option Explicit at the top of the module, because an undeclared typo silently becomes an empty Variant and the macro produces empty files.

Add a check that the output folder exists before you start writing thirty files into nowhere. When a macro is the wrong answer: if the job is import, clean, reshape or join, use Power Query, because the steps are visible, refreshable and readable by the next person, whereas the equivalent VBA is a wall of code only its author understands. If the workbook is going to a corporate mailbox, remember many organisations block or strip .xlsm attachments and Excel for the web will not run VBA at all, so a macro solution can simply fail to reach the user.

And if the requirement is really a scheduled process feeding several teams, the honest recommendation is a proper pipeline, not a macro anybody has to remember to click. A weak answer writes the loop but cannot name a case against VBA.

Option Explicit

Sub SplitByBranch()
    Dim ws As Worksheet, wbOut As Workbook
    Dim lastRow As Long, i As Long
    Dim branches As Object, key As Variant
    Dim outPath As String

    Set ws = ThisWorkbook.Sheets("Master")
    outPath = ThisWorkbook.Path & "\Branch Files\"
    If Dir(outPath, vbDirectory) = "" Then MkDir outPath

    Set branches = CreateObject("Scripting.Dictionary")
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    For i = 2 To lastRow
        branches(Trim(ws.Cells(i, 2).Value)) = 1
    Next i

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    On Error GoTo Cleanup

    For Each key In branches.Keys
        ws.Range("A1").CurrentRegion.AutoFilter Field:=2, Criteria1:=key
        Set wbOut = Workbooks.Add
        ws.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy _
            wbOut.Sheets(1).Range("A1")
        wbOut.SaveAs outPath & key & "_" & Format(Date, "yyyymmdd") & ".xlsx", 51
        wbOut.Close False
    Next key

Cleanup:
    ws.AutoFilterMode = False
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    If Err.Number <> 0 Then MsgBox "Failed on " & key & ": " & Err.Description
End Sub

Answer

Protection in Excel is subtractive. Every cell is Locked by default and that lock does nothing until you protect the sheet, so the workflow is: select the input cells, Ctrl+1, Protection tab, untick Locked, then Review, Protect Sheet, choosing which actions to still allow such as selecting unlocked cells, sorting or using AutoFilter. To stop people reading the logic, tick Hidden alongside Locked on the formula cells and the formula bar goes blank while the result still shows.

Protect Workbook separately stops sheets being added, deleted, renamed or unhidden, which is different from protecting a sheet. Very hidden sheets, set through the Visual Basic editor, do not appear in the Unhide list at all. Now the honest half, which is the part being tested.

Sheet protection is not security. The password is weak by design, removing it is trivial with widely available tools, and anyone can copy the values into a new file. Protection prevents accidents, not determined people.

Real confidentiality needs file level encryption through File, Info, Protect Workbook, Encrypt with Password, or information rights management, and even then the recipient can retype what they can see. On co-authoring: files on SharePoint or OneDrive support simultaneous editing with AutoSave, but protected ranges, some legacy features and macros restrict it, and the older Shared Workbook feature is legacy and best avoided. A weak answer says 'password protect it' and treats that as security.

Step order that people get wrong
1. Ctrl+A, Ctrl+1, Protection, ensure Locked is ticked (it already is)
2. Select only the input cells, Ctrl+1, Protection, untick Locked
3. Select formula cells, Ctrl+1, Protection, tick Hidden as well
4. Review, Protect Sheet, set a password, allow Select unlocked cells and AutoFilter

Find all formula cells fast
F5, Special, Formulas       or Ctrl+`  to toggle formula view

Stop structure changes
Review, Protect Workbook, tick Structure

Really hide a working sheet
Alt+F11, select the sheet, Properties, Visible = 2 xlSheetVeryHidden

Actual encryption, not the same thing as sheet protection
File, Info, Protect Workbook, Encrypt with Password

Allow a macro to edit a protected sheet without unprotecting it for the user
ws.Protect Password:="x", UserInterfaceOnly:=True

Answer

Four tools, four different questions, and the interviewer wants to see you pick rather than list. Goal Seek answers a single variable question: what value must this one input take for that one output cell to hit a target. Set cell must contain a formula, By changing cell must be a hard value, not a formula, which is the constraint people trip on.

It is a numerical search, so results are approximate and precision is governed by the iteration settings. Solver answers the multi variable, constrained question: maximise profit by changing the product mix, subject to capacity, minimum order quantities and integer constraints. It ships as an add in you enable from File, Options, Add-ins, offers GRG Nonlinear for smooth models, Simplex LP for linear ones, and Evolutionary for messy ones, and picking Simplex LP when the model is genuinely linear both runs faster and finds the true optimum.

Scenario Manager stores whole named sets of input values, base case, best case, worst case, and produces a summary comparing them, which is what management actually wants to see. A What-If data table answers sensitivity: one variable down the side or two variables across a grid, showing how the output moves across a whole range, which is the honest way to present a price and volume tradeoff. Do mention that data tables are volatile and recalculate with the sheet, so a large one is a common cause of a slow workbook. A weak answer knows only Goal Seek.

Goal Seek: what price gives break even
Data, What-If Analysis, Goal Seek
Set cell: $F$20 (profit formula)   To value: 0   By changing cell: $B$4 (unit price)

Solver: maximise contribution across three products
Data, Solver
Set Objective: $F$25   To: Max   By Changing: $B$10:$D$10
Subject to: $B$10:$D$10 <= $B$12:$D$12    (capacity)
            $B$10:$D$10 >= 0
            $B$10:$D$10 = integer
Solving Method: Simplex LP for a linear model

Scenario Manager: named input sets
Data, What-If Analysis, Scenario Manager, Add
Changing cells: $B$4,$B$5,$B$6
Add Base, Best, Worst, then Summary, Result cells: $F$20

Two variable data table: price down the side, volume across the top
Put =$F$20 in the top left corner of the grid
Select the whole grid, Data, What-If Analysis, Data Table
Row input cell: $B$5 (volume)   Column input cell: $B$4 (price)

Answer

Indian interviewers genuinely run this round, especially for MIS and back office roles at Genpact, WNS and Infosys BPM, because speed on a keyboard is a real productivity difference across a shift. The navigation core is Ctrl with arrow keys to jump to the edge of a data block, Ctrl+Shift with arrows to select to that edge, Ctrl+Home to return to A1 and Ctrl+End to reach the last used cell, which incidentally is how you discover why a file is fifty megabytes when the data ends at row 900. Selection and entry: Ctrl+A for the current region, Alt+= to insert a SUM over the block above, Ctrl+D and Ctrl+R to fill down and right, Ctrl+Enter to fill the same entry into a whole selection, and F2 to edit in place.

Formatting and structure: Ctrl+1 for the format dialog, Ctrl+T for a Table, Ctrl+Shift+L to toggle filters, Alt+; to select visible cells only, which is the one that saves you from copying hidden rows. Paste Special is Ctrl+Alt+V, F4 cycles reference locking while editing and repeats the last action otherwise, and Ctrl+` toggles formula view for a quick audit. Alt opens the ribbon key tips, which is how you reach anything without knowing its shortcut.

A weak answer recites Ctrl+C and Ctrl+V. Practise Alt+; and Ctrl+Shift+arrow until they are automatic, because those two are what the panel is watching for.

Navigate
Ctrl+Arrow            jump to the edge of the data block
Ctrl+Shift+Arrow      select to that edge
Ctrl+Home / Ctrl+End  first cell / last used cell
Ctrl+PgUp / PgDn      previous / next sheet

Select and enter
Ctrl+A                current region
Ctrl+Enter            fill the same entry into every selected cell
Ctrl+D / Ctrl+R       fill down / fill right
Alt+=                 insert SUM over the block above
Alt+;                 select visible cells only, essential after filtering
F5 then Special       go to blanks, formulas, constants, errors

Format and structure
Ctrl+1                format cells
Ctrl+T                create a Table
Ctrl+Shift+L          toggle filters
Ctrl+Alt+V            paste special
Ctrl+`                toggle formula view
Alt+H+O+I             autofit column width
F4                    cycle reference locking, or repeat the last action

Answer

State the plan out loud first, because the panel scores the sequence as much as the output. Minutes zero to five, validate: check the row count, confirm the date column is numeric and not text, check the amount column is numeric, look for blanks in the key dimensions, and reconcile the grand total against any number they gave you. Say what you find, even if it is nothing.

Minutes five to ten, structure: Ctrl+T to make a Table and name it, add only the derived columns you will need such as a month key and a fiscal quarter, and delete nothing from the source. Minutes ten to twenty, build: one pivot cache, then pivots for revenue by month, revenue by branch sorted descending, revenue by category, and a top ten customers view, plus a KPI block with total revenue, order count, average order value and month on month growth. Minutes twenty to twenty five, present: place the KPI block top left where the eye lands, charts to the right, add slicers for branch and category, connect them to every pivot through Report Connections, and use lakh or crore formatting rather than raw digits.

Last five minutes, finish: freeze panes, set the print area, hide working sheets, and write two lines of observation, for example which branch drove the movement and one data caveat. That closing observation is what separates an offer from a pass. A weak answer starts formatting cells in the first two minutes.

Validation checks to run first
=COUNTA($A$2:$A$100000)                            row count
=SUMPRODUCT(1*ISTEXT($A$2:$A$100000))              text dates hiding in the date column
=SUMPRODUCT(1*ISTEXT($D$2:$D$100000))              text amounts
=COUNTBLANK($B$2:$B$100000)                        missing branch
=SUM($D$2:$D$100000)                               grand total to reconcile

Derived columns inside the Table
=TEXT([@Date],"yyyy-mm")                            sortable month key
="FY"&TEXT(YEAR([@Date])-(MONTH([@Date])<4),"0000") Indian fiscal year

KPI block
Revenue        =SUM(tblSales[Amount])
Orders         =COUNTA(tblSales[OrderID])
AOV            =DIVIDE, or  =IFERROR(SUM(tblSales[Amount])/COUNTA(tblSales[OrderID]),0)
MoM growth     =IFERROR($C$3/$C$2-1,"")
Top branch     =INDEX(tblSales[Branch],MATCH(MAX(tblSales[Amount]),tblSales[Amount],0))

Crore and lakh number format for the KPI cells
[>=10000000]₹0.00,,," Cr";[>=100000]₹0.00,," L";₹#,##0

Answer

Do not start matching until you have defined the key. In an Indian bank reconciliation the usable keys are the UTR or cheque number, and where those are absent, a composite of date and amount with a tolerance window because a payment initiated on the twenty ninth may clear on the second. Normalise both sides first: trim and uppercase the reference, strip spaces and CHAR(160), make amounts numeric, and put credits and debits into one signed column so the sign convention matches, because banks and ledgers frequently disagree on which side is positive.

Then run three outputs rather than one. Matched, which is the intersection. In ledger not in bank, which is typically cheques issued but not presented, or a payment entered twice.

In bank not in ledger, which is typically bank charges, GST on charges, interest credited, direct debits and auto sweeps that nobody journalised. The formula route is COUNTIFS on the cleaned key both ways, or SUMIFS by key when a single reference carries several rows. The better route on four thousand rows is Power Query with two anti joins, which gives you both exception lists in one refreshable query and means next month is a button.

Finish with a reconciliation statement that starts from the bank balance and walks to the book balance line by line, because a list of exceptions is not a reconciliation. A weak answer sorts both columns and compares visually.

Normalise the key on both sheets first
=UPPER(TRIM(SUBSTITUTE(SUBSTITUTE($C2,CHAR(160),"")," ","")))

Signed amount so the two conventions agree
=IF($E2="CR",1,-1)*ABS($D2)

Presence checks both ways
=IF(COUNTIFS(Bank!$K:$K,$K2)=0,"Not in bank","")
=IF(COUNTIFS(Ledger!$K:$K,$K2)=0,"Not in ledger","")

Amount check where the reference matches but the value does not
=IF(ROUND(SUMIFS(Bank!$L:$L,Bank!$K:$K,$K2),2)<>ROUND($L2,2),"Amount mismatch","")

Date and amount fallback within a 5 day window
=COUNTIFS(Bank!$L:$L,$L2,Bank!$A:$A,">="&$A2-5,Bank!$A:$A,"<="&$A2+5)

Power Query route
Merge Ledger to Bank on UTR, Join Kind Left Anti   -> in ledger, not in bank
Merge Bank to Ledger on UTR, Join Kind Left Anti   -> in bank, not in ledger

Prove the difference adds up
=SUM(NotInBank)-SUM(NotInLedger)     should equal the ₹2.7 lakh gap

Answer

Work backwards through the dependency chain instead of rebuilding the sheet. Start on F42 and use Formulas, Trace Precedents, which draws arrows to every cell it reads, and press it repeatedly to walk up level by level. Dashed arrows with a small worksheet icon mean the precedent is on another sheet or in another workbook, and double clicking that arrow jumps you there.

Trace Dependents does the reverse and tells you who else consumes this number, which matters before you change anything. For a long formula, select part of it in the formula bar and press F9 to evaluate just that fragment in place, then press Escape rather than Enter so you do not overwrite the formula with its value. Evaluate Formula steps through the whole expression one operation at a time, which is the fastest way to find the branch of an IF that is firing unexpectedly.

Then check the usual suspects in order: a SUM range that no longer covers the added rows, a filter still applied so SUM includes hidden rows that SUBTOTAL would not, text numbers being ignored, a hard coded value pasted over a formula which you find with F5 Special Constants inside a formula block, an IFERROR masking a broken reference, a manual calculation mode leaving stale results, and a link to a closed workbook holding last month's value. Say which one it was and how you would prevent it. A weak answer starts retyping the formula.

Trace the chain
Formulas, Trace Precedents (press repeatedly to go up levels)
Formulas, Trace Dependents (who else reads this)
Double click a dashed arrow to jump to an external precedent

Evaluate a fragment safely
Select the fragment in the formula bar, press F9, read the value, press Esc
Formulas, Evaluate Formula to step through it operation by operation

Find hard coded values pasted over formulas
Select the block, F5, Special, Constants (in a range that should be all formulas)
Ctrl+`  to see the whole sheet as formulas at once

The usual suspects, checked with a formula
=SUM($D$2:$D$500)=SUBTOTAL(109,$D$2:$D$500)     FALSE means rows are hidden or filtered
=SUMPRODUCT(1*ISTEXT($D$2:$D$500))               text numbers being skipped
=COUNTIF($D$2:$D$500,"#REF!")                    broken references under an IFERROR
Formulas, Calculation Options                     confirm it is not on Manual
Data, Edit Links                                  a stale link to a closed workbook

Answer

Work through causes in order of frequency. First, volatile functions, which force recalculation of themselves and everything downstream on every single change: NOW, TODAY, RAND, RANDBETWEEN, OFFSET, INDIRECT, INFO and CELL with certain arguments. A few thousand OFFSET based dynamic ranges will produce exactly this symptom, and the fix is INDEX based ranges or a Table, and XLOOKUP or INDEX with MATCH instead of INDIRECT.

Second, full column references. SUMIFS across A:A repeated ten thousand times evaluates far more than it needs to, and SUMPRODUCT over whole columns is worse because it does not get the same optimisation. Bound the ranges or use structured references, which are bounded by definition.

Third, used range bloat, where Ctrl+End lands at row 1,048,576 because formatting or stray spaces were applied to entire columns. Delete the empty rows and columns properly, save, reopen, and the file often drops by tens of megabytes. Fourth, conditional formatting and data validation applied to whole columns, and rules fragmented into hundreds of ranges by copy pasting.

Fifth, array formulas and What-If data tables, which are volatile as a class. Sixth, links to closed workbooks and heavy add ins. The interim workaround is Formulas, Calculation Options, Manual with F9 to recalculate deliberately, but say clearly that it is a workaround and a stale number risk, not a fix.

The structural fix, if the file is genuinely large, is moving the data into Power Query and the data model. A weak answer only says 'switch to manual calculation'.

Find the damage
Ctrl+End                      lands far past your data means used range bloat
File size against row count   a 40 MB file over 20,000 rows is bloat, not data
Home, Conditional Formatting, Manage Rules, This Worksheet
Formulas, Name Manager        look for OFFSET based dynamic ranges
Data, Edit Links              links to closed workbooks

Replace the volatile patterns
Was:  =OFFSET($A$1,0,0,COUNTA($A:$A),8)
Now:  a Table, or  =$A$1:INDEX($H:$H,COUNTA($A:$A))

Was:  =INDIRECT("Sheet"&$B2&"!C5")
Now:  a single stacked table with a Sheet column, plus SUMIFS or XLOOKUP

Bound the ranges
Was:  =SUMIFS($D:$D,$B:$B,$F2)
Now:  =SUMIFS(tblSales[Amount],tblSales[Branch],$F2)

Reclaim the used range
Select the first empty row, Ctrl+Shift+Down, right click, Delete
Repeat for columns, then save, close and reopen

Interim only, and say so
Formulas, Calculation Options, Manual   then F9 to recalculate, Shift+F9 for this sheet

Answer

In older perpetual Excel, XLOOKUP, LET, LAMBDA, FILTER, SORT, UNIQUE, SEQUENCE, TEXTSPLIT, TEXTBEFORE and TEXTAFTER do not exist, so every formula using them opens as _xlfn.XLOOKUP or _xlfn._xlws.FILTER and returns #NAME?. Nothing is corrupted, and the formulas revive if the same file is reopened in a version that has the function, but the client sees a broken report, which is the outcome that matters. There is no compatibility shim: the Compatibility Checker warns about some things, but a missing function is simply missing.

The practical response has three parts. Ask which Excel the audience runs before you build, because a perpetual Excel 2019 or 2021 licence is still common in Indian finance teams and government facing work. Where the audience is mixed, write the portable equivalents: INDEX with MATCH instead of XLOOKUP, IFERROR instead of if_not_found, helper columns or an array entered formula instead of FILTER, and a pivot instead of UNIQUE.

Or send values rather than formulas by delivering a PDF or a values only copy for consumers who only read the report. Google Sheets is a different mix: it has XLOOKUP, FILTER, SORT, UNIQUE, SEQUENCE, LET and LAMBDA, so those travel, but pivot behaviour, conditional formatting rules, Power Query, Power Pivot, the data model, slicers on Tables, VBA and most custom number formats do not survive the conversion, and very large files hit the Sheets cell limit. A weak answer says 'it should be fine' without knowing the _xlfn prefix.

What the older Excel shows
=_xlfn.XLOOKUP(A2,Master!A:A,Master!D:D)     displays #NAME?
=_xlfn.LET(x,1,x+1)                          displays #NAME?
=_xlfn._xlws.FILTER(A2:D99,B2:B99="MUM")     displays #NAME?

Portable rewrites that work everywhere back to Excel 2010
=IFERROR(INDEX(Master!$D:$D,MATCH($A2,Master!$A:$A,0)),"Not found")
=IFERROR(INDEX($D$2:$D$999,SMALL(IF($B$2:$B$999=$H$1,ROW($B$2:$B$999)-1),ROW(1:1))),"")
   (older versions need Ctrl+Shift+Enter for that one)
Distinct list without UNIQUE: a pivot, or Data, Advanced Filter, Unique records only

Check before you send
File, Info, Check for Issues, Check Compatibility
Save As, Excel 97-2003 Workbook, only to see the warnings, then cancel

Survives an export to Google Sheets: XLOOKUP, FILTER, SORT, UNIQUE, SEQUENCE, LET, LAMBDA
Does not survive: Power Query, Power Pivot and the data model, slicers on Tables,
VBA, most custom number formats, several conditional formatting rule types

Answer

Give thresholds, not philosophy. Move the data layer to SQL or a warehouse when the source is bigger than a sheet can hold or is refreshed by a system rather than a person, when several people need the same definition of a metric, or when you find yourself downloading the same export every morning. SQL is where joins, history and the single agreed definition of revenue should live, and it also solves the version problem, since there is one table rather than nine files named final_v3.

Move the presentation layer to Power BI when the report has more than a handful of consumers, when it must refresh on a schedule without anyone opening a file, when row level security matters so a branch manager sees only their branch, or when the same numbers are needed on a phone. Keep the work in Excel when the task is genuinely ad hoc, when the recipient will want to poke at the numbers themselves, when the logic must be visible in the grid for audit, and for small models and one off reconciliations, which is a very large share of real finance work. Honest thresholds: past roughly a few hundred thousand rows Excel gets slow enough to hurt, past a million it cannot hold the data in the grid at all, and more than three or four people editing the same workbook means you have already outgrown it.

The mature answer says the three tools are a stack rather than rivals: SQL or Power Query prepares, the data model holds the logic, and Excel or Power BI presents. A weak answer defends Excel for everything, or dismisses it as outdated. Both read as inexperience.

Thresholds worth stating out loud
Under ~100k rows, single user, ad hoc          -> Excel
100k to ~1M rows, repeating monthly            -> Power Query plus the data model
Over ~1M rows, or system generated             -> SQL or a warehouse
More than a handful of consumers               -> Power BI
Scheduled refresh, no human opening a file     -> Power BI service or a pipeline
Row level security by branch or region         -> Power BI, not a workbook per branch
Audit needs the logic visible in the grid      -> stay in Excel

The stack, not a competition
SQL / Power Query   prepare and join
Data model / DAX    hold the metric definitions once
Excel / Power BI    present, and let people ask the next question

The same measure in each layer
SQL   SELECT branch, SUM(amount) AS revenue FROM sales GROUP BY branch;
DAX   Total Sales := SUM(Sales[Amount])
Excel =SUMIFS(tblSales[Amount],tblSales[Branch],$A2)