Getting Started with Google Sheets

Creating Your First Spreadsheet

1. Go to sheets.google.com
2. Click the + button or "Blank" template
3. Your new spreadsheet opens instantly

The Interface

Menu Bar: File, Edit, View, Insert, Format, Data, Tools, Extensions, Help

Toolbar: Quick access to common formatting and functions

Formula Bar: Shows the contents of the active cell

Sheet Tabs: Navigate between sheets (bottom of screen)

Essential Navigation

Your First Formula

Click a cell and type:

=SUM(A1:A10)

Sheets auto-completes function names as you type. Press Tab to accept suggestions.

Essential Functions

Mathematical Functions

=SUM(A1:A10)           // Add values
=AVERAGE(A1:A10)       // Calculate mean
=COUNT(A1:A10)         // Count numbers
=COUNTA(A1:A10)        // Count non-empty cells
=MAX(A1:A10)           // Largest value
=MIN(A1:A10)           // Smallest value
=ROUND(A1, 2)          // Round to 2 decimals

Text Functions

=CONCATENATE(A1, " ", B1)  // Join text (or use &)
=LEFT(A1, 5)               // First 5 characters
=RIGHT(A1, 3)              // Last 3 characters
=MID(A1, 3, 5)             // 5 chars from position 3
=UPPER(A1)                 // UPPERCASE
=LOWER(A1)                 // lowercase
=PROPER(A1)                // Title Case
=TRIM(A1)                  // Remove extra spaces
=LEN(A1)                   // Character count

Logical Functions

=IF(A1>100, "High", "Low")
=IF(AND(A1>50, B1>50), "Pass", "Fail")
=IF(OR(A1>100, B1>100), "At least one high", "Both low")
=IFS(A1>90, "A", A1>80, "B", A1>70, "C", TRUE, "F")

Lookup Functions

=VLOOKUP(A1, B:D, 3, FALSE)    // Look up and return 3rd column
=HLOOKUP(A1, 1:3, 3, FALSE)    // Horizontal lookup
=INDEX(C:C, MATCH(A1, B:B, 0)) // Flexible lookup
=XLOOKUP(A1, B:B, C:C)         // Modern lookup (available in Sheets)

Date Functions

=TODAY()                   // Current date
=NOW()                     // Current date and time
=DATE(2024, 12, 25)        // Create a date
=YEAR(A1)                  // Extract year
=MONTH(A1)                 // Extract month
=DAY(A1)                   // Extract day
=DATEDIF(A1, B1, "D")      // Days between dates
=EDATE(A1, 3)              // Add 3 months
=EOMONTH(A1, 0)            // End of month

Data Organization

Sorting Data

Simple Sort:

1. Click any cell in your data
2. Data → Sort range
3. Choose column and direction

Advanced Sort (Multiple Columns):

1. Data → Sort range → Advanced range sorting options
2. Add multiple sort conditions

Filtering Data

Create Filter:

1. Select your data range
2. Data → Create a filter
3. Click filter dropdowns in headers

Filter Views:

Create saved filter views that don't affect other users:

1. Data → Filter views → Create new filter view
2. Apply your filters
3. Name and save the view

Freezing Rows and Columns

Keep headers visible while scrolling:

Freeze Top Row: View → Freeze → 1 row

Freeze First Column: View → Freeze → 1 column

Custom Freeze:

1. Click the cell below and to the right of where you want to freeze
2. View → Freeze → Up to current row/column

Grouping Data

Collapse and expand sections:

1. Select rows or columns to group
2. Data → Group rows/columns
3. Click the +/- to expand/collapse

Conditional Formatting

Automatically format cells based on their values.

Basic Rules

1. Select your range
2. Format → Conditional formatting
3. Choose a rule type

Common Rules:

• Greater than / Less than
• Text contains
• Date is before/after
• Cell is empty

Color Scales

Visualize data ranges with gradients:

1. Format → Conditional formatting
2. Format rules → Color scale
3. Choose a preset or customize

Icon Sets

Not native to Sheets, but you can simulate with formulas:

