Skip to content
Tutorials Center
My learning

Topics

Lesson 29 of 29 ยท Projects, Practice and Interviews

Google Sheets Cheat Sheet

A quick Google Sheets cheat sheet: keyboard shortcuts, the most useful formulas, reference rules and fixes for common errors, on one page.

This page is a quick reference for everything in the Google Sheets tutorial. It has no long explanations: use it when you remember that something exists but not its exact spelling. Every section links back to the lesson that teaches it.

Google Sheets cannot run on this page, so all formulas and shortcuts are read-only, and there are no stand-ins to run. Shortcuts shown are for Windows and ChromeOS; on Mac, replace Ctrl with Cmd. Shortcuts can change, so check Ctrl + / in your own sheet for the current list.

On this page

Shortcuts Move, select, edit, format.
Formulas The ones you use every week.
References A1, $A$1, A$1, $A1.
Error fixes What each # message means.
1 / 2

Keyboard shortcuts

Task Shortcut
Show all shortcuts Ctrl + /
Edit the active cell F2
Cycle reference type while editing F4
Undo / redo Ctrl + Z / Ctrl + Y
Copy / cut / paste Ctrl + C / X / V
Paste values only Ctrl + Shift + V
Fill down / fill right Ctrl + D / Ctrl + R
Jump to the edge of the data Ctrl + Arrow
Select the whole column / row Ctrl + Space / Shift + Space
Select all Ctrl + A
New line inside a cell Alt + Enter
Insert the current date Ctrl + ;
Insert the current time Ctrl + Shift + ;
Bold / italic Ctrl + B / Ctrl + I
Find / find and replace Ctrl + F / Ctrl + H
Insert a link Ctrl + K
Insert a comment Ctrl + Alt + M

See A Tour of the Interface and Entering and Editing Data.

Reference rules

Written Meaning
A1 Relative: moves when copied
$A$1 Absolute: never moves
$A1 Column locked, row moves
A$1 Row locked, column moves
A1:B5 A range
A:A A whole column
A2:A Column A from row 2 to the end
Sheet2!A1 A cell on another sheet
'My Sheet'!A1 Sheet names with spaces need quotes

More in Relative, Absolute and Mixed References.

Top formulas

Math and counting

TEXT
=SUM(A2:A)            =AVERAGE(A2:A)        =MIN(A2:A)       =MAX(A2:A)
=COUNT(A2:A)          counts numbers        =COUNTA(A2:A)    counts non-empty
=COUNTBLANK(A2:A)     =ROUND(A1, 2)         =ROUNDUP(A1, 0)  =ROUNDDOWN(A1, 0)
=SUMPRODUCT(A2:A5, B2:B5)

Text

TEXT
=LEFT(A1, 3)    =RIGHT(A1, 3)    =MID(A1, 2, 4)    =LEN(A1)
=TRIM(A1)       =UPPER(A1)       =LOWER(A1)        =PROPER(A1)
=A1&" "&B1      =TEXTJOIN(", ", TRUE, A1:A5)
=SUBSTITUTE(A1, "-", "")         =SPLIT(A1, ",")
=FIND("x", A1)  =REGEXEXTRACT(A1, "[0-9]+")

Logic

TEXT
=IF(A1>=50, "Pass", "Fail")
=IFS(A1>=90, "A", A1>=80, "B", TRUE, "C")
=SWITCH(A1, 1, "One", 2, "Two", "Other")
=AND(A1>0, B1>0)   =OR(A1>0, B1>0)   =NOT(A1>0)
=IFERROR(A1/B1, 0)   =IFNA(VLOOKUP(...), "Missing")

See IF, AND, OR and IFS and Working with Text.

Lookups

TEXT
=VLOOKUP(key, A2:C100, 2, FALSE)           exact match, column 2
=XLOOKUP(key, A2:A100, C2:C100, "Missing")
=INDEX(C2:C100, MATCH(key, A2:A100, 0))
=INDEX(B2:E5, MATCH(r, A2:A5, 0), MATCH(c, B1:E1, 0))

