Google Sheets Mastery: From Beginner to Advanced

## 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:

### 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(A:E, "SELECT A, C, E")        // Specific 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.

## 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)

## 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

=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);
    }
  }
}