Some Unlimited Excel guide with 80 verious formulas for you
Ultimate Excel Guide
80 Most Used Excel Formulas
| # | Formula | Description |
|---|---|---|
| 1 | =SUM(A1:A10) | Adds a range of numbers |
| 2 | =AVERAGE(A1:A10) | Calculates average |
| 3 | =IF(A1>10,"Yes","No") | Conditional logic |
| 4 | =COUNT(A1:A10) | Counts numeric values |
| 5 | =COUNTA(A1:A10) | Counts non-empty cells |
| 6 | =IFERROR(A1/B1,"Error") | Error handling |
| 7 | =MAX(A1:A10) | Finds largest value |
| 8 | =MIN(A1:A10) | Finds smallest value |
| 9 | =LEN(A1) | Counts characters in cell |
| 10 | =TRIM(A1) | Removes extra spaces |
| 11 | =CONCAT(A1,B1) | Joins values |
| 12 | =TEXT(A1, "dd-mm-yyyy") | Formats a date |
| 13 | =VLOOKUP("Item", A2:C10, 2, FALSE) | Vertical lookup |
| 14 | =HLOOKUP("Jan", A1:D2, 2, FALSE) | Horizontal lookup |
| 15 | =INDEX(A2:C5, 2, 3) | Returns value at row/col |
| 16 | =MATCH(50, A1:A10, 0) | Finds position of value |
| 17 | =NOW() | Current date and time |
| 18 | =TODAY() | Current date |
| 19 | =LEFT(A1,5) | Leftmost characters |
| 20 | =RIGHT(A1,5) | Rightmost characters |
| 21 | =PROPER(A1) | Capitalizes each word |
| 22 | =LOWER(A1) | Converts to lowercase |
| 23 | =UPPER(A1) | Converts to uppercase |
| 24 | =REPLACE(A1,1,5,"Hello") | Replaces part of text |
| 25 | =SUBSTITUTE(A1,"old","new") | Substitutes text |
| 26 | =ROUND(A1, 2) | Rounds a number |
| 27 | =ROUNDUP(A1, 2) | Rounds up |
| 28 | =ROUNDDOWN(A1, 2) | Rounds down |
| 29 | =MOD(A1,2) | Remainder after division |
| 30 | =INT(A1) | Rounds down to integer |
| 31 | =ABS(A1) | Absolute value |
| 32 | =POWER(A1,2) | Exponentiation |
| 33 | =SQRT(A1) | Square root |
| 34 | =AND(A1>1,B1<5) | Logical AND |
| 35 | =OR(A1>1,B1<5) | Logical OR |
| 36 | =NOT(A1=1) | Logical NOT |
| 37 | =ISNUMBER(A1) | Checks for number |
| 38 | =ISTEXT(A1) | Checks for text |
| 39 | =ISBLANK(A1) | Checks if blank |
| 40 | =IFNA(VLOOKUP(A1,B2:C10,2,FALSE),"NA") | Handles N/A |
| 41 | =CHOOSE(2,"Red","Blue","Green") | Selects value |
| 42 | =TEXTJOIN(", ", TRUE, A1:A3) | Joins with delimiter |
| 43 | =FILTER(A2:A10, B2:B10>5) | Filters by condition |
| 44 | =UNIQUE(A1:A10) | Extracts unique values |
| 45 | =SORT(A1:A10) | Sorts data |
| 46 | =SEQUENCE(5) | Generates a sequence |
| 47 | =RAND() | Random number 0-1 |
| 48 | =RANDBETWEEN(1,100) | Random number between range |
| 49 | =DATE(2023,12,25) | Creates a date |
| 50 | =YEAR(A1) | Extracts year |
| 51 | =MONTH(A1) | Extracts month |
| 52 | =DAY(A1) | Extracts day |
| 53 | =WEEKDAY(A1) | Day of week |
| 54 | =WORKDAY(A1,5) | Future workday |
| 55 | =NETWORKDAYS(A1,B1) | Workdays between |
| 56 | =DATEDIF(A1,B1,"D") | Difference in days |
| 57 | =EDATE(A1,1) | Next month |
| 58 | =EOMONTH(A1,1) | End of month |
| 59 | =CELL("address",A1) | Returns cell address |
| 60 | =INFO("osversion") | Returns system info |
| 61 | =GETPIVOTDATA("Sales",A3) | Extracts from Pivot |
| 62 | =COLUMNS(A1:C1) | Counts columns |
| 63 | =ROWS(A1:A10) | Counts rows |
| 64 | =COLUMN(A1) | Returns column number |
| 65 | =ROW(A1) | Returns row number |
| 66 | =TRANSPOSE(A1:A5) | Transposes range |
| 67 | =FORMULATEXT(A1) | Displays formula |
| 68 | =HYPERLINK("url","text") | Creates hyperlink |
| 69 | =SPARKLINE(A1:A10) | Mini chart |
| 70 | =XLOOKUP(A1,A2:A10,B2:B10) | Flexible lookup |
| 71 | =IFS(A1>10,"High",A1>5,"Medium",TRUE,"Low") | Multiple IFs |
| 72 | =SWITCH(A1,1,"One",2,"Two",3,"Three") | Replaces nested IF |
| 73 | =LET(x,A1,x+5) | Names and reuses |
| 74 | =LAMBDA(x, x+1)(5) | Custom function |
| 75 | =MAP(A1:A3,LAMBDA(x,x*2)) | Array transform |
| 76 | =REDUCE(0,A1:A3,LAMBDA(a,b,a+b)) | Aggregate array |
| 77 | =SCAN(0,A1:A3,LAMBDA(a,b,a+b)) | Running total |
| 78 | =RANDARRAY(3,1) | Random array |
| 79 | =TEXTSPLIT("a,b,c",",") | Splits text |
| 80 | =TOCOL(A1:C3) | Converts range to column |
Shiben
As you came here, so you must go. So don't delay, do the main thing, many real genes will know or understand.
