Productivity

google-sheetsspreadsheetgooglecollaborationcloud

Google Sheets Cheat Sheet

Keyboard Shortcuts

Ctrl+Home - Move to beginning of sheet
Ctrl+End - Move to end of data
Ctrl+Arrow Keys - Jump to edge of data region
Alt+Down/Up - Move to bottom/top of current column
Alt+Right/Left - Move to right/left edge of current row
Ctrl+Backspace - Scroll to active cell

Selection

Ctrl+Space - Select entire column
Shift+Space - Select entire row
Ctrl+A - Select all cells
Ctrl+Shift+Arrow - Extend selection to edge of data
Shift+Click - Select range

Editing

F2 or Enter - Edit active cell
Ctrl+D - Fill down
Ctrl+R - Fill right
Ctrl+; - Insert current date
Ctrl+Shift+; - Insert current time
Ctrl+Alt+; - Insert time stamp
Alt+Enter - New line in cell
Ctrl+K - Insert link
Ctrl+Alt+Shift+H - Open edit history

Formatting

Ctrl+B - Bold
Ctrl+I - Italic
Ctrl+U - Underline
Alt+Shift+5 - Strikethrough
Ctrl+Shift+7 - Add border
*Ctrl+* - Clear formatting
Ctrl+Alt+1-6 - Apply heading styles

Formulas

Alt+Shift+= - Insert SUM formula
Ctrl+~ - Show/hide formulas
F4 - Absolute/relative reference (in formula)
Ctrl+Shift+Enter - Array formula
Shift+F9 - Calculate sheet

Data & Filtering

Ctrl+Alt+Shift+1 - Create filter
Alt+Down - Open filter dropdown (on header)
Ctrl+Alt+Shift+2 - Create pivot table
Ctrl+Alt+G - Group rows/columns
Alt+Shift+Right - Group
Alt+Shift+Left - Ungroup

Sheets Management

Shift+F11 - Insert new sheet
Alt+Down - Next sheet
Alt+Up - Previous sheet
Fn+F10 - Open sheet menu
Ctrl+Alt+Shift+K - Open explore

Comments & Notes

Ctrl+Alt+M - Insert comment
Ctrl+Alt+Shift+A - Open comment thread
Ctrl+Enter - Post comment (in comment box)
Shift+F2 - Insert/edit note

General

Ctrl+/ - Show keyboard shortcuts
Ctrl+Alt+Shift+G - Find and replace
Alt+/ - Search menus
Ctrl+P - Print
Ctrl+O - Open file
Ctrl+Shift+C - Column format
Ctrl+Shift+R - Row format

Google Sheets Exclusive Functions

Import Functions

=IMPORTRANGE("spreadsheet_url", "Sheet1!A1:C10")
=IMPORTDATA("https://example.com/data.csv")
=IMPORTXML("https://example.com", "//div[@class='price']")
=IMPORTHTML("https://example.com", "table", 1)
=IMPORTFEED("https://example.com/feed.xml")

Google Services Integration

=GOOGLETRANSLATE(A1, "en", "es")
=GOOGLEFINANCE("NASDAQ:GOOGL", "price")
=IMAGE("https://example.com/image.png")
=SPARKLINE(A1:A10)
=HYPERLINK("https://google.com", "Google")

Array & Query Functions

=QUERY(A1:C10, "SELECT A, SUM(C) WHERE B='Yes' GROUP BY A")
=ARRAYFORMULA(A1:A10*B1:B10)
=FILTER(A1:C10, B1:B10>100, C1:C10="Active")
=UNIQUE(A1:A100)
=SORT(A1:C10, 2, FALSE)
=SORTN(A1:C10, 5, 0, 2, FALSE)

Special Functions

=DETECTLANGUAGE(A1)
=ISURL(A1)
=REGEXMATCH(A1, "pattern")
=REGEXEXTRACT(A1, "pattern")
=REGEXREPLACE(A1, "pattern", "replacement")
=TO_DATE(A1)
=TO_DOLLARS(A1)
=TO_PERCENT(A1)
=TO_TEXT(A1)

Essential Formulas

