MS EXCELL
FORMULAS TAB
Alright — since you want all formulas explained from the Function Library in Excel’s
Formula Tab, I’ll go category by category, explaining each formula’s meaning, purpose,
syntax, and example in simple classroom-friendly language.
This will be long, but it will be complete so you can use it directly for teaching.
1. AutoSum (∑)
AutoSum is not just for SUM — it can also quickly insert SUM, AVERAGE, COUNT, MAX,
and MIN formulas.
Formulas:
1. SUM
oPurpose: Adds numbers in a range.
oSyntax: =SUM(number1, number2, ...)
oExample: =SUM(A1:A5) adds values in cells A1 to A5.
2. AVERAGE
o Purpose: Finds the average (mean) of numbers.
o Syntax: =AVERAGE(number1, number2, ...)
o Example: =AVERAGE(A1:A5)
3. COUNT
o Purpose: Counts how many cells contain numbers.
o Syntax: =COUNT(value1, value2, ...)
o Example: =COUNT(A1:A10)
4. MAX
o Purpose: Finds the largest value in a range.
o Syntax: =MAX(number1, number2, ...)
o Example: =MAX(A1:A5)
5. MIN
o Purpose: Finds the smallest value in a range.
o Syntax: =MIN(number1, number2, ...)
o Example: =MIN(A1:A5)
2. Recently Used
(No unique formulas — just a shortcut to your most recently used functions.)
3. Financial Functions
1. PMT
o Purpose: Calculates payment for a loan based on interest rate and term.
o Syntax: =PMT(rate, nper, pv)
rate = interest rate per period
nper = total number of periods
pv = present value (loan amount)
o Example: =PMT(5%/12, 60, 20000)
2. FV
o Purpose: Calculates future value of investment or savings.
o Syntax: =FV(rate, nper, pmt, [pv])
o Example: =FV(5%/12, 60, -200)
3. NPV
o Purpose: Calculates Net Present Value of an investment.
o Syntax: =NPV(rate, value1, value2, …)
o Example: =NPV(10%, -10000, 3000, 4200, 6800)
4. Logical Functions
1. IF
o Purpose: Returns one value if condition is TRUE, another if FALSE.
o Syntax: =IF(condition, value_if_true, value_if_false)
o Example: =IF(A1>=50, "Pass", "Fail")
2. AND
o Purpose: Returns TRUE if all conditions are TRUE.
o Syntax: =AND(condition1, condition2, …)
o Example: =AND(A1>10, B1<5)
3. OR
o Purpose: Returns TRUE if at least one condition is TRUE.
o Syntax: =OR(condition1, condition2, …)
o Example: =OR(A1>10, B1<5)
5. Text Functions
1. CONCAT
o Purpose: Joins text from multiple cells.
o Syntax: =CONCAT(text1, text2, …)
o Example: =CONCAT(A1, " ", B1)
2. LEFT
o Purpose: Returns first characters from text.
o Syntax: =LEFT(text, num_chars)
o Example: =LEFT("Excel", 2) → "Ex"
3. RIGHT
o Purpose: Returns last characters from text.
o Syntax: =RIGHT(text, num_chars)
o Example: =RIGHT("Excel", 3) → "cel"
4. LEN
o Purpose: Counts number of characters.
o Syntax: =LEN(text)
o Example: =LEN("Excel") → 5
6. Date & Time Functions
1. TODAY
o Purpose: Returns current date.
o Syntax: =TODAY()
o Example: =TODAY()
2. NOW
o Purpose: Returns current date and time.
o Syntax: =NOW()
o Example: =NOW()
3. DATEDIF
o Purpose: Calculates difference between two dates.
o Syntax: =DATEDIF(start_date, end_date, unit)
"Y" = years, "M" = months, "D" = days
o Example: =DATEDIF("1-Jan-2000", TODAY(), "Y")
7. Lookup & Reference Functions
1. VLOOKUP
o Purpose: Finds a value in the first column and returns related data from another
column.
o Syntax: =VLOOKUP(lookup_value, table_array, col_index_num,
[range_lookup])
o Example: =VLOOKUP(101, A2:D10, 3, FALSE)
2. HLOOKUP
o Purpose: Looks up a value in the first row and returns data from another row.
o Syntax: =HLOOKUP(lookup_value, table_array, row_index_num,
[range_lookup])
o Example: =HLOOKUP("Jan", A1:F3, 2, FALSE)
3. INDEX
o Purpose: Returns the value from a specific cell within a range.
o Syntax: =INDEX(array, row_num, [col_num])
o Example: =INDEX(A1:C10, 2, 3)
4. MATCH
o Purpose: Returns position of a value in a range.
o Syntax: =MATCH(lookup_value, lookup_array, [match_type])
o Example: =MATCH(50, A1:A10, 0)
8. Math & Trig Functions
1. ROUND
o Purpose: Rounds a number to a specific number of digits.
o Syntax: =ROUND(number, num_digits)
o Example: =ROUND(3.14159, 2) → 3.14
2. SUM
o Already explained in AutoSum.
3. PRODUCT
o Purpose: Multiplies numbers together.
o Syntax: =PRODUCT(number1, number2, …)
o Example: =PRODUCT(2, 3, 4) → 24
4. SIN, COS, TAN
o Purpose: Returns trigonometric values.
o Syntax: =SIN(number_in_radians)
o Example: =SIN(PI()/2) → 1
9. More Functions
Statistical:
AVERAGE, MEDIAN, STDEV → For statistical analysis.
Engineering:
CONVERT → =CONVERT(10, "m", "ft") → 32.8084 feet.
Information:
ISERROR → Checks if a formula returns an error.
TYPE → Returns type of data (1 = number, 2 = text).