=IF(A1>100, "🟢", IF(A1>50, "🟡", "🔴"))

Custom Formulas

Use any formula that returns TRUE/FALSE:

=A1>AVERAGE($A:$A)        // Above average
=ISEVEN(ROW())            // Even rows
=REGEXMATCH(A1, "urgent") // Contains "urgent" (case insensitive)

Mastering the QUERY Function

QUERY is Google Sheets' superpower—SQL-like queries on spreadsheet data.

Basic Syntax

=QUERY(data, query, [headers])

• data: Range to query
• query: SQL-like query string
• headers: Number of header rows (optional, usually 1)

SELECT - Choose Columns

=QUERY(A:E, "SELECT A, C, E")        // Specific columns
=QUERY(A:E, "SELECT *")              // All columns

WHERE - Filter Rows

=QUERY(A:E, "SELECT * WHERE B = 'North'")
=QUERY(A:E, "SELECT * WHERE C > 1000")
=QUERY(A:E, "SELECT * WHERE B = 'North' AND C > 1000")
=QUERY(A:E, "SELECT * WHERE B = 'North' OR B = 'South'")
=QUERY(A:E, "SELECT * WHERE B CONTAINS 'North'")
=QUERY(A:E, "SELECT * WHERE A > date '2024-01-01'")

ORDER BY - Sort Results

=QUERY(A:E, "SELECT * ORDER BY C DESC")          // Descending
=QUERY(A:E, "SELECT * ORDER BY B ASC, C DESC")   // Multiple columns

GROUP BY - Aggregate Data

=QUERY(A:E, "SELECT B, SUM(C) GROUP BY B")
=QUERY(A:E, "SELECT B, COUNT(A), AVG(C) GROUP BY B")

Available aggregations: SUM, COUNT, AVG, MAX, MIN

LIMIT - Restrict Results

=QUERY(A:E, "SELECT * ORDER BY C DESC LIMIT 10")  // Top 10

LABEL - Rename Columns

=QUERY(A:E, "SELECT B, SUM(C) GROUP BY B LABEL SUM(C) 'Total Sales'")

PIVOT - Create Crosstabs

=QUERY(A:E, "SELECT A, SUM(D) PIVOT B")

Using Cell References in QUERY

Use ampersand (&) to insert cell values:

=QUERY(A:E, "SELECT * WHERE B = '"&G1&"'")

For numbers (no quotes):

=QUERY(A:E, "SELECT * WHERE C > "&G1)

Array Formulas

Process multiple values in a single formula.

ARRAYFORMULA

Apply a formula to an entire column:

Instead of copying =A2*B2 down 1000 rows:

=ARRAYFORMULA(A2:A*B2:B)

With IF to handle empty cells:

=ARRAYFORMULA(IF(A2:A="", "", A2:A*B2:B))

UNIQUE

Get unique values:

=UNIQUE(A2:A)

Unique combinations:

=UNIQUE(A2:B)

FILTER

Return rows matching criteria:

=FILTER(A2:E, B2:B="North")                    // Single condition
=FILTER(A2:E, B2:B="North", C2:C>1000)         // Multiple conditions (AND)
=FILTER(A2:E, (B2:B="North")+(B2:B="South"))   // OR condition

SORT

Sort a range:

=SORT(A2:E, 3, FALSE)                          // Sort by column 3, descending
=SORT(A2:E, 1, TRUE, 3, FALSE)                 // Multiple columns

SORTN

Sort and limit:

=SORTN(A2:E, 10, 0, 3, FALSE)                  // Top 10 by column 3

Combining Array Functions

Get top 5 sales in North region:

=SORTN(FILTER(A2:E, B2:B="North"), 5, 0, 4, FALSE)

Data Validation

Control what users can enter.

Creating Dropdown Lists

1. Select target cells
2. Data → Data validation
3. Criteria: "List from a range" or "List of items"
4. Enter your options

Dynamic Dropdowns

Reference a range that can change:

=UNIQUE(FILTER(Products!A:A, Products!A:A<>""))

