Home › Excel formulas › Cheat sheet
Excel Formulas Cheat Sheet: 49 Must-Know Functions
These are the Excel functions people use most at work and are asked about in interviews, grouped by type. Each row shows the syntax and what it does. Click a function for a full example, then practise it in the live spreadsheet.
Math and Trig formulas
| Function | Syntax | What it does |
|---|---|---|
| SUM | =SUM(number1, [number2], ...) | Adds up all the numbers in a range — the most-used formula in Excel. |
| AVERAGE | =AVERAGE(number1, [number2], ...) | Finds the mean of a set of numbers. |
| ROUND | =ROUND(number, num_digits) | Rounds a number to a set number of decimal places. |
| SUMIF | =SUMIF(range, criteria, [sum_range]) | Adds up numbers, but only the ones that meet a condition. |
| SUMIFS | =SUMIFS(sum_range, criteria_range1, criteria1, ...) | Like SUMIF but can check multiple conditions at once. |
| AVERAGEIF | =AVERAGEIF(range, criteria, [average_range]) | Averages numbers that meet one condition. |
| SEQUENCE | =SEQUENCE(rows, [columns], [start], [step]) | Generates a list of sequential numbers in an array. |
| SUMPRODUCT | =SUMPRODUCT(array1, [array2], ...) | Multiplies matching items across ranges, then adds up the results. |
Statistical formulas
| Function | Syntax | What it does |
|---|---|---|
| COUNT | =COUNT(value1, [value2], ...) | Counts how many cells in a range contain numbers. |
| COUNTA | =COUNTA(value1, [value2], ...) | Counts how many cells in a range are not empty. |
| MAX | =MAX(number1, [number2], ...) | Returns the largest number in a set. |
| MIN | =MIN(number1, [number2], ...) | Returns the smallest number in a set. |
| COUNTIF | =COUNTIF(range, criteria) | Counts how many cells in a range match a condition. |
| COUNTIFS | =COUNTIFS(criteria_range1, criteria1, ...) | Like COUNTIF, but can check multiple conditions at once. |
| RANK | =RANK(number, ref, [order]) | Returns the rank of a number within a list of numbers. |
| LARGE | =LARGE(array, k) | Returns the k-th largest value in a set — e.g. the 2nd biggest sale. |
| MEDIAN | =MEDIAN(number1, [number2], ...) | Returns the middle value in a set of numbers. |
Logic formulas
| Function | Syntax | What it does |
|---|---|---|
| IF | =IF(logical_test, value_if_true, value_if_false) | Returns one value if a condition is true, another if false. |
| IFS | =IFS(logical_test1, value1, [logical_test2, value2], ...) | Checks multiple conditions in order and returns the value for the first one that's true. |
| AND | =AND(logical1, [logical2], ...) | Returns TRUE only if every condition given is true. |
| OR | =OR(logical1, [logical2], ...) | Returns TRUE if at least one condition given is true. |
| IFERROR | =IFERROR(value, value_if_error) | Runs a formula, but shows a fallback value instead of an error if it fails. |
Lookup and Reference formulas
| Function | Syntax | What it does |
|---|---|---|
| VLOOKUP | =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) | Looks up a value in the first column of a range and returns a value from another column, same row. |
| HLOOKUP | =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) | Like VLOOKUP but searches across the first row and pulls a value down a column. |
| XLOOKUP | =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found]) | The modern replacement for VLOOKUP — searches any column and returns from any other, no column counting needed. |
| INDEX | =INDEX(array, row_num, [column_num]) | Returns the value at a given row/column position within a range. |
| MATCH | =MATCH(lookup_value, lookup_array, [match_type]) | Finds the position of a value within a range — usually paired with INDEX. |
| FILTER | =FILTER(array, include, [if_empty]) | Returns only the rows of a range that match a condition. |
| SORT | =SORT(array, [sort_index], [sort_order]) | Sorts a range automatically as a formula, without changing the original data. |
| UNIQUE | =UNIQUE(array, [by_col], [exactly_once]) | Returns a list of distinct values from a range. |
| TRANSPOSE | =TRANSPOSE(array) | Flips a range from rows to columns or vice versa. |
| CHOOSE | =CHOOSE(index_num, value1, [value2], ...) | Picks one value from a list based on a position number. |
Text formulas
| Function | Syntax | What it does |
|---|---|---|
| LEFT | =LEFT(text, [num_chars]) | Pulls a set number of characters from the start of a text string. |
| RIGHT | =RIGHT(text, [num_chars]) | Pulls a set number of characters from the end of a text string. |
| MID | =MID(text, start_num, num_chars) | Pulls characters from the middle of a text string. |
| LEN | =LEN(text) | Counts how many characters are in a text string. |
| TRIM | =TRIM(text) | Removes extra spaces from text, leaving single spaces between words. |
| CONCAT | =CONCAT(text1, [text2], ...) | The modern version of CONCATENATE, also works across ranges. |
| TEXTJOIN | =TEXTJOIN(delimiter, ignore_empty, text1, ...) | Joins text together with a chosen separator between each piece. |
| SUBSTITUTE | =SUBSTITUTE(text, old_text, new_text, [instance_num]) | Replaces specific text within a string. |
| UPPER | =UPPER(text) | Converts text to all uppercase. |
| PROPER | =PROPER(text) | Capitalizes the first letter of each word. |
| FIND | =FIND(find_text, within_text, [start_num]) | Finds the position of text within another string (case-sensitive). |
| TEXT | =TEXT(value, format_text) | Converts a number into text, formatted the way you choose. |
Date and Time formulas
| Function | Syntax | What it does |
|---|---|---|
| TODAY | =TODAY() | Returns today's date, updated automatically. |
| DATEDIF | =DATEDIF(start_date, end_date, unit) | Calculates the difference between two dates in days, months, or years. |
| EOMONTH | =EOMONTH(start_date, months) | Returns the last day of the month, a set number of months away. |
| NETWORKDAYS | =NETWORKDAYS(start_date, end_date, [holidays]) | Counts working days between two dates, excluding weekends. |
Financial formulas
| Function | Syntax | What it does |
|---|---|---|
| PMT | =PMT(rate, nper, pv, [fv], [type]) | Calculates the fixed periodic payment for a loan. |
Turn the cheat sheet into skill
Reading is not enough. Solve graded questions in a real spreadsheet and see your answer checked instantly.
Practice these formulas → Excel exercisesFrequently asked questions
What are the most important Excel formulas to learn?
For most jobs: SUM, IF, COUNTIF, SUMIF, VLOOKUP or XLOOKUP, INDEX and MATCH, LEFT, RIGHT, MID, TRIM, TEXT and IFERROR. Together they cover calculations, lookups, text cleaning and error handling.
Should I learn VLOOKUP or XLOOKUP?
Learn both. XLOOKUP is newer and easier, but VLOOKUP is still everywhere in older files and in interviews. INDEX with MATCH is the third option that works in every version.
How long does it take to learn these Excel formulas?
With 20 to 30 minutes of daily practice, most people can use the core formulas on this sheet within two to three weeks. Practising in a live spreadsheet speeds it up.
Is this Excel cheat sheet free?
Yes. Reading it and practising each formula on Cellgrids is free, and you can start without signing up.