Ultimate Excel Guide: Top 80 Functions

Master Excel with these essential functions that will boost your productivity and data-handling efficiency. Below is a categorized list of top 80 Excel functions along with examples:

SUM

Adds up a range of numbers.

=SUM(A1:A5)

AVERAGE

Calculates the average of a range of numbers.

=AVERAGE(A1:A5)

COUNT

Counts the number of cells that contain numbers.

=COUNT(A1:A5)

MAX

Returns the highest value in a range.

=MAX(A1:A5)

MIN

Returns the lowest value in a range.

=MIN(A1:A5)

IF

Returns one value if a condition is true and another if it's false.

=IF(A1>10,"Yes","No")

AND

Returns true if all conditions are true.

=AND(A1>10,B1<20)

OR

Returns true if any argument is true.

=OR(A1>10,B1<20)

NOT

Returns the opposite of a logical value.

=NOT(A1>10)

ROUND

Rounds a number to a specified number of digits.

=ROUND(A1,2)

TODAY

Returns the current date.

=TODAY()

NOW

Returns current date and time.

=NOW()

CONCATENATE

Joins two or more text strings.

=CONCATENATE("Hello ","World")

LEFT

Returns characters from the left side of a string.

=LEFT(A1,5)

RIGHT

Returns characters from the right side of a string.

=RIGHT(A1,5)

MID

Returns characters from the middle of a string.

=MID(A1,3,5)

LEN

Returns the length of a string.

=LEN(A1)

SUBSTITUTE

Replaces text in a string with new text.

=SUBSTITUTE(A1,"old","new")

TRIM

Removes extra spaces from text.

=TRIM(A1)

PROPER

Capitalizes the first letter of each word.

=PROPER(A1)

LOWER

Converts text to lowercase.

=LOWER(A1)

UPPER

Converts text to uppercase.

=UPPER(A1)

DATE

Returns the serial number of a date.

=DATE(2022,4,6)

TIME

Returns the serial number of a time.

=TIME(12,30,0)

VLOOKUP

Vertical lookup in a table.

=VLOOKUP(A1,A2:B5,2,FALSE)

HLOOKUP

Horizontal lookup in a table.

=HLOOKUP(A1,A2:B5,2,FALSE)

INDEX

Returns a value from a table based on row/column.

=INDEX(A1:B5,3,2)

MATCH

Returns position of a value in a range.

=MATCH(A1,A2:A5,0)

CHOOSE

Selects a value from a list based on position.

=CHOOSE(2,"Mon","Tue","Wed")

ROUNDUP

Rounds a number up.

=ROUNDUP(A1,2)

ROUNDDOWN

Rounds a number down.

=ROUNDDOWN(A1,2)

RAND

Returns a random number between 0 and 1.

=RAND()

RANK

Returns the rank of a number.

=RANK(A1,A2:A5)

COUNTIF

Counts cells that meet one criterion.

=COUNTIF(A1:A5,">10")

SUMIF

Adds cells that meet a criterion.

=SUMIF(A1:A5,">10")

AVERAGEIF

Averages cells that meet a criterion.

=AVERAGEIF(A1:A5,">10")

COUNTIFS

Counts cells that meet multiple criteria.

=COUNTIFS(A1:A5,">10",B1:B5,"<20")

SUMIFS

Adds cells that meet multiple criteria.

=SUMIFS(A1:A5,">10",B1:B5,"<20")

AVERAGEIFS

Averages cells that meet multiple criteria.

=AVERAGEIFS(A1:A5,">10",B1:B5,"<20")

IFERROR

Returns custom value if formula results in error.

=IFERROR(A1/B1,"Error")

TEXT

Formats a value as text.

=TEXT(A1,"0.00")

Tip: Practice each of these functions in your own spreadsheet to become an Excel pro!