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.

Showing 102 Excel formulas across 10 categories

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

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

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

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

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

AVERAGE()

Math & Statistical

Calculates the average of numbers

Syntax:

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

Examples:

=AVERAGE(A1:A10)

Average of range A1 to A10

=AVERAGE(A1, B1, C1)

Average of specific cells

=AVERAGE(A:A)

Average of entire column A

Note: Excludes empty cells and text from calculation

COUNT()

Math & Statistical

Counts cells containing numbers

Syntax:

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

Examples:

=COUNT(A1:A10)

Count numeric cells in range

=COUNT(A:A)

Count numeric cells in column A

=COUNT(A1, B1, C1)

Count specific numeric cells

Note: Only counts cells with numeric values

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

ROUND()

Math & Statistical

Rounds a number to specified digits

Syntax:

=ROUND(number, num_digits)

Examples:

=ROUND(3.14159, 2)

Round to 2 decimal places (3.14)

=ROUND(1234.567, 0)

Round to nearest integer (1235)

=ROUND(1234.567, -2)

Round to nearest hundred (1200)

Note: Positive num_digits rounds to right of decimal, negative to left

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

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

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

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

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

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

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

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)

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

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

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

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

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

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

CLEAN()

Text Functions

Removes non-printable characters from text

Syntax:

=CLEAN(text)

Examples:

=CLEAN(A1)

Remove non-printable chars from A1

=CLEAN(TRIM(A1))

Clean and trim text

=LEN(A1) - LEN(CLEAN(A1))

Count non-printable characters

Note: Removes ASCII characters 0-31 (except line feed)

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

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

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

YEAR()

Date & Time

Extracts year from a date

Syntax:

=YEAR(serial_number)

Examples:

=YEAR(TODAY())

Current year

=YEAR(A1)

Year from date in cell A1

=YEAR("12/25/2024")

Returns 2024

Note: Returns a 4-digit year (1900-9999)

MONTH()

Date & Time

Extracts month from a date

Syntax:

=MONTH(serial_number)

Examples:

=MONTH(TODAY())

Current month number

=MONTH(A1)

Month from date in cell A1

=MONTH("12/25/2024")

Returns 12

Note: Returns month as number (1-12)

DAY()

Date & Time

Extracts day from a date

Syntax:

=DAY(serial_number)

Examples:

=DAY(TODAY())

Current day of month

=DAY(A1)

Day from date in cell A1

=DAY("12/25/2024")

Returns 25

Note: Returns day of month (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

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)

MINUTE()

Date & Time

Extracts minute from a time

Syntax:

=MINUTE(serial_number)

Examples:

=MINUTE(NOW())

Current minute (0-59)

=MINUTE(A1)

Minute from time in cell A1

=MINUTE("2:30 PM")

Returns 30

Note: Returns minute (0-59)

SECOND()

Date & Time

Extracts second from a time

Syntax:

=SECOND(serial_number)

Examples:

=SECOND(NOW())

Current second (0-59)

=SECOND(A1)

Second from time in cell A1

=SECOND("2:30:45 PM")

Returns 45

Note: Returns second (0-59)

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

YEARFRAC()

Date & Time

Returns fraction of year between two dates

Syntax:

=YEARFRAC(start_date, end_date, [basis])

Examples:

=YEARFRAC(A1, B1)

Fraction of year between dates

=YEARFRAC(DATE(2024,1,1), DATE(2024,7,1))

Returns 0.5 (half year)

=YEARFRAC(A1, B1, 1)

Using actual/actual basis

Note: Basis: 0=30/360, 1=actual/actual, 2=actual/360, 3=actual/365, 4=30/360 European

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

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

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

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()

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

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

WEEKDAY()

Date & Time

Returns day of week as number

Syntax:

=WEEKDAY(serial_number, [return_type])

Examples:

=WEEKDAY(TODAY())

Day of week (1=Sun to 7=Sat)

=WEEKDAY(A1, 2)

Day of week (1=Mon to 7=Sun)

=WEEKDAY("12/25/2024", 1)

Christmas 2024 day of week

Note: Return_type: 1=Sun-Sat (1-7), 2=Mon-Sun (1-7), 3=Mon-Sun (0-6)

WEEKNUM()

Date & Time

Returns week number of the year

Syntax:

=WEEKNUM(serial_number, [return_type])

Examples:

=WEEKNUM(TODAY())

Current week number

=WEEKNUM(A1, 2)

Week number (Monday as week start)

=WEEKNUM("7/4/2024", 1)

Week number of July 4th

Note: Return_type: 1=Sun start (default), 2=Mon start, others available

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

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

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

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

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

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

MATCH()

Lookup & Reference

Returns position of value in array

Syntax:

=MATCH(lookup_value, lookup_array, [match_type])

Examples:

=MATCH("Apple", A1:A10, 0)

