ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
Excel Functions Guide
Color Code
2
Updated as of 4/24/2012
Formula
3
Input
4
Comments or suggestions?
5
6
7
Total42
8
Filtered Total42
9
10
RankGroupExcel FunctionShort DescriptionQwerks/CommentsFormula ExamplesInput 1Input 2Input 3Input 4
11
DatabaseHLOOKUPLooks for the topmost row of a table and returns the value in the same column from a row specified.
12
TOP10DatabaseVLOOKUPLooks for the leftmost column of a table and returns the value in the same row from a column specified.Link
13
Date and TimeDAYConverts a serial number to a day of the month152/15/2011
14
Date and TimeEOMONTHAdds the # of months to the date entered and returns the month end dateTo get the beginning of the month subtract 1 month in the formula and +1 to the result4/30/20111/1/20113
15
Date and TimeMONTHConverts a serial number to a month22/15/2011
16
Date and TimeNOWReturns the serial number of the current date and timeThis is a dynamic formula that will update whenever the sheet recalculates7/8/23 5:02
17
Date and TimeTODAYReturns the serial number of today's dateThis is a dynamic formula that will update whenever the sheet recalculates7/8/2023
18
Date and TimeWEEKDAYConverts a serial number to a day of the week61/1/2010
19
Date and TimeYEARConverts a serial number to a year20101/1/2010
20
TOP10FiltersSUBTOTALReturns a subtotal of a filtered list or database (1: average, 2: count (numbers), 3: count (non blanks), 4: maximum, 5: minimum, 6: product, 7: standard deviation (sample), 8: standard deviation (population), 9: sum, 10: variance (sample), 11: variance (population)Link
21
TOP10LogicalIFThis is a top formula used to add logic to any spreadsheet. If(Condition, True, False)Greater than1
22
TOP10LogicalISERRORThis returns a True or False value if the formula returns a #NA, #VALUE, #DIV/0! etc.Especially handy when used in conjuction with an IF function0#DIV/0!
23
LogicalMATCHLink
24
MathematicalROUNDRounds a number to a specified number of digitsYou can use - digits to round to the nearest -1 = 10 or -2 = 100, etc.511510.5550
25
MathematicalROUNDDOWNRounds a number down, away from zeroYou can use - digits to round to the nearest -1 = 10 or -2 = 100, etc.510510.5550
26
MathematicalROUNDUPRounds a number up, away from zeroYou can use - digits to round to the nearest -1 = 10 or -2 = 100, etc.511510.5550
27
TOP10MathematicalSUMIFAdds the cells specified by one criteriaThe criteria range and the sum range need start and end at the same rows or columns.Link
28
MathematicalSUMIFSAdds the cells specified by using multiple criteriaLink
29
StatisticalAVERAGEReturns the average of its argumentsLink
30
StatisticalAVERAGEAReturns the average of its arguments, including numbers, text, and logical values
31
StatisticalAVERAGEIFCalculates the average within a range that meet a given criteria
32
StatisticalAVERAGEIFSCalculates the average within a range that meet one or many given criteria
33
StatisticalCOUNTCounts how many numbers are in the list of arguments
34
StatisticalCOUNTACounts how many values are in the list of arguments)
35
TOP10StatisticalCOUNTIFCounts the cells specified by one criteriaLink
36
StatisticalCOUNTIFSCounts the number of nonblank cells within a range that meet the given criteriaLink
37
StatisticalLARGEReturns the k-th largest value in a data set
38
StatisticalMAXReturns the maximum value in a list of numbersLink
39
StatisticalMINReturns the minimum value in a list of numbersLink
40
StatisticalRANKReturns the rank of a number in a list of numbersLink
41
StatisticalSMALLReturns the k-th smallest value in a data set
42
TOP10Text&Use of the Ampersand between 2 or more cells will connect the contents togetherWhen you use this with dates and other cells that are formatted, you may need to use the "TEXT" function as well to convert the value to a text format prior to using the "&" otherwise you will retrieve just the values.First Last NameFirstLast Name
43
TextDATEVALUEConverts a text argument date to a numeric date value1/31/20111/31/2011
44
TextFINDFinds one text value within another (case-sensitive)7LFirst Last Name
45
TextLEFTReturns the leftmost characters from a text value317317-333-1234
46
TextLENReturns the number of characters in a text string12317-333-1234
47
TextMIDReturns a specific number of characters from a text string starting at the position you specify333317-333-1234
48
TextREPTRepeats text a given number of timesThis can be used to create mini bar graphs.||||||5
49
TextRIGHTReturns the rightmost characters from a text value1234317-333-1234
50
TOP10TextTEXTFormats a number and converts it to text2011011/31/2011
51
TOP10TextTRIMRemoves spaces from the left and right side of a text stringThis is very useful for sumifs, vlookups, etc. UPS UPS
52
TOP10TextVALUEConverts a text argument to a number333
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100