Excel rounds test lookup functions (VLOOKUP, XLOOKUP, INDEX-MATCH), pivot tables for summarisation, conditional logic (IF, SUMIFS, COUNTIFS), data cleaning with text functions, and chart building. Analyst roles in Indian services and GCC companies still screen Excel heavily — fluency here is often the first filter before SQL.
Microsoft Excel is a spreadsheet program, part of Microsoft 365: a grid of rows and columns where you type data and write formulas that calculate from it, then sort, filter, summarise with pivot tables and chart the result. In simple words, it is a table and a calculator in one file. Analysts use it to clean, check and summarise data quickly.
Example: a 5,000-row sales export becomes a region-by-month summary in one pivot table, and a SUMIFS formula answers one question from it in one cell
Why it is used: nothing needs building before you start, a one-off analysis takes minutes, and the person reading the file can change a number and see the result
Why it matters for jobs: Excel was named in 23.6% of 2,467 data-analyst postings in PrepNPlaced's India Tech Hiring Report (23 August to 27 September 2026), second only to SQL among the 18 technologies counted, and in 81% of the 328 Indian analytics job descriptions AccioJob reviewed (July 2026)
How do you find the difference between two columns, two dates or two cells in Excel?
Subtract for numbers and dates, compare with = for text, and use DATEDIF when you need whole months or years. Excel stores dates as serial numbers, so =B2-A2 between two dates returns the number of days. The usual trap is text that only looks like a number or a date: the formula then errors or compares wrongly.
Two cells
=B2-A2 gives the difference between two numbers; =ABS(B2-A2) gives it without the sign, whichever is larger.
Two columns, row by row
=A2=B2 returns TRUE or FALSE for each row, =IF(A2=B2, "Match", "Different") labels it, and =EXACT(A2, B2) also checks capital letters. A conditional formatting rule with the formula =$A2<>$B2 highlights every row that differs.
Values in one column missing from the other
=COUNTIF(B:B, A2)=0 is TRUE when the value in A2 appears nowhere in column B. XLOOKUP with an if-not-found argument does the same job and returns the match when there is one.
Two dates
=B2-A2 returns days. =DATEDIF(A2, B2, "m") returns whole months and "y" whole years, with the earlier date first. =NETWORKDAYS(A2, B2) counts working days, Monday to Friday.
What is the difference between Excel and CSV?
A CSV file is plain text: one table of values separated by commas, with no formulas, formatting, charts or second sheet. An Excel workbook (.xlsx) keeps several sheets, formulas, formatting, data types, pivot tables and charts. Use CSV to move data between systems, such as loading it into SQL or Python, and Excel to work on it.
CSV moves data between systems; an Excel workbook is where people work on it
Dimension
CSV
Excel workbook (.xlsx)
What it stores
Values only, as plain text
Values, formulas, formatting, charts and pivot tables
Sheets
One table per file
Many sheets in one workbook
Data types
None: every value is text until a program reads it
Numbers, dates and text kept as entered
Opens in
Any text editor, Excel, Python or a database loader
Excel and other spreadsheet programs
Common trap
Excel drops leading zeros and turns long IDs into scientific notation when it opens the file
Saving as CSV keeps only the active sheet and loses formulas and formatting
Use it for
Exchanging data and loading it into SQL or Python
Analysis, models and reports people read and edit
Which Excel fundamentals get tested first?
Interviews check that you can look up data, reference cells correctly, and summarize with the everyday functions analysts use daily.
VLOOKUP, HLOOKUP, and XLOOKUP
Absolute vs relative references
SUMIF/SUMIFS and COUNTIF/COUNTIFS
When do you use VLOOKUP, XLOOKUP and INDEX-MATCH?
VLOOKUP is fine when the key sits in the first column and the sheet never changes shape. XLOOKUP is the default on a current install: exact match out of the box and a built-in not-found argument. INDEX-MATCH is what we write when the file must open in older Excel or the lookup is two-way.
Choose by the Excel version the file must open in
Dimension
VLOOKUP
XLOOKUP
INDEX-MATCH
Looks left of the key column
No, key must be the first column
Yes
Yes
Exact match by default
No, fourth argument defaults to approximate
Yes
No, MATCH defaults to approximate, pass 0
Survives inserting a column
No, the hardcoded column number shifts
Yes, return range is a reference
Yes, both ranges are references
Not-found handling
Wrap in IFERROR
Built-in if_not_found argument
Wrap in IFERROR
Two-way lookup (row and column)
No
Yes, nest one XLOOKUP inside another
Yes, one MATCH for each axis
Works in older Excel
Yes, every version
No, newer Excel only
Yes, every version
What analysis and cleaning tasks come up?
Expect pivot tables, removing duplicates, data validation, text functions, and conditional formatting — the tools for turning raw data into insight.
Pivot tables for summarizing
Cleaning with text functions and TRIM
Data validation and conditional formatting
What counts as advanced Excel in interviews?
Senior analyst questions cover INDEX-MATCH over VLOOKUP, Power Query for repeatable data prep, and reliable error handling.
INDEX-MATCH and XLOOKUP
Power Query (Get & Transform)
IFERROR and error types
Excel interview questions and answers: the ten that come up in analyst screens
Analyst screens use Excel questions because the answer shows whether you have cleaned real data or only watched it done. Each answer below is short enough to say aloud; the interviewer wants the mechanism and the trap, not the menu path.
VLOOKUP returns the wrong row and nobody notices
XLOOKUP against INDEX and MATCH
SUMIFS with two conditions and a date range
Pivot table: rows, values and why the count is off
Remove duplicates without losing the latest record
Text to columns, TRIM and the invisible space
Why did VLOOKUP return the wrong customer?
The fourth argument was left as TRUE (approximate match), so on an unsorted list it returned the nearest lower value instead of an error. Always pass FALSE, or use XLOOKUP, which is exact by default and takes an if-not-found value.
XLOOKUP vs INDEX-MATCH
Both look left and right and both use exact match. XLOOKUP is one function with a built-in not-found argument and a search-from-last option; INDEX-MATCH works on every Excel version and is what older teams still write. Know both; say which your workplace uses.
Sales in March for the East region
=SUMIFS(Sales[Amount], Sales[Region], "East", Sales[OrderDate], ">="&DATE(2026,3,1), Sales[OrderDate], "<"&DATE(2026,4,1)). The two date conditions bracket the month; typing "March" against a date column returns zero, which is the trap.
The pivot count is higher than the number of customers
Count counts rows, not unique customers; a customer with three orders is counted three times. Add the data to the Data Model and use Distinct Count, or a helper column with COUNTIFS to flag the first occurrence.
Remove duplicates but keep the latest order per customer
Sort by customer, then by date descending, then Remove Duplicates on the customer column: Excel keeps the first row it meets, which after the sort is the latest. Remove Duplicates without the sort keeps whichever row appeared first in the file.
The lookup fails but the values look identical
A trailing space or a non-breaking space from a web export. =LEN(A2) against the visible length shows it; =TRIM(A2) fixes ordinary spaces and SUBSTITUTE(A2, CHAR(160), "") fixes the non-breaking one.
Absolute and relative references
=B2*$E$1 keeps E1 fixed when copied down; =B2*E1 shifts to E2, E3 and returns zeros. Mixed references ($E1, E$1) lock one axis, which is what a two-way multiplication table needs.
How would you turn twelve month columns into one Month column?
Power Query: select the id columns, Unpivot Other Columns, rename Attribute to Month and Value to Amount. It reruns on refresh; doing it by hand each month is the answer that loses the round.
IFERROR: when to use it and when not to
Wrap lookups that legitimately miss (a new product with no target yet) so the sheet stays readable. Do not wrap a calculation that should never fail; IFERROR hides the bug and the wrong number ships.
Conditional formatting that flags late deliveries
A formula rule on the whole table row: =$F2>$E2 (delivered after promised) with the row anchored by the dollar on the column, so one rule formats every row. Colour scales on a KPI column are the follow-up.
What Excel questions do data analyst freshers get, and what is the panel checking?
For a first analyst role the Excel round is usually a file and thirty minutes: clean it, summarise it, chart one thing, explain what you found. The panel checks four habits more than functions: whether you look at the raw data before touching it, whether you keep the original sheet, whether the pivot's numbers reconcile with a SUM of the column, and whether the chart answers a question or decorates the sheet.
Open the raw sheet and describe its problems before cleaning
Copy the raw data to its own tab and never edit it
Reconcile: the pivot total equals =SUM of the amount column
One chart, one question, a title that states the finding
Say what you would check next if you had an hour more
Question bank
14 interview questions with answers
Real questions from beginner to advanced, each with a concise model answer — practice them, then rehearse live in a mock interview. Every answer is open; collapse any you have already covered.
BeginnerWhat is the difference between VLOOKUP and HLOOKUP?
VLOOKUP searches for a value in the first column of a range and returns a value from a column to the right (vertical lookup). HLOOKUP does the same across rows (horizontal lookup). VLOOKUP is far more common because most data is arranged in columns.
BeginnerWhat is the difference between absolute and relative cell references?
A relative reference (A1) shifts when you copy the formula. An absolute reference ($A$1) stays fixed. Mixed references lock only the row or column ($A1 or A$1). Use absolute references for constants you don't want to move.
BeginnerWhat is a pivot table?
A pivot table summarizes large datasets by dragging fields into rows, columns, values, and filters — letting you group, aggregate, and cross-tabulate data (like sales by region and month) without formulas. It's one of Excel's most powerful analysis tools.
BeginnerWhat do SUMIF and COUNTIF do?
SUMIF adds values that meet a single condition, and COUNTIF counts cells that meet one. Their plural versions, SUMIFS and COUNTIFS, handle multiple conditions at once — for example summing sales for a specific region and month.
IntermediateWhat is the difference between VLOOKUP and INDEX-MATCH?
INDEX-MATCH looks up a value with MATCH and returns a cell with INDEX. It's more flexible than VLOOKUP because it can look to the left, isn't broken by inserting columns, and is often faster on large sheets.
IntermediateWhat is XLOOKUP and why is it better than VLOOKUP?
XLOOKUP is the modern lookup function that can search in any direction, return an exact match by default, handle 'not found' with a built-in argument, and return multiple columns — replacing both VLOOKUP and INDEX-MATCH in newer Excel.
IntermediateHow do you remove duplicates in Excel?
Use Data > Remove Duplicates to delete duplicate rows based on chosen columns, or use conditional formatting to highlight them first. For a non-destructive approach, use UNIQUE() (in newer Excel) or a pivot table.
IntermediateWhat is data validation?
Data validation restricts what can be entered in a cell — a dropdown list, a number range, or a date — to keep data clean and consistent. It's set via Data > Data Validation and can show input prompts and error messages.
IntermediateWhat are common text functions in Excel?
LEFT, RIGHT, and MID extract characters; LEN counts them; TRIM removes extra spaces; UPPER/LOWER/PROPER change case; CONCAT or TEXTJOIN combine text; and FIND/SEARCH locate a substring. These clean and reshape text data.
IntermediateWhat is conditional formatting?
Conditional formatting automatically styles cells based on their values or a formula — highlighting numbers above a threshold, color scales, data bars, or duplicates. It makes patterns and exceptions visible at a glance without manual formatting.
AdvancedWhat is Power Query in Excel?
Power Query (Get & Transform) connects to and cleans data with repeatable steps — merging files, splitting columns, unpivoting, and filtering — before loading it into Excel or the data model. It automates the data-prep work that would otherwise be manual.
AdvancedWhat are the limitations of VLOOKUP?
VLOOKUP can only look to the right of the lookup column, breaks when columns are inserted, needs the lookup column first, and defaults to approximate match (a common source of errors). INDEX-MATCH or XLOOKUP avoid these issues.
AdvancedHow do you handle errors in Excel formulas?
Wrap formulas in IFERROR (or IFNA) to replace errors like #N/A or #DIV/0! with a friendly value or blank. Understanding each error type — #N/A (not found), #REF! (bad reference), #VALUE! (wrong type) — helps you fix the root cause.
AdvancedWhat is the difference between COUNT, COUNTA, and COUNTBLANK?
COUNT counts cells containing numbers, COUNTA counts all non-empty cells (numbers and text), and COUNTBLANK counts empty cells. Choosing the right one avoids miscounting when a column mixes text, numbers, and blanks.
By company
Data Analyst interviews that ask these questions
See how this topic shows up in real Data Analyst loops — rounds, difficulty and company-specific questions:
Be fluent with lookups (VLOOKUP, INDEX-MATCH, XLOOKUP), pivot tables, conditional aggregation (SUMIFS/COUNTIFS), and data cleaning. Many interviews include a hands-on test, so practice speed on real datasets.
Is Excel still relevant for data analyst roles?
Yes. Excel remains the fastest tool for quick analysis and is expected in most analyst and business roles, usually alongside SQL and a BI tool like Power BI or Tableau.
What is the difference between Excel and Google Sheets?
Both are spreadsheets with the same core: cells, formulas, pivot tables and charts, and most everyday formulas work in both. Google Sheets runs in a browser, saves to Google Drive and lets several people edit at once with a free Google account. Excel is part of Microsoft 365 and adds Power Query, Power Pivot and the Data Model, the parts of Excel that analyst interviews ask about.
Is Excel the same as a spreadsheet?
No. A spreadsheet is the kind of program or file: a grid of cells that hold values and formulas. Excel is Microsoft's spreadsheet program; Google Sheets and LibreOffice Calc are others. Everyday formulas such as SUM, IF and VLOOKUP work in all three.
How can PrepNPlaced help?
Use AI Mock Interview for analyst-focused practice and the Interview Prep hub to plan your rounds and revise Excel alongside SQL and BI.
Next Step
Turn The Guide Into Practice
Use PrepNPlaced tools to turn this learning path into resume proof, targeted practice, and interview-ready explanations.