How to Use Google Sheets: Formulas, QUERY, Charts & Automation

2026-06-05·SaaS Setup

Key Takeaways

  • Google Sheets formulas like SUM, AVERAGE, and VLOOKUP can handle most day-to-day data tasks without scripting.
  • The QUERY function acts like a mini SQL engine, letting you filter, sort, and aggregate data with a single formula.
  • Charts update automatically when source data changes—just set them up once and reuse.
  • Automation scripts (Google Apps Script) can save hours by scheduling repetitive tasks like email reports or data cleanup.

---

Introduction

I remember my first time opening Google Sheets and thinking, "This is just Excel but in a browser." Turns out, it’s both simpler and more powerful. You can collaborate live with a team, pull data from the web, and even write scripts that run on a timer. But if you’re a beginner, the interface can feel overwhelming.

This guide walks through four practical skills: formulas, the QUERY function, charts, and automation. I’ll use real numbers and examples you can test right now in a blank sheet.

---

1. Essential Formulas That Solve Real Problems

Start with the basics. These five formulas cover 80% of what most people need:

  • SUM = Adds numbers. Example: `=SUM(A2:A10)` totals sales from row 2 to 10.
  • AVERAGE = Average of numbers. Example: `=AVERAGE(B2:B10)` gives mean temperature.
  • IF = Conditional logic. Example: `=IF(C2>100, "High", "Low")` labels orders over $100.
  • VLOOKUP = Find a value in a table. Example: `=VLOOKUP("Widget", A2:B10, 2, FALSE)` returns the price of "Widget".
  • COUNTIF = Count cells matching a condition. Example: `=COUNTIF(D2:D100, "Paid")` counts paid invoices.

Pro tip: Use named ranges (Data > Named ranges) instead of cell references like A2:A10. The formula `=SUM(Sales)` is easier to read and less error-prone.

Let’s say you run a small online store. Your sheet has columns: Date, Product, Quantity, Price. To calculate total revenue, use `=SUM(Quantity * Price)`. Wait—that’s an array operation. Actually use `=SUMPRODUCT(C2:C100, D2:D100)` to multiply and sum in one step. That formula saved me from building a helper column.

---

2. The QUERY Function: SQL for Sheets

QUERY is the Swiss Army knife of Google Sheets. It lets you filter, sort, group, and aggregate data with a single formula. The syntax is:

`=QUERY(data, "SQL-like query", [headers])`

Example dataset: Sales data with columns A (Date), B (Product), C (Quantity), D (Price).

Filter rows where Quantity > 10:

`=QUERY(A:D, "SELECT * WHERE C > 10", 1)`

The `1` tells Sheets that row 1 has headers.

Group by product, sum quantity:

`=QUERY(A:D, "SELECT B, SUM(C) GROUP BY B LABEL SUM(C) 'Total Qty'", 1)`

Sort by price descending:

`=QUERY(A:D, "SELECT * ORDER BY D DESC", 1)`

Why this beats manual filtering:

  • It updates automatically when data changes.
  • You can nest queries inside other formulas.
  • No need to click “Filter” every time.

Warning: QUERY is case-sensitive. If your product names are "Widget" and "widget", they’ll be treated differently. Use `LOWER()` or `UPPER()` to normalize.

---

3. Creating Charts That Update Automatically

Charts in Google Sheets are surprisingly easy. The trick is structuring your data correctly.

Step-by-step example:

1. Put labels in the first row and first column. Example: Month (A1), Revenue (B1), Costs (C1).

2. Highlight the data (A1:C13).

3. Click Insert > Chart.

4. In Chart editor (right panel), choose Chart type: Column, Line, or Pie.

5. Under Setup > X-axis, verify it picks your date column.

Real-world tip: I once spent 20 minutes wondering why my chart showed wrong data. The culprit: blank rows in the middle. Google Sheets treats blanks as zero, so the line graph dropped to zero for those months. Always clean empty cells first.

Customize it:

  • Add a trendline (Customize > Series > Trendline).
  • Change colors to match your brand.
  • Set a min/max value for the vertical axis to avoid misleading scales.

Table: When to use each chart type

Chart TypeBest ForExample
-------------------------------
ColumnCompare categoriesSales by product
LineShow trends over timeMonthly revenue
PieShow proportions (max 5 slices)Market share
ScatterShow correlation between two variablesAd spend vs. clicks

---

4. Automation Scripts (Google Apps Script) for Repetitive Tasks

Google Apps Script is JavaScript that runs inside Sheets. You don’t need to be a programmer—just copy and modify existing code.

Example 1: Send an email when a cell changes

Open Extensions > Apps Script. Delete any code and paste:

```javascript

function sendEmailAlert() {

var sheet = SpreadsheetApp.getActiveSheet();

var value = sheet.getRange("A1").getValue();

if (value > 100) {

MailApp.sendEmail("you@example.com", "Alert", "A1 is " + value);

}

}

```

Click the disk icon to save, then run it once to authorize. To automate, click the clock icon (Triggers) and set it to run every minute or when the sheet is edited.

Example 2: Auto-archive old rows

Say you have a log of support tickets. You want to move rows older than 30 days to another sheet.

```javascript

function archiveOldRows() {

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Current");

var archive = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Archive");

var data = sheet.getDataRange().getValues();

var today = new Date();

var cutoff = new Date(today.getTime() - 30 * 24 * 60 * 60 * 1000);

for (var i = data.length - 1; i >= 1; i--) {

var rowDate = new Date(data[i][0]);

if (rowDate < cutoff) {

archive.appendRow(data[i]);

sheet.deleteRow(i + 1);

}

}

}

```

Set a trigger to run this daily at 2 AM.

Caveat: Scripts have a 6-minute execution limit. For huge datasets, break the work into batches.

---

FAQ

1. Can I use Google Sheets offline?

Yes. Enable offline access in Google Drive settings. Any changes you make sync when you reconnect. It works only in Chrome, and you need to install the Google Docs Offline extension.

2. How do I share a sheet without giving edit access?

Click the blue Share button in the top-right. Enter the email, then change the role from "Editor" to "Viewer" or "Commenter." You can also set an expiration date (under gear icon > General access).

3. What’s the limit on rows and cells?

Google Sheets supports up to 10 million cells for spreadsheets created after 2019. That’s roughly 10,000 rows by 1,000 columns. If you hit the limit, consider using BigQuery or splitting into multiple sheets.

---

Final Thoughts

Google Sheets is a tool you grow into. Start with formulas, then layer on QUERY for data analysis, charts for visuals, and scripts for automation. I’ve seen beginners go from zero to building a sales dashboard in a few hours by following this path. The key is to practice with real data—your own business numbers, a hobby project, or even a shared family budget. Every mistake teaches you something.

Now go open a blank sheet and try the QUERY example. You’ll surprise yourself.