Ian Klosowicz
The Excel functions worth memorizing for analyst work are VLOOKUP, XLOOKUP, SUMIFS, COUNTIFS, IF, IFERROR, TRIM, TEXT, LEFT, RIGHT, MID, and the date functions. That's the real list. Not all 400+ functions in Excel. Not the obscure statistical ones nobody uses. The ones that show up in analyst interviews, take-home assessments, and actual day-to-day work.
Excel rewards the analyst who knows 15 functions cold over the one who sort-of knows 60. Speed and accuracy matter more than breadth.
These are the most tested Excel functions in analyst interviews. Know them cold.
VLOOKUP — pulls a value from another table based on a matching key. The syntax that trips people up: =VLOOKUP(lookup_value, table_array, col_index_num, FALSE). The 4th argument is almost always FALSE (exact match). Never forget it.
XLOOKUP — the modern replacement for VLOOKUP. Cleaner syntax, no column number to count, handles left-side lookups natively: =XLOOKUP(lookup_value, lookup_array, return_array). Learn this as your default; learn VLOOKUP because interviewers still ask about it.
INDEX + MATCH — two functions used together to handle lookups VLOOKUP can't do cleanly: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)). The 0 in MATCH means exact match. Worth knowing for multi-criteria lookups and left-side lookups in older Excel versions.
HLOOKUP — like VLOOKUP but horizontal. Rarely used in analyst work. Know it exists; don't spend much time on it.
These are the functions you reach for when you need a sum or count that only includes rows meeting a condition — without building a pivot table.
SUMIF — sum where 1 condition is met: =SUMIF(range, criteria, sum_range). Note the argument order: range first, criteria second, sum range last.
SUMIFS — sum where multiple conditions are met: =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2). Note the reversed argument order vs SUMIF — sum range comes first here. This catches people constantly.
COUNTIF — count rows where 1 condition is met: =COUNTIF(range, criteria).
COUNTIFS — count rows where multiple conditions are met: =COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2).
AVERAGEIF / AVERAGEIFS — same pattern as SUMIF/SUMIFS but returns an average. Less commonly tested but comes up in work when you need mean values by segment.
The SUMIF/SUMIFS argument order difference is the one that trips people up in live interviews. Practice writing both from memory until the argument order is automatic.
Logic functions let you apply conditions directly in a cell, flag rows, categorize values, or control what a formula returns based on a test.
IF — the most fundamental: =IF(logical_test, value_if_true, value_if_false). You'll use this in some form in almost every analyst deliverable.
IFS — multiple conditions without nesting: =IFS(condition1, value1, condition2, value2, TRUE, default_value). Cleaner than stacking IFs 4 levels deep. The TRUE at the end is the catch-all default.
AND — returns TRUE only if all conditions are true: =AND(condition1, condition2). Used inside IF to require multiple conditions: =IF(AND(A2>100, B2="East"), "Flag", "").
OR — returns TRUE if any condition is true: =OR(condition1, condition2). Used inside IF to flag rows meeting any of several conditions.
NOT — reverses a logical value. Less common but useful when you want the inverse of a condition without rewriting the whole test.
Raw data always has gaps. These functions keep your spreadsheets from breaking when a lookup fails or a formula hits an unexpected value.
IFERROR — wraps any formula and returns a custom value if it errors: =IFERROR(formula, value_if_error). The most common pattern in analyst work: =IFERROR(VLOOKUP(A2, table, 2, FALSE), ""). Returns blank instead of #N/A when the lookup key doesn't exist in the table.
IFNA — like IFERROR but only catches #N/A errors, not all errors. More precise when you want other error types to surface so you can fix them.
ISBLANK — returns TRUE if a cell is empty. Useful in IF statements: =IF(ISBLANK(A2), "Missing", A2).
ISNUMBER / ISTEXT — check whether a cell contains a number or text. Useful for diagnosing data quality issues when a column that should be numeric has mixed types.
IFERROR is the one you'll use constantly. Wrap every VLOOKUP and every MATCH in IFERROR by default. It takes 10 seconds and saves hours of debugging broken formulas when a key is missing.
Real data is messy. These functions clean it without manual editing.
TRIM — removes leading, trailing, and extra internal spaces: =TRIM(A2). Use it on any column that came from a form, export, or copy-paste. VLOOKUP failures are often caused by spaces that TRIM would fix.
PROPER — capitalizes the first letter of each word: =PROPER(A2). Standardizes name fields that have inconsistent casing.
UPPER / LOWER — converts text to all caps or all lowercase. Use before comparing or matching text values to avoid case-sensitivity issues.
LEFT — extracts characters from the left side of a string: =LEFT(A2, 3) returns the first 3 characters. Useful for extracting codes, prefixes, or region identifiers embedded in a longer string.
RIGHT — extracts characters from the right side: =RIGHT(A2, 4).
MID — extracts characters from the middle: =MID(A2, start_position, num_chars). =MID(A2, 4, 3) starts at character 4 and returns 3 characters.
LEN — returns the length of a string in characters. Useful for validating fixed-length IDs or phone numbers: =IF(LEN(A2)<>10, "Invalid", "OK").
FIND / SEARCH — find the position of a character or substring within a string. FIND is case-sensitive; SEARCH is not. Often combined with LEFT, MID, or RIGHT to extract variable-length substrings.
SUBSTITUTE — replaces one string with another: =SUBSTITUTE(A2, "-", "") removes all hyphens. Useful for standardizing formats before matching or lookups.
TEXT — converts a number or date to a formatted string: =TEXT(A2, "YYYY-MM") turns a date into a year-month label. Essential when you need dates in a specific format for grouping or concatenation.
CONCATENATE / CONCAT / & — joins text strings together. The ampersand operator is the fastest: =A2&" "&B2. CONCAT is the modern function version. CONCATENATE is older; avoid it in new work.
I use TRIM and IFERROR(VLOOKUP()) together constantly in my data work. Source data from external systems almost always has trailing spaces that break exact-match lookups. TRIM before the lookup, IFERROR around it, and 90% of lookup failures disappear.
Date handling is where a lot of analysts get tripped up. These functions cover what actually comes up in analyst work.
TODAY — returns the current date: =TODAY(). Use it for age calculations, days-since formulas, and dynamic date references that update automatically.
NOW — returns the current date and time. Less common in analyst work than TODAY.
DATE — constructs a date from year, month, day components: =DATE(2024, 3, 15). Useful when date parts are in separate columns and need to be combined.
YEAR / MONTH / DAY — extracts components from a date: =YEAR(A2), =MONTH(A2), =DAY(A2). Use these to add year, month, or day columns to a dataset without a pivot table.
EOMONTH — returns the last day of the month a specified number of months from a start date: =EOMONTH(A2, 0) returns the last day of the same month. Used for monthly aggregation and date bucketing.
DATEDIF — calculates the difference between 2 dates in days, months, or years: =DATEDIF(start_date, end_date, "D"). Undocumented in Excel's function help (intentionally omitted) but works fine. Use "D" for days, "M" for months, "Y" for years.
NETWORKDAYS — counts working days between 2 dates, excluding weekends: =NETWORKDAYS(start_date, end_date). Useful for SLA calculations and business-day timelines.
WEEKDAY — returns a number representing the day of the week: =WEEKDAY(A2, 2) returns 1 for Monday through 7 for Sunday. The second argument controls which day is counted as 1.
These are the ones that show up in basic analysis beyond SUM and COUNT.
ROUND / ROUNDUP / ROUNDDOWN — round numbers to a specified number of decimal places. =ROUND(A2, 2) rounds to 2 decimal places. Use ROUND before displaying percentages or currency to avoid floating-point display issues.
ABS — returns the absolute value. Useful when you care about the magnitude of a change, not the direction: =ABS(A2-B2).
MAX / MIN — largest or smallest value in a range. =MAX(A:A) finds the highest value in a column.
LARGE / SMALL — returns the Nth largest or smallest value: =LARGE(A:A, 3) returns the 3rd largest. Useful for top-N analysis without sorting the dataset.
RANK — returns the rank of a value within a range: =RANK(A2, A:A, 0). The 0 means descending (highest = rank 1).
MEDIAN — middle value in a range. More meaningful than AVERAGE when the data has outliers that would skew the mean.
STDEV — standard deviation. Comes up in analyst work for quality control, anomaly flagging, and performance distribution analysis.
If you want a structured path through Excel — not all of these at once but in the right order for building analyst skills — that's part of what Analyst Hive covers. Day-by-day tasks, portfolio projects, the specific functions that come up in interviews. It's covered inside Analyst Hive.
Reading a list of functions doesn't build recall. Here's what does:
Type, don't paste. Every time you use a function, type the whole thing from scratch. Don't copy from a prior cell or Google the syntax every time. The friction of typing is how the pattern sticks.
Practice on real data. Open a Kaggle dataset or a free BigQuery public table, think of 3 questions, and answer them using functions. Abstract practice on made-up data doesn't build the same recall as applying a function to data you actually care about.
Use the function daily for a week. One week of daily use is worth more than a 3-hour session. Spaced repetition is how syntax moves from "I know this exists" to "I can write it without thinking."
Test yourself before looking it up. If you forget an argument, try to remember before opening Google. The failed recall attempt is part of the learning. Looking it up immediately skips the step that builds memory.
The functions that stick fastest are the ones you actually need to answer a question you care about. Pick a dataset, pick a question, and find out which function solves it.
Memorizing the functions is one thing; knowing which of them show up in interviews is another, and the Excel skills analysts are tested on narrow the list further.
Knowing these functions cold is useful right up until the dataset outgrows the grid, which is really a question of when to move from Excel to SQL.
How many Excel functions do I actually need to know?
15 to 20 functions cover the vast majority of analyst work and interviews. VLOOKUP or XLOOKUP, SUMIFS, COUNTIFS, IF, IFERROR, TRIM, TEXT, LEFT/RIGHT/MID, and the basic date functions get you through most situations. Depth on fewer functions beats shallow knowledge of many.
Is XLOOKUP replacing VLOOKUP?
In new work, yes. XLOOKUP is cleaner, more flexible, and doesn't require you to count column numbers. But a lot of companies still run older Excel versions without XLOOKUP, and interviewers still ask about VLOOKUP specifically. Learn XLOOKUP as your default; know VLOOKUP because it comes up in interviews and legacy files.
What's the most useful Excel function for data analysis?
SUMIFS is the one that shows up most often in both analyst work and interviews. It answers the most common analyst question — "what's the total for this segment under these conditions" — without needing a pivot table. If you had to pick one function to know cold, SUMIFS is it.
Why does VLOOKUP return #N/A?
#N/A means the lookup value wasn't found in the first column of your table array. The most common causes: trailing spaces (fix with TRIM), mismatched data types (the lookup value is text but the table has numbers, or vice versa), or the value genuinely doesn't exist in the table. Wrap the whole formula in IFERROR to handle the missing cases; fix the root cause to understand why they're missing.
Should I learn Excel formulas or Power Query?
Formulas first. Power Query is powerful for combining and transforming data from multiple files, but it's a separate skill with its own learning curve and it's rarely tested in entry-level analyst interviews. Get comfortable with the core formula set, then add Power Query when you encounter a task that formulas can't handle efficiently.
What's the difference between SUMIF and SUMIFS?
SUMIF handles 1 condition and puts the sum range last. SUMIFS handles multiple conditions and puts the sum range first. That argument order reversal catches people constantly. When in doubt, use SUMIFS for everything — it works fine with a single condition and the argument order is consistent when you add more conditions later.
Analyst Hive is a day-by-day program that covers the Excel functions and skills that actually show up in analyst interviews and roles — not a tour of all 400 functions, but the ones that matter for getting hired and doing the job. Join Analyst Hive.
