Google Sheets Tutorial: Formulas, Query, Charts & Automation
Key Takeaways
- Master essential formulas like VLOOKUP, SUMIF, and ARRAYFORMULA to automate calculations.
- Use the QUERY function to filter and summarize data with SQL-like syntax.
- Create dynamic charts that update automatically when data changes.
- Set up Google Apps Script to automate repetitive tasks, saving hours per week.
---
# Google Sheets Tutorial: Formulas, Query, Charts & Automation
Google Sheets is more than a free alternative to Excel. It’s a powerful tool for data analysis, reporting, and automation—once you know how to use it beyond basic data entry. I’ve spent years building dashboards and automating workflows in Sheets, and I’ll show you the exact steps I use.
Essential Formulas for Everyday Work
Formulas are the backbone of Sheets. Here are the ones I use most often, with examples you can try right now.
VLOOKUP for Vertical Lookups
VLOOKUP searches for a value in the first column of a range and returns a value in the same row from a specified column.
Example: You have a product list in columns A (ID) and B (Price). To find the price of product ID "P100":
`=VLOOKUP("P100", A:B, 2, FALSE)`
- The `FALSE` ensures an exact match. Use `TRUE` for approximate matches (e.g., grading scales).
SUMIF for Conditional Sums
SUMIF adds values based on a condition.
Example: Sum all sales over $500 in column C:
`=SUMIF(C:C, ">500")`
You can also sum by text: `=SUMIF(A:A, "Electronics", C:C)`.
ARRAYFORMULA for Bulk Calculations
ARRAYFORMULA applies a formula to an entire column without dragging.
Example: Multiply column D by column E for rows 2 to 100:
`=ARRAYFORMULA(D2:D100 * E2:E100)`
This updates automatically when new data is added.
Mastering the QUERY Function
The QUERY function is my favorite. It lets you use SQL-like commands to filter, sort, and aggregate data.
Syntax: `=QUERY(data, query, [headers])`
Basic Filtering
To get all rows where column B equals "Active":
`=QUERY(A:C, "SELECT * WHERE B = 'Active'", 1)`
The `1` tells Sheets the first row has headers.
Aggregation with GROUP BY
Sum sales by category from columns A (Category) and B (Sales):
`=QUERY(A:B, "SELECT A, SUM(B) GROUP BY A LABEL SUM(B) 'Total Sales'", 1)`
The `LABEL` clause renames the result column.
Real Example: Sales Dashboard
I manage a monthly sales report with columns: Date, Product, Region, Revenue. To see total revenue by region for Q1 2024:
`=QUERY(A:D, "SELECT C, SUM(D) WHERE A >= DATE '2024-01-01' AND A <= DATE '2024-03-31' GROUP BY C LABEL SUM(D) 'Q1 Revenue'", 1)`
This saved me 20 minutes per report.
Creating Dynamic Charts
Charts in Sheets update when your data changes. Here’s how to make them work for you.
Step-by-Step: Line Chart for Monthly Trends
1. Select your data (e.g., months in column A, revenue in column B).
2. Click Insert > Chart.
3. In the Chart Editor, choose Line chart.
4. Use the Setup tab to confirm data range. If you add rows, Sheets expands the chart automatically—but only if you include blank rows. I always convert my data to a Table (Insert > Table) to make this seamless.
Pro tip: Add a trendline via the Customize tab > Series > check Trendline. Choose polynomial for non-linear data.
Comparison Table: Chart Types
| Chart Type | Best For | Example Use |
| ------------ | ---------- | ------------- |
| Line | Trends over time | Monthly sales |
| Bar | Comparing categories | Revenue by product |
| Pie | Parts of a whole | Market share (max 5 slices) |
| Scatter | Correlation | Ad spend vs. conversions |
Automation Scripts with Google Apps Script
Google Apps Script is JavaScript-based and free. It runs directly in Sheets.
Simple Script: Auto-Send Email on Edit
This script emails you when a cell in column C is changed to "Complete":
1. Open Extensions > Apps Script.
2. Paste this code:
```javascript
function sendEmailOnEdit(e) {
var sheet = e.source.getActiveSheet();
var range = e.range;
if (sheet.getName() === "Sheet1" && range.getColumn() === 3 && range.getValue() === "Complete") {
MailApp.sendEmail("you@example.com", "Task Complete", "Row " + range.getRow() + " is done.");
}
}
```
3. Save and set up a trigger: In Apps Script, click the clock icon, add a new trigger, choose On edit event.
Advanced: Auto-Archive Old Rows
Move rows older than 30 days to a hidden "Archive" sheet:
```javascript
function archiveOldRows() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Main");
var archive = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Archive");
var data = sheet.getDataRange().getValues();
var cutoff = new Date();
cutoff.setDate(cutoff.getDate() - 30);
var newData = [];
for (var i = data.length - 1; i >= 0; i--) {
if (new Date(data[i][0]) < cutoff) {
archive.appendRow(data[i]);
sheet.deleteRow(i + 1);
}
}
}
```
Run this daily via a time-driven trigger.
Final Tips
- Use named ranges (Data > Named ranges) to make formulas easier to read.
- Press Ctrl+Shift+Enter for array formulas in older Sheets versions—though ARRAYFORMULA is more reliable.
- Explore feature (bottom right) suggests charts and insights automatically.
FAQ
How do I use VLOOKUP with multiple criteria?
VLOOKUP can’t handle multiple criteria directly. Use INDEX-MATCH or combine columns: create a helper column that concatenates criteria (e.g., `=A2&B2`), then VLOOKUP on that.
Can I schedule Google Apps Script to run automatically?
Yes. In Apps Script, go to the clock icon (Triggers), click Add Trigger, choose function, time interval (e.g., every hour or day), and save.
Why is my QUERY returning errors?
Common causes: mismatched data types (e.g., numbers stored as text), missing quotes for text values, or incorrect column letters. Use `SELECT *` first to debug, then add conditions.
---
*Google Sheets is free for personal use, with 15 GB of storage. For business, Google Workspace starts at $6/user/month.*