Microsoft excel Interview Questions

Note: Everyone claims to know Excel. The interviewer is trying to find out whether you use it as a grid or as a tool. Describe something with structure, not just formatting.

Give:

• The business purpose. A budget model, a reconciliation, a reporting pack, a forecast, or a tracker used by a team.
• The scale. Number of rows, number of source files, how many people used it, and how often it ran. "A model consolidating twelve regional files monthly" is concrete.
• What made it non-trivial. Strong answers: Power Query pulling and cleaning from multiple sources, a Power Pivot data model with DAX measures, scenario analysis with data tables, or a validated input sheet that stopped users breaking it.
• The outcome — hours saved, errors eliminated, or a decision it enabled.

Being able to say "I later moved it out of Excel because it had outgrown it" is a strong sign of judgement.

Spreadsheet errors are common and expensive, so a structured answer stands out.

• Separate inputs, calculations, and outputs. Ideally on different sheets, with a consistent colour convention — for example blue for hard-coded inputs, black for formulas. Anyone can then see immediately where a number comes from.
• Never hard-code a number inside a formula. A tax rate typed into forty formulas is impossible to update reliably. Put it in one labelled cell and reference it.
• Build in checks. A row that confirms a balance sheet balances, that percentages sum to 100, or that a total matches its source — flagged conditionally so it is impossible to miss.
• Use data validation on input cells to constrain what can be entered, and protect formula cells so they cannot be overwritten.
• Use named ranges and tables so formulas read meaningfully and expand automatically with new rows.
• Have someone else review it, and use Trace Precedents and Dependents when checking unfamiliar work.

Note: Version control is worth raising as a genuine weakness of Excel. Saying you kept the file in SharePoint or OneDrive with version history, and a dated changelog tab, shows you have felt that pain.

This is the highest-value Excel story you can tell, because it converts your skill directly into money saved.

Structure it as:

• The manual process and its cost. Be specific: "four hours every Monday copying data from six exports and reformatting it" — that is roughly 200 hours a year, and stating it that way makes the impact obvious.
• Why it was error-prone, not just slow. Manual copy-paste introduces mistakes, and mistakes in a report that drives decisions are the real cost.
• What you built. Power Query is usually the right answer for anything involving importing and cleaning — it is repeatable, refreshable, and does not require anyone to learn VBA to maintain it. A macro is appropriate for interface automation. Say why you chose what you chose.
• The result. Time reduced, errors eliminated, and — importantly — whether it survived after you handed it over.

Note: Mention documenting it and training someone else. An automation only one person understands is a liability, and interviewers who have inherited an undocumented macro workbook feel this strongly.

Showing you know Excel's limits is more impressive than claiming it can do anything.

Signs it has outgrown Excel:

• Data volume. Excel's limit is roughly a million rows per sheet, but practical trouble starts far earlier — files that take minutes to open or recalculate are already past the point.
• Multiple concurrent editors. Excel handles this poorly even with co-authoring; conflicts and overwritten work follow.
• The same report rebuilt repeatedly. If the workbook is refreshed and distributed on a schedule, that is a BI tool's job — Power BI with a scheduled refresh.
• Real relational structure. Once you are managing several related tables with keys, a database will do it better and more safely.
• Audit and access requirements. If you need row-level permissions or a change trail, Excel cannot provide them.

The pragmatic middle ground is worth naming: Power Query and Power Pivot handle far more than plain worksheets, and keeping Excel as the presentation layer over a proper data source is often the right compromise.

Note: Say that migration should follow a real problem rather than a preference — recommending a rebuild for a file that works fine is a bad trade.

The skill being tested is editing — deciding what to leave out.

• Lead with the conclusion. The first thing on the page should be what the data means and what you recommend, not the data itself. Detail supports the message; it does not replace it.
• One idea per chart. If a chart needs a paragraph to explain, split it. Title the chart with the finding — "North region drove 60% of Q3 growth" — rather than "Revenue by Region".
• Choose the right chart. Line for change over time, bar for comparison across categories, and avoid pie charts beyond a few slices. Skip 3D and heavy styling entirely.
• Format for reading. Round to a sensible precision — nobody needs four decimal places — use consistent units, and label directly rather than forcing a trip to a legend.
• Keep the detail available but separate. An appendix or a linked workbook for anyone who asks, so the main view stays clean.

Note: Mentioning that you state assumptions and data caveats up front is a strong point. A number presented without its assumptions gets challenged later, and by then trust in the whole analysis is damaged.

This tests two things: your technical eye and your professionalism. Interviewers want to see that you verified before raising the alarm, handled the owner with tact, and fixed the root cause, not just the number.

Structure it with STAR:

• Situation: what the file was and why it mattered, e.g. a monthly incentive calculation used to pay 120 sales staff.
• Task: your role. Perhaps you were preparing a summary from it, not auditing it.
• Action: how you spotted the error, how you confirmed it, and how you raised it.
• Result: the quantified impact and what changed afterwards.

Example points to draw on:

• How you found it: totals did not tie to the ledger; a VLOOKUP with approximate match returned the wrong slab; a SUM range stopped at row 500 while data ran to 640; a hard-coded number had overwritten a formula mid-column.
• How you verified: recalculated a few cases by hand, used Trace Precedents and Show Formulas, and cross-checked with a PivotTable before saying anything.
• How you raised it: privately with the owner first, framed as "I think I have found something, can you check my reasoning?" rather than blame; then with your manager, with the impact quantified, because payments were affected.
• How you fixed it: corrected the formula, converted the data to an Excel Table so ranges grow automatically, and added a check cell that turns red when totals do not reconcile.
• Result: e.g. an overpayment of about 3.2 lakh prevented, and the same check caught another issue two months later.

What to avoid: portraying the colleague as careless, or implying you quietly fixed it without telling anyone. Silent fixes in shared files cause confusion and damage trust.

Note: End with the process improvement. "I fixed the number" is fine; "I added controls so it cannot recur" is what makes the answer strong.

This situational question checks whether you are methodical and risk-aware. A strong answer shows you would understand the workbook before changing it, and leave it better documented than you found it. If you have done this for real, tell that story; otherwise walk through your approach.

A structured approach:

1. Protect first: save a dated copy before touching anything, and note which version is live.
2. Understand the purpose: talk to the previous owner if possible, and to the users. Which outputs do they rely on, how often, and what decisions do they make from them?
3. Map the structure: list the sheets and classify them as inputs, calculations and outputs. Check Name Manager, external links (Data > Edit Links), hidden sheets, macros, Power Query connections and pivot sources.
4. Trace the key outputs backwards: use Trace Precedents, Evaluate Formula and Show Formulas on the numbers that matter most. Inquire or Spreadsheet Compare, where available, help map dependencies.
5. Run one cycle in parallel: reproduce last month's output and reconcile it to what was actually sent. Differences reveal manual steps nobody wrote down.
6. Document as you go: a README sheet with purpose, data sources, refresh steps, owners and known issues.
7. Improve gradually: fix clear risks first (hard-coded numbers in formulas, broken links, fixed ranges), then simplify.

Example points: "I found three hidden sheets feeding the summary and a macro that pasted values over formulas each month. I rebuilt that step in Power Query and wrote a one-page runbook, so the next handover took a day instead of a week."

Note: Stress that you would not redesign on day one. Interviewers are wary of candidates who rebuild everything before understanding why it was built that way.

The interviewer is looking for prioritisation, calm under pressure and, critically, evidence that speed did not come at the cost of accuracy. Use STAR and be specific about the time pressure.

Situation and task: e.g. the CFO needed a region-wise revenue and collections analysis for a board meeting the next morning, and the data extract arrived only at 4 pm.

Actions worth describing:

• Clarify scope immediately: a five-minute conversation to confirm the three questions the report must answer, and what could wait. This alone saves hours.
• Reuse, do not rebuild: start from an existing template, put the data in an Excel Table, and use Power Query if the extract is messy.
• Work efficiently: keyboard shortcuts, XLOOKUP and SUMIFS instead of manual matching, PivotTables for the cuts, and one simple chart per key message.
• Protect accuracy: reconcile totals to the source system, spot-check a few records by hand, and ask a colleague for a ten-minute review before sending.
• Communicate: send an interim version or flag early if something cannot be completed, with caveats stated clearly.

