Instructor: waseem shareef jamro
Notes: All basics formula of excel
Here are detailed notes covering all basic formulas in Excel, explained clearly with examples
and use-cases. These are perfect for learning, revising, or preparing for assignments or
exams.
Excel Notes: Basic Formulas Explained
1. SUM()
Use: Adds a range of numbers.
Syntax:
=SUM(number1, [number2], ...)
Example:
=SUM(A1:A5)
Adds values from A1 to A5.
2. AVERAGE()
Use: Calculates the average (mean) of a group of numbers.
Syntax:
=AVERAGE(number1, [number2], ...)
Example:
=AVERAGE(B1:B4)
Returns the average of values in B1 to B4.
3. MAX()
Use: Returns the highest number in a range.
Syntax:
=MAX(number1, [number2], ...)
Example:
=MAX(C1:C10)
Returns the largest value in the range C1 to C10.
4. MIN()
Use: Returns the smallest number in a range.
Syntax:
=MIN(D1:D10)
Example:
=MIN(D1:D10)
Finds the smallest value from D1 to D10.
5. COUNT()
Use: Counts the number of cells that contain numbers.
Syntax:
=COUNT(value1, [value2], ...)
Example:
=COUNT(A1:A10)
Counts only numeric values in A1 to A10.
6. COUNTA()
Use: Counts non-empty cells (numbers + text).
Syntax:
=COUNTA(value1, [value2], ...)
Example:
=COUNTA(B1:B10)
Counts all filled cells (text, numbers, formulas).
7. IF()
Use: Logical test to return values based on condition.
Syntax:
=IF(condition, value_if_true, value_if_false)
Example:
=IF(C2>=35, "Pass", "Fail")
Returns "Pass" if C2 is 35 or more, otherwise "Fail".
8. IFERROR()(optional )
Use: Returns a value if there's an error, otherwise returns the formula result.
Syntax:
=IFERROR(value, value_if_error)
Example:
=IFERROR(A1/B1, "Error")
Prevents error if B1 is 0.
9. VLOOKUP()
Use: Searches for a value in the first column and returns from another column.
Syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Example:
=VLOOKUP(101, A2:C10, 2, FALSE)
Looks for 101 in column A and returns value from column 2.
10. HLOOKUP()
Use: Similar to VLOOKUP but works horizontally.
Syntax:
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Example:
=HLOOKUP("Math", A1:D4, 3, FALSE)
11. CONCATENATE() / CONCAT()
Use: Combines text from multiple cells into one.
Syntax (new version):
=CONCAT(text1, text2, ...)
Example:
=CONCAT(A1, " ", B1)
Combines first and last name.
12. TEXT()
Use: Format a number/date as text.
Syntax:
=TEXT(value, format_text)
Example:
=TEXT(TODAY(), "dd-mm-yyyy")
Formats the current date.
13. LEFT(), RIGHT(), MID()
Use: Extract characters from text.
LEFT:
=LEFT(A1, 3)
Extracts first 3 letters from A1.
RIGHT:
=RIGHT(A1, 4)
Extracts last 4 letters from A1.
MID:
=MID(A1, 2, 3)
Starts at 2nd character, extracts 3 characters.
14. LEN()
Use: Returns length of a text string.
Example:
=LEN(A1)
15. NOW() and TODAY()
NOW(): Returns current date and time
=NOW()
TODAY(): Returns current date
=TODAY()
16. ROUND(), ROUNDUP(), ROUNDDOWN()
ROUND(): Rounds number to specified digits
=ROUND(3.4567, 2) → 3.46
ROUNDUP(): Always rounds up
=ROUNDUP(3.1, 0) → 4
ROUNDDOWN(): Always rounds down
=ROUNDDOWN(3.9, 0) → 3
17. ABS()
Use: Returns absolute value (positive number).
=ABS(-50) → 50
18. TRIM()
Use: Removes extra spaces.
=TRIM(A1)
19. PROPER(), UPPER(), LOWER()
• PROPER(A1) – First letter capital in each word
• UPPER(A1) – ALL CAPS
• LOWER(A1) – all small
20. RANK()
Use: Ranks a number in a list.
=RANK(A2, A2:A10)
Summary Table
Formula Purpose Example
SUM() Add numbers =SUM(A1:A5)
AVERAGE() Find average =AVERAGE(A1:A5)
MAX(), MIN() Max/Min number =MAX(B1:B5)
IF() Logical condition =IF(B1>35,"Pass","Fail")
VLOOKUP() Lookup data vertically =VLOOKUP(101, A2:C5, 2, 0)
CONCAT() Combine text =CONCAT(A1," ",B1)
LEN() Count characters =LEN(A1)
TODAY() Current date =TODAY()
