Microsoft Excel offers a wide range of formulas and functions to perform calculations,

analyze data, and automate tasks. Below, I’ll provide a concise overview of some essential
Excel formulas, categorized by their use case, with examples. These include basic arithmetic,
logical, lookup, text, date/time, and financial formulas, as commonly used in Excel for
Microsoft 365, Excel 2024, and earlier versions.

1. Basic Arithmetic Formulas

These perform simple mathematical operations using operators like +, -, *, /, and ^.

Addition: =A1+B1
Adds values in cells A1 and B1. Example: If A1=5 and B1=3, result is 8.
Subtraction: =A1-B1
Subtracts B1 from A1. Example: If A1=5 and B1=3, result is 2.
Multiplication: =A1*B1
Multiplies A1 by B1. Example: If A1=5 and B1=3, result is 15.
Division: =A1/B1
Divides A1 by B1. Example: If A1=6 and B1=2, result is 3.
Exponentiation: =A1^2
Raises A1 to the power of 2. Example: If A1=4, result is 16.
SUM: =SUM(A1:A5)
Adds all values in the range A1 to A5. Example: If A1=1, A2=2, A3=3, A4=4,
A5=5, result is 15.
AVERAGE: =AVERAGE(A1:A5)
Calculates the average of values in A1 to A5. Example: For 1, 2, 3, 4, 5, result
is 3.
PRODUCT: =PRODUCT(A1:A3)
Multiplies all values in A1 to A3. Example: If A1=2, A2=3, A3=4, result is 24.

2. Logical Formulas
These evaluate conditions and return results based on logic.

IF: =IF(A1>10, "High", "Low")

Returns "High" if A1 is greater than 10, otherwise "Low". Example: If A1=15,
result is "High".
 IFERROR: =IFERROR(A1/B1, "Error")
Returns the result of A1/B1 or "Error" if the calculation fails (e.g., division by
zero). Example: If A1=10 and B1=0, result is "Error".
AND: =IF(AND(A1>5, B1<10), "Yes", "No")
Checks if A1>5 and B1<10. Returns "Yes" if both are true, else "No". Example:
If A1=7 and B1=8, result is "Yes".
OR: =IF(OR(A1>10, B1>10), "Pass", "Fail")
Returns "Pass" if either A1 or B1 is greater than 10, else "Fail". Example: If
A1=12 and B1=5, result is "Pass".

3. Lookup and Reference Formulas

These find and retrieve data from tables or ranges.

XLOOKUP: =XLOOKUP(A1, B1:B10, C1:C10)

Searches for A1 in B1:B10 and returns the corresponding value from C1:C10.
Example: If A1="Apple", B1:B3={"Apple", "Banana", "Orange"}, C1:C3={10, 20,
30}, result is 10. XLOOKUP is preferred over VLOOKUP for its flexibility and
exact matching.
VLOOKUP: =VLOOKUP(A1, B1:C10, 2, FALSE)
Searches for A1 in the first column of B1:C10 and returns the value from the
second column. FALSE ensures an exact match. Example: Similar to
XLOOKUP but limited to vertical lookup.
INDEX/MATCH: =INDEX(C1:C10, MATCH(A1, B1:B10, 0))
Combines INDEX (returns value from C1:C10) with MATCH (finds A1’s
position in B1:B10). More flexible than VLOOKUP. Example: If A1="Banana",
B1:B3={"Apple", "Banana", "Orange"}, C1:C3={10, 20, 30}, result is 20.
HLOOKUP: =HLOOKUP(A1, B1:E2, 2, FALSE)
Similar to VLOOKUP but searches horizontally. Example: Searches A1 in
B1:E1 and returns the value from B2:E2.

4. Text Formulas
These manipulate and combine text strings.
 CONCATENATE: =CONCATENATE(A1, " ", B1)
Joins text from A1 and B1 with a space. Example: If A1="John" and B1="Doe",
result is "John Doe".
TEXT: =TEXT(A1, "dd/mm/yyyy")
Formats A1 as a date. Example: If A1 contains 01/15/2025, result is
"15/01/2025".
LEFT: =LEFT(A1, 3)
Extracts the first 3 characters from A1. Example: If A1="Hello", result is "Hel".
RIGHT: =RIGHT(A1, 3)
Extracts the last 3 characters from A1. Example: If A1="Hello", result is "llo".
TRIM: =TRIM(A1)
Removes extra spaces from A1. Example: If A1=" Hello ", result is "Hello".
LEN: =LEN(A1)
Returns the length of text in A1. Example: If A1="Hello", result is 5.

