1
EXCEL FUNCTION ONE PAGER
Ser Function Functioning/ Purpose Function/ Formula Example
Title
1. = Is equal to value = is always in the start
2. Formula
3. Function
4. Sen text
5. Tab for ( Tab key for ( open Press tab for ( start
6. Enter for ) Enter key for ) Close Press Enter in last for )
7. Range Range of cells Type address of cells =COUNTIF(C2:D2,”P”)
8. $ sign
9. Criteria Rule assigned Always in comma “ “ “Yes”, “No”, “P”, “A”, “Laptop”
10. Space is _ _ is Space in function
11. Space cell “ “ is to joint two cells
12. Ctrl + D To apply on bellow
13. Shift arrow Selection of cell/ range Shift + Up/ Down arrows
14. SUM Shift down/ Up, Ctrl + =SUM(range) OR Alt+ =SUM(C2:D2). Alt+ row/
D column
15. SUMIF Sum of a single item in =SUMIF(range,”criteria”, =SUMIF(C2:D2,”T-shirt”, E2:R2)
range sum_range)
16. PRODUCT Multiplication =PRODUCT(no1,no2) =PRODUCT(C2:D2)
17. MAX Maximum value =MAX(range) =MAX(C2:D2)
18. MIN Minimum value =MIN(range) =MIN(C2:D2)
19. LEN To count chars =LEN(value) =LEN(nagese12) =8
20. COUNT No blank/ chars count =COUNT(value) =Count(C2:D2)= 2
21. COUNTA No blank count (A = All) =COUNTA(value) Including space in a cell
22. COUNTBL Count blank/ empty cell =COUNTBLANK(range) =COUNTBLANK(C2:D2)
ANK
23. COUNTIF/ Count (name/ No) that =COUNTIF(range,”criter =COUNTIF(C2:D2,”P”)
2
repetition meet given condition ia”) =COUNTIF(C2:D2,”A”)
24. COUNTIFS Count cells that meet =COUNTIFS(criteria =COUNTIFS(C2:D2,”Cash”,
set of condition range,”criteria”,criteria E2:R2,”>3500”)
range2,”criteria”)
25. AVERAGE average =AVERAGE(range) =AVERAGE(C2:D2)
26. PROPER First capital chars =PROPER(text) =PROPER(ram) =Ram
27. UPPER Small to capital =UPPER(text) =UPPER(am) = AM
28. CONCATE To join words =CONCATENATE(text1 =CONCATENATE(C2, “ “, D2)
NATE ,” “, text3)
29. LARGE K is position =LARGE(array,k) =LARGE(C2:D2,3)
30. SMALL K is position =SMALL(array,k) =SMALL(C2:D2,3)
31. ROMAN Convert in roman =ROMAN(numbers) =ROMAN(C2:D2)
32. IF Conditional Results. =IF(logical_test,”value if =IF(D7>=50000,”Laptop”,”Tab”)
Single Logic test true,”value if false”)
33. IF – AND More than one logic test =IF(AND(logical1,..),”val =IF(AND(C3>=45,D3>=45,E3>=
ue if true”,”value if 45),”Pass”,”Fail”)
false”)
34. IF – OR Optional. OR is less =IF(OR(Logical1,..) =IF(OR(C3<45,D3<45,E3<45),”
than Fail”,”Pass”)
35. IF-IF (IFS) =IF(Logical test,value if =IF(result=”Fail”,”Fail”,IF(%age>
true, value if false =75,”A+”,IF(%>=60,”A”,IF(%>=4
5,”C”)
36. LEFT Find from left =LEFT(text, number =LEFT(Sunday,3) = Sun
chars)
37. RIGHT Find from right =RIGHT(text, number =RIGHT(5497328,4) = 7328
chars)
38. NOW Today date and time =NOW() =NOW()
39. TODAY Date of today =TODAY() =TODAY()
40. Yesterday Date of yesterday =TODAY()-1
41. Tomorrow Date of tomorrow =TODAY()+1
42. TIME Create time format =TIME(hours,minute, =TIME(24hours,minute, second)
second)
43. MONTH Convert days into month =MONTH(days number) =MONTH(365) = 12
44. DATEDIF To calculate years =DATEDIF(DOB,Today(),”Y”)
45. DATEDIF To calculate months =DATEDIF(DOB,Today(),”YM”)
3
46. DATEDIF To calculate days =DATEDIF(DOB,Today(),”MD”)
47.
4
MATH FUNCTIONS IN MS EXCEL
Ser Function Formula
1. What are the math functions SUM() Alt > H > U > Sum
in Ms Excel? SUMIF()
Average() Alt > H > U > Average
AverageIF()
AverageIF()
AverageIFs()
COUNT() Alt > H > U > Count
ROUND()
INT()
ABS()
VLOOKUP()
2.
SUM FORMULAS FOR MS EXCEL
Ser Function Formula
1. Auto Sum Alt > H > U > Sum
2. Sum function with formula 1) =SUM(cell range) Enter OR
2) =SUM(Select first and last cell) and entre. OR
3) =SUM(Shift + Ctrl + Arrow down) and Enter
Example: =SUM(B2:B9)
3. Sum function with shortcut 1) =SUM(Select the lower cell > press Alt + = to auto select
key all data of that column > press entre
4. Sum function without formula 2) Select all data > Home tab > Auto Sum > Enter (for
calculation of automatic sum)
5. To add up individual items 1) =Value1 + Value2
Example: =B2+C2
6. Addition To getting the result of adding the numeric value
=sum(A1+A2)
=A1+A2
=Sum(10+2)
=10+2
To add the value in series from A1 to A10 =sum(A1:A8)
7. SUM() To add the value in the range
=SUM(CellAddress:CellAddress)
To add the value of different cells
=SUM(A1+C12+D9)
You can use a comma (,) instead of plus symbols in the
formula like =SUM(A1,C12,D9)
8. SUMIF() To add the numeric value in range with specific criteria.
SUMIF(Range, Criteria, Range)
5
SUBTRACTION FORMULAS FOR MS EXCEL
Ser Function Formula
1. Subtraction Function =Value1 – Value2
Example: =B2-C2
2. Subtraction To getting the result of subtracting the numeric value
=sum(A1-A2)
=A1-A2
=Sum(10-2)
=10-2
To subtract value in series from A1 to A10 =sub(A1:A8)
MULTIPLICATION FORMULAS FOR MS EXCEL
Ser Function Formula
1. Multiplication Function =Value1 * Value2
Example:
=B2*C2
2. Multiplication To get the result of multiplying of numeric value
=sum(A1*B1)
=A1*B1
=sum(2*3)
=2*3
DIVISION FORMULAS FOR MS EXCEL
Ser Function Formula
1. Division Function =Value1 / Value2
Example:
=B2/B2
2. Division To get the result of dividing of numeric value.
=sum(A1/A2)
=A1/A2
=sum(10/2)
=10/2
3.
6
AVERAGE FORMULAS FOR MS EXCEL
Ser Function Formula
1. Average Function 1) Manual Average: Sum of all Cells > divided by counts of
cells.
2) =AVERAGE(cell range) OR
3) For average in column only: Select data> Home Tab>
AutoSum> Average
Example: =AVRAGE(B2:B9)
2. Average Function and Text 4) Text is not considered in counting by Average Function.
3. Average Function and Blank 5) Blank cell is also not considered in counting by Average
Function.
4. Average() To calculate the average
=Average(StartCellAddress:EndCellAddress)
=Average(A1:A10)
5. AverageIF() Same used as SUMIF()
To get the average of specific criteria
6. AverageIFs() Same used as SUMIF()
To get the average of specific criteria.
AVERAGEA FORMULAS FOR MS EXCEL
Ser Function Formula
7. Average A Function 6) Manual Average: Sum of all Cells > divided by counts of
cells.
7) =AVERAGEA(cell range) OR
8) For average in column only: Select data> Home Tab>
AutoSum> Average
Example: =AVRAGEA(B2:B9)
8. Average A Function and Text 9) Text is considered as Zero in counting by AverageA
and False Function.
9. Average A Function and True 10)Text is considered as One in counting by AverageA
Function.
10. Average A Function and Blank 11)Blank cell is also not considered in counting by Average
Function.
PERCENTAGE FORMULAS FOR MS EXCEL
Ser Function Formula
1. Percentage Formula (No =Marks Obtain / Total marks x 100
Function (a pre define formula) =Marks Obtain/ Total marks select % from Home tab in
is not available in Excel. number section
Example: =50/100
50% and select % from Home Tab (No Section)
2. Difference between Average Average could be more than 100, but percentage could not.
7
8
MAXIMUM FORMULAS FOR MS EXCEL
Ser Function Formula
1. Maximum Function =MAX(cell range)
Example:
=MAX(B2:B9)
MINIMUM FORMULAS FOR MS EXCEL
Ser Function Formula
1. Minimum Function =MIN(cell range)
Example:
=MIN(B2:B9)
MEAN FORMULAS FOR MS EXCEL
Ser Function Formula
1. What is Mean? The mean (average) of a data set is found by
adding all numbers in the data set and then
dividing by the number of values in the set
2. Mean Function =MEAN(cell range)
Example: =MEAN (B2:B3)
MEDIAN FORMULAS FOR MS EXCEL
Ser Function Formula
1. What is Median? The median is the middle value when a data set
is ordered from least to greatest.
2. Median Function =MEDIAN(cell range)
Example: =MEDIAN(B2:B3)
MODE FORMULAS FOR MS EXCEL
Ser Function Formula
1. What is Mode? The mode is the number that occurs most often
in a data set.
2. MODE Function =MODE(cell range)
Example: =MODE(B2:B3)
9
EXPONENTS FORMULAS FOR MS EXCEL
Ser Function Formula
1. Exponents Meaning A quantity representing the power to which a given number
or expression is to be raised, usually expressed as a raised
symbol beside the number or expression (e.g. 3 in 23 = 2 ×
2 × 2).
2. Exponents Function =Value1 ^ Value2
Example: =B2^B2
RANK FORMULAS FOR MS EXCEL
Ser Function Formula
1. To calculate the rank of a value within a range of values
lets you determine someone's position with a single click.
2. Rank =RANK (range, current cell)
Example: =RANK(12,$1$2:$1$16,0)
=RANK(B3,($B$3:$B$7))
RANK(number, ref, [order])
n the above formula:
number: is the number you want to rank.
ref: is the range of numbers to take into account for ranking.
order: specifies the rank order. For ascending, use “1” as a
value; for descending, use “0” (default).
COUNT FORMULAS FOR MS EXCEL
Ser Function Formula
COUNT
1. Count To count the total number of values in the specified range
except for text.
2. Count Function =COUNT(Insert range)
Example: =COUNT(
3. Status of date in MS Excel Date is also counts in numbers.
4. Text status in COUNT COUNT function does not text in counting.
5. Blank cell status in COUNT Blank cell also does not COUNT by COUNT formula.
COUNTA OR COUNTALL
6. COUNTA OR COUNT It will count all numbers, text, logical values, etc.
Any type of value excluding blanks.
COUNTBLANK
7. COUNTBLANK To count blank cells or cells with an empty string.
COUNTIF & COUNTIFS
8. COUNTIF and COUNTIFS Both function we can use to count the matching criteria in a
specific range.
10
IF FORMULAS FOR MS EXCEL
Ser Function Formula
1. If Function =IF(condition, True Statement, False Statement)
Example: =SUMIFS(
=IF(M3<150,”Fail”,“Pass”)
=IF(M3<50%,”Fail”,”Pass”)
2. “” is represent blank cell in The IF function is used to test for a condition and return
result. one value if the condition is True, and the another value
if the condition is False.
If function has three parameters. The last one is optional.
1st The first parameter is thee condition OR value to test.
2nd The second parameter we will need to enter is the
value to return if the condition is True.
3rd is Option parameter. It contains the value that will be
returned if the condition evaluates to False.
COUNTIFS FORMULAS FOR MS EXCEL
Ser Function Formula
1. Count If’s Function =COUNTIFS(range1, criteria1,[range2],[criteria2],…)
Example:ra
=COUNTIFS(
SUMIFS FORMULAS FOR MS EXCEL
Ser Function Formula
1. Sum If’s Function =SUMIFS(sum_range,range1 criteria1,[range2],[criteria2],
…)
Example:
=SUMIFS(
AVERAGEIFS FORMULAS FOR MS EXCEL
Ser Function Formula
1. Average If’s Function =AVERAGEIFS(average_range,criteria_range1, criteria1,
[criteria_range2],[riteria2],…)
Example:
=SUMIFS(
11
AND FORMULAS FOR MS EXCEL
Ser Function Formula
1. AND Function =AND(condition, condition)
Example:
=AND(condition, condition)
BAND FORMULAS FOR MS EXCEL
Ser Function Formula
1. BAND Function =BAND()
=BAND()*100
=INT(RAND()*100)
Example:
=BAND(
OR FORMULAS FOR MS EXCEL
Ser Function Formula
1. OR Function =OR(condition, condition)
Example:
=OR(condition, condition)
SUMPRODUCT FORMULAS FOR MS EXCEL
Ser Function Formula
2. Sum Product Function =SUMPRODUCT(array1,[array2],[array3],…)
Example:
=SUMPRODUCT(
ROUND FUNCTION
3. ROUND() To round the after decimal value with a specific digit.
=ROUND(Decimal Value, number of digits to round)
Suppose you have 24.943 value in C10 and you want to
round it in 0 digit =ROUND(C10,0) will be 25
=Round(C10,1) will be 24.9
=Round(C10,2) will be 24.94
INT FUNCTION
4. INT() This function will remove the value after decimal point
like
10.9 after using =INT(10.9) it will be 10
ABS FUNCTION
5. ABS() To get the absolute value
=ABS(5-10)
5
If you solve this equation with SUM then result will be
=SUM(5-10) result -5
12
VLOOKUP FORMULAS FOR MS EXCEL
Ser Function Formula
1. VLOOKUP Function To find a value in a table or range
2. Vertical Look UP Function =VLOOKUP(lookup_value;table_array;col_index
_number;[rang_lookup})
Example: =VLOOKUP(
3. VLOOKUP() In Excel, VLOOKUP allows fetching the database on
criteria.
=VLOOKUP(lookup value, table array, col index)
4.
HLOOKUP FORMULAS FOR MS EXCEL
Ser Function Formula
1. H Look UP Function =HLOOKUP(lookup_value;table_array;col_
index_number;[range_lookup])
Example:
=HLOOKUP(