Dependent Dropdowns

Create cascading dropdowns where the second depends on the first:

1. First dropdown: Categories (A1)
2. Second dropdown validation formula:

=FILTER(B:B, A:A=A1)

Validation Rules

• Number: Within range, whole number, etc.
• Text: Specific length, contains certain text
• Date: Before, after, between
• Checkbox: Simple true/false input
• Custom formula: Any formula returning TRUE/FALSE

Pivot Tables

Summarize large datasets without formulas.

Creating a Pivot Table

1. Select your data
2. Insert → Pivot table
3. Choose: New sheet (recommended)
4. Click Create

Pivot Table Editor

Rows: Categories to group by (e.g., Region, Product) Columns: Cross-tabulate categories Values: Numbers to calculate (Sum, Count, Average, etc.) Filters: Filter the entire pivot table

Example: Sales by Region and Product

1. Drag "Region" to Rows
2. Drag "Product" to Columns
3. Drag "Sales" to Values

Instant summary table!

Calculated Fields

Add custom calculations:

1. Click "Add" next to Values
2. Choose "Calculated Field"
3. Create formula using field names

Pivot Table Tips

• Refresh: Data changes require manual refresh (right-click → Refresh)
• Formatting: Right-click cells to format numbers
• Show details: Double-click a value to see underlying data
• Sort: Use dropdown arrows to sort

Charts & Visualization

Turn data into insights.

Creating Charts

1. Select your data (including headers)
2. Insert → Chart
3. Chart editor opens automatically

Chart Types

• Column/Bar: Compare categories
• Line: Show trends over time
• Pie: Show proportions
• Scatter: Show correlation
• Combo: Mix chart types
• Geo: Map-based visualization

Chart Customization

Chart Editor - Setup Tab:

• Change chart type
• Modify data range
• Switch rows/columns

Chart Editor - Customize Tab:

• Chart title and style
• Axis titles and formatting
• Legend position
• Colors and fonts
• Data labels

Sparklines

Miniature charts in cells:

=SPARKLINE(A1:G1)                             // Line
=SPARKLINE(A1, {"charttype","bar"})           // Bar
=SPARKLINE(A1, {"charttype","column"})        // Column

With options:

=SPARKLINE(A1:G1, {"color","blue"; "linewidth",2})
=SPARKLINE(A1/B1, {"charttype","bar"; "max",1; "color1","green"})

Automation with Apps Script

Take Sheets to the next level with custom scripts.

Opening the Script Editor

Extensions → Apps Script

Your First Script

function myFirstFunction() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const range = sheet.getRange("A1");
  range.setValue("Hello, Sheets!");
}

Run it: Click ▶ button

Common Script Tasks

Read and Write Data:

function processData() {
  const sheet = SpreadsheetApp.getActiveSheet();

  // Read
  const value = sheet.getRange("A1").getValue();
  const values = sheet.getRange("A1:C10").getValues();

  // Write
  sheet.getRange("D1").setValue("Processed");
  sheet.getRange("D1:D10").setValues(newValues);
}

Create Custom Functions:

function DOUBLE(value) {
  return value * 2;
}
// Use in sheet: =DOUBLE(A1)

Send Email:

function sendEmail() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const data = sheet.getRange("A2:C2").getValues()[0];

  GmailApp.sendEmail(
    data[0],  // email
    "Subject",
    `Hello ${data[1]}, your order ${data[2]} shipped.`
  );
}

Triggers

Automate script execution:

1. Open Script Editor
2. Click clock icon (Triggers)
3. Create trigger:
   • Time-driven: Run every hour/day/week
   • On edit: Run when sheet changes
   • On form submit: Run when form submitted

Example: Auto-Archive

function archiveCompleted() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const source = ss.getSheetByName("Tasks");
  const archive = ss.getSheetByName("Archive");

  const data = source.getDataRange().getValues();

  for (let i = data.length - 1; i >= 1; i--) {
    if (data[i][3] === "Complete") {
      archive.appendRow(data[i]);
      source.deleteRow(i + 1);
    }
  }
}