Cellwise Try it free

Excel guide · 3 min read

Excel formulas cheat sheet: the 25 everyone should know

Quick answerThese 25 formulas cover most everyday Excel work: SUM, AVERAGE, MIN/MAX, COUNT, COUNTA and ROUND for math; IF, AND, OR, IFS and IFERROR for logic; COUNTIF, COUNTIFS, SUMIF and SUMIFS for conditional totals; XLOOKUP, VLOOKUP and INDEX/MATCH for lookups; TRIM, LEFT/RIGHT/MID and TEXTJOIN for text; and TODAY, DATEDIF, UNIQUE and FILTER for dates and lists.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABCD
1EmployeeDeptSalaryRating
2Priya ShahSales620004
3Marcus BellIT780003
4Elena RuizSales550005
5Tom FischerFinance690004
6Aisha KhanIT835005
7Jonah PriceSales480002

Math and counting (1-6)

Six functions handle most totals and counts. Each takes a range such as C2:C7.

  1. =SUM(C2:C7) adds up a range.
  2. =AVERAGE(C2:C7) gives the mean.
  3. =MIN(C2:C7) and =MAX(C2:C7) return the smallest and largest value.
  4. =COUNT(C2:C7) counts cells that hold numbers.
  5. =COUNTA(A2:A7) counts cells that are not empty, including text.
  6. =ROUND(number, digits) rounds the actual value. Number formatting only changes what you see; ROUND changes what later formulas use.
=ROUND(AVERAGE(C2:C7),0)

Result: 65917

Formulas nest, so you can round an average in one step. The average salary is 65,916.67, rounded to 65,917.

Logic (7-11)

Logic functions let a sheet make decisions: flag a row, pick a rate, or replace an error with something readable.

  1. =IF(test, if_true, if_false) picks between two results.
  2. =AND(test1, test2, ...) is TRUE only when every test is true.
  3. =OR(test1, test2, ...) is TRUE when any test is true.
  4. =IFS(test1, value1, test2, value2, ...) handles several bands without nesting (Excel 2019 and later).
  5. =IFERROR(formula, fallback) shows a fallback instead of an error.
=IF(AND(D2>=4,C2<70000),"Raise review","-")

Result: Raise review

IF and AND together flag strong performers paid below 70,000. Priya has a rating of 4 and a salary of 62,000, so she is flagged for a raise review.

Conditional counts and sums (12-15)

These answer questions like "how many Sales staff" or "total salary in IT". The S versions take several conditions.

  1. =COUNTIF(range, criteria) counts matching cells, such as =COUNTIF(B2:B7,"Sales").
  2. =COUNTIFS(range1, criteria1, range2, criteria2, ...) counts rows that match every condition.
  3. =SUMIF(range, criteria, sum_range) adds the values on matching rows.
  4. =SUMIFS(sum_range, range1, criteria1, ...) adds with several conditions. Note that the sum range comes first here, unlike SUMIF.
=SUMIFS(C2:C7,B2:B7,"Sales",D2:D7,">=4")

Result: 117000

Salaries of Sales staff rated 4 or above: Priya and Elena, 117,000 in total.

Lookups (16-18)

Lookups pull a value from another row or table, such as a price for a product code or a salary for a name.

  1. =XLOOKUP(value, lookup_range, return_range, if_not_found) finds a value and returns a match from any column (Excel 365 and 2021).
  2. =VLOOKUP(value, table, col_num, FALSE) is the classic version. It only looks to the right and needs FALSE for an exact match.
  3. =INDEX(return_range, MATCH(value, lookup_range, 0)) works in every version and in any direction.
=XLOOKUP("Aisha Khan",A2:A7,C2:C7,"Not found")

Result: 83500

XLOOKUP finds Aisha Khan and returns her salary: 83,500.

Text (19-21)

Text functions clean up and reshape names, codes and imported data.

  1. =TRIM(text) removes extra spaces, the most common reason a lookup fails on data that looks identical.
  2. =LEFT(text, n), =RIGHT(text, n) and =MID(text, start, n) pull characters from the start, end or middle. Pair them with FIND to split at a space or dash.
  3. =TEXTJOIN(", ", TRUE, range) joins cells with a separator and can skip blanks. CONCAT and the & operator join without a separator.
=LEFT(A2,FIND(" ",A2)-1)

Result: Priya

FIND locates the space in "Priya Shah", and LEFT returns everything before it: Priya.

Dates and dynamic arrays (22-25)

The last four cover dates and the newer functions that return whole lists at once.

  1. =TODAY() returns the current date and updates every day.
  2. =DATEDIF(start, end, "y") gives whole years between two dates, useful for age or tenure. Use "m" for months or "d" for days.
  3. =UNIQUE(range) lists each value once.
  4. =FILTER(range, condition) returns every row that matches. UNIQUE and FILTER spill into the cells below and need Excel 365 or 2021.
=FILTER(A2:A7,B2:B7="Sales")

Result: spills 3 rows × 1 column

Priya Shah
Elena Ruiz
Jonah Price

FILTER spills the three Sales employees: Priya Shah, Elena Ruiz and Jonah Price.

Common mistakes

Forgetting to lock ranges before copying

Use $ (press F4) on lookup tables and fixed cells, such as $A$2:$C$7, so they do not shift when you fill down.

Leaving out FALSE in VLOOKUP

Without it VLOOKUP does an approximate match and can quietly return the wrong row.

Typing numbers as text

Numbers with units or leading spaces are ignored by SUM and break lookups. Keep number cells pure and use number formats for units.

Using new functions in shared files

XLOOKUP, IFS, UNIQUE and FILTER show #NAME? in older Excel. Check what version your colleagues use.

FAQ

What are the most used Excel formulas?

SUM, AVERAGE, IF, COUNTIF, SUMIF and a lookup (XLOOKUP or VLOOKUP) cover most daily work. Learn those six first, then IFERROR and SUMIFS.

What is the difference between a formula and a function?

A formula is anything you type that starts with =. A function is a named, built-in calculation such as SUM or IF that you use inside a formula.

How do I see all formulas on a sheet?

Press Ctrl+` (the grave accent key, next to 1) or go to Formulas > Show Formulas. Press it again to go back to results.

Functions used

Related guides

Updated