Google Sheets Tutorial: Formulas, Query, Charts & Automation for Beginners
Key Takeaways
- Master core formulas like SUM, AVERAGE, and VLOOKUP to automate calculations and data lookups.
- Use the QUERY function to filter and sort data with SQL-like commands.
- Create charts and embed them in Sheets for visual data analysis.
- Automate repetitive tasks with Google Apps Script—no coding experience needed.
Introduction
Google Sheets is free, accessible from any device, and packed with features that can save you hours of manual work. Whether you're managing a budget, tracking sales, or analyzing survey responses, knowing how to use Sheets effectively makes you more productive. This tutorial covers four essential areas: formulas, the QUERY function, charts, and automation scripts. I’ll walk you through each with concrete examples so you can apply them immediately.
Formulas: The Foundation
Formulas are where Sheets starts to shine. Instead of manually adding numbers in a column, you can use SUM. Instead of guessing averages, use AVERAGE. Here are the basics.
Basic Math Formulas
- `=SUM(A1:A10)` adds all values in cells A1 through A10.
- `=AVERAGE(B1:B10)` returns the average.
- `=COUNT(C1:C10)` counts cells with numbers.
- `=MAX(D1:D10)` finds the highest value.
Example: You have a column of monthly expenses (cells A2 to A13). To get the total, type `=SUM(A2:A13)` in an empty cell. To see the average monthly spend, type `=AVERAGE(A2:A13)`.
VLOOKUP for Data Lookup
VLOOKUP searches for a value in the first column of a range and returns a value from the same row in another column. Syntax: `=VLOOKUP(search_key, range, index, is_sorted)`.
Example: You have a table of products with IDs in column A and prices in column B. To find the price of product ID "P1003", use `=VLOOKUP("P1003", A2:B100, 2, FALSE)`. The FALSE ensures an exact match.
| Feature | VLOOKUP | INDEX/MATCH |
| --------- | --------- | ------------- |
| Lookup direction | Left to right only | Any direction |
| Performance | Fast for small data | Faster for large data |
| Complexity | Simple | Slightly more complex |
For most beginners, VLOOKUP is enough. As you grow, INDEX/MATCH offers more flexibility.
QUERY Function: SQL-Like Data Filtering
The QUERY function lets you filter, sort, and aggregate data using a syntax similar to SQL. It’s powerful once you get the hang of it.
Syntax: `=QUERY(data, query, [headers])`
Basic QUERY Examples
- `=QUERY(A1:C100, "SELECT A, B WHERE C > 50", 1)` returns columns A and B where column C is greater than 50.
- `=QUERY(A1:C100, "SELECT A, SUM(B) GROUP BY A", 1)` groups by column A and sums column B.
- `=QUERY(A1:C100, "SELECT * ORDER BY C DESC", 1)` sorts all columns by column C descending.
Real-world example: You have sales data: columns A (salesperson), B (region), C (amount). To get total sales per region, use `=QUERY(A1:C100, "SELECT B, SUM(C) GROUP BY B LABEL SUM(C) 'Total Sales'", 1)`. The LABEL clause renames the sum column.
Tips for QUERY
- Enclose text values in single quotes.
- Use double quotes for the query string.
- Include headers parameter (1 if first row has headers).
- Combine with WHERE, ORDER BY, GROUP BY, and LIMIT.
Charts: Visualizing Data
Charts help you spot trends and outliers at a glance. Sheets offers bar, line, pie, scatter, and more.
Creating a Chart
1. Select your data range (including headers).
2. Click Insert > Chart.
3. Sheets suggests a chart type. You can change it in the Chart Editor panel.
4. Customize: titles, colors, labels, legend position.
Example: You have monthly revenue data (months in column A, revenue in column B). Select A1:B13, insert a line chart. Change the title to "Monthly Revenue 2024" and add data labels to show exact values.
Chart Best Practices
- Use bar charts for comparisons (e.g., sales by product).
- Use line charts for trends over time (e.g., website traffic).
- Use pie charts only for showing parts of a whole (e.g., budget allocation).
- Keep it simple: avoid 3D effects that distort proportions.
Automation with Google Apps Script
Google Apps Script is a JavaScript-based language that automates tasks in Sheets. You can send emails, update cells, or import data without manual work.
Writing Your First Script
1. Open your sheet.
2. Click Extensions > Apps Script.
3. Delete any placeholder code and paste:
```javascript
function myFunction() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var data = sheet.getDataRange().getValues();
for (var i = 1; i < data.length; i++) {
var row = data[i];
// Do something with each row
Logger.log(row[0]);
}
}
```
4. Press Run to test. You’ll need to authorize permissions.
Real Automation Example: Send Weekly Report
Imagine you have a sheet tracking weekly sales. You want to email the total sales to yourself every Friday.
```javascript
function sendWeeklyReport() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sales");
var total = sheet.getRange("D10").getValue(); // Total sales in cell D10
var recipient = "your@email.com";
var subject = "Weekly Sales Report";
var body = "Total sales for this week: $" + total;
MailApp.sendEmail(recipient, subject, body);
}
```
Then set a trigger: in Apps Script editor, click the clock icon (Triggers). Add a time-driven trigger to run `sendWeeklyReport` every Friday at 8 AM.
Tips for Beginners
- Start small: just log data to see if it works.
- Use the built-in Logger to check output.
- Google Apps Script documentation is excellent.
- Don’t be afraid to copy and modify existing scripts.
FAQ
1. How do I learn Google Sheets formulas quickly?
Start with SUM, AVERAGE, and VLOOKUP. Practice on real data—like your personal budget. Google’s built-in help (press F1) and the formula autocomplete guide you. I recommend keeping a cheat sheet of common formulas nearby.
2. Can I use QUERY with dates?
Yes. Format dates as text in the query string. For example, `=QUERY(A1:C100, "SELECT * WHERE A >= date '2024-01-01'")`. Use the DATE keyword and ISO format (YYYY-MM-DD).
3. Is Google Apps Script safe for beginners?
Yes, if you stick to basic scripts. Only grant permissions the script needs. Never run scripts from untrusted sources. Always test on a copy of your data first.
Conclusion
Google Sheets is a powerful tool once you move beyond basic data entry. Start with formulas, then explore QUERY for flexible data analysis. Add charts to communicate insights visually. Finally, automate repetitive tasks with Apps Script. Each step builds on the last. Practice on your own data—that’s the best way to learn. You’ll be surprised how quickly you can make Sheets work for you.