How to Use Google Sheets: Formulas, Queries, Charts & Automation
Key Takeaways
- Google Sheets formulas like SUM, VLOOKUP, and ARRAYFORMULA can save hours of manual work. I’ve seen beginners cut their weekly reporting time from 3 hours to 30 minutes.
- The QUERY function acts like a mini SQL engine inside your sheet—filter, sort, and aggregate data without leaving the browser.
- Interactive charts (like combo charts with dual axes) turn raw numbers into clear stories for stakeholders.
- Automation scripts using Google Apps Script can send emails, create backup copies, or update data from external APIs automatically.
---
Getting Started: The Formula Basics That Matter Most
When you first open Google Sheets, you see a grid of cells. The real power starts when you type an equals sign (`=`). That tells Sheets, "I want to compute something."
Essential formulas for everyday tasks:
- `=SUM(A1:A10)` – adds up numbers in a range. I use this for monthly expense totals.
- `=AVERAGE(B2:B50)` – finds the mean, great for calculating average order value from sales data.
- `=VLOOKUP("Product X", A2:D100, 3, FALSE)` – looks up a value in the first column and returns a matching cell. For example, finding the price of a product ID.
- `=IF(C2>100, "High", "Low")` – adds logic. If sales exceed 100 units, label it "High."
Pro tip: Always use absolute references (like `$A$1`) when dragging formulas across rows or columns. Without the dollar signs, the reference moves—and your calculations break. I learned this the hard way after a boss caught a $5,000 error in a budget sheet.
---
The QUERY Function: SQL Without the Server
The QUERY function is a Swiss Army knife for data manipulation. Its syntax looks intimidating at first, but once you get the hang of it, you’ll wonder how you lived without it.
Basic syntax:
`=QUERY(data_range, "query_string", [headers])`
Real-world example:
Imagine you have a sales table with columns: Date, Product, Region, Units, Revenue.
To get total revenue by region for 2024, use:
`=QUERY(A1:E100, "SELECT C, SUM(E) WHERE A >= DATE '2024-01-01' AND A <= DATE '2024-12-31' GROUP BY C LABEL SUM(E) 'Total Revenue'", 1)`
This returns a clean table with regions and their sums. No pivot table needed.
Common QUERY clauses:
| Clause | Purpose | Example |
| -------- | --------- | --------- |
| SELECT | Choose columns | `SELECT A, B` |
| WHERE | Filter rows | `WHERE C > 100` |
| GROUP BY | Aggregate data | `GROUP BY B` |
| ORDER BY | Sort results | `ORDER BY A DESC` |
| LIMIT | Restrict rows | `LIMIT 10` |
| LABEL | Rename columns | `LABEL SUM(D) 'Total'` |
Why QUERY beats manual filtering?
- It updates automatically when new data arrives.
- You can combine multiple conditions in one line.
- It handles dates and numbers more reliably than pivot tables (which sometimes misformat dates).
---
Charts That Tell a Story
A chart is only as good as the question it answers. Before you click Insert > Chart, ask: "What do I want my audience to see?"
Step-by-step for a combo chart:
1. Select your data (e.g., months in column A, sales in B, profit margin % in C).
2. Go to Insert > Chart.
3. In the Chart Editor (sidebar), choose "Combo chart" from the Chart type dropdown.
4. Under Customize > Series, assign the profit margin column to the right axis.
5. Adjust colors so the bars (sales) and line (margin) don’t blend.
Why this works: In one glance, you see if rising sales are eating into margins. I used this for a quarterly review—the CEO immediately spotted that discount-heavy months crushed profitability.
Common chart mistakes to avoid:
- Using pie charts with more than 5 slices (they become unreadable).
- Forgetting to label axes (your audience shouldn’t guess units).
- Leaving default colors (they clash and look unprofessional).
---
Automation Scripts: Let Google Do the Heavy Lifting
Google Apps Script is JavaScript that runs inside your sheet. It sounds technical, but you can start with tiny automations.
Example: Send an email when a cell changes value
1. Go to Extensions > Apps Script.
2. Delete any default code, then paste this:
```javascript
function sendAlert() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var cellValue = sheet.getRange("A1").getValue();
if (cellValue < 10) {
MailApp.sendEmail({
to: "you@example.com",
subject: "Inventory Alert",
body: "Stock level for item X is below 10. Current: " + cellValue
});
}
}
```
3. Click the disk icon to save, name your project.
4. Set a trigger: click the clock icon > Add Trigger > choose `sendAlert` function > Time-driven > Every hour.
Now, every hour, if cell A1 drops below 10, you get an email. No manual checking.
Other automation ideas:
- Auto-save a backup copy every night.
- Pull exchange rates from an API (using `UrlFetchApp.fetch()`).
- Create a custom menu with buttons for common tasks.
---
FAQ
1. What’s the difference between Google Sheets and Excel for beginners?
Google Sheets is free, lives in the cloud, and excels at collaboration (multiple people editing simultaneously). Excel has more advanced formulas and better offline support. For most small business or personal use, Sheets is faster to learn and share. I’ve switched entirely to Sheets for client reports because I can share a link instead of emailing attachments.
2. How do I handle errors like #N/A or #DIV/0!?
Wrap your formula with `=IFERROR(your_formula, "Fallback value")`. For example: `=IFERROR(VLOOKUP(...), "Not Found")`. This keeps your sheet clean and prevents confusion when data is missing.
3. Can I use Google Sheets to pull data from websites?
Yes, with the `IMPORTHTML` or `IMPORTXML` functions. For instance, `=IMPORTHTML("https://example.com/prices", "table", 1)` grabs the first table from a webpage. It’s handy for tracking competitor prices, but note that some websites block these functions. I use it to scrape stock prices for a personal portfolio tracker.