Result: quantify it. For example, delivered by 9 pm, numbers tied to the ledger, and the board used it to change credit terms for two regions.

Reflection: close with what you changed afterwards, such as building a refreshable template so the same report now takes 20 minutes each month. That turns a firefighting story into evidence of process thinking.

Note: Never tell a story where you skipped checks and "got away with it". Interviewers hear that as a risk. The strong version is fast and verified.

This question explores communication, patience and your ability to multiply your impact across a team. It is common for analyst and MIS roles, where you often become the team's go-to Excel person.

Structure:

• Situation: what triggered it, such as repeated questions, errors in shared files, or a team spending hours on manual work.
• What you did: the format and content, and how you tailored it to their level.
• Result: a measurable change, not just "they liked it".

Example points:

• "Our operations team of eight were matching data by eye and using VLOOKUP without FALSE, which caused wrong matches."
• "I ran four 45-minute sessions using our own data rather than generic examples: XLOOKUP, SUMIFS, PivotTables, and cleaning with TRIM and Remove Duplicates."
• "Each session ended with a small task on a real file, and I shared a one-page cheat sheet and short screen recordings."
• "I kept a Friday afternoon slot open for questions."
• "The weekly report that took one person a full day now takes two hours, and matching errors dropped to almost zero."

Show adaptability: mention how you handled mixed levels, for example pairing experienced users with beginners or giving advanced users an extra challenge, and how you explained ideas like absolute references in plain language rather than jargon.

If you have not formally trained anyone, use an informal example: helping a colleague fix a formula and explaining it so they could do it themselves next time. The principle of teaching rather than just fixing is what matters.

Note: Focus on the outcome for the team. Interviewers care less about how much you know than about whether you raise the capability of the people around you.

Assumptions change constantly: a new GST rate, revised sales targets, different pricing, a delayed launch. This question checks whether your models are built to flex, and whether you manage stakeholders well when the numbers move.

Structure with STAR:

• Situation: e.g. a budget model for a product launch, built on a July launch and a 12% discount.
• Change: two weeks before the review, the launch slipped to October and marketing wanted to test three discount levels.
• Action: how you adapted the model, and how its design made that easy or hard.
• Result: what the decision-makers could do as a result.

Example points on the model:

• "Every assumption sat on one Inputs sheet and nothing was hard-coded in formulas, so changing the launch month was a single cell."
• "I added a scenario selector with Base, Conservative and Aggressive cases, pulled into the model with XLOOKUP from a scenario table."
• "I built a two-variable Data Table showing profit across discount levels and launch months, so management could see the sensitivity at a glance."
• "I kept a change log of what moved, when and why, and showed the old versus new profit in a simple bridge chart."

If your model was not flexible, be honest: explain that you restructured it to separate inputs from calculations, and that you now build that way from the start. Learning is a valid result.

Stakeholder side: explain that you described the impact in business terms, such as "the delay reduces this year's profit by 1.8 crore, and the higher discount only pays back if volume rises by 15%", rather than just sending a new file.

Note: The key message is that good models expect change. Separating inputs, calculations and outputs is what makes adapting fast and safe.

All three retrieve a value from a table; they differ in flexibility and robustness.

VLOOKUP — =VLOOKUP(value, table, col_index, FALSE). Its limitations are why the others exist:

• It can only look rightwards — the lookup column must be leftmost.
• The column index is a hard-coded number, so inserting a column silently returns the wrong data. This is the dangerous one, because nothing errors.
• Always pass FALSE for exact match; omitting it defaults to approximate and returns wrong answers on unsorted data.

INDEX-MATCH — =INDEX(return_range, MATCH(value, lookup_range, 0)). INDEX returns a value at a position; MATCH finds the position. Together they look in any direction, break nothing when columns move, and are faster on large datasets because only two columns are referenced.

XLOOKUP — =XLOOKUP(value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). The modern answer: searches any direction, defaults to exact match, has built-in not-found handling instead of wrapping in IFERROR, can search from the bottom up, and can return an entire row or column.

Note: Use XLOOKUP where available, INDEX-MATCH for compatibility with older versions. The compatibility point matters — XLOOKUP requires Microsoft 365 or Excel 2021 and breaks for anyone on an older build.

A PivotTable summarises a large table by grouping and aggregating, without writing a single formula. You drag fields into four areas — Rows, Columns, Values, and Filters — and Excel produces the cross-tabulation.

When to use one: any time the question is "what is the total, average, or count of X, broken down by Y". Sales by region and month, headcount by department, spend by category — these take seconds in a PivotTable and would need a page of SUMIFS otherwise.

What the source data must look like — this is what people get wrong:

• One header row, no blank rows or columns, no merged cells.
• Flat, not cross-tabulated. Months should be values in one column, not twelve separate columns.
• Consistent data types per column.
• Formatted as an Excel Table so the range grows automatically.

Beyond the basics: Value Field Settings changes the aggregation and can display values as a percentage of total or a running total. Slicers and timelines give clean interactive filtering. Calculated fields add derived measures. Grouping turns dates into months or quarters automatically.

Note: Two practical points: PivotTables do not refresh automatically, so stale numbers are a common error; and for multiple related tables, adding them to the Data Model lets one PivotTable span several tables through relationships.

A reference decides what happens when a formula is copied — controlled by the dollar sign, which locks the part it precedes.

• Relative — A1. Both parts shift. Copy a formula one column right and one row down, and it becomes B2. This is the default and is what you want most of the time.
• Absolute — $A$1. Neither part shifts. Use it for a constant referenced from many formulas — a tax rate, an exchange rate, a threshold.
• Mixed — $A1 locks the column and lets the row move; A$1 locks the row and lets the column move.

Mixed references are what the question is really testing, because they are what makes a two-dimensional grid work. In a multiplication table or a rate matrix where headers run across the top and down the side, one formula written as =$A2*B$1 can be filled across the entire grid — the column lock keeps it reading the row labels, the row lock keeps it reading the column headers.

Note: F4 cycles through the four combinations while editing. And mention named ranges as the more readable alternative — =Price*TaxRate is self-documenting in a way that =B2*$F$1 is not, and named ranges are absolute by default.

The strategic answer first: for anything you will do more than once, use Power Query. Manual cleaning is a one-off; a Power Query transformation is recorded as steps, re-runs on refresh, and documents itself. That distinction is what interviewers want to hear.

Common problems and their fixes:

• Duplicates — Data → Remove Duplicates, or COUNTIF to identify them first if you want to inspect before deleting.
• Whitespace and non-printing characters — TRIM() and CLEAN(). Invisible trailing spaces are the single most common cause of a lookup failing on values that look identical.
• Numbers stored as text — the green triangle, Text to Columns with no delimiter, or VALUE(). These break SUM silently.
• Splitting and combining — Text to Columns, Flash Fill for pattern-based extraction, TEXTSPLIT and TEXTJOIN in newer versions.
• Inconsistent case and spelling — PROPER(), UPPER(), LOWER(), and a lookup table mapping variants to a standard value.
• Blanks and errors — Go To Special to select them in bulk, and IFERROR() to control what displays.
• Inconsistent dates — usually the hardest, because a text date and a real date look alike. DATEVALUE() or Power Query's locale-aware type conversion.

Note: Always keep the raw data untouched on its own sheet and clean into a copy, so you can trace anything back.

Conditional aggregation — the workhorses:

• SUMIFS, COUNTIFS, AVERAGEIFS — aggregate with multiple criteria. These replace most manual filtering.

Lookup:

• XLOOKUP or INDEX with MATCH for retrieving related values.

Logic:

• IF, and IFS for multiple conditions without nesting six levels deep.
• AND, OR, NOT to combine tests.
• IFERROR to handle failures gracefully — but use it deliberately, since it hides genuine problems as well as expected ones.

Text:

• TEXTJOIN, LEFT, RIGHT, MID, LEN, SUBSTITUTE, TRIM.

Date:

• EOMONTH, EDATE, DATEDIF, NETWORKDAYS, YEAR, MONTH.