Always use exact match (FALSE or 0) unless you really want a banded lookup. See XLOOKUP, INDEX and MATCH.

Conditional aggregates

TEXT
=SUMIF(range, "West", sum_range)
=SUMIFS(sum_range, r1, "West", r2, ">=10")
=COUNTIF(range, ">100")     =COUNTIFS(r1, "A", r2, "B")
=AVERAGEIF(range, "West", avg_range)

Wildcards: * any text, ? one character, ~* a literal star. See SUMIF, COUNTIF, AVERAGEIF and Friends.

Dates

TEXT
=TODAY()   =NOW()   =DATE(2026, 3, 1)   =EDATE(A1, 1)   =EOMONTH(A1, 0)
=YEAR(A1)  =MONTH(A1)  =DAY(A1)  =WEEKDAY(A1)
=DATEDIF(A1, B1, "D")    days   ("M" months, "Y" years)
=NETWORKDAYS(A1, B1)     =TEXT(A1, "yyyy-mm-dd")

See Dates and Times.

Data tools

TEXT
=SORT(A2:C, 2, FALSE)                    =UNIQUE(A2:A)
=FILTER(A2:C, B2:B > 100)                =SORTN(A2:B, 5, 0, 2, FALSE)
=ARRAYFORMULA(A2:A * B2:B)               =MAP(A2:A, LAMBDA(x, x * 2))
=QUERY(A1:D, "select A, sum(D) where B = 'West' group by A order by sum(D) desc label sum(D) 'Total'", 1)
=IMPORTRANGE("spreadsheet_url", "Sheet1!A1:D")
=SPARKLINE(B2:G2)

See Sorting and Filtering Data, Array Formulas and ARRAYFORMULA, The QUERY Function and Importing Live Data.

Number format codes

Code Shows Example result for 1234.5
#,##0.00 Thousands separator and two decimals 1,234.50
0% (on 0.25) Percent 25%
$#,##0 Currency without decimals $1,235
yyyy-mm-dd ISO date 2026-03-01
dddd Weekday name Sunday

Details in Formatting Cells.

Error fixes

Error Look for Fix
#DIV/0! A zero or blank divisor =IF(B1=0, "", A1/B1)
#N/A Lookup key missing, extra spaces TRIM the key, add a fallback argument
#REF! A deleted row, column or sheet Re-enter the reference
#VALUE! Text where a number is expected Convert with VALUE or clean the data
#NAME? A misspelled function or name Check spelling and quotes around text
#NUM! An impossible number, such as SQRT(-1) Check the arguments
#ERROR! A syntax problem (missing bracket or comma) Read the formula parts
Circular dependency A formula refers to its own cell Move the result elsewhere

See Understanding Formula Errors.

Cheat sheet habits

  • Keep raw data, calculations and summaries apart.
  • Keep numbers as numbers and dates as dates.
  • Put assumptions (rates, thresholds) in labelled cells.
  • Use exact match for lookups and absolute references for copied ranges.
  • Test a formula on two or three rows before filling it down.
  • Use version history before big changes (Sharing, Comments and Version History).

Check your understanding

Quick check ยท 3 questions

of 3 answered You scored / 3 ยท perfect!

  1. 1. Which shortcut pastes only values, without formatting or formulas?

    Ctrl + Shift + V pastes values only.
  2. 2. Which reference locks the row but lets the column change?

    A$1 has a dollar sign before the row number only.
  3. 3. Which formula performs an exact-match XLOOKUP with a fallback?

    The fourth argument of XLOOKUP is the value shown when nothing matches, and the default match mode is exact.

Summary

  • Learn a handful of shortcuts: edit, fill down, paste values, jump to the edge.
  • Remember the four reference forms and use F4 to cycle them.
  • A short list of formulas covers most work: sums, text, logic, lookups, conditional totals, dates, FILTER and QUERY.
  • Match every error message to its usual cause before changing formulas.
  • Review the tutorial from the start at What Is Google Sheets? whenever you need a refresher.