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 45 formulas matching "AND"

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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)

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

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

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

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

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

📊 102 Total Formulas 🏷️ 10 Categories ⚡ Quick Search