Dynamic arrays, which changed how modern Excel is written:

• FILTER — returns rows matching a condition, spilling into a range. This alone replaces a great deal of manual work.
• UNIQUE, SORT, SEQUENCE.
• LET to name intermediate calculations inside a formula, which makes long formulas readable and faster.
• LAMBDA to define reusable custom functions without VBA.

Note: Naming FILTER, LET, and LAMBDA signals you have kept up. Many candidates are still describing Excel 2010.

Power Query — labelled Get & Transform — is a data connection and transformation engine built into Excel. You connect to a source, apply a sequence of transformation steps through the interface, and load the result. Every step is recorded, so refreshing re-runs the whole pipeline against new data.

How it differs from formulas:

• It is repeatable by design. Formulas transform data already in the sheet; Power Query defines a process that runs again whenever the source changes. Replacing a monthly manual clean-up with a refresh button is the whole point.
• It handles far more data. Transformation happens outside the worksheet grid, so you can process millions of rows and load only the summary.
• It connects to real sources — databases, folders of files, web APIs, SharePoint — not just what you pasted in.
• The steps are visible and editable. The Applied Steps pane is self-documenting in a way that a nested formula is not.

What it is especially good at: combining every file in a folder into one table, unpivoting cross-tabulated data into the flat format PivotTables need, merging tables the way a SQL join would, and splitting or reshaping columns.

Note: The rule of thumb worth stating: use Power Query to get data into shape, and formulas or PivotTables to analyse it once it is there. Mixing the two — cleaning with formulas that must be dragged down every month — is what Power Query exists to eliminate.

Conditional formatting applies formatting based on a rule, so the formatting updates automatically as values change.

The built-in types: highlight rules (greater than, between, text contains, duplicate values), top and bottom rules, data bars, colour scales, and icon sets.

Formula-based rules are where the real power is. Under "Use a formula to determine which cells to format", the formula is written for the top-left cell of the selection and applied relatively to the rest. Two patterns cover most needs:

• Highlight an entire row based on one column: select the whole range and use =$E2="Overdue". The column is locked with a dollar sign so every cell in the row tests column E; the row is not, so it moves down.
• Compare against another cell: =B2>$C$2 to flag anything above a threshold held in one place.

Using it effectively:

• Highlight exceptions, not everything. If most of the sheet is coloured, nothing stands out and the formatting has failed.
• Do not rely on colour alone — around one in twelve men has some colour vision deficiency. Pair colour with an icon or text.
• Keep rules few and manage them centrally through Manage Rules; overlapping rules applied ad hoc become impossible to reason about, and a large number of them slows the workbook noticeably.

VBA (Visual Basic for Applications) is the programming language embedded in Office. A macro is a stored VBA procedure. The macro recorder captures your actions as code, which is a useful starting point but produces verbose, fragile code that relies on selecting cells.

When VBA is the right tool:

• Automating interface actions — formatting, printing, creating sheets, moving files.
• Building custom user forms for data entry.
• Custom functions that dynamic arrays and LAMBDA cannot express.
• Interacting with other applications, such as generating Outlook emails from a list.
• Loops with genuinely complex conditional logic.

When it is the wrong tool — and this is the more important half:

• Importing and cleaning data. Use Power Query. It is more maintainable, faster, and does not require anyone to read code.
• Calculations. Formulas and dynamic arrays are clearer and recalculate automatically.
• Anything that needs to run unattended or in Excel Online, where VBA does not run at all. Office Scripts or Power Automate fit better.

Practical cautions: macro-enabled files are .xlsm and are frequently blocked by security policy; VBA actions generally cannot be undone; and avoid Select and Activate — work with object references directly, which is faster and far more reliable.

First, work out what is slow — opening, calculating, or scrolling. They have different causes.

The usual culprits and fixes:

• Volatile functions — NOW, TODAY, RAND, OFFSET, INDIRECT, and CELL recalculate on every change to the workbook, not just when their inputs change. Replacing OFFSET and INDIRECT with INDEX is often a dramatic improvement.
• Whole-column references — SUMIF(A:A, ...) processes a million rows. Use a Table reference or a bounded range instead.
• Array formulas over huge ranges, and SUMPRODUCT across entire columns.
• Excessive conditional formatting, which is re-evaluated constantly and quietly duplicates rules when rows are copied.
• Unused cells with formatting. If Ctrl+End goes far beyond your data, delete the empty rows and columns and save — this alone often shrinks a file dramatically.
• Too many links to other workbooks, and images or shapes accumulated over time.

For genuinely large data, change approach rather than optimise:

• Power Pivot and the Data Model hold far more than a worksheet, compress heavily, and are built for aggregation.
• Power Query to filter and aggregate at the source so you load only what you need.
• Manual calculation mode while building, then F9 to recalculate.

Note: Saving as .xlsb rather than .xlsx is a quick practical win — the binary format is smaller and opens faster.

An Excel Table — created with Ctrl+T — is a defined object rather than a loose collection of cells, and it changes several behaviours:

• It expands automatically. Add a row and formulas, formatting, PivotTable sources, chart ranges, and data validation all extend with it. This alone removes a whole category of "the report missed the last month" errors.
• Structured references. =SUM(Sales[Revenue]) instead of =SUM(C2:C5000) — readable, and it never points at the wrong range.
• Formulas fill down automatically across the column.
• Built-in filter buttons, banded rows, and a total row with a dropdown of aggregations.
• A header row that stays visible when scrolling, without freezing panes.

Named ranges give a meaningful name to a cell or range. =Revenue-Costs is self-explanatory where =B12-B18 is not, and if a constant is used in fifty formulas, changing it in one named cell updates everything.

Note: Two practical points. Rename tables immediately — Table1 and Table2 defeat the purpose. And be aware that structured references behave differently inside and outside the table, and that some older features and shared workbook functionality do not work with tables, which is occasionally why you would keep a plain range.

A two-way lookup returns the value at the intersection of a row label and a column label, for example sales for a chosen region (rows) in a chosen month (columns).

INDEX with two MATCHes works in every Excel version:

=INDEX(B2:M20, MATCH(P2, A2:A20, 0), MATCH(P3, B1:M1, 0))

• The first MATCH returns the row position of the region typed in P2.
• The second MATCH returns the column position of the month in P3.
• INDEX returns the cell at that row and column of the data block.

Nested XLOOKUP (Microsoft 365 and Excel 2021 onwards):

=XLOOKUP(P2, A2:A20, XLOOKUP(P3, B1:M1, B2:M20))

The inner XLOOKUP returns the entire column for the chosen month; the outer one then picks the region's row from that column. Either can take an if_not_found argument for a clean message instead of #N/A.

Points interviewers look for:

• Use exact match (0 in MATCH) and absolute references if the formula will be copied.
• The header ranges must line up exactly with the data block. If B1:M1 is one column wider than B2:M20, results shift silently.
• Driving P2 and P3 from drop-down lists turns this into a simple interactive lookup panel.
• Both approaches are far more robust than VLOOKUP with a hard-coded column number, which breaks when a column is inserted.

Note: If the data is a flat table (Region, Month, Sales as columns) rather than a grid, the same question is really a multi-criteria lookup or a SUMIFS, not an INDEX-MATCH-MATCH.

When no single column uniquely identifies a row, for example the price of a product in a particular region, you need to match on two or more columns at once. There are four common approaches.

1. XLOOKUP with Boolean logic (Microsoft 365):

=XLOOKUP(1, (A2:A500=G2)*(B2:B500=G3), C2:C500, "Not found")

Each comparison returns an array of TRUE/FALSE values. Multiplying them gives 1 only where every condition is true, and XLOOKUP finds the first 1.

2. INDEX-MATCH with the same logic, which works in older versions when entered with Ctrl+Shift+Enter:

=INDEX(C2:C500, MATCH(1, (A2:A500=G2)*(B2:B500=G3), 0))

3. A helper key column: concatenate the keys in the source, =A2&"-"&B2, and look up G2&"-"&G3. It is simple, fast on large data and easy to audit. The delimiter matters: without it, "AB" + "C" and "A" + "BC" produce the same key.

