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.

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.