Excel is still the first screen in most Indian analyst hiring processes, including at companies whose real stack is SQL and Power BI. The round is short, practical and unforgiving about lookups and pivot tables. Every question below has the answer an interviewer is listening for, and where a question carries a trap, the trap is named.
Fresher level: the screening round
These come up in phone screens and the first ten minutes of a practical test.
What is the difference between a formula and a function in Excel?
A formula is any expression you type into a cell after an equals sign, including plain arithmetic. A function is a named, pre-built routine such as SUM or VLOOKUP, which takes arguments and returns a result. Every function sits inside a formula, but plenty of formulas contain no function at all.
Explain relative, absolute and mixed cell references.
A relative reference such as A2 shifts as you copy the formula across rows and columns. An absolute reference, written with dollar signs before the column and row, stays fixed in both directions. A mixed reference locks only one of them. Forgetting to lock a lookup range is the most common reason a copied VLOOKUP starts returning wrong values halfway down a column.
What does VLOOKUP do?
VLOOKUP searches for a value in the leftmost column of a range and returns a value from a column to its right, identified by a number. Its four arguments are the value to find, the range, the column index and a TRUE or FALSE match flag. It cannot look to the left of its search column, which is what most follow-up questions target.
What is the fourth argument of VLOOKUP and why does it matter?
It controls whether the lookup is approximate or exact. FALSE demands an exact match and errors when there is none. TRUE assumes the lookup column is sorted ascending, so on unsorted data it silently returns the wrong row rather than erroring. Omitting the argument defaults to approximate, which is why interviewers treat that as a red flag.
What is the difference between COUNT, COUNTA, COUNTBLANK and COUNTIF?
COUNT counts cells holding numbers. COUNTA counts cells that are not empty, including text and errors. COUNTBLANK counts empty cells, and COUNTIF counts cells meeting a condition. A cell holding an empty string returned by a formula looks blank but is counted by COUNTA, which trips people up in data-quality checks.
What is a pivot table?
A pivot table summarises records without changing the source data, letting you drag fields into rows, columns, values and filters. Excel groups the records and aggregates the value field by sum, count, average or other functions. It is the fastest way to answer questions such as revenue by city by month, and the single most-tested Excel skill in analyst interviews.
How do you remove duplicates from a dataset?
The Data tab has a Remove Duplicates command where you choose which columns define a duplicate. It deletes rows permanently, so most analysts first flag duplicates with COUNTIFS and inspect them. On all columns it is safe; on one column it keeps whichever row appears first, which may not be the row you wanted.
What does Text to Columns do?
It splits one column into several, either at a delimiter such as a comma or at fixed widths. It is also the quickest fix for numbers stored as text, because running it and setting the format to General forces Excel to re-evaluate the type. That second use shows you have cleaned real data.
The Excel functions interviewers actually ask you to write
Practical rounds ask you to produce a working formula against a sample sheet. These are the functions that appear most often, with the expected argument order and an example against a sales sheet for an Indian retailer.
| Function | What it does | Syntax | Example |
|---|---|---|---|
| VLOOKUP | Finds a value in the first column of a range and returns a value from another column | VLOOKUP(lookup_value, table_array, col_index, range_lookup) | VLOOKUP(A2, Employees!A:D, 4, FALSE) |
| XLOOKUP | Finds a value in one array and returns the match from a separate result array | XLOOKUP(lookup_value, lookup_array, return_array, if_not_found) | XLOOKUP(A2, Products!A:A, Products!C:C, “Not found”) |
| INDEX with MATCH | Returns the value at a position located by a separate lookup | INDEX(return_range, MATCH(lookup_value, lookup_range, 0)) | INDEX(D2:D500, MATCH(A2, B2:B500, 0)) |
| SUMIFS | Adds values in a range that meet several conditions | SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2) | SUMIFS(E2:E500, B2:B500, “Bengaluru”, C2:C500, “Q1”) |
| COUNTIFS | Counts rows meeting several conditions | COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2) | COUNTIFS(B2:B500, “Mumbai”, F2:F500, “Active”) |
| IFERROR | Returns a chosen value when the formula inside it errors | IFERROR(value, value_if_error) | IFERROR(VLOOKUP(A2, Data!A:C, 3, FALSE), “Not found”) |
| TEXTJOIN | Joins text from a range using a separator | TEXTJOIN(delimiter, ignore_empty, range) | TEXTJOIN(“, “, TRUE, A2:A10) |
| FILTER | Returns the rows of an array that meet a condition | FILTER(array, include, if_empty) | FILTER(A2:E500, C2:C500=”Bengaluru”, “None”) |
| UNIQUE | Returns the distinct values from a range | UNIQUE(array) | UNIQUE(B2:B500) |
| TRIM | Removes extra spaces from text | TRIM(text) | TRIM(A2) |
Intermediate level: the practical round
Here the interviewer stops asking what a function does and starts asking why you chose it.
Why do analysts prefer INDEX with MATCH or XLOOKUP over VLOOKUP?
VLOOKUP only looks rightwards from its search column, and its column index is a hard-coded number that breaks when someone inserts a column. INDEX with MATCH searches any column and returns from any other, surviving insertions because both ranges are referenced directly. XLOOKUP does the same in one function, defaults to an exact match and takes a not-found argument, which makes it the preferred answer on recent Excel versions.
How do you handle a VLOOKUP that returns an error even though the value clearly exists?
Three causes account for almost all of these: trailing or leading spaces, fixable with TRIM; a number stored as text on one side and as a number on the other, visible from the cell alignment and fixable with Text to Columns; and a lookup range that shifted because it was not locked. Naming these signals you have actually debugged a sheet.
What is the difference between IFERROR and IFNA?
IFERROR catches every error type, including division by zero and a mistyped range name. IFNA catches only the not-available error. Wrapping a formula in IFERROR is convenient and dangerous, because it hides genuine mistakes behind a tidy blank; when the only error you expect is a failed lookup, IFNA is the honest choice.
What is a named range and why use one?
A named range gives a cell or range a readable name you can use in formulas instead of coordinates. Formulas become self-documenting and the reference stays correct when rows are inserted. The cost is that names are invisible on the sheet, so a workbook full of undocumented names is hard for the next person to unpick.
When would you use Power Query in Excel instead of formulas?
Whenever the cleaning has to repeat. Power Query records each step and re-runs the sequence on refresh, so combining twelve monthly files or unpivoting a cross-tab becomes a one-click job. Formulas are better for calculations alongside your data; Power Query is better for reshaping data before it arrives.
What is the difference between a pivot table calculated field and a helper column?
A calculated field lives inside the pivot table and computes on aggregated values, so it recalculates as the layout changes. A helper column sits in the source data and computes row by row before aggregation. The difference matters for ratios: a margin percentage computed on totals is correct, whereas averaging a row-level percentage column is subtly wrong.
How do you refresh and maintain a pivot table when the source grows?
Convert the source into an Excel Table so the pivot range expands automatically as rows are added, then refresh from the Data tab or on file open. Pointing a pivot at a fixed range is the usual reason a monthly report quietly stops including the newest rows.
What is data validation and where does it help an analyst?
Data validation restricts what can be typed into a cell, using a list, a numeric or date range, or a custom formula. It matters when a sheet is filled in by other people, because a dropdown of eight approved city names prevents the twenty spelling variants that later break your pivot. Interviewers like candidates who think about the input stage.
Advanced level: workbooks other people depend on
These come up for any job where you will inherit a reporting file.
What are dynamic arrays and how did they change Excel?
Dynamic array functions such as FILTER, UNIQUE and SORT return a range of results that spills into the cells below and to the right, resizing as the data changes. They removed the need for old Ctrl-Shift-Enter array formulas and for helper columns that existed only to build a unique list. A spilled range is referenced by its top-left cell followed by a hash symbol.
What are volatile functions and why do they matter?
Volatile functions such as NOW, TODAY, RAND, INDIRECT and OFFSET recalculate on every change anywhere in the workbook, not only when their inputs change. A few are harmless; hundreds turn a file into something that freezes after every keystroke. Replacing OFFSET with INDEX, and INDIRECT with structured Table references, is the standard fix.
How would you speed up a slow Excel workbook?
Find what is heavy before changing anything: formulas referencing entire columns, volatile functions, and array formulas over large ranges. Then cut the work by referencing only the used range, replacing repeated lookups with one helper column, moving cleaning into Power Query, and converting settled calculations into pasted values. Conditional formatting applied to a million rows is a common hidden cost.
How would you audit a workbook you did not build?
Use Trace Precedents and Trace Dependents on the output cells to see where numbers come from. Check Name Manager for broken names, look for external links and hidden sheets, and use Go To Special to find constants sitting inside a column that is otherwise formulas. A hard-coded number in the middle of a formula column is the classic defect and the likeliest cause of a wrong report.
When is VBA or a macro the right answer, and when is it not?
A macro earns its place for repetitive interface work with no formula equivalent, such as formatting and exporting thirty sheets in a fixed way. It is the wrong answer for transformation that Power Query already handles, or for anything a colleague must maintain without knowing VBA. Saying this is stronger than claiming macro expertise, because most reporting teams are reducing their VBA footprint.
How do you build a dashboard in Excel that someone will actually use?
Separate the file into three layers: raw data, a calculation sheet with pivots and formulas, and a presentation sheet with only charts and controls. Drive it from slicers connected to shared pivots so one click updates the whole view, and keep the top of the sheet for the numbers the reader came for. Protect the presentation sheet so users cannot type over your formulas.
What are the limits of Excel and when would you move to a database or Power BI?
Excel slows badly past a few hundred thousand rows of live formulas, has no real concurrency and keeps no version history unless someone is disciplined about it. Once several people need the same numbers at once, or the source outgrows a file, the data belongs in a database with SQL for queries and Power BI for presentation. Saying so shows judgement rather than disloyalty to the tool.
How to prepare for the practical round
Get a messy file and clean it under a timer. Most practical rounds give you forty-five minutes with an ugly export and a list of questions, and the time pressure is what people are unprepared for.
Learn the keyboard for navigation and selection, because interviewers watching your screen notice when you reach for the mouse to select ten thousand rows. And explain your choices as you go: one sentence on why XLOOKUP over VLOOKUP turns a correct answer into a strong one.
Frequently asked questions
How much Excel is needed for a data analyst job in India?
You need fluent lookups, conditional aggregation, pivot tables, text and date functions, and enough cleaning ability to make a messy export usable. Dynamic arrays and Power Query are increasingly expected at mid-level. Advanced VBA is rarely required and is not worth prioritising over SQL.
Is Excel still relevant when companies use SQL and Power BI?
Yes, because the last mile of most business reporting in India still happens in a spreadsheet someone emails. Excel is where ad hoc requests get answered, where finance reconciles, and where stakeholders want the export. Treat it as the tool you cannot avoid rather than the one you build a career on.
What is the most common Excel interview mistake?
Using VLOOKUP with the fourth argument omitted, and being unable to explain why a lookup returns an error when the value is visible on the screen. Both signal that the candidate has copied formulas rather than debugged them. Practise deliberately breaking a lookup and fixing it.
Do interviewers give a practical Excel test?
Very often, especially for freshers and for roles in operations, finance and consulting. The usual format is a spreadsheet with a few thousand messy rows and five questions to answer in under an hour. Speed matters less than getting the right number and showing how you got it.
Which Excel version should I practise on?
Practise on Microsoft 365 if you can, because XLOOKUP and the dynamic array functions only exist there and in Excel 2021 onwards. If your workplace is on an older version, learn INDEX with MATCH properly, because that is what you will be allowed to use. Mention both and you cover either environment.
How long does it take to get Excel interview-ready?
Four to six weeks of regular practice is enough for most people to handle a fresher-level practical round at a few hours a week. The functions take days to learn and weeks to become fast at. Cleaning judgement, knowing what to check in an unfamiliar file, takes longest and only comes from real exports.
Learn Excel the way analysts actually use it
Watching formula tutorials builds recognition. Cleaning a genuinely messy file while someone reviews your approach builds the speed a practical round tests.
SkilloVilla’s Excel: Beginner to Advanced course at ₹33,110 is taught live with mentor reviews rather than as recordings. If you want Excel inside a full analyst path with SQL, Power BI, statistics and interview preparation, the Data Analytics with Python track is ₹71,999, currently ₹58,999 and includes placement support. Scholarships are available depending on your profile.
Fees and ratings last checked August 2026; confirm current numbers with the provider before enrolling.
Still deciding? Compare prices in our data analyst course fees breakdown and check what employers ask for in data analyst qualifications in India.
