Excel and Sheets cheat sheet
Formulas work the same in Excel and Google Sheets unless noted.
Math and counting
=SUM(B2:B10)- Add a range
=AVERAGE(B2:B10)- Average
=MIN(B2:B10) =MAX(B2:B10)- Smallest and largest
=COUNT(B2:B10)- Count numbers
=COUNTA(A2:A10)- Count non-empty cells
=ROUND(B2, 2)- Round to 2 decimals
Conditions
=IF(B2>=50, "Pass", "Fail")- Choose between two results
=IF(AND(A2>0, B2>0), "ok", "no")- Both conditions
=IFERROR(B2/C2, 0)- Replace an error with a value
=COUNTIF(A:A, "Paid")- Count matching cells
=SUMIF(A:A, "Paid", C:C)- Add where another column matches
=SUMIFS(C:C, A:A, "Paid", B:B, ">100")- Add with several conditions
Lookups
=VLOOKUP(A2, Data!A:C, 3, FALSE)- Exact match, column 3 (FALSE matters)
=XLOOKUP(A2, Data!A:A, Data!C:C, "none")- Modern lookup with a not-found value
=INDEX(C:C, MATCH(A2, A:A, 0))- Works in every version, any direction
Text
=LEFT(A2, 3) =RIGHT(A2, 3) =MID(A2, 2, 4)- Parts of text
=LEN(A2) =TRIM(A2)- Length; remove extra spaces
=UPPER(A2) =LOWER(A2) =PROPER(A2)- Change case
=A2&" "&B2- Join text
=SUBSTITUTE(A2, "-", "")- Replace text
=TEXTJOIN(", ", TRUE, A2:A6)- Join a range with a separator
Dates
=TODAY() =NOW()- Current date; date and time
=B2-A2- Days between two dates
=EDATE(A2, 3)- Same day, 3 months later
=EOMONTH(A2, 0)- Last day of the month
=NETWORKDAYS(A2, B2)- Working days between dates
=TEXT(A2, "yyyy-mm-dd")- Format a date as text
Shortcuts
F4- Cycle $ locks on a reference
Ctrl+Shift+Enter- Enter an array formula (older Excel)
Ctrl+;- Insert today's date
Ctrl+Arrow- Jump to the edge of the data
Alt+=- AutoSum (Excel)
Learn it properly