Find exact position of 'Apple'

=MATCH(100, B1:B10, 1)

Find largest value ≤ 100

=MATCH(A1, B:B, 0)

Find position of A1 value in column B

Note: 0=exact match, 1=largest ≤ value, -1=smallest ≥ value

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

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

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

OFFSET()

Lookup & Reference

Returns reference offset from starting reference

Syntax:

=OFFSET(reference, rows, cols, [height], [width])

Examples:

=OFFSET(A1, 2, 3)

Reference 2 rows down, 3 columns right from A1

=SUM(OFFSET(A1, 0, 0, 5, 1))

Sum 5 cells starting from A1

=OFFSET(A1, 0, 0, COUNTA(A:A), 1)

Dynamic range from A1 to last data

Note: Creates dynamic references. Often used with named ranges

INDIRECT()

Lookup & Reference

Returns reference specified by text string

Syntax:

=INDIRECT(ref_text, [a1])

Examples:

=INDIRECT("A1")

Reference to cell A1

=INDIRECT("Sheet2!A1")

Reference to A1 on Sheet2

=INDIRECT(A1&"1")

Dynamic reference using cell value

Note: Reference updates when text changes. Can reference other sheets

ADDRESS()

Lookup & Reference

Returns address of cell as text

Syntax:

=ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

Examples:

=ADDRESS(1, 1)

Returns '$A$1'

=ADDRESS(5, 3, 4)

Returns 'C5' (relative reference)

=ADDRESS(ROW(), COLUMN())

Current cell address

Note: abs_num: 1=absolute, 2=abs row/rel col, 3=rel row/abs col, 4=relative

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

ROWS()

Lookup & Reference

Returns number of rows in reference

Syntax:

=ROWS(array)

Examples:

=ROWS(A1:A10)

Returns 10

=ROWS(A1:C5)

Returns 5

=ROWS(A:A)

Returns 1048576 (max rows)

Note: Counts total rows in the reference range

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

COLUMNS()

Lookup & Reference

Returns number of columns in reference

Syntax:

=COLUMNS(array)

Examples:

=COLUMNS(A1:E1)

Returns 5

=COLUMNS(A1:C10)

Returns 3

=COLUMNS(1:1)

Returns 16384 (max columns)

Note: Counts total columns in the reference range

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

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+

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

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

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)

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

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

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

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

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

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

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

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

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

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

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

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

FILTER()

Dynamic Array Functions

Filters array based on criteria

Syntax:

=FILTER(array, include, [if_empty])

Examples:

=FILTER(A1:C10, B1:B10>100)

Filter rows where column B > 100

=FILTER(A1:A10, A1:A10<>"", "No Data")

Filter non-empty cells

=FILTER(A1:C10, (B1:B10="Apple")*(C1:C10>50))

Multiple criteria filter

Note: Excel 365 and Excel 2021. Returns dynamic array that spills

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

UNIQUE()

Dynamic Array Functions

Returns unique values from array

Syntax:

=UNIQUE(array, [by_col], [exactly_once])

Examples:

=UNIQUE(A1:A10)

Unique values from column

=UNIQUE(A1:C10, FALSE, TRUE)

Values that appear exactly once

=UNIQUE(A1:C10, TRUE)

Unique rows (by column)

Note: Excel 365 and Excel 2021. by_col: TRUE=compare rows, FALSE=compare by row

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

RANDARRAY()

Dynamic Array Functions

Generates array of random numbers

Syntax:

=RANDARRAY([rows], [columns], [min], [max], [whole_number])

Examples:

=RANDARRAY(5, 3)

5x3 array of random decimals 0-1

=RANDARRAY(10, 1, 1, 100, TRUE)

10 random integers 1-100

=RANDARRAY(3, 3, -1, 1, FALSE)

3x3 array of decimals -1 to 1

Note: Excel 365 and Excel 2021. whole_number: TRUE=integers, FALSE=decimals

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

TAKE()

Dynamic Array Functions

Takes specified number of rows/columns from array

Syntax:

=TAKE(array, rows, [columns])

Examples:

=TAKE(A1:C10, 5)

Take first 5 rows

=TAKE(A1:C10, -3)

Take last 3 rows

=TAKE(A1:C10, 5, 2)

Take first 5 rows and 2 columns

Note: Excel 365 Insider. Negative numbers take from end

DROP()

Dynamic Array Functions

Drops specified number of rows/columns from array

Syntax:

=DROP(array, rows, [columns])

Examples:

=DROP(A1:C10, 2)

Drop first 2 rows

=DROP(A1:C10, -1)

Drop last row

=DROP(A1:C10, 1, 1)

Drop first row and first column

Note: Excel 365 Insider. Negative numbers drop from end

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

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

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.

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

📊 102 Total Formulas 🏷️ 10 Categories ⚡ Quick Search