Lookup Functions

=VLOOKUP(A2, Sheet2!A:C, 3, FALSE)
=HLOOKUP(A2, Sheet2!1:3, 3, FALSE)
=INDEX(A1:C10, 5, 2)
=MATCH("Apple", A1:A10, 0)
=XLOOKUP(A2, A:A, B:B)  // Available in some accounts
=LOOKUP(A1, A:A, B:B)

Conditional Functions

=IF(A1>100, "High", "Low")
=IFS(A1>90, "A", A1>80, "B", A1>70, "C", TRUE, "F")
=SWITCH(A1, "Red", 1, "Blue", 2, "Green", 3, 0)
=AND(A1>50, B1<100)
=OR(A1="Yes", B1="Yes")
=NOT(A1="")
=IFERROR(A1/B1, 0)
=IFNA(VLOOKUP(A1, B:C, 2, 0), "Not Found")

Text Functions

=CONCATENATE(A1, " ", B1)
=JOIN(", ", A1:A10)
=TEXTJOIN(", ", TRUE, A1:A10)
=SPLIT(A1, " ")
=LEFT(A1, 5)
=RIGHT(A1, 3)
=MID(A1, 2, 4)
=LEN(A1)
=TRIM(A1)
=UPPER(A1)
=LOWER(A1)
=PROPER(A1)
=SUBSTITUTE(A1, "old", "new")
=FIND("text", A1)
=SEARCH("text", A1)
=REPLACE(A1, 1, 3, "new")

Math Functions

=SUM(A1:A10)
=SUMIF(A1:A10, ">100")
=SUMIFS(C1:C10, A1:A10, "Yes", B1:B10, ">50")
=AVERAGE(A1:A10)
=AVERAGEIF(A1:A10, ">100")
=COUNT(A1:A10)
=COUNTA(A1:A10)
=COUNTBLANK(A1:A10)
=COUNTIF(A1:A10, ">100")
=COUNTIFS(A1:A10, "Yes", B1:B10, ">50")
=COUNTUNIQUE(A1:A10)
=MAX(A1:A10)
=MIN(A1:A10)
=ROUND(A1, 2)
=ROUNDUP(A1, 2)
=ROUNDDOWN(A1, 2)
=MROUND(A1, 5)
=CEILING(A1)
=FLOOR(A1)
=ABS(A1)
=RAND()
=RANDBETWEEN(1, 100)

Date & Time

=TODAY()
=NOW()
=DATE(2024, 12, 25)
=YEAR(A1)
=MONTH(A1)
=DAY(A1)
=WEEKDAY(A1)
=WEEKNUM(A1)
=EOMONTH(A1, 0)
=EDATE(A1, 3)
=NETWORKDAYS(A1, B1)
=WORKDAY(A1, 10)
=DATEDIF(A1, B1, "D")
=TEXT(A1, "MMMM DD, YYYY")
=TIMEVALUE("14:30:00")

QUERY Function Deep Dive

The QUERY function uses Google Visualization API Query Language (similar to SQL).

Basic Syntax

=QUERY(data, query, [headers])

Common Queries

// Select specific columns
=QUERY(A1:D10, "SELECT A, C, D")

// Where clause
=QUERY(A1:D10, "SELECT * WHERE B > 100")

// Multiple conditions
=QUERY(A1:D10, "SELECT * WHERE B > 100 AND C = 'Active'")

// Group by with aggregation
=QUERY(A1:D10, "SELECT A, SUM(D) GROUP BY A")

// Order by
=QUERY(A1:D10, "SELECT * ORDER BY B DESC")

// Limit results
=QUERY(A1:D10, "SELECT * LIMIT 5")

// Label columns
=QUERY(A1:D10, "SELECT A, SUM(D) GROUP BY A LABEL SUM(D) 'Total'")

// Date filtering
=QUERY(A1:D10, "SELECT * WHERE A > DATE '2024-01-01'")

// String matching
=QUERY(A1:D10, "SELECT * WHERE B CONTAINS 'text'")
=QUERY(A1:D10, "SELECT * WHERE B STARTS WITH 'A'")
=QUERY(A1:D10, "SELECT * WHERE B MATCHES '.*pattern.*'")

