How to Use Google Sheets: Formulas, Query, Charts & Scripts for Beginners
Key Takeaways
- Start with simple formulas like SUM and AVERAGE using cell references (e.g., =SUM(A1:A10)).
- The QUERY function lets you filter and sort data like SQL, but without a database.
- Create charts in two clicks—bar, line, or pie—then customize colors and labels.
- Automation scripts (Google Apps Script) can save hours by sending emails or updating sheets on a schedule.
---
Getting Started with Google Sheets Basics
Google Sheets is free, cloud-based, and works on any device. If you’ve used Excel, you’ll feel at home, but Sheets has its own quirks. Let’s start with the essentials.
Your First Formula: SUM
Say you have sales data in column B (rows 2 to 10). Instead of adding manually, type:
`=SUM(B2:B10)`
Press Enter. Sheets totals the numbers. Real example: If B2=150, B3=200, B4=75, the result is 425.
Other basic formulas:
- `=AVERAGE(B2:B10)` – mean value
- `=COUNT(B2:B10)` – how many cells have numbers
- `=MAX(B2:B10)` – highest value
Pro tip: Always use cell references, not hard-coded numbers. If data changes, the formula updates automatically.
---
The QUERY Function: Filter Like a Pro
QUERY is powerful. It uses a pseudo-SQL syntax. For example, you have a table with columns A (Product), B (Sales), C (Region). To get all sales over $500:
`=QUERY(A1:C100, "SELECT A, B WHERE B > 500", 1)`
The `1` means the top row is a header. Change it to `0` if no header.
Real numbers: I once had a client with 2,000 rows of store data. Instead of filters, I used:
`=QUERY(A1:C2000, "SELECT A, SUM(B) WHERE C = 'East' GROUP BY A LABEL SUM(B) 'Total'", 1)`
This grouped products and summed sales for the East region. It ran in under a second.
Common QUERY Examples
- Sort by column C descending: `=QUERY(A1:C, "SELECT * ORDER BY C DESC", 1)`
- Count unique items: `=QUERY(A1:A, "SELECT A, COUNT(A) WHERE A IS NOT NULL GROUP BY A", 1)`
- Filter dates after 2024: `=QUERY(A1:C, "WHERE A > DATE '2024-01-01'", 1)`
Limitation: QUERY can’t handle array formulas inside itself. For complex math, combine with other functions.
---
Creating Charts in Two Clicks
Charts turn numbers into stories. Here’s how:
1. Select your data (including headers).
2. Click Insert > Chart.
3. Sheets suggests a chart type. Change it in the Chart Editor on the right.
Chart Type Comparison
| Chart Type | Best For | Example Use Case |
| ------------ | ---------- | ------------------ |
| Bar/Column | Comparing categories | Sales by product |
| Line | Trends over time | Monthly revenue |
| Pie | Parts of a whole | Budget allocation |
| Scatter | Relationships between variables | Ad spend vs. clicks |
Customization tips:
- Double-click the chart title to edit it.
- In Customize tab, adjust colors, fonts, and axis labels.
- Add data labels to show values directly on bars.
- For a pie chart, limit to 5-7 slices. More than that becomes unreadable.
I once created a dashboard for a startup using three charts: a bar chart for monthly sales, a line chart for growth rate, and a pie chart for customer segments. It took 10 minutes.
---
Automation Scripts with Google Apps Script
Repetitive tasks? Write a script. Apps Script is JavaScript-based, runs on Google’s servers.
Simple Script: Send an Email Alert
Suppose you want an email when a cell value drops below 100. Here’s a script:
1. Open Extensions > Apps Script.
2. Delete placeholder code and paste:
```javascript
function checkStock() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
var value = sheet.getRange("B2").getValue();
if (value < 100) {
MailApp.sendEmail("you@example.com", "Low Stock Alert", "Product stock is: " + value);
}
}
```
3. Click Save, then Run (authorize if asked).
4. To run automatically, click the clock icon (Triggers) and set it to run hourly.
Real-world example: I set up a script for a client that emailed a weekly summary of expenses from a sheet. It saved them 2 hours per month.
More Advanced Automation Ideas
- Copy data between sheets: Use `copyTo()` to move rows to an archive sheet.
- Create PDFs: Use `getAs('application/pdf')` and email the file.
- Pull live data: Use `UrlFetchApp.fetch()` to get stock prices or weather data.
Caveat: Google imposes daily quotas (e.g., 100 emails per day for free accounts). Check [Google’s quota docs](https://developers.google.com/apps-script/guides/services/quotas).
---
FAQ
Q1: How do I freeze rows or columns in Google Sheets?
A1: Select the row below (or column to the right) where you want the freeze. Go to View > Freeze > choose how many rows/columns. For example, freeze row 1 so headers stay visible when scrolling.
Q2: Can I use Excel formulas in Google Sheets?
A2: Most Excel formulas work, but some are different. For instance, VLOOKUP works, but INDEX/MATCH is more reliable in Sheets. Also, Sheets uses `ARRAYFORMULA` instead of Excel’s Ctrl+Shift+Enter array formulas.
Q3: How do I share a sheet without giving edit access?
A3: Click Share in the top-right. Add email addresses, then change the permission from “Editor” to “Viewer” or “Commenter.” You can also set an expiration date for access under Advanced settings.