Scribd
Upload
4 views3 pages

Essential Excel Formulas & When to Use Them 2

The document outlines essential Excel formulas categorized into six sections: Basic Math & Counting, Text Manipulation, Lookup & Search Functions, Logical & Conditional Formulas, Data Cleaning & Error Handling, and Date & Time Functions. Each section includes specific formulas along with their use cases to assist users in performing various tasks in Excel. This serves as a quick reference guide for effectively utilizing Excel formulas.

Uploaded by

gracepulka
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
4 views3 pages

Essential Excel Formulas & When to Use Them 2

The document outlines essential Excel formulas categorized into six sections: Basic Math & Counting, Text Manipulation, Lookup & Search Functions, Logical & Conditional Formulas, Data Cleaning & Error Handling, and Date & Time Functions. Each section includes specific formulas along with their use cases to assist users in performing various tasks in Excel. This serves as a quick reference guide for effectively utilizing Excel formulas.

Uploaded by

gracepulka
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/ 3

Essential Excel Formulas & When to Use

Them
1️⃣ Basic Math & Counting
Formula Use Case

=SUM(A:A) Adds up all numbers in a range

=AVERAGE(A:A) Finds the average (mean) of a range

=COUNT(A:A) Counts how many numeric values are in a


range

=COUNTA(A:A) Counts all non-empty cells (text & numbers)

=COUNTIF(A:A, ">50") Counts how many values meet a condition

=COUNTIFS(A:A, ">50", B:B, Counts values meeting multiple conditions


"<100")

=SUMIF(A:A, ">50") Adds only numbers that meet a condition

=SUMIFS(A:A, B:B, "<100") Adds numbers meeting multiple conditions

2️⃣ Text Manipulation


Formula Use Case

=A2&" "&B2 Combines first & last name with a space

=CONCATENATE(A2, B2) Joins two text values (but no space)

=TEXTJOIN(", ", TRUE, Joins multiple cells with a separator


A2:C2)

=LEFT(A2, 3) Extracts the first 3 characters from text

=RIGHT(A2, 4) Extracts the last 4 characters from text


=MID(A2, 2, 3) Extracts 3 characters starting at position
2

=LEN(A2) Counts the number of characters in a cell

=TRIM(A2) Removes extra spaces from text

3️⃣ Lookup & Search Functions


Formula Use Case

=VLOOKUP(1001, A:B, 2, FALSE) Finds a value in a table (e.g., price of product ID


1001)

=HLOOKUP("Product A", A1:Z2, 2, Searches horizontally for a value


FALSE)

=INDEX(A:A, 5) Returns the 5th value in column A

=MATCH(1001, A:A, 0) Finds the row number of value 1001 in column


A

=XLOOKUP(1001, A:A, B:B, "Not Advanced lookup (replacement for VLOOKUP)


Found")

4️⃣ Logical & Conditional Formulas


Formula Use Case

=IF(A2>100, "High", "Low") If A2 is greater than 100, return "High"; else


"Low"

=IF(AND(A2>50, B2<100), "Valid", If both conditions are met, return "Valid"


"Invalid")

=IF(OR(A2>50, B2<100), "Valid", If at least one condition is met, return "Valid"


"Invalid")

5️⃣ Data Cleaning & Error Handling


Formula Use Case

=ISNUMBER(A2) Returns TRUE if A2 is a number

=ISERROR(A2/B2) Returns TRUE if there's an error

=IFERROR(A2/B2, If there's an error, show "Error!" instead of #DIV/0


"Error!")

6️⃣ Date & Time Functions


Formula Use Case

=TODAY() Returns today’s date

=NOW() Returns current date & time

=YEAR(A2) Extracts the year from a date

=MONTH(A2) Extracts the month from a date

=DAY(A2) Extracts the day from a date

=DATEDIF(A2, B2, Calculates the number of days between two


"D") dates

You might also like