Google Sheets and Excel basics.
Spreadsheets are among the most valuable office skills. Many job ads simply say "good Excel knowledge". Today you'll learn the essentials: cells, formulas, functions, sorting, filters and a simple chart.
The basics
- A spreadsheet is a grid of cells. Each cell has an address: column letter + row number (A1, B3, C10).
- You can type numbers, text or formulas. Formulas always start with
=.
Your first formulas
Imagine a monthly expense sheet:
| A | B | |
|---|---|---|
| 1 | Item | Amount |
| 2 | Rent | 8000 |
| 3 | Food | 6000 |
| 4 | Travel | 1500 |
| 5 | Phone | 300 |
| 6 | Total | =SUM(B2:B5) |
=SUM(B2:B5) adds the amounts → 15800
=AVERAGE(B2:B5) average → 3950
=MAX(B2:B5) largest → 8000
=B2/B6 rent as a share of total (format as %)Functions you'll use at work
| Function | What it does | Example |
|---|---|---|
| SUM | Adds | =SUM(B2:B100) |
| AVERAGE | Average | =AVERAGE(C2:C50) |
| COUNT / COUNTA | Counts numbers / non-empty cells | =COUNTA(A2:A200) |
| IF | Decides | =IF(C2>=40,"Pass","Fail") |
| SUMIF | Adds only matching rows | =SUMIF(A2:A100,"Food",B2:B100) |
| VLOOKUP / XLOOKUP | Finds a value in a table | =VLOOKUP(E2,A2:B100,2,FALSE) |
| TODAY | Today's date | =TODAY() |
XLOOKUP is newer and easier than VLOOKUP where available (Excel 365 and Google Sheets).
Sort, filter and freeze
- Sort: Data → Sort range (Sheets) / Data → Sort (Excel).
- Filter: show only some rows (e.g. only "Food" expenses).
- Freeze the header row so it stays visible: View → Freeze → 1 row.
A quick chart
Select your data → Insert → Chart. A pie chart shows shares (like expense categories); a column chart compares amounts; a line chart shows change over time.
A shop wants to mark orders above ₹1,000 as "Free delivery". Which formula fits (order amount in B2)?
IF checks a condition and returns one of two values.
When copying formulas, cells shift (B2 → B3). To lock a cell, add $: =B2*$E$1 keeps E1 fixed. You'll need this often, e.g. for a GST rate in one cell.
Which function adds only the rows where the category is "Food"?
SUMIF adds values that match a condition.
Your task (40 min)
Build your own monthly expense tracker in Google Sheets or Excel with at least 15 rows (date, category, amount). Use SUM, AVERAGE, SUMIF by category, an IF for "Over budget", a filter and a pie chart by category.
Lesson 10 complete!
You can use cells, formulas, SUM/AVERAGE/IF/VLOOKUP, sorting, filtering and charts. Next: typing and keyboard shortcuts.
Next: Typing and keyboard shortcuts →