4. SUMIFS, when the result is numeric:

=SUMIFS(C2:C500, A2:A500, G2, B2:B500, G3)

This is fast and version-proof, but returns 0 rather than an error when nothing matches, and silently adds up duplicates.

Choosing: XLOOKUP for readability in modern Excel, a helper column for very large sheets or older versions, and SUMIFS only when you are certain each combination is unique.

Note: Before choosing, check that the combination really is unique with COUNTIFS. With duplicates, a lookup returns only the first match while SUMIFS adds them together, and neither tells you there is a problem.

Tax slabs, commission tiers, discount bands and grading scales are all "find the band this value falls into" problems. Approximate-match lookups solve them cleanly without long nested IFs.

Set up a slab table with the lower bound of each band, in ascending order:

XLOOKUP: =XLOOKUP(B2, E2:E5, F2:F5, , -1). A match_mode of -1 means "exact match, otherwise the next smaller item", so a sale of 6,20,000 returns 6%. In this mode XLOOKUP does not strictly need sorted data, though keeping the table sorted is still good practice.

VLOOKUP: =VLOOKUP(B2, E2:F5, 2, TRUE) does the same, but only if the first column is sorted ascending. On unsorted data it returns wrong answers without any error.

Progressive slabs such as Indian income tax are different. Each band is taxed at its own rate, so looking up a single rate and applying it to the whole income is wrong. Two correct patterns:

• Add a cumulative "tax up to this lower bound" column to the slab table and use =base + (income - lower) * rate, with base, lower and rate each looked up using match_mode -1.
• Or compute each band's portion with MAX(0, MIN(income, upper) - lower) * rate and sum across bands, which SUMPRODUCT can do in one formula.

Note: Keep slab values in a table, never inside the formula. When the Budget changes the slabs, you update one table rather than hunting through nested IFs across the workbook.

In Microsoft 365 and Excel 2021 onwards, a formula can return multiple values, which spill into neighbouring cells automatically. =UNIQUE(A2:A100) written in one cell fills as many rows as there are unique values. Only the top-left cell holds the formula; the rest shows it greyed out in the formula bar.

Key concepts:

