Excel Interview Questions for Freshers (2026): The Practical Test, and Why Lookups Fail
Updated August 2026
This page is written as much for B.Com, BA, B.Sc and MBA candidates as for engineers, because Excel is the gate on a very large number of Indian fresher jobs that have nothing to do with software: accounts and audit support, MIS and reporting, operations, banking and insurance back-office, HR and payroll support, sales coordination, and analyst roles. For many of these, the spreadsheet round is not one part of the assessment — it is the assessment.
The thing to understand about that round is that it is usually not a quiz. You are given a messy sample file and a list of tasks with a clock running: clean this column, look up these values against that sheet, produce a summary by month and region, add a chart. Nobody asks you to define a pivot table; they watch whether you can build one in ninety seconds. Which means preparation is practice rather than reading, and the questions below are written to tell you what the tasks will be and where candidates lose time.
One specific failure deserves flagging up front because it costs more candidates the round than any missing function does: a lookup that returns #N/A when the value is visibly sitting in the other sheet. It is almost never the formula. It is trailing spaces, or numbers stored as text, or two columns whose data types do not match — and knowing to check that first is the difference between fixing it in fifteen seconds and losing ten minutes to rewriting a formula that was correct all along.
Frequently asked questions
What does an Excel test round in an interview actually involve?
Typically a workbook of deliberately imperfect data and a timed task list — commonly twenty to forty minutes. Expect some combination of cleaning a column, matching data across two sheets with a lookup, conditional totals with SUMIFS or COUNTIFS, a pivot table summarising by category and month, a chart, and formatting the result so it is readable. Sometimes it runs on the interviewer's machine, sometimes without internet, and occasionally in Google Sheets rather than Excel. Ask which environment and version you will be using, because XLOOKUP is not available everywhere and discovering that mid-test wastes time you do not have.
Why does VLOOKUP return #N/A when I can see the value in the other sheet?
Almost always a data problem rather than a formula problem, and there are three usual culprits. Trailing or leading spaces, so "Hyderabad " does not match "Hyderabad" — fix with TRIM. Numbers stored as text, where an invoice number that looks numeric is text in one sheet and a number in the other, often shown by a left-aligned cell or a small green triangle. And an inexact match where the last argument was left as TRUE, which returns approximate matches on unsorted data and produces silently wrong values as well as #N/A. Say this diagnostic order out loud in an interview — check the data types, TRIM both sides, confirm FALSE for exact match — because demonstrating the debugging sequence is worth more than reciting the syntax.
VLOOKUP, XLOOKUP or INDEX and MATCH — which should I use?
XLOOKUP where it is available: it looks in any direction, takes an if-not-found argument so you avoid wrapping everything in IFERROR, and does not break when someone inserts a column. INDEX with MATCH where XLOOKUP is not available, which is the same flexibility with slightly more typing and is still the answer that reads as competent in an older environment. VLOOKUP when the file or the interviewer expects it, remembering that it only looks rightward and that its column index is a position rather than a reference, so inserting a column silently breaks it. Knowing all three and being able to say why you chose one is the answer that lands.
What is the difference between absolute and relative references, and why does my dragged formula break?
A relative reference like A1 shifts as you copy the formula; an absolute one like $A$1 does not, and mixed forms like A$1 or $A1 lock only the row or only the column. The classic breakage is a lookup whose table range was left relative — drag it down and the range slides down with it, so later rows search a shrinking table and start returning #N/A. Lock the lookup range with F4, and lock the row or column when copying a formula across a grid. This is the single most common cause of a formula that works in the first cell and fails further down, and interviewers notice whether you reach for F4 automatically.
How do you build a pivot table, and what do interviewers usually ask you to do with one?
Select the data, insert a pivot table, then place fields into Rows, Columns, Values and Filters. The tasks that come up repeatedly: summarise a value by category, group dates by month or quarter rather than by individual day, change the summarisation from Sum to Count or Average, show values as a percentage of the total, and add a second dimension in Columns to create a cross-tab. Two gotchas worth knowing — a pivot table does not update when the source changes until you refresh it, and blank rows or inconsistent spellings in the source produce stray categories, which is why cleaning comes before summarising.
When would you use SUMIFS instead of SUMIF, and what about COUNTIFS?
Use SUMIFS by default. It handles one or many conditions with a consistent argument order — sum range first, then criteria pairs — while SUMIF takes its arguments in a different order and supports only one condition, which is exactly the kind of inconsistency that causes mistakes under time pressure. COUNTIFS is the same idea for counting rows that satisfy several conditions, and AVERAGEIFS for averages. In a practical test these are how you answer questions like "total sales for the south region in March", and they are usually faster and more auditable than filtering manually and reading a number off the screen.
How do you handle errors like #N/A, #DIV/0! and #REF!?
Understand them before you suppress them. #N/A means a lookup found nothing, #DIV/0! means a division by an empty or zero cell, #VALUE! means a type mismatch such as text where a number is expected, and #REF! means a referenced cell no longer exists, usually because something was deleted. IFERROR wraps a formula to return a chosen value instead, and XLOOKUP has an if-not-found argument that is cleaner. The judgement interviewers are testing is when to hide an error — legitimate for a blank in a report, dangerous when the error was telling you your data does not match and a zero will quietly flow into a total someone then acts on.
What text-cleaning functions should I know?
TRIM to remove extra spaces, which fixes most failed lookups. CLEAN for non-printing characters that arrive with exported data. UPPER, LOWER and PROPER for consistent casing. LEFT, RIGHT, MID with FIND or SEARCH to extract part of a string. SUBSTITUTE to replace text, and LEN to check lengths when something looks identical but is not. Text to Columns to split a single column into several, and TEXTJOIN or the ampersand operator to combine. Flash Fill is worth knowing for one-off pattern extraction because it is startlingly fast in a timed test. Cleaning is normally the first task in a practical round, and everything downstream depends on it.
Why do my dates behave strangely, and how do I fix dates stored as text?
Excel stores a date as a serial number and shows you a formatted version, so a date can display correctly and still be text underneath — left-aligned by default, ignored by sorting and by date functions. To convert, use Text to Columns with a date format specified, or DATEVALUE, or a multiply-by-one trick, then apply a proper date format. The Indian-context trap worth naming: files often mix DD/MM/YYYY and MM/DD/YYYY, so 03/04 becomes ambiguous and a bulk conversion can silently swap days and months. Check a few known dates after any conversion. DATEDIF, EOMONTH, TODAY, YEAR, MONTH and TEXT cover most date work you will be asked for.
How do you find and remove duplicates, and what is the risk?
Two different jobs. To find them, use COUNTIF against the range and flag any count above one, or conditional formatting's duplicate-values rule — this is diagnostic and reversible. To remove them, Remove Duplicates on the Data tab, or Advanced Filter for unique records. The risk is that Remove Duplicates is destructive and acts on the columns you tick: choosing one column when rows are only duplicates across three columns deletes legitimate data permanently. In an interview say you would identify duplicates first, look at them, and only then remove — and that you would work on a copy.
What is the most dangerous thing you can do to a spreadsheet?
Sort one column instead of the whole range. Select a single column, sort it, and that column reorders while every other column stays put — so names now sit against other people's amounts. Nothing errors, nothing is highlighted, and the file looks fine. This has caused real financial mistakes in real offices. Always select the full data range or convert the range to a Table, use Sort with a header row specified, and if you are ever unsure, undo and check a row you recognise. Naming this in an interview signals genuine experience rather than tutorial knowledge.
What is the difference between a range and an Excel Table?
A Table — created with Ctrl+T — gives the data a name, headers that stay visible, automatic expansion when you add rows, banded formatting, a built-in totals row, and structured references so formulas read as Sales[Amount] rather than as cell coordinates. The practical benefits in a test are that formulas and pivot sources extend automatically to new rows and that sorting cannot desynchronise your columns. The one thing to know is that structured references behave differently from ordinary ranges when copied, which occasionally surprises people. Using a Table unprompted in a practical round is a small, visible competence signal.
Which chart should I use, and what makes a chart look amateur?
Column or bar for comparing categories, line for a trend over time, stacked column for composition across categories, scatter for the relationship between two numeric variables. What reads as amateur: a pie chart with more than about five slices, three-dimensional effects, a truncated axis that exaggerates a difference, missing units, a title that names the fields rather than the finding, and default colours applied to categories with no logic. Label directly where you can, sort bars by value rather than alphabetically unless the order means something, and make the title state the point — "sales fell in the south in Q3" rather than "sales by region".
Which shortcuts should I actually know for a timed test?
A small set covers most of the speed difference. F4 to toggle absolute references, and F4 again to repeat your last action. Ctrl with arrow keys to jump to the edge of a data region, and Ctrl+Shift with arrows to select to it — far faster and safer than dragging across thousands of rows. Ctrl+T for a Table, Ctrl+Shift+L for filters, Alt+= for AutoSum, Ctrl+; for today's date, Ctrl+D to fill down, Ctrl+1 for format cells, and Ctrl+Page Up or Page Down to move between sheets. Practise them until they are muscle memory; in a timed round, mouse-dragging through a large selection is where minutes disappear.
How do you check your own work before submitting a spreadsheet?
Have a routine and say it out loud in the interview. Reconcile a total against an independent calculation — a SUM of the source against the pivot total. Compare row counts before and after any join, filter or duplicate removal. Spot-check three or four lookup results manually against the source. Search the sheet for error values so no #N/A or #REF! ships. Confirm no rows were left hidden by a filter you forgot to clear. And sanity-check the magnitude of the answer: if a monthly total looks ten times too large, something is duplicated. A candidate who checks their own numbers is trusted with real work far sooner than one who does not.
Do I need to know macros or VBA as a fresher?
Rarely, and it is not usually the best use of your preparation time. A few reporting-heavy roles value it, and if a posting names it then learn the basics — recording a macro, editing it lightly, and understanding what a loop is doing. For most freshers, being genuinely fast with lookups, pivots, conditional aggregation and cleaning matters far more, and Power Query has taken over much of the repetitive work that macros used to do. If asked, honesty serves you: say you have not used VBA in anger, name what you would automate, and mention Power Query if you know it.
What is Power Query, and is it worth learning before an interview?
Power Query is the built-in tool for importing, reshaping and cleaning data with steps that are recorded and repeatable — so next month's file runs through the same transformations with a refresh instead of an hour of manual work. It handles merging tables, unpivoting badly structured reports, splitting columns and combining multiple files from a folder. For MIS and reporting roles it is a genuine differentiator, because most candidates at this level have never opened it and much of the actual job is repeating the same cleaning monthly. Learning the basics takes a weekend and gives you something concrete to describe when asked how you would handle a recurring report.
Does it matter whether I know Excel or Google Sheets?
The concepts transfer almost entirely — lookups, conditional aggregation, pivot tables, cleaning functions and charts all exist in both — so being genuinely good at one means you can work in the other with a little friction. The differences worth knowing are that Sheets has QUERY, ARRAYFORMULA and IMPORTRANGE with no direct Excel equivalent, while Excel has Power Query, more capable pivot tables and better performance on large files. Some employers test in Sheets because their whole workflow lives there. Ask which environment the test uses, and if you have used only one, say so plainly rather than being caught by an unfamiliar menu.
How should I answer "rate your Excel out of 10"?
Give a specific, defensible number with evidence rather than a bare figure. Something like: "About seven — I am comfortable with lookups, pivot tables, SUMIFS and cleaning messy exports, I have built a monthly report from raw data, and I have not worked with VBA." That is more credible than a nine you are about to be tested on, and more useful than a modest four that talks you out of a role you could do. Whatever number you give, expect the practical round to check it, so pick one your hands can back up.
I am from a B.Com or BA background. Is Excel alone enough to get a job?
For entry into accounts support, MIS and reporting, operations and back-office roles, strong Excel plus clear communication is genuinely enough to be hired, and those roles exist in large numbers. What Excel alone does not do is carry you upward — the people who move from MIS into analyst work are the ones who add SQL and one BI tool, because that is the boundary between maintaining a report and answering a question. So take the Excel-first role if it comes, and treat SQL as the next thing you learn while employed. Our data analyst guide covers what that next step is assessed on.
I graduate in 2027. How should I practise?
Not by watching tutorials. Get a genuinely messy file — a government open-data download, your college's records, a family business's sales register — and set yourself timed tasks on it: clean the text and dates, match it against a second sheet, produce a monthly summary by category, chart it, and write two sentences on what the numbers say. Do that weekly and you will be faster than most candidates within a couple of months, because you will have handled real inconsistencies rather than clean tutorial data. Alongside it, drill the shortcuts until they are automatic, and build one finished workbook you would be willing to show an interviewer.
Don't just read Excel questions — get asked them
Phiny's AI interviews you on exactly these topics, follows up on weak answers, and tells you what a stronger answer looks like. Text interviews are free and unlimited.
Start a free AI mock interviewHow to prepare
- Practise against a deliberately messy file with a timer running, not against tutorial data. The practical round is usually twenty to forty minutes on an imperfect workbook, and speed on real inconsistencies is the only thing that transfers.
- When a lookup fails, check the data before the formula: trailing spaces, numbers stored as text, and mismatched types cause most #N/A results. TRIM both sides and confirm the exact-match argument before rewriting anything.
- Learn F4 and Ctrl+arrow navigation until they are muscle memory. Unlocked ranges are the reason dragged formulas break further down a column, and mouse-dragging through large selections is where a timed test is lost.
- Never sort a single column inside a data set. Select the whole range or convert it to a Table with Ctrl+T — a column sorted alone silently misaligns every other column, and nothing about the file will look wrong afterwards.
- Identify duplicates before removing them, and work on a copy. Remove Duplicates is destructive and acts only on the columns you tick, which is how legitimate rows get deleted permanently.
- Have a self-check routine and say it out loud in the interview: reconcile a total independently, compare row counts, spot-check a few lookups, search for error values, clear filters, and sanity-check the magnitude of the result.
- Spend a weekend on Power Query if you are targeting MIS or reporting roles. Most candidates at this level have never opened it, and repeatable monthly cleaning is a large part of what those jobs actually involve.
Where these questions get asked
- TCS NQT guide and Infosys hiring guide — the two biggest exams these questions appear in.
- All company placement guides — pattern, syllabus and rounds for every mass recruiter.