Scribd
Upload
30 views3 pages

Essential Excel Formulas & When to Use Them

The document provides a comprehensive list of essential Excel formulas categorized by their functions, including basic math, text manipulation, lookup functions, logical operations, data cleaning, and date/time functions. Each formula is accompanied by its use case to help users understand when to apply them. This serves as a practical guide for data management and analysis in Excel.

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
30 views3 pages

Essential Excel Formulas & When to Use Them

The document provides a comprehensive list of essential Excel formulas categorized by their functions, including basic math, text manipulation, lookup functions, logical operations, data cleaning, and date/time functions. Each formula is accompanied by its use case to help users understand when to apply them. This serves as a practical guide for data management and analysis in Excel.

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

Here’s a list of the most important Excel formulas for data management and analysis, along
with their use cases:

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, Counts how many values meet a condition


">50")

=SUMIF(A:A, Adds only the numbers that meet a condition


">50")

2️⃣ Text Manipulation


Formula Use Case

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

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


B2)

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

=RIGHT(A2, 4) Extracts the last 4 characters from a 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, Same as VLOOKUP, but searches horizontally


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

4️⃣ Logical & Conditional Formulas


Formula Use Case

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


return "Low"

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


"Invalid")

=IF(OR(A2>50, B2<100), "Valid", If at least one condition is true, 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