Excel Formulas Cheat Sheet

Master Excel with our comprehensive formulas reference guide. From basic math to advanced lookup functions, find syntax, examples, and expert tips for all essential Excel formulas.

Found 79 formulas matching "OR"

Excel Mastery Guide

Beginner

Start with basic calculations and data organization

Intermediate

Master lookup functions and data analysis

Advanced

Advanced functions and financial modeling

  • • INDEX-MATCH combinations
  • • Array formulas
  • • Financial functions (PV, FV, PMT)
  • • Statistical analysis

Excel Pro Tips

F4 Key: Toggle between absolute and relative references ($A$1, A$1, $A1, A1)

Ctrl + Shift + Enter: Enter array formulas (older Excel versions)

F9 Key: Calculate formulas manually when set to manual calculation mode

Ctrl + `: Toggle between formula view and result view

ISERROR()

Information Functions

Tests if value is an error

Syntax:

=ISERROR(value)

Examples:

=ISERROR(A1/B1)

TRUE if division results in error

=IF(ISERROR(VLOOKUP(A1, B:C, 2, FALSE)), "Not Found", VLOOKUP(A1, B:C, 2, FALSE))

Handle VLOOKUP errors

=IFERROR(A1/B1, "Division Error")

Simpler error handling

Note: Use IFERROR() for simpler error handling

FORMULATEXT()

Lookup & Reference

Returns formula in a cell as text

Syntax:

=FORMULATEXT(reference)

Examples:

=FORMULATEXT(A1)

Shows formula in A1 as text

=FORMULATEXT(SUM(B1:B10))

Returns error (not a reference)

=IF(FORMULATEXT(A1)="", A1, FORMULATEXT(A1))

Show value or formula

Note: Returns #N/A if cell doesn't contain formula. Available in Excel 2013+

SORT()

Dynamic Array Functions

Sorts array by one or more columns

Syntax:

=SORT(array, [sort_index], [sort_order], [by_col])

Examples:

=SORT(A1:C10)

Sort by first column ascending

=SORT(A1:C10, 2, -1)

Sort by column 2 descending

=SORT(A1:C10, {1,2}, {1,-1})

Sort by column 1 asc, then column 2 desc

Note: Excel 365 and Excel 2021. sort_order: 1=ascending, -1=descending

SORTBY()

Dynamic Array Functions

Sorts array based on corresponding values in other arrays

Syntax:

=SORTBY(array, by_array1, [sort_order1], [by_array2], [sort_order2], ...)

Examples:

=SORTBY(A1:A10, B1:B10)

Sort A1:A10 by values in B1:B10

=SORTBY(A1:C10, B1:B10, -1)

Sort rows by column B descending

=SORTBY(A1:C10, B1:B10, 1, C1:C10, -1)

Sort by B ascending, then C descending

Note: Excel 365 and Excel 2021. More flexible than SORT function

WORKDAY()

Date & Time

Returns workday date that is specified days from start date

Syntax:

=WORKDAY(start_date, days, [holidays])

Examples:

=WORKDAY(TODAY(), 10)

Date 10 workdays from today

=WORKDAY(A1, 5, C1:C10)

5 workdays from A1, excluding holidays

=WORKDAY("1/1/2024", -5)

5 workdays before Jan 1

Note: Excludes weekends (Saturday/Sunday) and optional holidays

NETWORKDAYS()

Date & Time

Returns number of workdays between two dates

Syntax:

=NETWORKDAYS(start_date, end_date, [holidays])

Examples:

=NETWORKDAYS(A1, B1)

Workdays between two dates

=NETWORKDAYS("1/1/2024", "1/31/2024", C1:C5)

January workdays minus holidays

=NETWORKDAYS(TODAY(), TODAY()+30)

Workdays in next 30 days

Note: Excludes weekends and optional holidays from count

WORKDAY.INTL()

Date & Time

Returns workday with custom weekend definition

Syntax:

=WORKDAY.INTL(start_date, days, [weekend], [holidays])

Examples:

=WORKDAY.INTL(TODAY(), 10, 1)

Standard weekend (Sat/Sun)

=WORKDAY.INTL(A1, 5, 7)

Weekend = Friday/Saturday

=WORKDAY.INTL(A1, 10, "0000011")

Custom weekend (Sat/Sun)

Note: Weekend: 1=Sat/Sun, 2=Sun/Mon, 7=Fri/Sat, or custom 7-char string

NETWORKDAYS.INTL()

Date & Time

Returns workdays between dates with custom weekend

Syntax:

=NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])

Examples:

=NETWORKDAYS.INTL(A1, B1, 1)

Standard weekend counting

=NETWORKDAYS.INTL(A1, B1, 7, C1:C5)

Fri/Sat weekend with holidays

=NETWORKDAYS.INTL(A1, B1, "0000011")

Custom weekend definition

Note: Same weekend options as WORKDAY.INTL

OR()

Logical Functions

Returns TRUE if any condition is true

Syntax:

=OR(logical1, [logical2], ...)

Examples:

=OR(A1>100, B1>100)

True if either condition met

=OR(A1="Red", A1="Blue")

True if cell is Red or Blue

=IF(OR(A1<0, B1<0), "Has Negative", "All Positive")

Use with IF function

Note: Only one argument needs to be TRUE for result to be TRUE

TEXT()

Text Functions

Formats number as text using format code

Syntax:

=TEXT(value, format_text)

Examples:

=TEXT(A1, "mm/dd/yyyy")

Format date as text

=TEXT(1234.567, "$#,##0.00")

Format as currency

=TEXT(0.75, "0.0%")

Format as percentage

Note: Common formats: 0=numbers, mm/dd/yyyy=dates, $#,##0.00=currency

INFO()

Information Functions

Returns information about operating environment

Syntax:

=INFO(type_text)

Examples:

=INFO("version")

Excel version information

=INFO("system")

Operating system type

=INFO("directory")

Current directory path

Note: Types: version, system, osversion, directory, numfile, origin, memavail, memused, totmem

RANK.AVG()

Statistical Functions

Returns average rank for tied values

Syntax:

=RANK.AVG(number, ref, [order])

Examples:

=RANK.AVG(A1, A$1:A$10)

Average rank for tied values

=RANK.AVG(A1, A$1:A$10, 1)

Ascending order with average ranking

=RANK.AVG(90, {95,90,90,85})

Returns 2.5 for both 90s

Note: Tied values get average of ranks they would occupy

CELL()

Information Functions

Returns information about cell formatting or location

Syntax:

=CELL(info_type, [reference])

Examples:

=CELL("address", A1)

Returns cell address as text

=CELL("contents", A1)

Returns cell contents

=CELL("format", A1)

Returns format code of cell

Note: Info types: address, col, color, contents, filename, format, parentheses, prefix, protect, row, type, width

LOOKUP()

Lookup & Reference

Looks up value in vector or array

Syntax:

=LOOKUP(lookup_value, lookup_vector, [result_vector])

Examples:

=LOOKUP(A1, B:B, C:C)

Vector form lookup

=LOOKUP("z", A1:A10)

Find last value ≤ 'z'

=LOOKUP(9.99E+307, B:B)

Get last non-empty value in column

Note: Requires sorted data. Use VLOOKUP or XLOOKUP for more control

ISNA()

Information Functions

Tests if value is #N/A error

Syntax:

=ISNA(value)

Examples:

=ISNA(VLOOKUP(A1, B:C, 2, FALSE))

TRUE if VLOOKUP returns #N/A

=IF(ISNA(MATCH(A1, B:B, 0)), "Not Found", "Found")

Handle MATCH #N/A errors

=IFNA(VLOOKUP(A1, B:C, 2, FALSE), "Not Found")

Simpler #N/A handling

Note: Specifically tests for #N/A error. Use IFNA() for simpler handling

PMT()

Financial Functions

Calculates payment for loan or investment

Syntax:

=PMT(rate, nper, pv, [fv], [type])

Examples:

=PMT(5%/12, 60, 20000)

Monthly payment for $20,000 loan at 5% for 5 years

=PMT(0.08/12, 30*12, 300000)

Monthly mortgage payment

=PMT(A1/12, B1*12, C1)

Using cell references for loan calculation

Note: Result is negative (payment going out)

SUBTOTAL()

Math & Statistical

Returns a subtotal in a list or database using specified function

Syntax:

=SUBTOTAL(function_num, ref1, [ref2], ...)

Examples:

=SUBTOTAL(9, A1:A10)

Sum of visible cells (function 9)

=SUBTOTAL(1, A1:A10)

Average of visible cells (function 1)

=SUBTOTAL(109, A1:A10)

Sum ignoring hidden rows and filtered data

Note: Functions 1-11 include hidden cells, 101-111 ignore hidden cells

RANK()

Statistical Functions

Returns rank of number in list (compatibility function)

Syntax:

=RANK(number, ref, [order])

Examples:

=RANK(A1, A$1:A$10)

Rank A1 in descending order

=RANK(A1, A$1:A$10, 1)

Rank A1 in ascending order

=RANK(95, {90,85,95,88,92})

Rank 95 in array

Note: Use RANK.EQ or RANK.AVG in newer Excel versions for better functionality

SINGLE()

Dynamic Array Functions

Returns single value from array with error checking

Syntax:

=SINGLE(array)

Examples:

=SINGLE(FILTER(A1:A10, A1:A10="Apple"))

Get single filtered result

=SINGLE(UNIQUE(A1:A10))

Error if more than one unique value

=SINGLE(B5)

Returns B5 value (identity function)

Note: Excel 365 Insider. Returns error if array has more than one value

CONCAT()

Text Functions

Joins text from multiple ranges and/or strings

Syntax:

=CONCAT(text1, [text2], ...)

Examples:

=CONCAT(A1:A3)

Concatenate range A1:A3

=CONCAT("Hello ", A1, " World")

Mix text and cell references

=CONCAT(A1:A5, " | ", B1:B5)

Concatenate multiple ranges

Note: Replaces CONCATENATE. Available in Excel 2016+. Can handle ranges

TEXTJOIN()

Text Functions

Joins text with specified delimiter

Syntax:

=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

Examples:

=TEXTJOIN(", ", TRUE, A1:A5)

Join range with commas, skip blanks

=TEXTJOIN(" | ", FALSE, A1, B1, C1)

Join with pipe separator

=TEXTJOIN(CHAR(10), TRUE, A1:A10)

Join with line breaks

Note: Available in Excel 2016+. ignore_empty: TRUE skips empty cells

TEXTSPLIT()

Text Functions

Splits text into array using delimiters

Syntax:

=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])

Examples:

=TEXTSPLIT(A1, ",")

Split by comma into columns

=TEXTSPLIT(A1, " ", CHAR(10))

Split by space and line breaks

=TEXTSPLIT("apple,banana,,cherry", ",", , TRUE)

Split and ignore empty values

Note: Excel 365 Insider. Returns dynamic array. ignore_empty: TRUE skips empty results

EDATE()

Date & Time

Returns date that is months before/after start date

Syntax:

=EDATE(start_date, months)

Examples:

=EDATE(TODAY(), 6)

Date 6 months from today

=EDATE(A1, -3)

Date 3 months before A1

=EDATE("1/15/2024", 12)

Same day next year

Note: Positive months = future, negative = past

RANK.EQ()

Statistical Functions

Returns rank of number, with ties getting same rank

Syntax:

=RANK.EQ(number, ref, [order])

Examples:

=RANK.EQ(A1, A$1:A$10)

Rank with tied values getting same rank

=RANK.EQ(A1, A$1:A$10, 1)

Ascending order ranking

=RANK.EQ(90, {95,90,90,85})

Returns 2 for both 90s

Note: Ties receive the same rank, next rank is skipped

SUMIFS()

Math & Statistical

Sums cells that meet multiple criteria

Syntax:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Examples:

=SUMIFS(C1:C10, A1:A10, "Apple", B1:B10, ">100")

Sum C where A='Apple' AND B>100

=SUMIFS(D1:D10, A1:A10, ">=" & DATE(2024,1,1), A1:A10, "<" & DATE(2025,1,1))

Sum D for dates in 2024

=SUMIFS(C1:C10, A1:A10, "North", B1:B10, "Q1")

Sum for North region in Q1

Note: All criteria must be met for a cell to be included in sum

AVERAGEIFS()

Math & Statistical

Averages cells that meet multiple criteria

Syntax:

=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Examples:

=AVERAGEIFS(C1:C10, A1:A10, "Apple", B1:B10, ">100")

Average C where A='Apple' AND B>100

=AVERAGEIFS(D1:D10, A1:A10, ">=" & DATE(2024,1,1), A1:A10, "<" & DATE(2025,1,1))

Average D for dates in 2024

=AVERAGEIFS(C1:C10, A1:A10, "North", B1:B10, "Q1")

Average for North region in Q1

Note: All criteria must be met for a cell to be included in average

EOMONTH()

Date & Time

Returns last day of month that is months before/after start date

Syntax:

=EOMONTH(start_date, months)

Examples:

=EOMONTH(TODAY(), 0)

Last day of current month

=EOMONTH(A1, 1)

Last day of next month from A1

=EOMONTH("1/15/2024", -1)

Last day of previous month

Note: Always returns last day of the target month

LAMBDA()

Advanced Functions

Creates custom functions using Excel formulas

Syntax:

=LAMBDA([parameter1], [parameter2], ..., calculation)

Examples:

=LAMBDA(x, x^2)(5)

Square function: returns 25

=LAMBDA(x,y, x*y+10)(3,4)

Custom calculation: returns 22

=MAP(A1:A10, LAMBDA(x, x*2))

Double each value in range

Note: Excel 365 Insider. Create reusable custom functions. Often used with MAP, SCAN, etc.

COUNTIFS()

Math & Statistical

Counts cells that meet multiple criteria

Syntax:

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Examples:

=COUNTIFS(A1:A10, "Apple", B1:B10, ">100")

Count where A='Apple' AND B>100

=COUNTIFS(A1:A10, ">=" & DATE(2024,1,1), A1:A10, "<" & DATE(2025,1,1))

Count dates in 2024

=COUNTIFS(B1:B10, "North", C1:C10, "Q1")

Count North region in Q1

Note: All criteria must be met for a cell to be counted

TRIM()

Text Functions

Removes extra spaces from text

Syntax:

=TRIM(text)

Examples:

=TRIM(A1)

Remove leading/trailing/extra spaces from A1

=TRIM(" hello world ")

Returns 'hello world'

=TRIM(A1&" "&B1)

Trim concatenated text

Note: Leaves single spaces between words

MINIFS()

Math & Statistical

Returns minimum value among cells specified by criteria

Syntax:

=MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Examples:

=MINIFS(C1:C10, A1:A10, "Apple", B1:B10, ">100")

Minimum C where A='Apple' AND B>100

=MINIFS(D1:D10, A1:A10, "North")

Minimum D for North region

=MINIFS(C1:C10, A1:A10, ">=" & DATE(2024,1,1), A1:A10, "<" & DATE(2025,1,1))

Minimum for dates in 2024

Note: Available in Excel 2016 and later

MAXIFS()

Math & Statistical

Returns maximum value among cells specified by criteria

Syntax:

=MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Examples:

=MAXIFS(C1:C10, A1:A10, "Apple", B1:B10, ">100")

Maximum C where A='Apple' AND B>100

=MAXIFS(D1:D10, A1:A10, "North")

Maximum D for North region

=MAXIFS(C1:C10, A1:A10, ">=" & DATE(2024,1,1), A1:A10, "<" & DATE(2025,1,1))

Maximum for dates in 2024

Note: Available in Excel 2016 and later

IFS()

Logical Functions

Tests multiple conditions and returns first TRUE result

Syntax:

=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...)

Examples:

=IFS(A1>90, "A", A1>80, "B", A1>70, "C", TRUE, "F")

Grade based on score

=IFS(B1="Red", 1, B1="Blue", 2, B1="Green", 3, TRUE, 0)

Assign numbers to colors

=IFS(A1<0, "Negative", A1=0, "Zero", A1>0, "Positive")

Check number sign

Note: Available in Excel 2016 and later. Use TRUE as final condition for default value

FV()

Financial Functions

Calculates future value of investment

Syntax:

=FV(rate, nper, pmt, [pv], [type])

Examples:

=FV(5%/12, 60, -500)

Future value of $500/month for 5 years at 5%

=FV(0.08, 10, -1000, 0, 0)

FV of $1000 payments for 10 years at 8%

=FV(A1/12, B1*12, -C1, -D1)

FV with present value included

Note: Use for calculating investment growth

ISBLANK()

Information Functions

Tests if cell is empty

Syntax:

=ISBLANK(value)

Examples:

=ISBLANK(A1)

TRUE if A1 is empty

=IF(ISBLANK(A1), "Empty", A1)

Display 'Empty' if cell is blank

=COUNTIF(A1:A10, ISBLANK(A1:A10))

Count blank cells in range

Note: Returns TRUE only for truly empty cells

ISTEXT()

Information Functions

Tests if value is text

Syntax:

=ISTEXT(value)

Examples:

=ISTEXT(A1)

TRUE if A1 contains text

=IF(ISTEXT(A1), UPPER(A1), A1)

Convert to uppercase if text

=COUNTIF(A1:A10, ISTEXT(A1:A10))

Count text cells

Note: Returns FALSE for numbers, dates, and blank cells

TYPE()

Information Functions

Returns data type of value

Syntax:

=TYPE(value)

Examples:

=TYPE(A1)

Data type of cell A1

=TYPE(123)

Returns 1 (number)

=TYPE("Hello")

Returns 2 (text)

Note: Returns: 1=number, 2=text, 4=logical, 16=error, 64=array

RIGHT()

Text Functions

Extracts characters from right side of text

Syntax:

=RIGHT(text, [num_chars])

Examples:

=RIGHT(A1, 3)

Extract last 3 characters

=RIGHT("Hello World", 5)

Returns 'World'

=RIGHT(A1)

Extract last character (default)

Note: Default num_chars is 1 if omitted

MID()

Text Functions

Extracts characters from middle of text

Syntax:

=MID(text, start_num, num_chars)

Examples:

=MID(A1, 2, 3)

Extract 3 chars starting at position 2

=MID("Hello World", 7, 5)

Returns 'World'

=MID(A1, 3, 1)

Extract single character at position 3

Note: Start_num is 1-based (first character is position 1)

UPPER()

Text Functions

Converts text to uppercase

Syntax:

=UPPER(text)

Examples:

=UPPER(A1)

Convert cell A1 to uppercase

=UPPER("hello world")

Returns 'HELLO WORLD'

=UPPER(B2&" "&C2)

Convert concatenated text to uppercase

Note: Only affects alphabetic characters

LOWER()

Text Functions

Converts text to lowercase

Syntax:

=LOWER(text)

Examples:

=LOWER(A1)

Convert cell A1 to lowercase

=LOWER("HELLO WORLD")

Returns 'hello world'

=LOWER(TRIM(A1))

Convert to lowercase and trim spaces

Note: Only affects alphabetic characters

CONCATENATE()

Text Functions

Joins text strings together

Syntax:

=CONCATENATE(text1, [text2], ...)

Examples:

=CONCATENATE(A1, " ", B1)

Join cells with space

=CONCATENATE("Hello ", A1)

Join text with cell value

=A1&" "&B1

Alternative using & operator

Note: Use & operator as alternative. CONCAT() available in newer versions

HYPERLINK()

Text Functions

Creates clickable hyperlink

Syntax:

=HYPERLINK(link_location, [friendly_name])

Examples:

=HYPERLINK("https://www.example.com", "Visit Site")

Web link with display text

=HYPERLINK("mailto:user@example.com", "Send Email")

Email link

=HYPERLINK("#Sheet2!A1", "Go to Sheet2")

Internal workbook link

Note: link_location can be URL, email, or cell reference

INDEX()

Lookup & Reference

Returns value at intersection of specified row and column

Syntax:

=INDEX(array, row_num, [column_num])

Examples:

=INDEX(A1:C10, 5, 2)

Value from row 5, column 2 of range

=INDEX(A:A, 10)

10th value in column A

=INDEX(A1:A10, MATCH("Apple", B1:B10, 0))

Use with MATCH for lookup

Note: Often combined with MATCH for flexible lookups

XLOOKUP()

Lookup & Reference

Modern lookup function that replaces VLOOKUP and HLOOKUP

Syntax:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Examples:

=XLOOKUP(A1, B:B, C:C)

Basic lookup from B to C

=XLOOKUP(A1, B:B, C:C, "Not Found")

With custom error message

=XLOOKUP(A1, B:B, C:E)

Return multiple columns

Note: Available in Excel 365 and Excel 2021. More flexible than VLOOKUP

XMATCH()

Lookup & Reference

Enhanced version of MATCH function

Syntax:

=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])

Examples:

=XMATCH(A1, B:B)

Find exact position of A1 in column B

=XMATCH(A1, B:B, 1)

Find exact match or next smallest

=XMATCH(A1, B:B, 0, -1)

Search from last to first

Note: Available in Excel 365 and Excel 2021. More options than MATCH

CHOOSE()

Lookup & Reference

Chooses value from list based on index number

Syntax:

=CHOOSE(index_num, value1, [value2], ...)

Examples:

=CHOOSE(A1, "Red", "Blue", "Green")

Return color based on number 1-3

=CHOOSE(WEEKDAY(TODAY()), "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat")

Day name from today's date

=CHOOSE(A1, B:B, C:C, D:D)

Choose column based on index

Note: Index starts at 1. Returns error if index is out of range

SEQUENCE()

Dynamic Array Functions

Generates sequence of numbers

Syntax:

=SEQUENCE(rows, [columns], [start], [step])

Examples:

=SEQUENCE(10)

Numbers 1 to 10 in column

=SEQUENCE(5, 3, 0, 2)

5x3 array starting at 0, step by 2

=SEQUENCE(1, 12, DATE(2024,1,1), 1)

Monthly dates for 2024

Note: Excel 365 and Excel 2021. Great for creating number series

PV()

Financial Functions

Calculates present value of investment

Syntax:

=PV(rate, nper, pmt, [fv], [type])

Examples:

=PV(5%/12, 60, -500)

Present value of $500/month for 5 years at 5%

=PV(0.08, 10, -1000, 0, 0)

PV of $1000 payments for 10 years at 8%

=PV(A1/12, B1*12, -C1)

Using cell references

Note: Payments are negative, receipts are positive

ISNUMBER()

Information Functions

Tests if value is a number

Syntax:

=ISNUMBER(value)

Examples:

=ISNUMBER(A1)

TRUE if A1 contains a number

=IF(ISNUMBER(A1), A1*2, "Not a number")

Multiply by 2 if number

=SUMPRODUCT(--(ISNUMBER(A1:A10)))

Count numeric cells

Note: Dates are considered numbers in Excel

LEFT()

Text Functions

Extracts characters from left side of text

Syntax:

=LEFT(text, [num_chars])

Examples:

=LEFT(A1, 3)

Extract first 3 characters

=LEFT("Hello World", 5)

Returns 'Hello'

=LEFT(A1)

Extract first character (default)

Note: Default num_chars is 1 if omitted

LEN()

Text Functions

Returns the length of text

Syntax:

=LEN(text)

Examples:

=LEN(A1)

Count characters in cell A1

=LEN("Hello World")

Returns 11 (including space)

=LEN(TRIM(A1))

Length after removing extra spaces

Note: Counts all characters including spaces

SUM()

Math & Statistical

Adds all numbers in a range

Syntax:

=SUM(number1, [number2], ...)

Examples:

=SUM(A1:A10)

Sum all values in range A1 to A10

=SUM(A1, B1, C1)

Sum specific cells

=SUM(A1:A5, C1:C5)

Sum multiple ranges

Note: Ignores text values and empty cells

SUMIF()

Math & Statistical

Sums cells that meet a criteria

Syntax:

=SUMIF(range, criteria, [sum_range])

Examples:

=SUMIF(A1:A10, ">100")

Sum values greater than 100

=SUMIF(A1:A10, "Apple", B1:B10)

Sum B values where A contains 'Apple'

=SUMIF(A1:A10, A12, B1:B10)

Sum B values where A equals value in A12

Note: If sum_range is omitted, range is used for both criteria and sum

COUNTA()

Math & Statistical

Counts non-empty cells

Syntax:

=COUNTA(value1, [value2], ...)

Examples:

=COUNTA(A1:A10)

Count non-empty cells in range

=COUNTA(A:A)

Count non-empty cells in column A

=COUNTA(A1:C1)

Count non-empty cells in row

Note: Counts cells with any value (text, numbers, errors)

MAX()

Math & Statistical

Returns the largest value

Syntax:

=MAX(number1, [number2], ...)

Examples:

=MAX(A1:A10)

Find maximum value in range

=MAX(A1, B1, C1)

Find maximum among specific cells

=MAX(A:A)

Find maximum in entire column

Note: Ignores text and logical values

MIN()

Math & Statistical

Returns the smallest value

Syntax:

=MIN(number1, [number2], ...)

Examples:

=MIN(A1:A10)

Find minimum value in range

=MIN(A1, B1, C1)

Find minimum among specific cells

=MIN(A:A)

Find minimum in entire column

Note: Ignores text and logical values

COUNTIF()

Math & Statistical

Counts cells that meet a criteria

Syntax:

=COUNTIF(range, criteria)

Examples:

=COUNTIF(A1:A10, ">100")

Count cells greater than 100

=COUNTIF(A1:A10, "Apple")

Count cells containing 'Apple'

=COUNTIF(A1:A10, A12)

Count cells equal to value in A12

Note: Supports wildcards: * for multiple characters, ? for single character

AVERAGEIF()

Math & Statistical

Averages cells that meet a criteria

Syntax:

=AVERAGEIF(range, criteria, [average_range])

Examples:

=AVERAGEIF(A1:A10, ">100")

Average of values greater than 100

=AVERAGEIF(A1:A10, "Apple", B1:B10)

Average B values where A contains 'Apple'

=AVERAGEIF(A1:A10, A12, B1:B10)

Average B values where A equals value in A12

Note: If average_range is omitted, range is used for both criteria and average

TODAY()

Date & Time

Returns current date

Syntax:

=TODAY()

Examples:

=TODAY()

Current date (updates daily)

=TODAY()+7

Date 7 days from today

=TODAY()-30

Date 30 days ago

Note: Updates automatically when file is opened or calculated

HOUR()

Date & Time

Extracts hour from a time

Syntax:

=HOUR(serial_number)

Examples:

=HOUR(NOW())

Current hour (0-23)

=HOUR(A1)

Hour from time in cell A1

=HOUR("2:30 PM")

Returns 14

Note: Returns hour in 24-hour format (0-23)

DATEDIF()

Date & Time

Calculates difference between two dates

Syntax:

=DATEDIF(start_date, end_date, unit)

Examples:

=DATEDIF(A1, B1, "Y")

Years between dates

=DATEDIF(A1, B1, "M")

Months between dates

=DATEDIF(A1, B1, "D")

Days between dates

Note: Units: Y=years, M=months, D=days, MD=days ignoring months/years, YM=months ignoring years, YD=days ignoring years

IF()

Logical Functions

Returns different values based on condition

Syntax:

=IF(logical_test, value_if_true, value_if_false)

Examples:

=IF(A1>100, "High", "Low")

Return text based on value

=IF(B1="", "Empty", B1)

Handle empty cells

=IF(A1>B1, A1, B1)

Return larger of two values

Note: Can be nested for multiple conditions

AND()

Logical Functions

Returns TRUE if all conditions are true

Syntax:

=AND(logical1, [logical2], ...)

Examples:

=AND(A1>10, B1<100)

True if both conditions met

=AND(A1="Yes", B1="Yes")

True if both cells are 'Yes'

=IF(AND(A1>0, B1>0), "Both Positive", "Not Both")

Use with IF function

Note: All arguments must be TRUE for result to be TRUE

VLOOKUP()

Lookup & Reference

Looks up value in leftmost column and returns value from specified column

Syntax:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Examples:

=VLOOKUP(A1, B:D, 2, FALSE)

Exact match lookup in column B, return from column C

=VLOOKUP("Apple", A1:C10, 3, TRUE)

Approximate match lookup

=VLOOKUP(A1, Sheet2!B:E, 4, FALSE)

Lookup from another sheet

Note: Use FALSE for exact match, TRUE for approximate match

HLOOKUP()

Lookup & Reference

Looks up value in top row and returns value from specified row

Syntax:

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Examples:

=HLOOKUP(A1, B1:F5, 3, FALSE)

Exact match in row 1, return from row 3

=HLOOKUP("Q1", A1:E10, 2, FALSE)

Find Q1 and return value from row 2

=HLOOKUP(A1, Sheet2!A1:Z5, 4, TRUE)

Approximate match from another sheet

Note: Horizontal version of VLOOKUP

ROW()

Lookup & Reference

Returns row number of reference

Syntax:

=ROW([reference])

Examples:

=ROW()

Current row number

=ROW(A5)

Returns 5

=ROW(A1:A10)

Array of row numbers 1-10

Note: Without reference, returns row number of cell containing formula

COLUMN()

Lookup & Reference

Returns column number of reference

Syntax:

=COLUMN([reference])

Examples:

=COLUMN()

Current column number

=COLUMN(C1)

Returns 3 (C is 3rd column)

=COLUMN(A1:E1)

Array of column numbers 1-5

Note: Without reference, returns column number of cell containing formula

TRANSPOSE()

Lookup & Reference

Transposes rows and columns of array

Syntax:

=TRANSPOSE(array)

Examples:

=TRANSPOSE(A1:C1)

Convert row to column

=TRANSPOSE(A1:A5)

Convert column to row

=TRANSPOSE(A1:C3)

Flip 3x3 array

Note: Enter as array formula in older Excel versions

MEDIAN()

Statistical Functions

Returns the middle value in a dataset

Syntax:

=MEDIAN(number1, [number2], ...)

Examples:

=MEDIAN(A1:A10)

Middle value of range A1:A10

=MEDIAN(1, 2, 3, 4, 5)

Returns 3 (middle value)

=MEDIAN(A:A)

Median of entire column A

Note: For even number of values, returns average of two middle values

MODE()

Statistical Functions

Returns the most frequently occurring value

Syntax:

=MODE(number1, [number2], ...)

Examples:

=MODE(A1:A10)

Most common value in range

=MODE(1, 2, 2, 3, 4)

Returns 2 (appears twice)

=MODE.SNGL(A1:A10)

Single mode (newer Excel versions)

Note: Returns error if no value appears more than once

STDEV()

Statistical Functions

Calculates standard deviation of sample

Syntax:

=STDEV(number1, [number2], ...)

Examples:

=STDEV(A1:A10)

Standard deviation of sample data

=STDEV.S(A1:A10)

Sample standard deviation (newer versions)

=STDEV.P(A1:A10)

Population standard deviation

Note: STDEV assumes sample data, use STDEV.P for population

MODE.SNGL()

Statistical Functions

Returns the most frequently occurring single value

Syntax:

=MODE.SNGL(number1, [number2], ...)

Examples:

=MODE.SNGL(A1:A10)

Most common single value in range

=MODE.SNGL(1, 2, 2, 3, 4)

Returns 2 (appears twice)

=MODE.SNGL(1, 1, 2, 2, 3)

Returns first mode found (1)

Note: Replaces MODE function in Excel 2010+. Returns error if no duplicate values

MODE.MULT()

Statistical Functions

Returns vertical array of most frequently occurring values

Syntax:

=MODE.MULT(array)

Examples:

=MODE.MULT(A1:A10)

All most frequently occurring values

=MODE.MULT({1,1,2,2,3,4})

Returns array with 1 and 2

=INDEX(MODE.MULT(A1:A10), 1)

Get first mode from array

Note: Returns array formula. Available in Excel 2010+. Use Ctrl+Shift+Enter in older versions

NOW()

Date & Time

Returns current date and time

Syntax:

=NOW()

Examples:

=NOW()

Current date and time

=NOW()+1

Same time tomorrow

=INT(NOW())

Current date only (removes time)

Note: Updates automatically and includes time

DATE()

Date & Time

Creates a date from year, month, day

Syntax:

=DATE(year, month, day)

Examples:

=DATE(2024, 12, 25)

Create date for December 25, 2024

=DATE(A1, B1, C1)

Create date from cell values

=DATE(YEAR(TODAY()), 12, 31)

Last day of current year

Note: Year can be 1900-9999, month 1-12, day 1-31

TIME()

Date & Time

Creates a time from hour, minute, second

Syntax:

=TIME(hour, minute, second)

Examples:

=TIME(14, 30, 0)

Create time for 2:30 PM

=TIME(A1, B1, C1)

Create time from cell values

=TIME(12, 0, 0) + TIME(0, 30, 0)

Add 30 minutes to noon

Note: Hour 0-23, minute/second 0-59. Values over limits roll over

NOT()

Logical Functions

Reverses the logic of its argument

Syntax:

=NOT(logical)

Examples:

=NOT(A1>100)

True if A1 is NOT greater than 100

=NOT(A1="")

True if A1 is NOT empty

=IF(NOT(A1=""), A1, "No Data")

Use with IF for non-empty check

Note: Changes TRUE to FALSE and FALSE to TRUE

SWITCH()

Advanced Functions

Evaluates expression against list of values and returns result

Syntax:

=SWITCH(expression, value1, result1, [value2, result2], ..., [default])

Examples:

=SWITCH(A1, 1, "One", 2, "Two", 3, "Three", "Other")

Convert numbers to words

=SWITCH(WEEKDAY(TODAY()), 1, "Sunday", 7, "Saturday", "Weekday")

Check if weekend

=SWITCH(LEFT(A1,1), "A", "Apple", "B", "Banana", "Unknown")

Based on first letter

Note: Available in Excel 2016+. Cleaner alternative to nested IF statements

Master Excel formulas with practice and repetition. Bookmark this page for quick reference!

📊 102 Total Formulas 🏷️ 10 Categories ⚡ Quick Search