Top 100 Spreadsheet Formulas You Should Master in 2025
Category | Formula | Function |
Basic Math | =SUM(A1:A10) | Adds all numbers in the range |
=AVERAGE(A1:A10) | Calculates average | |
=MIN(A1:A10) | Returns the smallest number | |
=MAX(A1:A10) | Returns the largest number | |
=COUNT(A1:A10) | Counts numeric cells | |
=COUNTA(A1:A10) | Counts all non-empty cells | |
=ROUND(A1, 2) | Rounds a number to 2 decimal places | |
=ROUNDUP(A1, 2) | Rounds up to 2 decimal places | |
=ROUNDDOWN(A1, 2) | Rounds down to 2 decimal places | |
=PRODUCT(A1:A10) | Multiplies all numbers in the range | |
Lookup & Reference | =VLOOKUP(A2, B2:D10, 2, FALSE) | Finds a value in a table (vertical) |
=HLOOKUP(A1, A2:Z4, 2, FALSE) | Finds a value in a table (horizontal) | |
=INDEX(A2:C10, 3, 2) | Returns value at row 3, column 2 | |
=MATCH(42, A1:A100, 0) | Finds the position of a value | |
=XLOOKUP("Name", A1:A10, B1:B10) | Modern lookup for Excel | |
=LOOKUP(10, A1:A10, B1:B10) | Looks for a value | |
=FILTER(A2:B10, B2:B10="Active") | Filters rows based on condition (Sheets) | |
=CHOOSE(2, "Red", "Blue", "Green") | Picks the 2nd item from a list | |
=OFFSET(A1, 2, 1) | Returns a reference offset from A1 | |
=INDIRECT("A1") | Refers to a cell via text | |
Date & Time | =TODAY() | Current date |
=NOW() | Current date and time | |
=DAY(A1) | Extracts day from date | |
=MONTH(A1) | Extracts month | |
=YEAR(A1) | Extracts year | |
=WEEKDAY(A1) | Returns weekday number | |
=HOUR(A1) | Extracts hour | |
=MINUTE(A1) | Extracts minutes | |
=SECOND(A1) | Extracts seconds | |
=DATEDIF(A1, B1, "d") | Calculates date difference in days | |
=NETWORKDAYS(A1, B1) | Working days between two dates | |
=EDATE(A1, 2) | Returns date after 2 months | |
=EOMONTH(A1, 1) | End of next month | |
=WORKDAY(A1, 5) | Date after 5 working days | |
=TEXT(A1, "dd/mm/yyyy") | Formats date | |
Logical Functions | =IF(A1>50, "Pass", "Fail") | Returns condition result |
=IFERROR(A1/B1, "Error") | Catches error | |
=IFS(A1=1, "One", A1=2, "Two") | Multiple IF conditions | |
=AND(A1>1, B1<5) | Returns TRUE if both are TRUE | |
=OR(A1>1, B1<5) | Returns TRUE if any condition is TRUE | |
=NOT(A1=1) | Reverses logic | |
=SWITCH(A1, 1, "One", 2, "Two", "Other") | Replaces nested IFs | |
Text Functions | =CONCATENATE(A1, B1) | Joins texts |
=TEXTJOIN(" ", TRUE, A1:A3) | Joins texts with delimiter | |
=LEFT(A1, 5) | First 5 characters | |
=RIGHT(A1, 5) | Last 5 characters | |
=MID(A1, 2, 3) | Middle 3 characters starting at 2 | |
=LEN(A1) | Number of characters | |
=FIND("a", A1) | Finds position of character | |
=SEARCH("a", A1) | Like FIND but case-insensitive | |
=UPPER(A1) | Converts to uppercase | |
=LOWER(A1) | Converts to lowercase | |
=PROPER(A1) | Capitalizes first letter of each word | |
=REPLACE(A1, 1, 3, "New") | Replaces part of string | |
=SUBSTITUTE(A1, "old", "new") | Replaces all instances | |
=TRIM(A1) | Removes extra spaces | |
=TEXT(A1, "0.00") | Formats number | |
Math & Trig | =ABS(A1) | Absolute value |
=MOD(A1, 3) | Remainder of division | |
=POWER(A1, 2) | A1 squared | |
=SQRT(A1) | Square root | |
=CEILING(A1, 5) | Rounds up to nearest 5 | |
=FLOOR(A1, 5) | Rounds down to nearest 5 | |
=PI() | Returns Pi | |
=SIN(A1), =COS(A1), =TAN(A1) | Trigonometry | |
=RADIANS(A1), =DEGREES(A1) | Convert between radians and degrees | |
Statistics | =MEDIAN(A1:A10) | Middle number |
=MODE(A1:A10) | Most frequent number | |
=STDEV.P(A1:A10) | Standard deviation (population) | |
=STDEV.S(A1:A10) | Standard deviation (sample) | |
=VARP(A1:A10) | Variance (population) | |
=VAR.S(A1:A10) | Variance (sample) | |
=PERCENTILE(A1:A10, 0.9) | 90th percentile | |
=QUARTILE(A1:A10, 1) | 1st quartile | |
=RANK(A1, A1:A10) | Rank of value | |
=LARGE(A1:A10, 2) | 2nd largest | |
=SMALL(A1:A10, 2) | 2nd smallest | |
Array & Dynamic | =UNIQUE(A1:A10) | Removes duplicates |
=SORT(A1:A10) | Sorts range | |
=SORTBY(A1:B10, B1:B10) | Sorts by another column | |
=SEQUENCE(5) | Generates a series of numbers | |
=SPLIT(A1, ",") | Splits string by comma | |
=TRANSPOSE(A1:B2) | Flips rows and columns | |
=ARRAYFORMULA(A1:A5*2) | Applies a formula to a range (Sheets only) | |
=LET(x, A1+5, x*2) | Names values inside formulas (Excel only) | |
Web & External | =IMPORTDATA("url") | Imports CSV or TSV from URL (Sheets) |
=IMPORTRANGE("url", "Sheet1!A1:C10") | Imports data from another sheet | |
=IMPORTHTML("url", "table", 1) | Imports HTML table | |
=WEBSERVICE("url") | Gets web response (Excel) | |
=ENCODEURL(A1) | Converts text to URL-safe string | |
Finance | =PMT(0.05/12, 60, -10000) | Monthly payment |
=FV(0.05, 5, -1000, -5000) | Future value | |
=NPV(0.1, A2:A6) | Net present value | |
=IRR(A1:A6) | Internal rate of return | |
=RATE(60, -200, 10000) | Loan interest rate | |
=CUMIPMT(0.05/12, 60, 10000, 1, 12, 0) | Cumulative interest | |
=SLN(10000, 1000, 5) | Straight-line depreciation |