Query Operators

Operator	Description
AND	Logical AND
OR	Logical OR
NOT	Logical NOT
=	Equal to
!= or <>	Not equal to
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
CONTAINS	String contains
STARTS WITH	String starts with
ENDS WITH	String ends with
MATCHES	Regex match
LIKE	Pattern matching

Aggregate Functions in QUERY

=QUERY(A1:D10, "SELECT A, AVG(D), MAX(D), MIN(D), COUNT(D) GROUP BY A")

SUM - Sum of values
AVG - Average
COUNT - Count of values
MAX - Maximum value
MIN - Minimum value

Array Formulas

ARRAYFORMULA Basics

// Apply formula to entire range
=ARRAYFORMULA(A2:A*B2:B)

// Conditional array formula
=ARRAYFORMULA(IF(A2:A>100, "High", "Low"))

// Skip header row
=ARRAYFORMULA(IF(ROW(A:A)=1, "Header", A:A*2))

// Array with multiple conditions
=ARRAYFORMULA(IF(LEN(A2:A), B2:B*C2:C, ""))

Combining Array Functions

// Filter, then sort
=SORT(FILTER(A2:C100, B2:B100>100), 2, FALSE)

// Unique values from filtered data
=UNIQUE(FILTER(A2:A100, B2:B100="Active"))

// Sum filtered results
=SUM(FILTER(C2:C100, A2:A100="Category1"))

Google Apps Script Integration

Custom Functions

// In Script Editor (Extensions → Apps Script)
function DOUBLE(input) {
  return input * 2;
}

function CUSTOMCONCAT(text1, text2) {
  return text1 + " - " + text2;
}

// Use in sheet
=DOUBLE(A1)
=CUSTOMCONCAT(A1, B1)

Common Scripts

// Auto-email on form submit
function sendEmail(e) {
  MailApp.sendEmail({
    to: "email@example.com",
    subject: "New Form Response",
    body: "Response: " + e.namedValues
  });
}

// Create timestamp
function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  var cell = sheet.getActiveCell();

  if (cell.getColumn() == 1 && cell.getValue()) {
    sheet.getRange(cell.getRow(), 2).setValue(new Date());
  }
}

Collaboration Features

Share button → Add people
Permissions:
- Viewer (can view)
- Commenter (can comment)
- Editor (can edit)

Version History

File → Version history → See version history
Ctrl+Alt+Shift+H

Features:

See all changes
Restore previous versions
Name versions
Compare versions

Comments & Suggestions

Insert → Comment (Ctrl+Alt+M)
View → Suggestion mode

Protected Ranges

Data → Protect sheets and ranges

Options:

Protect entire sheet
Protect specific ranges
Set permissions per range
Warning messages

Data Validation

Data → Data validation

Validation Types

// List from range
List from range: A1:A10

// List of items
List of items: Apple,Orange,Banana

// Number range
Number → Between → 1 to 100

// Date range
Date → Between → 01/01/2024 to 12/31/2024

// Custom formula
Custom formula: =LEN(A1)<=10
Custom formula: =COUNTIF(A:A, A1)=1  // Unique values

Conditional Formatting

Format → Conditional formatting

Format Rules

Cell is empty/not empty
Text contains/doesn’t contain
Date is before/after
Greater than/less than
Between
Custom formula

Custom Formula Examples

=MOD(ROW(), 2)=0  // Alternate row colors
=$B1>100  // Highlight row if column B > 100
=COUNTIF($A$1:$A$10, A1)>1  // Highlight duplicates
=AND($A1="Complete", $B1>TODAY()-7)  // Multiple conditions

Charts & Visualization

Chart Types

Line chart
Area chart
Column chart
Bar chart
Pie chart
Scatter chart
Combo chart
Map (Geo chart)
Gauge chart
Candlestick chart

Chart Best Practices

Dynamic ranges: Use named ranges or formulas
Combo charts: Mix line + column for comparisons
Sparklines: Mini charts in cells
Slicers: Interactive filtering

Sparkline Examples

