Scribd
Upload
18 views5 pages

Excel Formulas For Data Analysts

The document outlines essential Excel formulas for data analysts, categorized into basic formulas, logical and conditional formulas, text functions, and lookup & reference formulas. Each formula is accompanied by a brief description and an example of its usage. Key formulas include SUM, AVERAGE, IF, VLOOKUP, and XLOOKUP, among others.

Uploaded by

letsearn012
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
18 views5 pages

Excel Formulas For Data Analysts

The document outlines essential Excel formulas for data analysts, categorized into basic formulas, logical and conditional formulas, text functions, and lookup & reference formulas. Each formula is accompanied by a brief description and an example of its usage. Key formulas include SUM, AVERAGE, IF, VLOOKUP, and XLOOKUP, among others.

Uploaded by

letsearn012
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

Excel Formulas

Every Data
Analyst Must
Know (with
Examples!)
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.


Logical & Conditional Formulas:
• |F → =IF(A1>50, "Pass", "Fail") → Returnsa value based
on a condition.

• IFERROR → =IFERROR(A1/B1, "Error") → Handles errors


gracefully.
• IFNA → =|FNA(VLOOKUP(A1, B1:010, 2, FALSE), "Not
Found") → Handles #NJA 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.


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) → Repeatsa character multiple


times.

• TEXT → =TEXT(A1, "MM/DD/YYYY") → Formats a


number as text.
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, O) →> Returns the
position of a value in a range.
• CHOOSE → =CHOOSE(2, "Apple", "Banana", "Cherry")
→ Returns a value based on an index.

• OFFSET → =0FFSET(A1, 2, 1) → Returns a value at a


specific offset.

You might also like