comprehensive Excel formula cheat sheet organized

1. Basic Excel Formulas

Formula Description
=SUM(A1:A10) Adds values in a range.
=AVERAGE(A1:A10) Calculates the average of a range.
=IF(A1>10, "Yes", "No") Logical IF statement.
=MIN(A1:A10) Finds the smallest value.
=MAX(A1:A10) Finds the largest value.
=ROUND(A1,2) Rounds a number to 2 decimal places.
=COUNT(A1:A10) Counts numeric entries in a range.
=COUNTA(A1:A10) Counts all non-blank cells.
=LEN(A1) Counts the number of characters in a cell.

2. Lookup & Reference

Formula Description
Looks up a value in the first column of a
=VLOOKUP(lookup_value, table_array,
col_index_num, [range_lookup]) range and returns a value in the same row
from another column.
=HLOOKUP(lookup_value, table_array, Similar to VLOOKUP but searches in a
row_index_num, [range_lookup]) row instead of a column.
=INDEX(array, row_num, [column_num])
Returns the value of a cell in a table
based on the row and column number.
=MATCH(lookup_value, lookup_array, Returns the relative position of a value in
[match_type]) a range.
=XLOOKUP(lookup_value, lookup_array,
return_array, [if_not_found], [match_mode],
A more versatile replacement for
[search_mode]) VLOOKUP.
=CHOOSE(index_num, value1, value2, ...)
Returns a value from a list based on its
position.
=OFFSET(reference, rows, cols, [height], Returns a range offset from a given
[width]) reference.

3. Text Functions

Formula Description
=CONCATENATE(A1, " ", B1) Joins text strings (use =CONCAT in newer Excel).
=TEXT(A1, "dd/mm/yyyy") Formats a number and converts it to text.
 Formula Description
=LEFT(A1, 5) Extracts the first 5 characters.
=RIGHT(A1, 5) Extracts the last 5 characters.
=MID(A1, 3, 4) Extracts 4 characters starting from the 3rd character.
=TRIM(A1) Removes extra spaces from text.
=UPPER(A1) Converts text to uppercase.
=LOWER(A1) Converts text to lowercase.
=PROPER(A1) Capitalizes the first letter of each word.
=FIND("word", A1) Returns the position of a substring in text (case-sensitive).
=SUBSTITUTE(A1, "old", "new") Replaces occurrences of a text string with another.

4. Logical Functions

Formula Description
=AND(A1>5, B1<10) Returns TRUE if all conditions are true.
=OR(A1>5, B1<10) Returns TRUE if any condition is true.
=NOT(A1>5) Reverses the logic of the argument.
=IFERROR(A1/B1, "Error") Returns a custom value if there's an error.

5. Date & Time Functions

Formula Description
=TODAY() Returns the current date.
=NOW() Returns the current date and time.
=DAY(A1) Extracts the day from a date.
=MONTH(A1) Extracts the month from a date.
=YEAR(A1) Extracts the year from a date.
=WEEKDAY(A1) Returns the day of the week as a number.
=NETWORKDAYS(A1, B1) Returns the number of working days between two dates.
=EDATE(A1, 3) Returns a date n months in the future or past.
=EOMONTH(A1, 1) Returns the last day of the month n months from the date.

6. Math Functions

Formula Description
=ABS(A1) Returns the absolute value.
=POWER(A1, 3) Returns a number raised to a power.
=SQRT(A1) Returns the square root.
=MOD(A1, B1) Returns the remainder after division.
=RAND() Generates a random number between 0 and 1.
 Formula Description
=RANDBETWEEN(1, 100) Generates a random integer between two values.
=PI() Returns the value of π.
=ROUNDUP(A1, 2) Rounds a number up.
=ROUNDDOWN(A1, 2) Rounds a number down.

7. Financial Functions

Formula Description
=PMT(rate, nper, pv) Calculates the payment for a loan.
=FV(rate, nper, pmt, [pv], [type]) Calculates the future value of an investment.
=PV(rate, nper, pmt) Calculates the present value of an investment.
=NPV(rate, value1, [value2], ...) Calculates the net present value of cash flows.
=IRR(values) Calculates the internal rate of return.

8. Error Handling

Formula Description
=IFERROR(A1/B1, "Error") Returns "Error" if there's an error.
=ISERROR(A1) Returns TRUE if there’s an error.
=ISNUMBER(A1) Checks if a cell contains a number.
=ISBLANK(A1) Checks if a cell is blank.