=SPARKLINE(A1:A10)
=SPARKLINE(A1:A10, {"charttype", "bar"})
=SPARKLINE(A1:A10, {"charttype", "line"; "color", "red"})
=SPARKLINE(A1:A10, {"charttype", "column"; "max", 100})

Pivot Tables

Create Pivot Table

Insert → Pivot table
Ctrl+Alt+Shift+2

Pivot Table Components

Rows: Group by categories
Columns: Split data across columns
Values: Aggregated data (SUM, COUNT, AVG)
Filters: Filter entire pivot table

Calculated Fields

Pivot table editor → Values → Add → Calculated field

Example:

=Sales / Quantity  // Average price

Add-ons & Extensions

Useful Add-ons

Power Tools - Bulk operations
Remove Duplicates - Advanced duplicate removal
Supermetrics - Marketing data import
Awesome Table - Advanced filtering/display
Form Publisher - PDF generation from forms
Yet Another Mail Merge - Email campaigns

Installing Add-ons

Extensions → Add-ons → Get add-ons

Tips & Best Practices

Performance Optimization

Avoid volatile functions in large datasets
- NOW(), TODAY(), RAND(), RANDARRAY()
Use ARRAYFORMULA instead of dragging formulas
- Single formula vs thousands of cells
Limit IMPORTRANGE usage
- Can slow down sheets
- Consider copying data periodically
Use FILTER instead of complex IF arrays
- More efficient for large datasets

Data Organization

Use headers in row 1
Freeze header row (View → Freeze → 1 row)
No merged cells in data tables
One data point per cell
Consistent date/number formats
Use data validation for consistency

Formula Best Practices

Name ranges for clarity
Use structured references
Comment complex formulas (Insert → Note)
Break complex formulas into steps
Use helper columns when needed

Collaboration Tips

Use comments for questions (@mention people)
Suggestion mode for proposed changes
Protect important sheets/ranges
Use version history regularly
Create a changelog sheet

Mobile App Features

Mobile-Specific Shortcuts

Swipe left - Delete row
Double tap - Edit cell
Long press - Open menu
Pinch - Zoom in/out

Offline Mode

File → Make available offline
(Requires Google Drive app)

Common Issues & Solutions

Issue	Solution
#REF!	Check for deleted cells/ranges
#DIV/0!	Check for division by zero
#N/A	Lookup value not found
#NAME?	Function name misspelled
#VALUE!	Wrong data type
Circular dependency	Check for cell referencing itself
Loading…	Formula taking too long, simplify
IMPORTRANGE error	Give permission in popup

Resources

Google Sheets Help Center: Official documentation
Ben Collins: Advanced Google Sheets tutorials
Sheets for Marketers: Marketing-focused tutorials
Google Apps Script: Script documentation
r/googlesheets: Community support

Google Sheets Cheat Sheet

Keyboard Shortcuts

Navigation

Selection

Editing

Formatting

Formulas

Data & Filtering

Sheets Management

Comments & Notes

General

Google Sheets Exclusive Functions

Import Functions

Google Services Integration

Array & Query Functions

Special Functions

Essential Formulas

Lookup Functions

Conditional Functions

Text Functions

Math Functions

Date & Time

QUERY Function Deep Dive

Basic Syntax

Common Queries

Query Operators

Aggregate Functions in QUERY

Array Formulas

ARRAYFORMULA Basics

Combining Array Functions

Google Apps Script Integration

Custom Functions

Common Scripts

Collaboration Features

Sharing & Permissions

Version History

Comments & Suggestions

Protected Ranges

Data Validation

Validation Types

Conditional Formatting

Format Rules

Custom Formula Examples

Charts & Visualization

Chart Types

Chart Best Practices

Sparkline Examples

Pivot Tables

Create Pivot Table

Pivot Table Components

Calculated Fields

Add-ons & Extensions

Useful Add-ons

Installing Add-ons

Tips & Best Practices

Performance Optimization

Data Organization

Formula Best Practices

Collaboration Tips

Mobile App Features

Mobile-Specific Shortcuts

Offline Mode

Common Issues & Solutions

Resources