Here are the basic Excel formulas along with their syntax and a simple example.
Formula Syntax Example
=SUM(number1, number2,
SUM =SUM(A1:A10)
...)
MIN =MIN(range) =MIN(A1:A10)
MAX =MAX(range) =MAX(A1:A10)
AVERAGE =AVERAGE(range) =AVERAGE(A1:A10)
=COUNT(A1:A10)
COUNT =COUNT(range)
(Counts only numbers)
=COUNTA(A1:A10)
COUNTA =COUNTA(range)
(Counts non-empty cells)
=COUNTBLANK(A1:A10
COUNTBLANK =COUNTBLANK(range)
)
=ROUND(number,
ROUND =ROUND(A1,2)
num_digits)
=ROUNDUP(number,
ROUNDUP =ROUNDUP(A1,2)
num_digits)
=ROUNDDOWN(number,
ROUNDDOWN =ROUNDDOWN(A1,2)
num_digits)
TODAY =TODAY() =TODAY()
NOW =NOW() =NOW()
=AND(condition1, =AND(A1>50,B1="Pas
AND
condition2, ...) s")
Formula Syntax Example
=OR(condition1, =OR(A1>50,B1="Pass
OR
condition2, ...) ")
=VLOOKUP(lookup_value,
table_array, =VLOOKUP(101,A2:D2
VLOOKUP
col_index_num, 0,3,FALSE)
[range_lookup])
=IF(logical_test,
=IF(A1>=50,"Pass",
IF value_if_true,
"Fail")
value_if_false)
=IF(condition1,result1,
=IF(A1>=80,"A",IF(
Nested IF IF(condition2,result2,r
A1>=60,"B","C"))
esult3))
=SUMIF(range, criteria, =SUMIF(A1:A10,"App
SUMIF
[sum_range]) le",B1:B10)
=AVERAGEIF(range,
=AVERAGEIF(A1:A10,
AVERAGEIF criteria,
"Apple",B1:B10)
[average_range])
=COUNTIF(range, =COUNTIF(A1:A10,"P
COUNTIF
criteria) ass")
SQRT =SQRT(number) =SQRT(144)
POWER =POWER(number, power) =POWER(5,2)
MOD =MOD(number, divisor) =MOD(10,3)
=RIGHT(text,
RIGHT =RIGHT(A1,3)
[num_chars])
Formula Syntax Example
=LEFT(text,
LEFT =LEFT(A1,3)
[num_chars])
=LARGE(A1:A10,2)
LARGE =LARGE(array, k)
(2nd largest)
=SMALL(A1:A10,2)
SMALL =SMALL(array, k)
(2nd smallest)
=CONVERT(number, =CONVERT(100,"cm",
CONVERT
from_unit, to_unit) "m")
Quick Notes
• SUM → Adds values.
• MIN → Returns the smallest number.
• MAX → Returns the largest number.
• AVERAGE → Calculates the mean.
• COUNT → Counts numeric cells only.
• COUNTA → Counts all non-empty cells.
• COUNTBLANK → Counts empty cells.
• ROUND → Rounds to the nearest specified decimal.
• ROUNDUP → Always rounds upward.
• ROUNDDOWN → Always rounds downward.
• TODAY() → Returns the current date.
• NOW() → Returns the current date and time.
• AND → Returns TRUE if all conditions are TRUE.
• OR → Returns TRUE if at least one condition is TRUE.
• VLOOKUP → Searches the first column of a table and returns a value from another
column.
• IF → Performs logical tests.
• Nested IF → Tests multiple conditions.
• SUMIF → Adds values that meet a condition.
• AVERAGEIF → Averages values that meet a condition.
• COUNTIF → Counts cells meeting a condition.
• SQRT → Calculates the square root.
• POWER → Raises a number to a power.
• MOD → Returns the remainder after division.
• RIGHT → Extracts characters from the end of a text string.
• LEFT → Extracts characters from the beginning of a text string.
• LARGE → Returns the k-th largest value.
• SMALL → Returns the k-th smallest value.
• CONVERT → Converts between measurement units (e.g., cm to m, kg to lb, °C to °F).