• Spill range: the block the result occupies, outlined in blue when selected. It resizes automatically as the source data changes.
• Spilled range reference (#): =D2# refers to the whole spill range starting at D2, however big it currently is. =SUM(D2#) or =COUNTA(D2#) stays correct as the list grows or shrinks, which is far better than a fixed D2:D50. It also works as a drop-down source.
• #SPILL! error: the result cannot spill because something is in the way: a value, even a space, in the target cells, merged cells, or a formula placed inside an Excel Table, since tables do not support spilling. Select the error cell and Excel outlines the blocked area.
• Implicit intersection operator (@): when an older workbook opens in new Excel, a formula that used to return a single value may appear as =@A2:A10. The @ tells Excel to return one value (from the same row) rather than spilling, preserving the old behaviour. The same idea appears in table references like [@Sales], meaning "this row".

Why it matters: FILTER, SORT, UNIQUE, SEQUENCE and XLOOKUP returning whole columns replace many helper columns and the old Ctrl+Shift+Enter array formulas.

Note: Check your audience's Excel version before relying on dynamic arrays. In Excel 2016 or 2019, new functions such as FILTER show #NAME? on recalculation, and spilled results become fixed legacy array formulas.

=FILTER(array, include, [if_empty]) returns every row of array where include is TRUE, spilling the result into the cells below.

AND conditions: multiply.

=FILTER(A2:D500, (B2:B500="North")*(D2:D500>50000), "No records")

Returns rows where the region is North and sales exceed 50,000. TRUE times TRUE is 1; anything involving a FALSE is 0.

OR conditions: add.

=FILTER(A2:D500, (B2:B500="North")+(B2:B500="West"))

Any row where at least one test is TRUE gives a non-zero total, which FILTER treats as TRUE.

Mixed logic: =FILTER(A2:D500, ((B2:B500="North")+(B2:B500="West"))*(C2:C500="Laptop")). Brackets control precedence exactly as in algebra: (North or West) and Laptop.

Other useful patterns:

• Match against a list: ISNUMBER(MATCH(B2:B500, G2:G5, 0)) as the include argument keeps rows whose region appears in a selection list.
• Partial text: ISNUMBER(SEARCH("pro", A2:A500)) keeps rows containing "pro" anywhere.
• Date window: (E2:E500>=H1)*(E2:E500<=H2).
• Sorted output: =SORT(FILTER(...), 4, -1), or CHOOSECOLS to return only some columns.

The if_empty argument matters: without it, a filter with no matches returns #CALC!, which then breaks every formula that references the result.

Note: Do not use the AND() and OR() functions inside FILTER. They collapse the whole array to a single TRUE or FALSE instead of testing row by row, which is the most common FILTER mistake.

These three dynamic array functions together replace much of what used to need Remove Duplicates, manual sorting or a PivotTable.

• UNIQUE(array, [by_col], [exactly_once]) returns the distinct values or rows. With exactly_once set to TRUE it returns only values that appear exactly once, which is handy for spotting one-off customers or unmatched IDs.
• SORT(array, [sort_index], [sort_order], [by_col]) sorts by a column position within the array: =SORT(A2:C100, 3, -1) sorts by the third column, descending.
• SORTBY(array, by_array1, [order1], by_array2, ...) sorts by other ranges, which need not appear in the output. =SORTBY(A2:A100, C2:C100, -1) lists names ordered by sales without showing sales. Multi-level sorts are clear too: =SORTBY(A2:C100, B2:B100, 1, C2:C100, -1) sorts by region, then sales descending.

Combining them:

• A sorted, distinct list for a drop-down: =SORT(UNIQUE(B2:B500))
• Distinct regions ranked by their total sales:
  =LET(r, UNIQUE(B2:B500), SORTBY(r, SUMIFS(D2:D500, B2:B500, r), -1))
• Top 5 rows by sales: =TAKE(SORTBY(A2:D500, D2:D500, -1), 5)
• Number of distinct customers: =ROWS(UNIQUE(A2:A500))

Because the results spill and recalculate, adding data to the source updates every list automatically, especially if the source is an Excel Table.

Note: UNIQUE is not case-sensitive and treats an empty cell as a value, so blank rows at the end of the source produce a 0 in the output and inflate a distinct count. Filter them out first, e.g. UNIQUE(FILTER(B2:B500, B2:B500<>"")).

LET assigns names to intermediate results inside a single formula and then uses those names in a final calculation:

=LET(name1, value1, name2, value2, ..., calculation)

Example without LET: a bonus of 10% on sales above target, capped at 50,000.

=IF(SUMIFS(D2:D500,A2:A500,G2)-H2>0, MIN((SUMIFS(D2:D500,A2:A500,G2)-H2)*10%, 50000), 0)

The SUMIFS is written, and calculated, twice, and the logic is hard to follow.

With LET:

=LET(sales, SUMIFS(D2:D500, A2:A500, G2), excess, sales - H2, IF(excess > 0, MIN(excess*10%, 50000), 0))

Benefits:

• Readability: names such as sales and excess document the logic, so a reviewer can follow it without decoding nested functions.
• Performance: each named expression is calculated once and reused. When an expensive lookup or FILTER would otherwise repeat three or four times, the saving on a large sheet is real.
• Maintainability: change the SUMIFS in one place rather than several.
• Dynamic arrays: hold a filtered array in a name, then sort, count or stack it in the final step.

Rules to remember: names must start with a letter and cannot look like cell references (tax1 is invalid because TAX1 is a real cell), the last argument must be the calculation, and names exist only inside that formula. Pressing Alt+Enter in the formula bar puts each name on its own line, which makes long LET formulas much easier to read.

Note: LET is the natural stepping stone to LAMBDA. Once a calculation is neatly parameterised with LET, turning it into a reusable custom function is a small step.

LAMBDA lets you build your own reusable function from ordinary Excel formulas, without VBA. The earlier arguments are parameters and the last one is the calculation:

=LAMBDA(price, gst_rate, price * (1 + gst_rate))

Making it a named function: go to Formulas > Name Manager > New, name it ADDGST and paste the LAMBDA into Refers to. It can then be used anywhere in the workbook as =ADDGST(B2, 18%). Test it in a cell first by calling it directly: =LAMBDA(p, r, p*(1+r))(B2, 18%).

Why it matters: business logic lives in one place. When the rule changes, you edit the name and every formula using it updates. There is no macro-enabled file and no security warning.

LAMBDA helper functions apply a LAMBDA across arrays:

• MAP applies it to each element: =MAP(A2:A10, LAMBDA(x, IF(x>100, "High", "Low")))
• BYROW / BYCOL return one result per row or column: =BYROW(B2:M20, LAMBDA(r, MAX(r))) spills each row's maximum, which MAX alone cannot do.
• REDUCE accumulates to a single value: =REDUCE(0, B2:B10, LAMBDA(acc, v, acc + IF(v>0, v, 0))) sums only the positive numbers.
• SCAN is like REDUCE but returns every intermediate step, e.g. a running balance.
• MAKEARRAY builds an array from row and column positions.

Good practice: give parameters meaningful names, combine LAMBDA with LET for multi-step logic, and add a comment in Name Manager describing what the function expects and returns.

Note: LAMBDA needs Microsoft 365 or Excel 2024. Document named LAMBDAs clearly, because a colleague who does not know they exist will find a formula like ADDGST mysterious.

These array-shaping functions (Microsoft 365 and Excel 2024) let you assemble and trim tables with live formulas instead of copy-paste.

• VSTACK(array1, array2, ...) stacks ranges on top of each other. =VSTACK(North!A2:D100, South!A2:D100) combines regional sheets into one list.
• HSTACK(array1, array2, ...) places arrays side by side, for example adding a calculated column next to existing ones: =HSTACK(A2:A50, C2:C50, C2:C50*10%).
• TAKE(array, rows, [cols]) keeps the first n rows, or the last n with a negative number. =TAKE(SORT(A2:D100, 4, -1), 10) gives the top 10.
• DROP(array, rows, [cols]) removes rows or columns, e.g. dropping a header row: =DROP(A1:D100, 1).
• CHOOSECOLS / CHOOSEROWS pick columns or rows by position, in any order: =CHOOSECOLS(FILTER(A2:F500, C2:C500="Delhi"), 1, 4, 2) returns only three columns, reordered.

A realistic combination: consolidate two quarterly sheets, remove blank rows and sort by amount.

=LET(all, VSTACK(Q1!A2:C200, Q2!A2:C200), clean, FILTER(all, CHOOSECOLS(all, 1)<>""), SORT(clean, 3, -1))

Fixed source ranges that are only partly filled bring empty rows along with them, so filtering out blanks is a standard second step.

Related functions: TOCOL and TOROW flatten a grid into one column or row (optionally ignoring blanks and errors), WRAPROWS and WRAPCOLS do the reverse, and EXPAND pads an array to a set size.

Note: These formulas stay live, so adding rows to a source updates the output. For heavy, repeatable consolidation of many files, Power Query is still the better tool; stacking functions shine for quick, transparent reshaping inside the sheet.

These three features turn a basic PivotTable summary into real analysis without touching the source data.

Show Values As (right-click a value > Show Values As) changes how a number is displayed without changing the aggregation:

• % of Grand Total, % of Column Total, % of Row Total: each region's or product's contribution.
• % of Parent Row Total: share within a group, e.g. each product's share of its category.
• Difference From / % Difference From: month-on-month or year-on-year change, using (previous) as the base item.
• Running Total In: cumulative sales across months.
• Rank Largest to Smallest: a ranking without any RANK formula.

Add the same field to Values twice, once as Sum and once as % of total, to show both side by side.

Grouping:

• Dates group into years, quarters and months. Grouping follows the calendar year, so for an April-March financial year add a Fiscal Year column in the source or in Power Query.
• Numbers group into bands, e.g. order values in steps of 5,000 or ages in 10-year bins.
• Text items can be selected and grouped manually, e.g. states into zones.

Calculated fields (PivotTable Analyze > Fields, Items & Sets) create a new field from others, e.g. Margin = Profit / Sales. The formula runs on the summed fields, so it gives the correct weighted margin for each group, unlike averaging a margin column from the source.

Note: Calculated fields always work on the sum of each field, so they cannot express counts, distinct counts or row-level logic. When you need those, add the data to the Data Model and write a DAX measure instead.

Slicers are clickable filter buttons for a PivotTable or an Excel Table. Insert one from PivotTable Analyze > Insert Slicer and choose a field such as Region. Users click to filter, Ctrl-click to multi-select, and can see at a glance what is filtered, which is much clearer than the drop-down filter.

Timelines are slicers built specifically for date fields. Users filter by years, quarters, months or days by dragging across a bar, which is ideal for period selection on a dashboard.

Connecting one slicer to several PivotTables: select the slicer, then Slicer > Report Connections and tick every PivotTable it should control. This is how one set of slicers drives a whole dashboard. The condition is that the PivotTables share the same source, meaning the same table, pivot cache or Data Model. Pivots built from separate copies of the data will not appear in the list.

GETPIVOTDATA retrieves a value from a PivotTable by field and item names rather than by cell address:

=GETPIVOTDATA("Sales", $A$3, "Region", "North", "Month", "Jan")

Excel generates it automatically when you click a pivot cell while writing a formula. Its big advantage is that the value is found by labels, so it stays correct when the pivot is re-sorted, filtered or grows. A plain =C7 would point to whatever happens to land in C7 afterwards. Replace the hard-coded items with cell references and you can build a formatted report layout that pulls figures from the pivot.

It returns #REF! if the requested item is not visible in the pivot, which is a useful signal that a filter has excluded it.

Note: If you genuinely need plain references, for example to copy a formula down alongside a pivot, switch off Generate GetPivotData under PivotTable Analyze > Options. Just be clear on why the default exists.

The Data Model is an in-memory database inside the workbook, powered by the same VertiPaq engine as Power BI. Power Pivot is the window used to manage it: tables, relationships and DAX measures.

What it solves:

• Multiple tables without VLOOKUP. Load a Sales fact table plus Product, Customer and Date tables, then create relationships on their keys in Diagram View. One PivotTable can then use fields from all of them.
• Volume. Data in the model is compressed by column and is not bound by the sheet's 1,048,576-row limit, so tens of millions of rows are practical.
• Distinct count becomes available in Value Field Settings.

DAX measures versus worksheet formulas: a worksheet formula lives in a cell and references cells. A measure is defined once on a table and evaluated for whatever each PivotTable cell represents, its filter context.

Total Sales := SUM(Sales[Amount])
Sales LY := CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))
Margin % := DIVIDE([Total Profit], [Total Sales])

Put Margin % into a pivot by region and month and every cell computes the correct ratio for that slice, with no copying of formulas and no averaging of percentages.

Good practice: shape data as a star schema, mark a proper Date table so time intelligence works, load data through Power Query, and prefer measures to calculated columns. Cube functions such as CUBEVALUE can pull measure results into a custom report layout.

Note: The skills transfer directly: a Power Pivot model can be imported into Power BI Desktop, which is why interviewers for analyst roles like hearing that you have used it.

A classic scenario: every branch or month sends a file in the same layout, and you need one consolidated table that updates when new files arrive. Power Query's From Folder connector does this with no copy-paste or VBA.

Steps:

1. Put all the files in one folder; SharePoint and OneDrive folders work too.
2. Data > Get Data > From File > From Folder, and select the folder.
3. Click Combine & Transform Data, then choose a sample file and the sheet or table to use.
4. Power Query creates a sample file query and a Transform Sample File function. Any cleaning you apply to the sample, such as removing top rows, promoting headers or setting types, is applied to every file.
5. The combined query includes a Source.Name column with each file name. Keep it, or extract the month or branch from it.
6. Close & Load to a table, a PivotTable or the Data Model.

Next month, drop the new file into the folder and click Refresh All.

What goes wrong, and how to guard against it:

