Monday, November 28, 2022
Excel Formulas list
50 Essential Excel Formulas
Complete reference guide with formulas, descriptions, and practical examples
Click image to view full size in new tab
Click image to view full size in new tab
Click image to view full size in new tab
Complete Excel Formulas Table
| # | Formula | Description | Example |
|---|---|---|---|
| 1 | RANDBETWEEN | Generates a random number between a specified range |
=RANDBETWEEN(1, 100)
|
| 2 | ABS | Gives the absolute value of a number |
=ABS(-5)
|
| 3 | AVERAGE | Calculates the average of given numbers |
=AVERAGE(10, 20, 30)
|
| 4 | COUNT | Counts the number of numeric values in a range |
=COUNT(A1:A10)
|
| 5 | CONCATENATE | Joins multiple text strings |
=CONCATENATE("Hello", " ", "World")
|
| 6 | DAYS360 | Calculates days between dates (360-day year) |
=DAYS360("01/01/2023", "31/12/2023")
|
| 7 | EVEN | Rounds up to nearest even number |
=EVEN(3)
|
| 8 | ODD | Rounds up to nearest odd number |
=ODD(4)
|
| 9 | SUM | Adds up given numbers |
=SUM(10, 20, 30)
|
| 10 | SUMIF | Adds numbers based on a condition |
=SUMIF(A1:A10, ">10")
|
| 11 | INT | Returns the integer part of a number |
=INT(5.7)
|
| 12 | RIGHT | Extracts characters from the right side |
=RIGHT("Excel", 2)
|
| 13 | LEN | Returns the length of a text string |
=LEN("Excel")
|
| 14 | LEFT | Extracts characters from the left side |
=LEFT("Excel", 2)
|
| 15 | LOWER | Converts text to lowercase |
=LOWER("EXCEL")
|
| 16 | UPPER | Converts text to uppercase |
=UPPER("excel")
|
| 17 | MAX | Returns maximum value |
=MAX(1, 2, 3)
|
| 18 | MINA | Returns minimum value including text |
=MINA(1, 2, "3", TRUE)
|
| 19 | MOD | Returns remainder of division |
=MOD(10, 3)
|
| 20 | MODE | Returns most frequent value |
=MODE(1, 2, 2, 3)
|
| 21 | POWER | Returns number raised to power |
=POWER(2, 3)
|
| 22 | PRODUCT | Multiplies given values |
=PRODUCT(2, 3, 4)
|
| 23 | SQRT | Returns square root of number |
=SQRT(16)
|
| 24 | LOG | Returns logarithm of number |
=LOG(100, 10)
|
| 25 | COUNTA | Counts non-empty cells |
=COUNTA(A1:A5)
|
| 26 | SUBTOTAL | Returns subtotal in range |
=SUBTOTAL(9, A1:A10)
|
| 27 | TRIM | Removes extra spaces |
=TRIM(" Excel ")
|
| 28 | COUNTBLANK | Counts empty cells |
=COUNTBLANK(A1:A10)
|
| 29 | CODE | Returns ASCII code |
=CODE("A")
|
| 30 | PMT | Calculates loan EMI |
=PMT(0.05/12, 60, -100000)
|
| 31 | ROUND | Rounds number to specified digits |
=ROUND(3.14159, 2)
|
| 32 | VLOOKUP | Searches vertically in table |
=VLOOKUP("A", A1:B10, 2, FALSE)
|
| 33 | TODAY | Returns current date |
=TODAY()
|
| 34 | NOW | Returns current date and time |
=NOW()
|
| 35 | IF | Conditional function |
=IF(A1>10, "Yes", "No")
|
| 36 | DATE | Creates a date from year, month, day |
=DATE(2023, 12, 31)
|
| 37 | TIME | Creates a time from hours, minutes, seconds |
=TIME(14, 30, 0)
|
| 38 | AND | Logical AND function |
=AND(A1>10, B1<20)
|
| 39 | OR | Logical OR function |
=OR(A1>10, B1<5)
|
| 40 | NOT | Logical NOT function |
=NOT(A1>10)
|
| 41 | COUNTIF | Counts cells meeting criteria |
=COUNTIF(A1:A10, ">10")
|
| 42 | AVERAGEIF | Averages cells meeting criteria |
=AVERAGEIF(A1:A10, ">10")
|
| 43 | TEXT | Formats number as text |
=TEXT(1234.56, "$#,##0.00")
|
| 44 | FIND | Finds text within text (case-sensitive) |
=FIND("e", "Excel")
|
| 45 | SEARCH | Finds text within text (not case-sensitive) |
=SEARCH("e", "Excel")
|
| 46 | MID | Extracts characters from middle of text |
=MID("Excel", 2, 3)
|
| 47 | NETWORKDAYS | Calculates workdays between dates |
=NETWORKDAYS("01/01/2023", "31/12/2023")
|
| 48 | YEAR | Extracts year from date |
=YEAR("01/01/2023")
|
| 49 | ROMAN | Converts number to Roman numerals |
=ROMAN(2024)
|
| 50 | HLOOKUP | Searches horizontally in a table |
=HLOOKUP("Feb", A1:D2, 2, FALSE)
|
Complete 50 Excel formulas with practical examples
50
Formulas
4
Categories
50
Examples