S No. |
What to do? |

1 | Count cells between dates COUNTIFS Example: =COUNTIFS($A$1:$A$20,”>=1/12/18″,$A$1:$A$20,”<=31/12/18″) |

2 | Count cells between two numbers COUNTIFS Example: =COUNTIFS($A$1:$A$20,”>=345″,$A$1:$A$20,”<=678″) |

3 | Count blank cells in range COUNTBLANK Example: =COUNTABLANK(A1:A10) |

4 | Count texts or any value in a range COUNTA Example: =COUNTA(A1:A10) |

5 | Count texts which contains any part text at start COUNTIF Example: =COUNTIF(A1:A10,”Excel*”) |

6 | Count texts which contains any part text at end COUNTIF Example: =COUNTIF(A1:A10,”*Excel”) |

7 | Count texts which contains any part text at any position COUNTIF Example: =COUNTIF(A1:A10,”*Excel*”) |

8 | Count cells greater than COUNTIF Example: =COUNTIF(A1:A10,”>10″) |

9 | Count cells less than COUNTIF Example: =COUNTIF(A1:A10,”<10″) |

10 | Count cells equal to COUNTIF Example: =COUNTIF(A1:A10,10) |

11 | Count cells that are blank COUNTBLANK Example: =COUNTBLANK(A1:A10) |

12 | Count cells which are negative in a range COUNTIF Example: =COUNTIF(A1:A10,”<0″) |

13 | Count cells which are positive in a range COUNTIF Example: =COUNTIF(A1:A10,”>0″) |

14 | Count items in list COUNTIFS Example: =COUNTIFS(A1:A10,”A”,B1:B10,”B”) |

15 | Count particular item in list COUNTIF Example: =COUNTIF(A1:A10,”A”) |

16 | Count dates which are older than COUNTIF Example: =COUNTIF(A1:A10,”<11/12/18″) |

17 | Count dates which are newer than COUNTIF Example: =COUNTIF(A1:A10,”>11/12/18″) |

18 | Count the visible rows after filtering data SUBTOTAL |

19 | Sum up many numerical values in range SUM Example: =SUM(A1:A10) |

20 | Cumulative sum of a range SUM Example: =SUM($A$1:A1) and drag it up to down like =SUM($A$1:A10) |

21 | Sum by group of items SUMIF Example: =SUMIF(A1:A10,”A”,B1:B10) |

22 | Sum cells greater than SUMIF Example: =SUMIF(A1:A10,”>10″) |

23 | Sum cells less than SUMIF Example: =SUMIF(A1:A10,”<10″) |

24 | Sum cells equal to SUMIF Example: =SUMIF(A1:A10,10) |

25 | Sum if multiple criteria matched SUMIFS Example: =SUMIFS(A1:A10,”<15″,B1:B10,”Boy”) |

26 | Sum the no of vegetables in list only SUMIF Example: =SUMIF(A1:A10,”VEG”,B1:B10) |

27 | Sum if cell contains texts in another cell at start of text SUMIF Example: =SUMIF(A1:A10,”Excel*”) |

28 | Sum if cell contains texts in another cell at end of text SUMIF Example: =SUMIF(A1:A10,”*Excel”) |

29 | Sum if cell contains texts in another cell at any position of text SUMIF Example: =SUMIF(A1:A10,”*Excel*”) |

30 | Sum up the products of values in two or more thean two array SUMPRODUCT Example: SUMPRODUCT(A1:A10,B1:B10,C1;C10) |

31 | Get the total by multiplying items no with rate. SUMPRODUCT Example: SUMPRODUCT(A1:A10,B1:B10,C1;C10) |

32 | Sum if cells contain both a and b SUMIFS Example: =SUMIFS(A1:A10,”*a*”,A1:A10,”*b*”) |

33 | Sum up the visible rows after filtering data SUBTOTAL |

34 | Sum by group of items SUMIF Example: =SUMIF(A1:A10,”A”) |

35 | Sum cells greater than SUMIF Example: =SUMIF(A1:A10,”>10″) |

36 | Sum cells less than SUMIF Example: =SUMIF(A1:A10,”<10″) |

37 | Sum cells equal to SUMIF Example: =SUMIF(A1:A10,10) |

38 | Sum the largest three values SUMIFS, LARGE Example: =SUMIFS(A1:A10,LARGE(A1:A2,1),A1:A10,LARGE(A1:A2,2),A1:A10,LARGE(A1:A2,3)) |

39 | Sum the smallest three values SUMIFS, SMALL Example: =SUMIFS(A1:A10,SMALL(A1:A2,1),A1:A10,SMALL(A1:A2,2),A1:A10,SMALL(A1:A2,3)) |

40 | Average numerical values AVERAGE Example: =AVERAGE(A1:A10) |

41 | Average numerical values with a criteria AVERAGEIF Example: =AVERAGEIF(A1:A10,10) |