• Stray files such as a temporary ~$ lock file or a PDF break the combine. Filter the Extension column to .xlsx or .csv and exclude names starting with ~$ early in the query.
• Different sheet names across files: use a consistent table name, or navigate by position instead of by sheet name.
• Renamed columns in one file produce nulls. Standardise headers, or rename columns inside the transform function.
• Data types: set them after combining and use Keep Errors to find rows that failed conversion.

Note: This one pattern replaces most monthly consolidation macros, and because every step is recorded in the Applied Steps pane, the process is fully auditable, which is a strong point to make in an interview.

Reconciliation, such as a bank statement against the ledger, vendor invoices against purchase orders, or this month's employee list against last month's, comes up constantly. The approach depends on data size and how often you repeat it.

1. COUNTIF flag (quick, any version):

=IF(COUNTIF(Ledger!$A$2:$A$5000, A2)=0, "Missing in ledger", "Found")

Add the mirror formula on the other sheet to catch items missing in the reverse direction.

2. XLOOKUP to compare values, not just existence:

=XLOOKUP(A2, Ledger!$A$2:$A$5000, Ledger!$C$2:$C$5000, "Not found") next to a difference column such as =C2-E2, so you catch amount mismatches as well as missing IDs.

3. FILTER for a spilled exception list (Microsoft 365):

=FILTER(A2:C500, COUNTIF(Ledger!A2:A5000, A2:A500)=0, "All matched")

4. Power Query anti joins, best for large or recurring reconciliations: load both lists, then Merge Queries on the key with a Left Anti join (rows only in the first list) and a Right Anti join (rows only in the second). An Inner join gives the matched rows for comparing amounts. Next month, just refresh.

Clean the keys first: trailing spaces (TRIM), numbers stored as text versus real numbers, leading zeros lost from IDs, and inconsistent text. Most "missing" items in a first pass are really formatting mismatches.

Note: Also check each side for duplicate keys with COUNTIF greater than 1. A duplicated invoice matches perfectly in a lookup, yet the totals will still not tie.

Data Validation (Data > Data Validation) controls what users can enter. Beyond simple lists, two techniques come up often in interviews: dependent drop-downs and custom formula rules.

Dependent drop-down: choose a State in B2, and only that state's cities appear in C2.

Modern approach (Microsoft 365): keep a two-column table named Map with State and City. In a helper cell H2, =SORT(UNIQUE(Map[State])) spills the state list, and B2's validation list source is =$H$2#. In J2, =FILTER(Map[City], Map[State]=B2) spills the matching cities, and C2's source is =$J$2#. This suits a single input form.

Classic approach (any version): create a named range of cities for each state, named after the state with underscores for spaces, and set the City source to =INDIRECT(SUBSTITUTE(B2," ","_")). It works everywhere, but INDIRECT is volatile and the names must be maintained by hand.

Custom formula rules (Allow: Custom):

• No duplicate entries: =COUNTIF($A$2:$A$500, A2)=1
• End date after start date: =C2>B2
• Weekdays only: =WEEKDAY(A2, 2)<6
• Exactly 10 characters, such as a PAN: =LEN(A2)=10

Use the Input Message tab to explain the rule, and set the Error Alert style to Stop so invalid entries are blocked rather than just warned about.

Note: Validation only checks typed input. Pasting over a validated cell replaces the rule, and data entered before the rule existed is not flagged. Use Circle Invalid Data to find violations, and protect the sheet where it matters.

All three live under Data > What-If Analysis and answer "what happens if the inputs change?", but each answers a different question.

Goal Seek works backwards: set cell (the formula), to value (the target), by changing cell (one input). For example, the selling price needed for a profit of 10 lakh. It iterates to an answer, overwrites the input, and does not update later.

Data Tables work forwards across many values at once, such as profit for every combination of price and volume. They stay live, so changing the model recalculates the whole table.

Scenario Manager stores sets of inputs, such as Best, Base and Worst case, swaps them into the model, and produces a Scenario Summary that lays results side by side for management.

Beyond these, Solver (an add-in) handles several changing cells with constraints and finds optimal values, not just a single target.

Note: In practice many analysts replace Scenario Manager with a scenario selector cell and XLOOKUP or CHOOSE pulling from a visible scenario table. It is more transparent, easier to audit and does not hide values inside a dialog box.

A two-variable Data Table shows how one output changes across every combination of two inputs, for example net profit across different selling prices and sales volumes. It is the standard sensitivity grid in financial models.

Setup:

1. The model must already calculate the output from input cells, e.g. price in B2, volume in B3 and profit in B10.
2. On the same sheet as those inputs, type price values across a row (F1:J1) and volume values down a column (E2:E8).
3. In the corner cell where they meet, E1, link to the output: =B10.
4. Select the whole block, E1:J8.
5. Data > What-If Analysis > Data Table. Set Row input cell to B2, because prices run across the row, and Column input cell to B3.
6. Excel fills the grid with a {=TABLE(B2,B3)} array, and every cell shows the profit for that price and volume.

Common mistakes:

• Swapping the row and column input cells. The grid fills, but with wrong numbers.
• Pointing to input cells on another sheet. Excel refuses, so keep the inputs on the table's sheet.
• Typing grid values as formulas that reference the input cells themselves, which creates circular results.

Making it readable: give the corner cell a custom number format so it shows a label instead of the raw profit, and add a colour scale so loss-making combinations stand out.

Performance: a Data Table recalculates the whole model for every cell, so large ones slow a workbook. Set calculation to Automatic except for data tables and press F9 when you need them refreshed.

Note: A one-variable Data Table works the same way with a single list of inputs, and can show several outputs at once by linking multiple output cells across its top row.

Solver is a free Excel add-in (File > Options > Add-ins > Excel Add-ins > Solver Add-in) that finds the best value of an objective by changing several input cells, subject to constraints. Goal Seek changes one cell to hit one target; Solver optimises across many.

Three parts of every Solver model:

• Objective: the cell to maximise, minimise or set to a value, e.g. total profit.
• Variable cells: what Solver may change, e.g. units of each product to produce.
• Constraints: limits such as machine hours used <= hours available, raw material used <= stock, units >= 0, and units must be integers.

Example, product mix: a factory makes three products with different margins and machine-hour needs. Units are in B2:D2, profit per unit in B3:D3 and hours per unit in B4:D4. The objective is =SUMPRODUCT(B2:D2, B3:D3), maximised. The hours constraint is =SUMPRODUCT(B2:D2, B4:D4) <= 2,000, plus minimum order quantities.

Choosing the solving method:

• Simplex LP for linear models. It is fast and guarantees the global optimum.
• GRG Nonlinear for smooth nonlinear models. It can stop at a local optimum, so try different starting values.
• Evolutionary for models containing IF, lookups or other non-smooth functions.

Other common uses include minimising transport cost across warehouses, shift scheduling, portfolio weights that maximise return for a given risk, and marketing budget allocation.

Note: Always read Solver's result message and generate the Answer and Sensitivity reports. "Infeasible" usually means the constraints conflict, and a result that defies common sense usually means a missing constraint such as non-negativity or integers.

Both discount future cash flows to today, but they make different assumptions about timing.

NPV(rate, value1, value2, ...) assumes cash flows occur at equal intervals, one per period, and, crucially, that the first value arrives at the end of period 1. It discounts every value it is given, including the first.

The common mistake is including the initial investment made today inside NPV:

=NPV(12%, B1:B6), where B1 is an outflow of 10,00,000 made today

This discounts the investment by a year it should not be, understating its cost and overstating the project's value. The correct form keeps the time-zero flow outside:

=NPV(12%, B2:B6) + B1

XNPV(rate, values, dates) uses the actual date of each cash flow and discounts by the exact number of days on a 365-day year. The first date is treated as the valuation date and its cash flow is not discounted, so the initial investment belongs inside the range:

=XNPV(12%, B1:B6, A1:A6)

When to use which:

• NPV for regular, evenly spaced flows, such as annual cash flows in a budget model.
• XNPV for irregular timing, such as real estate deals, staggered investment drawdowns or uneven milestone receipts, and whenever actual dates are known. It is standard in project finance and private equity models.

Rate periods: the NPV rate must match the period, so a monthly series needs a monthly rate, whereas XNPV always takes an annual rate.

