Excel Functions (my top 21)

Did you know that Excel boasts over 400 built-in functions? The one with which you’re most familiar is probably the SUM() function. Rather than learn these 400 plus functions, the large majority of which you’ll never need, I have compiled my list of the top 21. I encourage you to master these functions. I also encourage you to periodically go through Excel’s function listing, looking for the one that might help you solve a problem you had a week or a month ago.  Watch the videos. Also be sure to check out what Excel has to say about the function.

Perusing Excel’s Functions

One way to find out what Excel has to offer, in terms of built-in functions, is to periodically go through the list. If one looks interesting then click on it and read the short blurb about what it does. If the blurb is tantalizing enough then click “help on this function” to see a full blown explanation.

VLOOKUP () Looks for a value in the leftmost column of a table, and then returns a value in the same row from a column that you specify. By default the table must be sorted in ascending order.

Syntax: VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

LEFT() Returns the specified number of characters from the start of a text string.

Syntax: LEFT(text,num_chars)

RIGHT() Returns the specified number of characters from the end of a text string.

Syntax: RIGHT(text,num_chars)

MID() Returns the characters from the middle of a text string, given a starting position and length.

Syntax: MID(text,start_num,num_chars)

FIND() Returns the starting position of one text string within another text string. FIND is case-sensitive.

Syntax: FIND(find_text,within_text,start_num)

LEN() Returns the number of characters in a text string.

Syntax: LEN(text)

LOWER() Converts a text string to all lowercase letters.

Syntax: LOWER(text)

UPPER() Converts a Text string to all uppercase letters.

Syntax: UPPER(text)

PROPER() Converts a Text string to Proper Case; the first letter in each word in uppercase, and all other letters to lowercase.

Syntax: PROPER(text)

TEXT() Converts a value to Text in a specific number format.

Syntax: TEXT(value,format_text)

VALUE() Converts a text string that represents a number to a number.

Syntax: VALUE(text)

IF() Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE. Use IF to conduct conditional tests on values and formulas.

Syntax: IF(logical_test,value_if_true,value_if_false)

AND() Checks whether all arguments are true. Returns TRUE if all its arguments are TRUE; returns FALSE if one or more argument is FALSE.

Syntax: AND(logical1,logical2, …)

MAX() Returns the largest value in a set of values.Ignores logical values and text.

Syntax: MAX(number1,number2,…)

MIN() Returns the largest value in a set of values.Ignores logical values and text.

Syntax: MIN(number1,number2,…)

YEAR() Returns the year of a date, and integer in the range 1900 – 9999

Syntax: YEAR(serial_number)

MONTH() Returns the month, a number from 1 (January) to 12 (December)

Syntax: MONTH(serial_number)

DAY() Returns the day of the month, a number from 1 to 31.

Syntax: DAY(serial_number)

SUMIF() Adds the cells specified by a given condition or criteria.

Syntax: SUMIF(range,criteria,sum_range)

COUNTIF() Counts the number of cells within a range that meet the given condition.

Syntax: COUNTIF(range,criteria)

SUBTOTAL() Returns a subtotal in a list or database.

Syntax: SUBTOTAL(function_num, ref1, ref2, …)