42 | Average the largest three values AVERAGEIFS, LARGE Example: =AVERAGEIFS(A1:A10,LARGE(A1:A2,1),A1:A10,LARGE(A1:A2,2),A1:A10,LARGE(A1:A2,3)) |

43 | Average the smallest three values AVERAGEIFS, SMALL Example: =AVERAGEIFS(A1:A10,SMALL(A1:A2,1),A1:A10,SMALL(A1:A2,2),A1:A10,SMALL(A1:A2,3)) |

44 | Maximum value within a range MAX Example: =MAX(A1:A10) |

45 | Minimum value within a range MIN Example: =MIN(A1:A10) |

46 | Maximum value within a range with criteria MAXIF, MAXIFS |

47 | Minimum value within a range with criteria MINIF, MINIFS |

48 | kth largest value LARGE Example: for 3rd largest value in a range A1:A10 use this formula =LARGE(A1:A10,3) |

49 | kth smallest value SMALL Example: for 3rd smallst value in a range A1:A10 use this formula =SMALL(A1:A10,3) |

50 | Looking up values in columns of table vertically VLOOKUP Example: =VLOOKUP(A1, B1:D10,2,FALSE) |

51 | Looking up values in rows of table horizontally HLOOKUP Example: =HLOOKUP(A1, B1:D10,2,FALSE) |

52 | Looking value horizontally or vertically INDEX, MATCH Example: =INDEX(B1:E8,MATCH(B10,B1:B8,0),MATCH(B11,B1:E1,0)) |

53 | Lookup the value from a table if matches certain criteria IF, VLOOKUP Example: =IF(A1>10,VLOOKUP(A1, B1:D10,2,FALSE),0) |

54 | Lookup the value from a table if doesn't matches certain criteria IF, VLOOKUP, NOT Example: =IF(NOT(A1>10),VLOOKUP(A1, B1:D10,2,FALSE),0) |

55 | Lookup the value from a table if matches multiple criteria at same time IF, VLOOKUP, AND Example: =IF(AND(A1>10,A1<55),VLOOKUP(A1, B1:D10,2,FALSE),0) |

56 | Lookup the value from a table if matches atleast one of the multiple criteria IF, VLOOKUP, OR Example: =IF(OR(A1>10,A1<55),VLOOKUP(A1, B1:D10,2,FALSE),0) |

57 | Lookup the value from a table if matches atleast one of the multiple criteria but not all at same time IF, VLOOKUP, XOR Example: =IF(XOR(A1>10,A1<55),VLOOKUP(A1, B1:D10,2,FALSE),0) |

58 | Lookup the value from a table if it contains part of any text as value VLOOKUP Example: =VLOOKUP(“*excel*”, B1:D10,2,FALSE) |

59 | Lookup the kth smallest value from a table array VLOOKUP, SMALL Example: =VLOOKUP(SMALL(C1:C10,3), B1:D10,2,FALSE) |

60 | Lookup the kth largest value from a table array VLOOKUP, LARGE Example: =VLOOKUP(LARGE(C1:C10,3), B1:D10,2,FALSE) |

61 | Lookup the smallest value from a table array VLOOKUP, MIN Example: =VLOOKUP(MIN(C1:C10), B1:D10,2,FALSE) |

62 | Lookup the largest value from a table array VLOOKUP, MAX Example: =VLOOKUP(MAX(C1:C10), B1:D10,2,FALSE) |

63 | Making a date from three different dates by taking year, month, day from each of DATE, YEAR, MONTH, DAY Example: =DATE(YEAR(A1),MONTH(B1),DAY(C1)) |

64 | Return a text length LEN Example: =LEN(“Microsoft Excel”) Returnes =15 |

65 | Extract characters from start of text LEFT Example: =LEFT(“Microsoft Excel”, 3) Returnes “Mic” |

66 | Extract characters from end of text RIGHT Example: =RIGHT(“Microsoft Excel”, 3) Returnes “cel” |

67 | Extract characters from middle of text MID Example: =MID(“Microsoft Excel”, 3,2) Returnes “cr” |

68 | If 3rd character of two text are same then return a value IF, MID, EXACT Example: =IF(EXACT(MID(A1,3,1),MID(B1,3,1)),”Similar 3rd”,”not similar”) |

69 | Extract PAN no from GST no (India) MID Example: If you are having GST no in A1 cell then use this formula =MID(A1, 3,10) |

70 | When having only first name and last name in a full name then extract first name LEFT, FIND Example: =LEFT(A1,FIND(” “,A1)-1) |

71 | When having only first name and last name in a full name then extract last name RIGHT, LEN, FIND Example: =RIGHT(A1,LEN(A1)-FIND(” “,A1)) |

72 | Generic formula to separate words from text TRIM, MID, SUBSTITUTE, REPT, LEN Example: =TRIM(MID(SUBSTITUTE($A2,” “,REPT(” “,LEN($A2))),(C$1-1)*LEN($A2)+1,LEN($A2))) Where A2 contains the text and C1 is the nth word in the text |

