Shortcuts & Productivity · Cheatsheet
Spreadsheet Formulas Cheatsheet
Use basic Excel and Google Sheets formulas for totals, conditions, text, percentages, and rounding. Check references and locale separators.
38 shortcuts 7 sections
No entry matches that filter.
-
Excel and Google SheetsThese basic formulas use English function names -
=SUM(A2:A10)Start a formula with =; this example adds a range -
Comma separatorsSome locales use semicolons between function arguments -
A2:A10A colon includes both ends of the cell range -
Example cellsReplace the cell references with your own data
-
=A2+B2Add two cell values -
=A2-B2Subtract B2 from A2 -
=A2*B2Multiply two cell values -
=A2/B2Divide A2 by B2; B2 must not be zero -
=(A2+B2)*C2Calculate the bracketed sum first -
=A2*$B$1Keep B1 fixed when you copy the formula
-
=SUM(B2:B10)Add the numeric values in the range -
=AVERAGE(B2:B10)Find the mean; ignore empty cells and text in the range -
=MIN(B2:B10)Find the smallest numeric value -
=MAX(B2:B10)Find the largest numeric value -
=COUNT(B2:B10)Count cells with numeric values -
=COUNTA(B2:B10)Count nonempty cells, including formulas that return empty text
-
=IF(B2>=50,"Pass","Review")Choose a result from one condition -
=COUNTIF(A2:A10,"Done")Count cells that match the text -
=SUMIF(A2:A10,"Food",B2:B10)Add B values where the corresponding A value matches -
=AND(B2>=0,B2<=100)Check whether both conditions are true -
=OR(A2="Yes",B2="Yes")Check whether at least one condition is true -
=IFERROR(A2/B2,"Check input")Replace any formula error; inspect the cause before use
-
=TRIM(A2)Remove extra ordinary spaces; nonbreaking spaces can remain -
=LEN(A2)Count characters, including spaces -
=LEFT(A2,3)Read the first three characters -
=RIGHT(A2,4)Read the last four characters -
=LOWER(A2)Convert text to lowercase -
=A2&" "&B2Join two values with a space
-
=B2/A2Find a part of a nonzero total; format the result as a percentage -
=A2*(1-B2)Apply a discount when B2 contains a rate such as 20% -
=ROUND(A2,2)Round to two decimal places -
=ROUNDUP(A2,0)Round away from zero to an integer -
=ROUNDDOWN(A2,0)Round toward zero to an integer