Excel Formulas
Every Data
Analyst Must
Know (with
Examples!)
Swipe for more
Basic Formulas
SUM → =SUM(A1:A10) → Adds values in a range.
AVERAGE → =AVERAGE(A1:A10) → Calculates the mean.
COUNT → =COUNT(A1:A10) → Counts numeric values in a
range.
COUNTA → =COUNTA(A1:A10) → Counts all non-empty cells.
COUNTBLANK → =COUNTBLANK(A1:A10) → Counts blank
cells.
LEN → =LEN(A1) → Returns the length of a string.
TRIM → =TRIM(A1) → Removes extra spaces in text
CONCATENATE (or CONCAT) → =CONCAT(A1, B1) → Joins
text values.
TEXTJOIN → =TEXTJOIN(", ", TRUE, A1:A5) → Joins text with a
delimiter.
NOW → =NOW() → Returns current date & time.
TODAY → =TODAY() → Returns current date only.
Swipe for more
Logical & Conditional Formulas:
IF → =IF(A1>50, "Pass", "Fail") → Returns a value based
on a condition.
IFERROR → =IFERROR(A1/B1, "Error") → Handles errors
gracefully.
IFNA → =IFNA(VLOOKUP(A1, B1:C10, 2, FALSE), "Not
Found") → Handles #N/A errors.
IFS → =IFS(A1>90, "A", A1>80, "B", A1>70, "C") →
Multiple conditions in one formula.
AND → =AND(A1>50, B1<100) → Returns TRUE if all
conditions are met.
OR → =OR(A1>50, B1<100) → Returns TRUE if any
condition is met.
NOT → =NOT(A1=100) → Reverses a logical condition.
Swipe for more
Lookup & Reference Formulas:
VLOOKUP → =VLOOKUP(101, A2:C10, 2, FALSE) →
Searches for a value in a column.
HLOOKUP → =HLOOKUP(101, A2:C10, 2, FALSE) →
Searches for a value in a row.
XLOOKUP → =XLOOKUP(101, A:A, B:B, "Not Found") →
Advanced lookup (no need for sorting).
INDEX → =INDEX(A2:C10, 3, 2) → Returns a value at a
specific row/column.
MATCH → =MATCH(50, A2:A10, 0) → Returns the
position of a value in a range.
CHOOSE → =CHOOSE(2, "Apple", "Banana", "Cherry")
→ Returns a value based on an index.
OFFSET → =OFFSET(A1, 2, 1) → Returns a value at a
specific offset.
Swipe for more
Text Functions:
LEFT → =LEFT(A1, 5) → Extracts the first 5 characters.
RIGHT → =RIGHT(A1, 3) → Extracts the last 3
characters.
MID → =MID(A1, 3, 4) → Extracts characters from a
specific position.
FIND → =FIND("X", A1) → Finds the position of a
substring
.SEARCH → =SEARCH("X", A1) → Like FIND but case-
insensitive.
SUBSTITUTE → =SUBSTITUTE(A1, "Old", "New") →
Replaces a word.
REPT → =REPT("*", 5) → Repeats a character multiple
times.
TEXT → =TEXT(A1, "MM/DD/YYYY") → Formats a
number as text.
Swipe for more
Date & Time Functions:
YEAR → =YEAR(A1) → Extracts year from a date.
MONTH → =MONTH(A1) → Extracts month from a date.
DAY → =DAY(A1) → Extracts day from a date.
WEEKDAY → =WEEKDAY(A1, 2) → Returns the day of
the week (1=Monday).
EOMONTH → =EOMONTH(A1, 1) → Returns the last day
of the month.
DATEDIF → =DATEDIF(A1, B1, "Y") → Finds the
difference in years.
NETWORKDAYS → =NETWORKDAYS(A1, B1) →
Calculates working days between two dates.
WORKDAY → =WORKDAY(A1, 5) → Returns a future
date skipping weekends.
TIME → =TIME(12, 30, 0) → Creates a time value.
Swipe for more
Statistical & Mathematical Functions:
ROUND → =ROUND(A1, 2) → Rounds to 2 decimal
places.
ROUNDDOWN → =ROUNDDOWN(A1, 2) → Rounds
down.
ROUNDUP → =ROUNDUP(A1, 2) → Rounds up
.RAND → =RAND() → Generates a random number
between 0 and 1.
RANDBETWEEN → =RANDBETWEEN(1, 100) Generates
a random integer.
ABS → =ABS(A1) → Returns the absolute value.
SQRT → =SQRT(A1) → Returns the square root.
MOD → =MOD(A1, 3) → Returns remainder after
division.
POWER → =POWER(A1, 2) → Raises a number to a
power.
Swipe for more
Data Cleaning Functions:
CLEAN → =CLEAN(A1) → Removes non-printable
characters.
UNIQUE → =UNIQUE(A1:A10) → Returns unique values
.SORT → =SORT(A1:A10, 1, TRUE) → Sorts data in
ascending order.
FILTER → =FILTER(A1:A10, B1:B10="Yes") → Filters data
based on a condition.
REMOVE DUPLICATES (Excel Feature, Not Formula)
Swipe for more
If you
find this
helpful, please
like and share
it with your
friends