73 | Last date of current month EOMONTH, TODAY Example: =EOMONTH(TODAY(),0) |

74 | Return a value when a date is Sunday IF, WEEKDAY Example: =IF(WEEKDAY(A1)=1, “Sunday no work”, “Working Day”) |

75 | Calculate your age in years, months, days DATEDIF, TODAY Example if you have date of birth in A1 =DATEDIF(A1, TODAY(), “y”)& ” Years “& DATEDIF(A1, TODAY(), “ym”)&” Months “& DATEDIF(A1, TODAY(), “md”)& ” Days” |

76 | Calculate your age in fraction of years YEARFRAC, TODAY Example if you have date of birth in A1 =YEARFRAC(A1,TODAY(),1) |

77 | Get difference of a date from today TODAY, DAYS, ABS Example: =ABS(DAYS(TODAY(),”23/09/2018″)) |

78 | Get workday after numbers of workdays from today WORKDAY, TODAY Example: =WORKDAY(TODAY(),56) |

79 | Get workday after numbers of workdays from today, modifying weekend holiday WORKDAY.INTL, TODAY Example: =WORKDAY.INTL(TODAY(),56,”0000010″) It makes offday on Friday |

80 | Get net workdays between today and another date NETWORKDAY, TODAY Example: =NETWORKDAYS(TODAY(),”11/11/2018″) |

81 | Get net workdays between today and another date, modifying weekend holiday NETWORKDAY.INTL, TODAY Example: =NETWORKDAYS.INTL(TODAY(),”11/11/2018″,”0000010″) It makes offday on Friday |

82 | Show a date as day of week TEXT Example: Suppose you are having date value in A1 cell =TEXT(A1,”dddd”) |

83 | Show date as long format date TEXT Example: Suppose you are having date value in A1 cell =TEXT(A1,”dd-mmmm-yyyy, dddd”) |

84 | Count column numbers of an array COLUMNS Example: =COLUMNS(A1:D10) |

85 | Count row numbers of an array ROWS Example: =ROWS(A1:D10) |

86 | Total numbers of cells in an array COLUMNS, ROWS Example: =COLUMNS(A1:D10)*ROWS(A1:D10) |

87 | Check whether multiple texts are same EXACT, AND Example: =AND(EXACT(A1,B1),EXACT(A1,C1),EXACT(A1,D1)) |

88 | Join name and surname in one text and make it in proper case CONCATENATE, PROPER Example: =PROPER(CONCATENATE(A1&” “&B1)) |

89 | Create random date between two dates RANDBETWEEN Example: =RANDBETWEEN(“11/6/2018”,TODAY()) |

90 | Create random number between two numbers RANDBETWEEN Example: =RANDBETWEEN(23,32) |

91 | Random number from fixed set of options CHOOSE, RANDBETWEEN, ROUND Example: =CHOOSE(ROUND(RANDBETWEEN(1,5),0),34,78,12,93,19) |

92 | Random text values from fixed set of options CHOOSE, RANDBETWEEN, ROUND Example: =CHOOSE(ROUND(RANDBETWEEN(1,5),0),”a”,”b”,”c”,”d”,”e”) |

93 | Remove numbers of characters text from end LEFT, LEN Example: If you need to remove 5 characters from a text from cell A1 =LEFT(A1, (LEN(A1)-5)) |

94 | Remove numbers of characters text from start RIGHT, LEN Example: If you need to remove 5 characters from a text from cell A1 =RIGHT(A1, (LEN(A1)-5)) |

95 | Finding exact middle character(s) of text MID, LEN, MOD, IF Example: If you have ext in A1 cell =IF(MOD(LEN(A1),2)=1, MID(A1, ((LEN(A1)-1)/2+1), 1), MID(A1, LEN(A1)/2, 2)) This formula returns one character if the text length is odd and two if it is even. |

96 | Rounddown a number in the multiple of 5 FLOOR Example: =FLOOR(567,5) |

97 | Roundup a number in the multiple of 5 CEILING Example: =CEILING(567,5) |

98 | Square root of number SQRT Example: =SQRT(64) |

99 | Square root of number POWER Example: =POWER(64,0.5) |

100 | Cube root of number POWER Example: =POWER(27,(1/3)) |

101 | Square of a number POWER Example: =POWER(20,2) |

102 | Cube of number POWER Example: =POWER(2,3) |

103 | Return a value when square root is a whole number SQRT, MOD, IF Example: =IF(MOD(SQRT(A1),1)=0,”Square”,”NA”) |

104 | Finding the closest value in an array from higher side MIN, IF Example: =MIN(IF(A1:A10>24,A1:A10)) Then Press Ctrl Shift Enter |

105 | Finding the closest value in an array from lower side MAX, IF Example: =MAX(IF(A1:A10<24,A1:A10)) Then Press Ctrl Shift Enter |