Excel Formulas
Basic math
Function Formula Example
To add up the total =SUM(cell range) =SUM(B2:B9)
To add individual items =Value1 + Value 2 =B2+C2
Subtract =Value1 - Value 2 =B2-C2
Multiply =Value1 * Value2 =B2*C2
Divide =Value1 / Value2 =B2/C2
Exponents =Value1 ^ Value2 =B2^C2
Average =AVERAGE(cell range) =AVERAGE(B2:B9)
Median =MEDIAN(cell range) =MEDIAN(B2:B9)
Max =MAX(cell range) =MAX(B2:B9)
Min =MIN(cell range) =MIN(B2:B9)
Simple formatting tricks
Function Formula Example
To change a cell to proper
case =PROPER(cell) =PROPER(A2)
To change a cell to upper
case =UPPER(cell) =UPPER(A2)
To change a cell to lower
case =LOWER(cell) =LOWER(A2)
Conditional statements
Function Formula Example
If statement =IF(logical test, “result if
the test answer is true”,
“result if the test answer is
false”)
=IF(B2>69,”Pass”,”Fail”)
Exact =EXACT(Value1, value2) =EXACT(B2, C2)
Absolute cell references
When a formula contains an absolute reference, no matter which cell the formula
occupies the cell reference does not change: if you copy or move the formula, it refers
to the same cell as it did in its original location. In an absolute reference, each part of
the reference (the letter that refers to the row and the number that refers to the column)
is preceded by a “$” – for example, $A$1 is an absolute reference to cell A1. Wherever
the formula is copied or moved, it always refers to cell A1.
