[go: up one dir, main page]
Scribd
Upload
34 views9 pages

Excel

The document outlines essential Excel formulas for data analysts, categorized into basic, logical, lookup, text, date & time, statistical, and data cleaning functions. Each formula is accompanied by a brief description and example usage. This resource serves as a quick reference for commonly used Excel functions to enhance data analysis skills.

Uploaded by

Mdmuneer Shaik
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
34 views9 pages

Excel

The document outlines essential Excel formulas for data analysts, categorized into basic, logical, lookup, text, date & time, statistical, and data cleaning functions. Each formula is accompanied by a brief description and example usage. This resource serves as a quick reference for commonly used Excel functions to enhance data analysis skills.

Uploaded by

Mdmuneer Shaik
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 9

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

You might also like