Excel Functions (my top 21)


Did you know that Excel boasts over 470 built-in functions? The one with which you’re most familiar is probably the SUM() function. Rather than learn these 470 plus functions, the large majority of which you’ll never need, take a look at my top 21 favorites. 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)

Example VLOOKUP workbook!


ARRAYVLOOKUP () Looks for a value in a column of a table, then returns a value from the same row and another column that you specify. Values returned may be from the LEFT or RIGHT of the item found. Returns a single string with the items separated by a comma.

Your macros must be enabled because this is a user-defined function.

Syntax: ARRAYVLOOKUP(lookup_value,table_array,col_index_num,[Boolean return unique_list],[integer maximum return items])

Example ARRAYVLOOKUP workbook!


SKIPVLOOKUP () Looks for a value in a column of a table, then returns a value from the same row and another column that you specify. Values returned may be from the LEFT or RIGHT of the item found. Can skip over the first 1,2,3 or any number of results found.

Your macros must be enabled because this is a user-defined function.

Syntax: SKIPVLOOKUP(lookup_value,table_array,col_index_num,skipcount)

Example SKIPVLOOKUP workbook!


XLOOKUP () Looks for a value in a column of a table, then returns a value from the same row and another column that you specify. May also be used in a horizontal lookup. Values returned may be from the LEFT or RIGHT of the item found. Has several interesting features. Check out both of these videos to see two previews of this function soon to come to all 365 users.


LEFT(), RIGHT(), MID(), FIND(), LEN(), REVERSETEXT()  Basic string functions that do what they intuitively claim to do.

  • Syntax: LEFT(text,num_chars)
  • Syntax: RIGHT(text,num_chars)
  • Syntax: MID(text,start_num,num_chars)
  • Syntax: FIND(find_text,within_text,start_num)
  • Syntax: LEN(text)
  • Syntax: REVERSETEXT(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.

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: IF(logical_test,value_if_true,value_if_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, an integer in the range 1900 – 9999

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

DAY() Returns the day of the month, a number from 1 to 31.
  • Syntax: YEAR(serial_number)
  • Syntax: MONTH(serial_number)
  • Syntax: DAY(serial_number)

SUMIFS() Adds the cells within a range that meet the given condition(s).

Syntax: SUMIFS(sum_range,criteria_range1,criteria1, ….)


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

Syntax: COUNTIFS(criteria_range1,criteria1, ….)


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

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