SUM(number1,number2,…)
Adds all the numbers in a range of cells.
Link to Video
Value Result
4 15
8 35
3
9
2
3
6
Notes:
-You can use a range of numbers
-Empty cells and text values are ignored
-Alt + = is the shortcut to enter the SUM function
More Office Help
AVERAGE(number1,number2,…)
Returns the average (arithmetic mean) of its arguments, which can be number or
names, arrays, or references that contain numbers.
Link to Video
Value Result
4 5
8 5
3
9
2
3
6
Notes:
-You can use a range of numbers
-Empty cells and text values are ignored
More Office Help
ROUND(number,num_digits)
Rounds a number to a specified number of digits.
Link to Video
Value Result
123.6 124
123.3 123
23.847 23.85
23.847 20
Notes:
-You can use a negative number for num_digits
-Check out the ROUNDDOWN and ROUNDUP functions
More Office Help
IF(logical_test,value_if_true,value_if_false)
Checks whether a condition is met, and returns one value if TRUE, and another
value if FALSE.
Link to Video
Value 1 Value 2 Result
Jones Jones Yes
Smith Smyth No
Value 1 Value 2 Result
9 4 5
Notes:
-If the logical_test is invalid it returns a #VALUE! or #NAME! error
More Office Help
COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition.
Link to Video
Value 1 Value 2 Result
Red 10 2
Blue 8 3
Green 2 5
Black 14 6
Green 7 2
8 3
Blue 14
Notes:
-The criteria must be in quotes
-The criteria ignores upper and lower case
-Use a named range to make it easier copying formulas
-Use wildcard characters of * or ?
More Office Help
SUMIF(range,criteria,sum_range)
Adds the cells specified by a given condition or criteria.
Link to Video
Value 1 Value 2 Result
Red 10 38
Blue 8 22
Green 2 41
Black 14 8
Green 7
8
Blue 14
Notes:
-The criteria must be in quotes
-The criteria ignores upper and lower case
-Use a named range to make it easier copying formulas
-The range and sum_range do not have to be the same size
More Office Help
RANK(number,ref,order)
Returns the rank of a number in a list of numbers: its size relative to other values in the
list
[Link](number,ref,order)
Returns the rank of a number in a list of numbers; its size relative to other values in the
list; if ore than one value has the same rank, the average rank is returned.
[Link](number,ref,order)
Returns the rank of a number in a list of numbers; its size relative to other values in the
list; if ore than one value has the same rank, the top rank of that set of values is
returned.
Link to Video
Value Result 1 Result 2 [Link] [Link]
4 4 4 4 4
8 2 6 2 2
3 5 2 5.5 5
9 1 7 1 1
2 7 1 7 7
3 5 2 5.5 5
6 3 5 3 3
Notes:
-If order is 0 or omitted ranks in decending order, otherwise ascending order
-Use [Link] or [Link] for more accuracy
More Office Help RANK
More Office Help [Link]
More Office Help [Link]
TODAY()
Returns the current date formatted as a date.
Link to Video
Result
8/5/2019
8/10/2019
5
8
142
Notes:
-TODAY() is updated automatically when you open or refresh the spreadsheet
-Day or Month values must be formatted as a number rather than a date
More Office Help
DATE(year,month,day)
Returns the number that represents the date in Microsoft Excel date-time code.
Link to Video
Month Day Year Result
11 17 2019 11/17/2019
2019
11
17
Notes:
-Excel stores dates as sequencial numbers starting with 1 at January 1, 1900
-Format the cells to change between sequential numbers and date format
-Use YEAR, MONTH, and DAY to do the reverse
More Office Help
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
Looks for a value in the leftmost column of a table, and then returns a value in the
same row from a column you specify. By default, the table must be sorted in
ascending order.
Link to Video
Value Result Name Sales
John 324 Fred 490
Fred 490 Sally 987
John 324
Mary 667
Tom 816
Notes:
-The lookup value must always be in the leftmost column of your table
-The result with be #N/A if nothing is found
-Use absolute references or name the table in order to copy formulas easily
-Description states that it must be sorted in ascending order. That is only required if you use TRUE in the range_lookup
-If you use a col_index_num beyond the table you selected it will return a #REF! error
More Office Help
RUE in the range_lookup
FIND(find_text,within_text,start_num)
Returns the starting position of one text string within another text string (case
sensitive).
SEARCH(find_text,within_text,start_num)
Returns the starting position of one text string within another text string is first
found (not case-sensitive).
REPLACE(old_text,start_num,num_chars,new_text)
Replaces part of a text string with a different text string.
Link to Video
Value Result
This is a test 3
6
#VALUE!
3
3
1
This is the test
This is a new test
Notes:
-If the start_num is omitted it is assumed to be 1
-Wildcard characters can be the question mark (?) or asterisk (*)
-Main differences between FIND and SEARCH is that SEARCH allows wildcards and is not case-sensitive
More Office Help FIND
More Office Help SEARCH
More Office Help REPLACE
CONCATENATE(text1,text2,…)
Joins several text strings into one text string.
Link to Video
Value 1 Value 2 Result
Jesse James JesseJames
1 2 Jesse James
James, Jesse
Jesse James
12
Notes:
-Use quote marks around any text. Not required for numbers
-A #NAME? error usually means quote marks are missing where needed
More Office Help
TRIM(text)
Removes all spaces from a text string except for single spaces between words.
Link to Video
Value Result
This is a test This is a test
Notes:
-TRIM removes leading and trailing spaces as well as extra spaces between words
-TRIM is often used in conjuntion with LEN, LEFT, MID, and RIGHT to modify text
More Office Help
LEN(text)
Returns the number of characters in a text string.
Link to Video
Value Result
This is a test 19
14
Notes:
-LEN counts spaces in between words as well as added to the end
-LEN is often used in conjuntion with TRIM, LEFT, MID, and RIGHT to modify text
More Office Help
LEFT(text,num_chars)
Returns the specified number of characters from the start of a text string.
RIGHT(text,num_chars)
Returns the specified number of characters from the end of a text string.
Link to Video
Value Result
This is a test This
test
Notes:
-LEFT and RIGHT is often used in conjuntion with TRIM and MID to modify text
-Use MID if you need to start in the middle of text
More Office Help LEFT
More Office Help RIGHT
MID(text,start_num,num_chars)
Returns the characters from the middle of a text string, given a starting position and
length.
Link to Video
Value Result
This is a test is
is a test
Notes:
-If start_num exceeds the legth of text, MID returns nothing
-If start_num + num_chars exceeds the length of text, MID returns the characters up to the end of text
More Office Help
UPPER(text)
Converts a text string to all uppercase letters.
LOWER(text)
Converts a text string to all lower case letters.
PROPER(text)
Converts a text string to proper case; the first letter in each word to uppercase, and
all other letters to lowercase.
Link to Video
Value Result
This is a test THIS IS A TEST
this is a test
This Is A Test
Notes:
-Numbers are ignored
More Office Help UPPER
More Office Help LOWER
More Office Help PROPER
MIN(number1,number2,…)
Returns the smallest value in a set of values. Ignores logical values and text.
MAX(number1,number2,…)
Returns the largest value in a set of values. Ignores logical values and text.
Link to Video
Value Result
4 3
8 2
3 2
9 8
2 9
3 9
6
Notes:
-You can use a range of numbers
-Empty cells and text values are ignored
More Office Help MIN
More Office Help MAX
AND(logical1,logical2,…)
Checks whether all arguments are TRUE, and returns TRUE if all arguments are
TRUE.
OR(logical1,logical2,…)
Checks whether any of the arguments are TRUE, and returns TRUE or FALSE.
Returns FALSE only if all arguments are FALSE.
Link to Video
Value Result
4 1
8 0
1
1
1
Notes:
-Also check out the NOT function
More Office Help AND
More Office Help OR
IFERROR(value,value_if_error)
Returns value_if_error if expression is an error and the value of the expression itself
otherwise.
Link to Video
Value 1 Value 2 Result
100 25 4
200 0 error
Test Text #VALUE! error
Notes:
-Evaluates errors of type #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!
More Office Help