5. Date and Time Formulas

These handle dates and times for scheduling or analysis.

TODAY: =TODAY()
Returns the current date. Example: On July 20, 2025, result is 07/20/2025.
NOW: =NOW()
Returns the current date and time. Example: On July 20, 2025, at 01:39 AM,
result is 07/20/2025 01:39.
EOMONTH: =EOMONTH(A1, 3)
Returns the last day of the month, 3 months after the date in A1. Example: If
A1=01/01/2025, result is 04/30/2025.
DATEDIF: =DATEDIF(A1, B1, "d")
Calculates the number of days between dates in A1 and B1. Example: If
A1=01/01/2025 and B1=01/10/2025, result is 9.

6. Financial Formulas
These are useful for financial analysis and modeling.
 PMT: =PMT(5%/12, 360, 200000)
Calculates the monthly payment for a $200,000 loan at 5% annual interest
over 30 years (360 months). Example: Result is approximately -$1,073.64.
NPV: =NPV(5%, B1:B5)
Calculates the net present value of cash flows in B1:B5 at a 5% discount rate.
Example: If B1:B5={-1000, 300, 300, 300, 300}, result approximates the
present value.
IRR: =IRR(B1:B5)
Calculates the internal rate of return for cash flows in B1:B5. Example: For a
series of cash flows, it finds the rate where NPV=0.
SUMIFS: =SUMIFS(C1:C10, A1:A10, "Sales", B1:B10, ">100")
Sums values in C1:C10 where A1:A10="Sales" and B1:B10>100. Example:
Sums sales amounts over $100.
COUNTIFS: =COUNTIFS(A1:A10, "Sales", B1:B10, ">100")
Counts rows where A1:A10="Sales" and B1:B10>100. Example: Counts sales
transactions over $100.

7. Statistical Formulas
These analyze data distributions.

COUNT: =COUNT(A1:A10)
Counts numeric values in A1:A10. Example: If A1:A10 contains 5 numbers,
result is 5.
MIN: =MIN(A1:A10)
Returns the smallest value in A1:A10. Example: If A1:A10={10, 5, 8}, result is
5.
MAX: =MAX(A1:A10)
Returns the largest value in A1:A10. Example: If A1:A10={10, 5, 8}, result is 10.
RANDBETWEEN: =RANDBETWEEN(1, 100)
Returns a random number between 1 and 100. Example: Result could be 42.

8. Advanced Formulas
These combine functions for complex tasks, often used in financial modeling or
data analysis.

OFFSET: =SUM(OFFSET(B4, 0, 0, E2, 1))

Sums a dynamic range starting at B4, with height defined by E2. Example: If
E2=3, sums B4:B6.
CHOOSE: =CHOOSE(A1, 5%, 12%, 18%)
Returns the value based on A1’s index (1, 2, or 3). Example: If A1=2, result is
12%.
Nested IF with AND/OR: =IF(AND(A1>100, OR(B1="Yes", B1="Maybe")), "Valid", "Invalid")
Checks multiple conditions. Example: If A1=150 and B1="Yes", result is
"Valid".

Key Notes:
Formula Structure: All Excel formulas start with an equal sign (=) and may
include constants (e.g., 5), cell references (e.g., A1), operators (e.g., +, -), or
functions (e.g., SUM).

Cell References:

Relative: A1 adjusts when copied.

Absolute: $A$1 stays fixed.
Mixed: $A1 or A$1 locks column or row, respectively.

AutoSum: Use the AutoSum button (Home tab) to quickly insert =SUM() for a
range.

Formula AutoComplete: Type = and initial letters to see a dropdown of matching

functions.

Copying Formulas: Use the fill handle (bottom-right corner of a cell) to copy
formulas with adjusted references.
GUI Assistance: Click the “fx” button next to the formula bar to insert functions
via a dialog box.

Version Notes: Some functions (e.g., XLOOKUP) are only available in newer
versions like Excel for Microsoft 365 or Excel 2024. Older versions may use
VLOOKUP or HLOOKUP.

Performance Tip: For large datasets, avoid excessive nested formulas to prevent
slow calculations. Tools like ClickUp can help manage large datasets outside
Excel.