Ian Klosowicz
The Excel skills data analysts are actually tested on in interviews are VLOOKUP and XLOOKUP, pivot tables, IF and nested IF logic, SUMIF and COUNTIF, basic data cleaning functions, and the ability to structure a spreadsheet another person can read. That's the real list. Not 400 functions. Not macros. Not VBA.
I've worked in data long enough to know what comes up at work versus what gets put on a job description because someone copied it from another posting. They're not the same list.
Most Excel interview questions for analyst roles fall into 4 categories: can you look something up across tables, can you summarize data, can you apply logic, and can you build something a non-analyst can use.
Everything else is either role-specific (financial modeling, macros) or a stretch requirement someone added to the job post without thinking about it.
Here's what shows up consistently across entry-level and mid-level analyst interviews:
If you can do all of those without hesitation, you'll pass the Excel portion of any standard analyst interview. The companies doing truly advanced Excel testing are the exception, and they'll tell you upfront.
Lookup functions are the most commonly tested Excel skill in analyst interviews. The question usually involves 2 tables and a task like "bring the region from this reference table into the main dataset based on the store ID."
VLOOKUP is the one you absolutely need to know. It's older but still dominant in most company environments, and interviewers often use it specifically because they assume you know it.
The syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
The 4th argument trips people up most. FALSE means exact match. Always use FALSE unless you have a specific reason not to. VLOOKUP with an approximate match is a different tool that analysts rarely need.
XLOOKUP is the modern replacement. It doesn't have VLOOKUP's column-number limitation, handles left-side lookups natively, and has cleaner error handling. If the company you're interviewing with uses Microsoft 365, there's a decent chance they've moved to XLOOKUP. Know both.
INDEX MATCH is the one power users swear by. It's more flexible than VLOOKUP and works when you need to look left or match on multiple criteria. You don't need to master it for entry-level interviews, but understanding what it does and when you'd reach for it over VLOOKUP is worth knowing.
The functions that get tested are a subset of the Excel functions worth memorizing, so start there and narrow down.
Pivot tables are the fastest way to summarize a flat dataset in Excel. Interviewers test them because they're the first thing any analyst reaches for when handed a raw export and asked to find something in it.
What you need to be able to do:
The interview scenario is usually: "Here's a dataset of sales transactions. Show me total revenue by region by month." If you can build that pivot in under 2 minutes and format it so the numbers are readable, you've passed that section.
Where people fail: building the pivot but not knowing how to refresh it, or not knowing how to change the aggregation. Practice both.
Pivot tables come up in almost every Excel interview, so it is worth being genuinely comfortable with how pivot tables actually work rather than just recognizing them.
IF statements show up in 2 forms in analyst interviews: standalone logic and nested logic.
Standalone: =IF(A2>100, "High", "Low") — if this condition is true, return this, otherwise return that. Most people can handle this.
Nested: =IF(A2>100, "High", IF(A2>50, "Medium", "Low")) — multiple conditions stacked inside each other. This is where people slow down. Practice building nested IFs 3 or 4 levels deep until it feels automatic.
IFS is the cleaner alternative: =IFS(A2>100, "High", A2>50, "Medium", TRUE, "Low"). More readable than deep nesting. Know both — some interviewers will specifically ask which you prefer and why.
The practical test is usually something like: "Flag orders as High, Medium, or Low priority based on dollar value." Simple setup, clean signal on whether you can apply logic without help.
These are the conditional aggregation functions — they let you sum or count rows that meet a specific condition without building a pivot table.
SUMIF: =SUMIF(range, criteria, sum_range) — sum the values in one column where another column meets a condition.
COUNTIF: =COUNTIF(range, criteria) — count rows where a column meets a condition.
SUMIFS and COUNTIFS are the multi-condition versions. They let you add as many criteria as you need: =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2).
The interview question is usually something like: "What's total revenue for the Northeast region in Q3?" You can answer that with a pivot table, but being able to write a SUMIFS formula shows you can also answer it inline, without restructuring the data.
Know the difference between SUMIF and SUMIFS argument order — they're reversed, which catches people off guard. SUMIF puts the sum range last. SUMIFS puts it first.
Real data is messy. Analyst interviews often include a "clean this dataset" section specifically to see if you know how to handle it.
The functions that come up:
The practical scenario: "This export has trailing spaces in the customer name column and the date is formatted as text. Fix it." TRIM plus a date conversion formula handles most of that.
In my data engineering work I see messy source data constantly — Snowflake pipelines have to handle the same problems Excel functions solve, just at scale. The underlying thinking is identical: identify what's wrong, apply the right transformation, verify the output. Learning it in Excel first is the right sequence.
This one doesn't get tested explicitly, but it shows up in every practical Excel interview. You're handed a task, you build something, and the interviewer can immediately see whether you know how to structure a spreadsheet another person can read.
What good structure looks like:
The fastest way to show you don't know what you're doing in Excel is to build a pivot table on the same tab as the raw data, leave numbers unformatted, and have formulas referencing cells 4 sheets away with no explanation.
If you want a structured path through Excel alongside SQL and Power BI, the Analyst Hive program covers the specific parts that matter for analyst roles — not 400 functions, but the ones you'll actually use day one. Check it out at analysthive.io.
A lot of Excel tutorials spend time on things you won't need for a standard analyst interview or entry-level role:
The goal at the interview stage is to show competence on the core functions, not breadth. Get fast at the things on the tested list first. The rest comes with time on the job.
Do data analysts actually use Excel or is it all SQL and Python?
Both. SQL pulls and transforms the data. Excel is often where the output lives — a formatted table, a pivot summary, a chart for a stakeholder who doesn't want a dashboard. Most analyst jobs still expect Excel competence even if the heavy lifting happens in SQL or a BI tool. The two don't replace each other at the entry level.
Is VLOOKUP still relevant or should I just learn XLOOKUP?
Learn both. XLOOKUP is better in almost every way, but a lot of companies still run older Excel versions where XLOOKUP isn't available. Interviewers also tend to ask about VLOOKUP specifically because it's been the standard for 20 years. Know XLOOKUP as your go-to; know VLOOKUP because you'll be asked about it.
How advanced does my Excel need to be to get an entry-level analyst job?
Comfortable with pivot tables, VLOOKUP or XLOOKUP, SUMIFS and COUNTIFS, IF logic, and basic data cleaning functions. That covers the vast majority of entry-level Excel interviews. You don't need macros, VBA, or advanced financial functions unless the role specifically calls for them.
Will I be tested on Excel in a data analyst interview?
Often yes, especially at companies where Excel is part of the day-to-day workflow. The test is usually practical — a take-home file or a screen share where you're asked to complete a task. Knowing the functions isn't enough; you need to be fast enough that you're not visibly hunting through menus during a live screen share.
What's the difference between SUMIF and SUMIFS?
SUMIF handles 1 condition. SUMIFS handles multiple conditions and also reverses the argument order — the sum range comes first in SUMIFS, last in SUMIF. That argument order difference catches people off guard constantly. If you're ever unsure, use SUMIFS for everything; it works fine with just 1 condition too.
Should I use INDEX MATCH instead of VLOOKUP?
For entry-level interviews, VLOOKUP or XLOOKUP is fine. INDEX MATCH is worth learning because it handles left-side lookups and multi-criteria matching that VLOOKUP can't do cleanly. But if you're choosing between getting faster at pivot tables and learning INDEX MATCH, do the pivot tables first. That's what gets tested more often.
Analyst Hive is a day-by-day program that covers Excel, SQL, and Power BI in the context of building a portfolio and getting hired — not as standalone tools but as part of a job search that actually moves. Learn more at skool.com/analysthive/about.