Note: Keep the pairs consistent: NPV goes with IRR, and XNPV goes with XIRR. At the IRR, a correctly built NPV equals zero; at the XIRR, XNPV equals zero.

All three return a rate of return on a series of cash flows. They differ in timing assumptions and in how interim cash is treated.

• IRR(values, [guess]) is the discount rate at which the NPV of the flows is zero, assuming equal periods. The result is per period, so monthly flows give a monthly IRR that you annualise with =(1+IRR(B2:B37))^12-1. The range needs at least one negative and one positive value.
• XIRR(values, dates, [guess]) applies the same idea to actual dates and returns an annualised rate. Use it for irregular flows such as SIP investments, private equity drawdowns and distributions, or projects with uneven milestones. Mutual fund and portfolio returns in India are commonly quoted as XIRR for this reason.
• MIRR(values, finance_rate, reinvest_rate) fixes IRR's unrealistic assumption that interim positive cash flows are reinvested at the IRR itself. You specify the borrowing cost for negative flows and a realistic reinvestment rate for positive ones.

Weaknesses of IRR worth mentioning:

• Multiple IRRs can exist when cash flows change sign more than once, such as an investment, returns, then a large closure cost. Excel returns only one, depending on the guess.
• #NUM! appears when no rate converges; try a different guess.
• Scale blindness: a small project with a 40% IRR can create less value than a large one at 18%. That is why NPV is the primary decision rule and IRR a supporting metric.

Example: invest 1,00,000 on 1 Jan 2025 and receive 1,20,000 on 30 Sep 2025. XIRR gives about 27.7% a year, whereas the simple 20% return ignores that the money was tied up for only nine months.

Note: Always state the period of an IRR. A monthly IRR of 1.5% is about 19.6% a year, and confusing the two is a common interview trip-up.

Take a home loan of 50,00,000 at 9% a year for 20 years, repaid monthly. Inputs: principal in B1, annual rate in B2, years in B3.

EMI:

=PMT(B2/12, B3*12, -B1)

This returns about 44,986. Two points interviewers check:

• Match the rate to the period: divide the annual rate by 12 and multiply the years by 12 for monthly payments.
• Sign convention: Excel treats money received as positive and money paid out as negative. With a positive principal, PMT returns a negative number; entering the principal as negative shows the EMI as positive.

The schedule, one row per month with the period number in column A:

• Interest: =IPMT($B$2/12, A6, $B$3*12, -$B$1)
• Principal: =PPMT($B$2/12, A6, $B$3*12, -$B$1)
• Check: interest plus principal equals the EMI in every row.
• Balance roll-forward: opening balance, less principal, gives the closing balance, and each row's opening equals the previous row's closing.

The final closing balance should be zero, give or take a rounding difference, which acts as a built-in check on the whole schedule.

Useful extensions:

• Total interest over the loan: =CUMIPMT(B2/12, B3*12, B1, 1, B3*12, 0) returns about 57.97 lakh, shown as a negative outflow.
• Prepayments: add a prepayment column and calculate interest on the opening balance (=opening * $B$2/12) instead of using IPMT, because IPMT assumes the original schedule.
• Payments at the start of each period: set the type argument of PMT to 1.

Note: Early in a long loan most of each EMI is interest: in this example about 37,500 of the first month's 44,986. Showing that split is often the most useful insight for a non-finance audience.

CAGR (Compound Annual Growth Rate) is the constant yearly rate that takes a value from its starting level to its ending level over a number of years. It smooths out year-to-year volatility, which makes it the standard way to compare growth in revenue, users or investments.

Formula: CAGR = (End value / Start value)^(1 / years) - 1

Revenue grew from 40 crore in FY2020 (B2) to 72 crore in FY2025 (G2). That is five years of growth, not six:

• Direct formula: =(G2/B2)^(1/5)-1 returns 12.47%.
• RRI: =RRI(5, B2, G2) returns the same rate and reads clearly.
• RATE: =RATE(5, 0, -B2, G2). Note the sign convention: the start value is entered as negative.
• POWER: =POWER(G2/B2, 1/5)-1.

With exact dates rather than whole years, use =(End/Start)^(365/(EndDate-StartDate))-1, or XIRR on the two cash flows.

Common mistakes:

• Counting periods wrongly: six annual values span five growth periods. Use =COUNT(B2:G2)-1 if in doubt.
• Averaging the yearly growth rates instead: growth of +50% followed by -50% averages 0%, but you have actually lost 25%.
• Negative or zero start values: CAGR is undefined, so report the absolute change instead.

Note: CAGR hides the path. Two businesses with the same 12% CAGR can have very different volatility, so pair it with a year-by-year chart when presenting.

Each error code points to a specific kind of problem, so reading it correctly is the fastest route to a fix.

How to approach them: errors are information. Trace back to the first cell in the chain that errors (Formulas > Error Checking > Trace Error), fix the cause there, and suppress an error only when it is genuinely expected.

Note: #REF! is the most dangerous because it is permanent. Once a reference is lost, the formula no longer knows what it pointed to, even if you re-insert the column, so undo immediately or restore a backup.

IFERROR(value, value_if_error) catches every error type, including #N/A, #VALUE!, #REF!, #DIV/0!, #NAME? and #NUM!, and returns your alternative instead.

IFNA(value, value_if_na) catches only #N/A, the error a lookup returns when it finds no match. Every other error passes through and stays visible.

Why the difference matters. Consider:

=IFERROR(VLOOKUP(A2, Prices!$A$2:$C$500, 3, FALSE), 0)

If a colleague deletes a column in the Prices range, the VLOOKUP now returns #REF!. If a function name is mistyped, #NAME?. IFERROR quietly converts all of these to 0, and the total still looks plausible, but it is wrong. Nobody notices until the numbers are challenged.

With IFNA, only the expected "not found" case becomes 0, while a broken reference still shows #REF! and gets fixed.

Good practice:

• Use IFNA, or XLOOKUP's built-in if_not_found argument, for lookups.
• Use IFERROR only where you have thought through which errors can occur. Even for division, =IF(B2=0, "", A2/B2) states the intent more explicitly.
• Return a visible value such as "Not found" rather than 0, which blends into totals and averages.
• Wrap the smallest part of the formula that can legitimately fail, not the whole expression.
• Count how many rows fell into the fallback, e.g. =COUNTIF(D2:D500, "Not found"), so exceptions remain visible.

Note: Blanket IFERROR wrapping is one of the most common findings when models are audited. Saying so in an interview shows you care about model integrity, not just a tidy-looking sheet.

Work systematically, testing each piece, rather than rereading the formula and hoping to spot the mistake.

Tools:

• Evaluate Formula (Formulas > Evaluate Formula) steps through the calculation one part at a time, showing each intermediate result. It is ideal for nested IFs and lookups.
• F9 in the formula bar: select part of a formula, such as a MATCH, and press F9 to see its value. Press Esc afterwards, otherwise the result permanently replaces that part.
• Trace Precedents and Trace Dependents draw arrows to the cells feeding a formula and those relying on it. A dashed arrow means another sheet.
• Watch Window keeps key cells from other sheets in view while you change inputs elsewhere.
• Show Formulas (Ctrl + `) displays formulas instead of values, making an inconsistent formula in a column easy to spot.
• Error Checking and the green triangles flag inconsistent formulas and numbers stored as text.

Method:

1. Reproduce the problem with a simple case you can check by hand.
2. Break the formula into helper cells, or named steps with LET, and check each one.
3. Check the usual suspects: references that shifted when copied, a range that misses rows, text-numbers, trailing spaces, approximate match where exact was intended, and calculation set to Manual.
4. Cross-check the answer another way, such as a PivotTable or a SUMIFS total.

Example: a SUMIFS total was short. F9 on the criteria range showed it stopped at row 500 while the data now ran to row 620. Converting the source to an Excel Table fixed it permanently, because structured references grow with the data.

Note: For larger audits, the Inquire add-in, where available, maps formula inconsistencies and links across a whole workbook.

SUMPRODUCT(array1, [array2], ...) multiplies corresponding elements of arrays and adds up the results. That simple idea makes it one of the most versatile functions in Excel.

Weighted average: for example, average price weighted by quantity, where a plain AVERAGE of prices would mislead.

=SUMPRODUCT(C2:C100, D2:D100) / SUM(D2:D100)

C holds price and D quantity. The same pattern gives a weighted vendor score, a weighted average cost of capital or a portfolio's weighted return.

Conditional sums with Boolean logic:

=SUMPRODUCT((B2:B100="North")*(E2:E100>=DATE(2025,4,1))*F2:F100)

Each condition returns TRUE or FALSE; multiplying converts them to 1 or 0, so only rows meeting every condition contribute. Use + between conditions for OR logic.

Where SUMPRODUCT beats SUMIFS:

• Conditions on a calculated value: =SUMPRODUCT((MONTH(A2:A100)=3)*F2:F100) sums March sales without a helper column. SUMIFS cannot apply MONTH to its criteria range.
• Multiplying before summing: revenue as quantity times price for one region, =SUMPRODUCT((B2:B100="North")*C2:C100*D2:D100).
• Counting with complex logic: =SUMPRODUCT(--(LEN(A2:A100)>10)) counts entries longer than 10 characters.

Watch out: all arrays must be the same size or it returns #VALUE!; text inside a multiplied range also causes #VALUE!; and whole-column references are slow because every row is evaluated.

Note: For simple conditional sums SUMIFS is faster and easier to read, so reach for SUMPRODUCT when the logic genuinely needs it. In Microsoft 365, SUM with the same Boolean arrays also works, but SUMPRODUCT is compatible with every version.

Excel stores dates as serial numbers, so date arithmetic is ordinary arithmetic. These functions are reliable once dates are true dates rather than text.

• EOMONTH(start, months) returns the last day of the month n months away. =EOMONTH(A2, 0) gives the month-end of A2, and =EOMONTH(A2, -1)+1 gives the first day of A2's month. Ideal for month-end accruals and period grouping.
• EDATE(start, months) returns the same day n months later and handles month lengths: one month after 31-Jan-2025 is 28-Feb-2025. Use it for EMI due dates, renewals and probation end dates.
• NETWORKDAYS.INTL(start, end, [weekend], [holidays]) counts working days with a custom weekend. Weekend code 11 means Sunday only, useful for six-day-week operations, and the holidays argument takes a range of public holidays. WORKDAY.INTL does the reverse, returning the date n working days ahead, which suits SLA deadlines.
• DATEDIF(start, end, "Y") returns completed years, e.g. tenure or age; use "M" for months.

Indian financial year, April to March:

• Fiscal year label: ="FY"&(YEAR(A2)+(MONTH(A2)>=4)). A date of 15-May-2025 becomes FY2026, named after the year it ends.
• "2025-26" style: =IF(MONTH(A2)>=4, YEAR(A2)&"-"&RIGHT(YEAR(A2)+1,2), YEAR(A2)-1&"-"&RIGHT(YEAR(A2),2))
• Fiscal quarter: ="Q"&INT(MOD(MONTH(A2)-4, 12)/3)+1, so April to June is Q1 and January to March is Q4.
• Fiscal year start date: =DATE(YEAR(A2)-(MONTH(A2)<4), 4, 1)

Note: If a date formula returns #VALUE! or dates sort strangely, the dates are probably text. Test with ISNUMBER and convert with DATEVALUE or Text to Columns before doing anything else.

Splitting full names, extracting domains from email addresses and building codes are everyday tasks. Microsoft 365 added functions that replace most of the old LEFT, MID and FIND gymnastics.

Splitting:

• TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty]): =TEXTSPLIT(A2, ", ") spills "Delhi, Mumbai, Pune" into three cells across the row.
• TEXTBEFORE / TEXTAFTER(text, delimiter, [instance_num]): =TEXTAFTER(A2, "@") returns an email's domain and =TEXTBEFORE(A2, " ") the first name. A negative instance searches from the end, so =TEXTAFTER(A2, " ", -1) returns the last word, i.e. the surname even when there is a middle name.

Combining:

• TEXTJOIN(delimiter, ignore_empty, text1, ...): =TEXTJOIN(", ", TRUE, B2:B10) joins a range with a separator and skips blanks. With FILTER it lists every item for a key: =TEXTJOIN(", ", TRUE, FILTER(C2:C100, A2:A100=F2)).
• & and CONCAT for simple joins: =B2&" "&C2.

The classic approach, needed for older versions and still asked in interviews:

• First name: =LEFT(A2, FIND(" ", A2)-1)
• Domain: =MID(A2, FIND("@", A2)+1, LEN(A2))
• FIND is case-sensitive; SEARCH is not and accepts wildcards.

Also useful: TRIM and CLEAN to remove stray spaces and non-printing characters, TEXT to format numbers and dates inside strings, as in =TEXT(A2, "dd-mmm-yyyy"), and Flash Fill (Ctrl+E) for one-off pattern extraction.

Note: For repeatable cleaning of large imports, Power Query's Split Column by Delimiter is usually better than formulas, because it reruns on refresh and does not leave thousands of formulas in the sheet.

A typical task: loop through invoice rows and flag each unpaid invoice that is past its due date. A clean version:

Option Explicit

Sub FlagOverdue()
    Dim ws As Worksheet, lastRow As Long, i As Long
    Dim data As Variant
    Set ws = ThisWorkbook.Worksheets("Invoices")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    Application.ScreenUpdating = False
    data = ws.Range("A2:E" & lastRow).Value
    For i = 1 To UBound(data, 1)
        If data(i, 4) < Date And data(i, 3) = "Unpaid" Then
            data(i, 5) = "Overdue"
        Else
            data(i, 5) = ""
        End If
    Next i
    ws.Range("A2:E" & lastRow).Value = data
    Application.ScreenUpdating = True
End Sub

Best practices it shows:

• Option Explicit forces variables to be declared, so a typo becomes a compile error rather than a silent new variable.
• No Select or Activate. Work through object references such as ws.Range; selecting is slow and breaks if the user clicks elsewhere.
• Find the last row dynamically instead of hard-coding row 1000.
• Read into an array, process in memory, write back once. Reading and writing cells one at a time inside a loop is the biggest cause of slow macros, and the array approach is often dramatically faster.
• Turn off ScreenUpdating, and set calculation to manual for heavy workbooks, then restore both at the end, ideally from an error handler so they are always restored.
• Use Long rather than Integer for row counters, because Integer overflows at 32,767.
• Qualify sheets through ThisWorkbook, use meaningful names and comment the purpose.

Note: If the job is just filtering, lookups or cleaning, first ask whether a formula or Power Query can do it without code. Interviewers like candidates who know when not to write VBA.

Without error handling, a runtime error stops a macro midway, shows a cryptic dialog and can leave the workbook half-updated with screen updating still switched off. A structured handler prevents that.

Standard pattern:

Sub ImportData()
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False

    ' ... main work ...

CleanExit:
    Application.ScreenUpdating = True
    Exit Sub

ErrHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation
    Resume CleanExit
End Sub

• On Error GoTo ErrHandler jumps to the handler as soon as an error occurs.
• Err.Number and Err.Description say what went wrong; for unattended jobs, log them to a sheet or text file.
• Resume CleanExit clears the error and routes through a single exit that always restores settings.
• Exit Sub before the handler stops a normal run from falling into it.

Why On Error Resume Next is dangerous: it tells VBA to ignore every error and carry on. If a sheet is missing, a file fails to open or a calculation fails, the macro continues with bad or missing data and appears to succeed. Placed at the top of a procedure, it hides every bug.

Its legitimate use is narrow and deliberate: one line where failure is expected, then checked and switched off straight away.

On Error Resume Next
Set ws = ThisWorkbook.Worksheets("Summary")
On Error GoTo ErrHandler
If ws Is Nothing Then Set ws = ThisWorkbook.Worksheets.Add

Other habits: validate inputs before acting (does the file exist, is the range empty), use On Error GoTo 0 to reset handling, and test the failure paths, not only the normal run.

Note: In an interview, saying that you restore application settings in a cleanup block, rather than just showing a message box, signals production-quality VBA experience.