100+ Excel Formula Examples
| 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 |