Excel All Formulas - Quick Reference
1. Basic Formulas
=SUM(A1:A10) - Adds values in cells A1 to A10
=AVERAGE(A1:A10) - Returns the average
=MIN(A1:A10) - Returns the smallest number
=MAX(A1:A10) - Returns the largest number
=COUNT(A1:A10) - Counts numeric values
=COUNTA(A1:A10) - Counts all non-empty cells
=ROUND(A1, 2) - Rounds to 2 decimal places
=INT(A1) - Rounds down to nearest integer
=MOD(A1, 3) - Remainder of A1 divided by 3
2. Text Formulas
=LEFT(A1, 3) - Returns first 3 letters
=RIGHT(A1, 4) - Returns last 4 letters
=MID(A1, 2, 3) - Extracts characters from the middle
=LEN(A1) - Returns number of characters
=LOWER(A1) - Converts text to lowercase
=UPPER(A1) - Converts text to uppercase
=PROPER(A1) - Converts to Title Case
=TRIM(A1) - Removes extra spaces
=CONCAT(A1, B1) - Combines text
=TEXT(A1, "dd/mm/yyyy") - Formats numbers/dates as text
3. Logical Formulas
=IF(A1>10, "Yes", "No") - Returns Yes if A1>10, else No
=AND(A1>10, B1<5) - True if both are true
=OR(A1>10, B1<5) - True if either is true
=NOT(A1=10) - Returns TRUE if A1 is not 10
4. Lookup & Reference
=VLOOKUP(lookup_value, table, col_index, FALSE) - Vertical lookup
=HLOOKUP(lookup_value, table, row_index, FALSE) - Horizontal lookup
=INDEX(A1:C3, 2, 3) - Returns value at 2nd row, 3rd column
=MATCH(50, A1:A10, 0) - Returns position of 50 in the range
=XLOOKUP(lookup_value, lookup_array, return_array) - Modern version of VLOOKUP
=OFFSET(A1, 2, 1) - Returns value offset from A1 (2 rows down, 1 column right)
5. Date & Time
=TODAY() - Current date
Excel All Formulas - Quick Reference
=NOW() - Current date and time
=DAY(A1) - Extracts day from date
=MONTH(A1) - Extracts month
=YEAR(A1) - Extracts year
=DATEDIF(A1, B1, "d") - Difference in days
=NETWORKDAYS(A1, B1) - Working days between two dates
6. Financial
=PMT(rate, nper, pv) - Payment for a loan
=FV(rate, nper, pmt) - Future value
=NPV(rate, value1, [value2], ) - Net present value
=IRR(values) - Internal rate of return
7. Error Handling
=IFERROR(A1/B1, "Error") - Returns 'Error' if division fails
=ISERROR(A1) - Returns TRUE if error
=ISNA(A1) - TRUE if #N/A error
8. Array & Dynamic Formulas (New Excel Versions)
=FILTER(A1:A10, A1:A10>5) - Filters values greater than 5
=UNIQUE(A1:A10) - Extracts unique values
=SORT(A1:A10) - Sorts values
=SEQUENCE(5) - Generates 1 to 5 vertically
=TRANSPOSE(A1:A3) - Converts rows to columns or vice versa
