Spreadsheet basics: cells, formulas and functions

§Text Passage

1Summary 重點摘要

Cells are addressed by column letter plus row number (e.g. B3); formulas start with '='; SUM adds, AVERAGE averages, COUNT counts; sort and filter organise data fast.

2Vocabulary cards Vocabulary

3Step by step Steps

1. Know your cell addressesOpen a spreadsheet and you see a large grid of cells: columns are labelled A, B, C and rows 1, 2, 3, and together they form cell addresses — C4 is the cell where column C meets row 4. Each cell can hold text, a number or a formula. Click any cell and the formula bar above shows its real content. Spend a minute moving around with the arrow keys, reading each address aloud: column letter first, row number second.
2. Your first formulaSuppose A1 to A3 hold 10, 20 and 30. Select A4, type '=A1+A2+A3' and press Enter; A4 immediately shows 60. Every formula must begin with '=' — the sign tells the spreadsheet this is a calculation. Without it, A1+A2 is treated as plain text and never calculated. Formulas also update automatically: change A1 from 10 to 5 and A4 instantly falls from 60 to 55, with no manual recalculation.
3. Adding up with SUMSUM is the most common adding function. If B2 to B6 hold five team scores 8, 9, 7, 10 and 6, typing '=SUM(B2:B6)' in B7 gives a total of 40. The colon marks a continuous range: B2:B6 means every cell from B2 to B6. Check by hand: 8+9+7+10+6 = 40. When a sixth member joins, just widen the range to B2:B7 and the total updates itself — far easier and safer than adding cell by cell.
4. Averaging with AVERAGEAVERAGE finds the mean of the numbers in a range. For the same scores, '=AVERAGE(B2:B6)' in B8 gives 40÷5 = 8. Averages make fair comparisons between groups of different sizes: team A has 5 players and 40 points while team B has 8 players and 56 points — by total B looks far stronger, yet by average A wins 8 to 7. Take care not to include the total cell inside the range, or the mean will be distorted.
5. Counting with COUNTCOUNT only counts cells that contain numbers. If B2 to B20 record homework marks and 18 of the 20 cells are filled while 2 are blank, '=COUNT(B2:B20)' returns 18 — so 18 students have handed in. COUNT ignores text; to count text entries such as grades, use COUNTA instead. The function suits quick headcounts, such as attendance or the number of questionnaires collected.
6. Ranking with SortSuppose the class mark sheet occupies A1:C31 — column A for student numbers, B for names and C for totals. To rank the class, select the whole range A1:C31 (names included) and choose sort largest to smallest; the top scorer jumps to the first row. Never sort column C alone, or scores will be mismatched with names. If the first row holds headings, tick the option that keeps the header row out of the sort.
7. Pulling out targets with FilterFilter temporarily hides rows that fail a condition. With filter buttons on, choose 'less than 50' in the Chinese subject column and only failing students remain — handy for arranging remedial lessons. Click clear filter and every row returns; the two views switch in a second. Filter and sort often work together: filter out the target students first, then sort by score to spot those who need the most help.

4Diagrams Diagrams

5Worked example Example

6Common mistakes Mistakes

7Quick check Quick check

8Recap Recap

• A cell address = column letter + row number, e.g. B3.
• Formulas always start with '='; results update when numbers change.
• SUM adds, AVERAGE averages, COUNT counts numeric cells, MAX/MIN find extremes.
• B2:B10 means the continuous range from B2 to B10.
• Sort with the whole row selected; Filter shows only rows matching a condition.