Revision notes

Spreadsheet: Practice

The key points, rules to remember, tables and diagrams from all 5 lessons, on one page for quick revision. To keep a copy, use your browser's Print and choose "Save as PDF".

Lesson 1

Formula evaluation drills: order of operations, comparisons, percentages and text joins

FormulaWrong readingCorrect workingShows
=20/4*220 ÷ 8 = 2.520 ÷ 4 = 5, 5 × 2 = 1010
=10-3-210 − 1 = 910 − 3 = 7, 7 − 2 = 55
=2*3^26² = 363² = 9, 2 × 9 = 1818
=(2*3)^2-6² = 3636
=18-6/3*212/6 = 26 ÷ 3 = 2, 2 × 2 = 4, 18 − 4 = 1414

Brackets → ^ → × ÷ (left to right) → + − (left to right) → & → comparisons. 10% = 0.1, so 'add 10%' is ×(1+10%), never +10%.

Key takeaways: run the four passes every time; treat × and ÷, and + and −, strictly left to right; read % as ÷ 100; bracket the numerator in a percentage change; and expect TRUE or FALSE from a comparison.

Read the full lesson

Lesson 2

Relative, absolute and mixed references: copy-and-predict tasks

ReferenceColumn when copied acrossRow when copied down
A1 (relative)changeschanges
$A$1 (absolute)fixedfixed
$A1 (mixed)fixedchanges
A$1 (mixed)changesfixed
ItemABC
1BranchSalesShare
2North400=B2/$B$6 → 40%
3South250=B3/$B$6 → 25%
4East150=B4/$B$6 → 15%
5West200=B5/$B$6 → 20%
6Total=SUM(B2:B5) → 1000

New formula = old formula with every un-dollared row moved by the rows copied and every un-dollared column moved by the columns copied.

Key takeaways: measure the move, shift only the parts without $, then evaluate. Use $A$1 for one shared cell such as a rate or a total, $A1 or A$1 for tables that grow both ways, and remember that moving (cut) never changes references while copying does.

Read the full lesson

Lesson 3

Functions in practice: a complete marksheet with SUM, AVERAGE, IF, nested IF, COUNTIF and text functions

The finished marksheet - every number below was produced by the formulas
NameMathsScienceEnglishTotal (E)Average (F)Grade (G)Result (H)
Meena78856923277.33APass
Arjun45523813545CFail
Farah92889527591.67APass
Kabir60617319464.67BPass
Lata33705816153.67CFail

Nest to combine: ROUND(AVERAGE(...),2). Nested IF: test bands in order, highest first. COUNTIF(range,"criterion") counts; SUMIF(range,"criterion",sum_range) adds.

Key takeaways: write each formula once in row 2 and fill down; nest functions to combine steps; order nested IF tests carefully; use MIN inside IF to require every subject; and use COUNTIF and SUMIF for summaries with a condition.

Read the full lesson

Lesson 4

Data tasks: series, dates and formats, multi-level sorting, filtering and freeze panes

Apply the percentage format before typing, or type the % sign
You doStored valueDisplayed
Type 7%0.077%
Type 0.07, then apply Percentage0.077%
Type 7, then apply Percentage7700%
Type 2500 with Currency, 2 decimals2500₹2,500.00
Original orderAfter Class ↑ then Marks ↓
Ravi, class 10, 81Uday, class 9, 88
Sana, class 9, 74Sana, class 9, 74
Tara, class 10, 95Tara, class 10, 95
Uday, class 9, 88Ravi, class 10, 81
Vikas, class 10, 67Vikas, class 10, 67

Date + number = later date; date − date = days. Two keys: the second breaks ties in the first. Freeze at (first column not kept, first row not kept).

Key takeaways: give the fill handle two values or use Fill Series for exact steps; calculate with dates directly; type % or format before typing; use a second sort key for ties; filters hide but do not delete; and freeze at the cell below-right of what must stay visible.

Read the full lesson

Lesson 5

Charts, printing and fixing error values: troubleshooting tasks

ShowsCauseTypical fix
#DIV/0!A divisor is 0 or an empty cell=IF(C2=0,"-",B2/C2), or fill in the missing value
#NAME?Misspelt function (SUMM), text without quotes (Pass instead of "Pass"), or a range missing its colonCorrect the spelling; put text in double quotes
#VALUE!Text where a number is needed, e.g. "12 kg" in an additionKeep units in the heading and only numbers in cells
#REF!A cell the formula used was deletedUndo, or rewrite the formula with valid cells
#N/AA lookup could not find its valueCheck the value being looked up
#####Column too narrow (or, in Excel, a negative date or time)Widen the column

#DIV/0! divide by 0 or blank · #NAME? unknown name or missing quotes · #VALUE! wrong data type · #REF! deleted cell · ##### narrow column. Excel: F11 = chart sheet.

Key takeaways: read the error value as a diagnosis, keep only numbers in number cells, guard divisions with IF, and keep totals out of the range they add. Pick the chart from the question being asked, and preview, orient, scale and repeat headings before printing.

Read the full lesson

Privacy · Terms · Refunds · Delivery