Google Sheets Essentials
Build and share spreadsheets in Google Sheets, use its formulas, and understand where it differs from Excel in practice.
By the end of this lesson you can
- Enter and format data, and build formulas using cell references
- Use functions shared with Excel, and the ones unique to Sheets
- Sort, filter, and protect ranges in a shared spreadsheet
- Choose between Sheets and Excel for a given task
Lesson Notes
Read through the key concepts before you try the challenge.
The same grid, a different set of trade-offs
You track supply orders at Lakeside Medical Associates.
Three staff need to update the supply log during the day. The Excel file on the shared drive keeps opening read-only because someone left it open at lunch, and two people have started keeping their own copies. Within a fortnight there are three versions and none of them is right.
Your task: Use a shared spreadsheet where simultaneous editing is the normal case rather than a conflict.
Google Sheets uses the same fundamental model as Excel: a grid of addressed cells, formulas beginning with an equals sign, and functions like SUM, AVERAGE, IF, and VLOOKUP that work the same way. Almost everything you learned about Excel formulas transfers directly.
What differs is the collaboration model. Sheets was built for many people in one file at once, which is exactly the supply-log problem. Excel is stronger for large data sets, PivotTables, advanced analysis, and anything involving macros. Sheets slows noticeably on very large files where Excel is still comfortable.
| Capability | Google Sheets | Microsoft Excel |
|---|---|---|
| Several people editing at once | Built for it; the normal case | Possible via OneDrive, but less fluid |
| Very large data sets | Slows down well before Excel does | Handles far more rows comfortably |
| PivotTables | Yes, simpler | Yes, considerably more capable |
| Pulling live web or sheet data | IMPORTRANGE, IMPORTHTML, GOOGLEFINANCE | Requires Power Query or add-ins |
| Macros and automation | Apps Script (JavaScript) | VBA, more mature for desktop automation |
| Working offline | Requires setup; limited | Fully offline |
Formulas, and the functions Sheets adds
Every formula begins with an equals sign, cell references work identically, and the dollar sign makes a reference absolute exactly as it does in Excel. SUM, AVERAGE, COUNT, MIN, MAX, IF, and the lookup functions all behave the same way.
| Function | Does | Use for |
|---|---|---|
| IMPORTRANGE | Pulls a range from another Google Sheet | A summary sheet drawing from several department logs |
| QUERY | Runs a SQL-like query against a range | Filtering and summarizing without building a PivotTable |
| ARRAYFORMULA | Applies one formula down a whole column | A calculated column that covers new rows automatically |
| GOOGLETRANSLATE | Translates text in a cell | Rough first-pass translation — never for clinical content |
| SPARKLINE | Draws a miniature chart in a cell | A trend indicator beside each supply category |
Three staff need to update the same supply log throughout the day, and the total row must never be overwritten. What is the appropriate setup?
Challenge
Apply what you've learned in this lesson.
Build a working shared tracker rather than a static sheet.
- Create a Google Sheet named 'Supply Log — Lakeside'. Add columns for Item, Category, Quantity, Unit Cost, and Total Cost. Enter eight rows of sample data.
- In Total Cost, write a formula multiplying Quantity by Unit Cost, and fill it down. Add a total row using SUM.
- Use Data > Protect sheets and ranges to protect the Total Cost column and the total row, leaving the other columns editable. Share the sheet with a classmate as Editor and confirm they cannot alter the protected cells.
- Add a Category dropdown using Data > Data validation with four options, then write two sentences explaining what validation prevents that protection does not.
Finished this lesson?
Progress is saved in this browser only. It is not a grade — official progress lives in Brightspace.