Excel Formulas Cheat Sheet

1. Basic Formulas

Formula	Structure	Explanation
AVERAGE	`=AVERAGE(A2:A10)`	Returns the mathematical average of a given cell range.
COUNT	`=COUNT(A2:A10)`	Counts the numbers in a given cell range.
MAX	`=MAX(A2:A10)`	Returns the largest value in a given cell range.
MEDIAN	`=MEDIAN(A2:A10)`	Returns the median (middle) value of a range.
MIN	`=MIN(A2:A10)`	Returns the smallest value in a given cell range.
SUM	`=SUM(A2:A10)`	Adds all numbers in a given cell range.

Note: A2:A10 is an example cell range used in all formulas above. Change it according to your data.

Formula	Structure	Explanation
TODAY	`=TODAY()`	Returns today’s date. No arguments needed.
NOW	`=NOW()`	Returns the current date and time. No arguments needed.
DATEDIF	`=DATEDIF(Start_Date, End_Date, Unit)`	Returns the difference between two dates. Unit: “Y” = years, “M” = months, “D” = days. Not in Excel function library.
YEAR	`=YEAR(Date)`	Returns the year portion of a date.
MONTH	`=MONTH(Date)`	Returns the month portion of a date.
DAY	`=DAY(Date)`	Returns the day portion of a date.

Tip: If a date or time formula gives an unexpected result, check the cell format (date vs. number).

Formula	Structure	Explanation
IF	`=IF(Logical_Test, TRUE, FALSE)`	Evaluates a condition: if TRUE, returns the first value; if FALSE, returns the second.
OR	`=OR(Logical_Test1, Logical_Test2, …)`	Returns TRUE if any test is TRUE; otherwise FALSE.
AND	`=AND(Logical_Test1, Logical_Test2, …)`	Returns TRUE only if all tests are TRUE; otherwise FALSE.

Comparison operators: =, <, >, <=, >=, <>

Formula	Structure	Explanation
VLOOKUP	`=VLOOKUP(Lookup_Value, Table_Array, Col_Index, Range_Lookup)`	Searches the leftmost column of a table. Returns the value from the specified column. Use FALSE for exact match.

Tip: Use absolute references for Table_Array to avoid errors when copying formulas (e.g., $A$2:$D$10).

Formula	Structure	Explanation
PMT	`=PMT(rate, nper, pv, [fv], [type])`	Calculates loan payments.
	rate	Annual rate ÷ 12
	nper	Total periods (months)
	pv	Loan amount
	[fv]	Future value (optional, usually 0)
	[type]	0 = end of period, 1 = start of period (optional)

Formula	Structure	Explanation
SUMIF	`=SUMIF(range, criteria, [sum_range])`	Sums values in a range based on a single condition.
SUMIFS	`=SUMIFS(sum_range, criteria_range1, criteria1, …)`	Sums values based on multiple criteria.
COUNTIF	`=COUNTIF(range, criteria)`	Counts cells that meet a single condition.
COUNTIFS	`=COUNTIFS(criteria_range1, criteria1, …)`	Counts cells that meet multiple conditions.

Formula	Structure	Explanation
AVERAGEIF	`=AVERAGEIF(range, criteria, [average_range])`	Returns the average of cells that meet a single condition.
AVERAGEIFS	`=AVERAGEIFS(average_range, criteria_range1, criteria1, …)`	Returns the average of cells that meet multiple conditions.