Learn Excel by typing into a real spreadsheet

A spreadsheet is a grid of cells, each named by its column letter and row number, so C4 is the cell in column C, row 4. You click a cell, type into it, and press Enter. A formula is anything you type that starts with an equals sign: it points at other cells instead of holding a value of its own, and it recalculates whenever those cells change.

A range is a rectangle of cells, written as its top-left corner, a colon, and its bottom-right corner: B2:B6 is the five cells from B2 down to B6, and A1:C10 is the whole block thirty cells wide. Every Excel function that works on more than one cell expects this notation, which is why it is worth making automatic before meeting any of them.

An Excel formula starts with an equals sign and uses +, -, * and / to combine cell references. Excel does multiplication and division before addition and subtraction regardless of the order you typed them, so =B2+C2*0.2 adds only the VAT, not the VAT on the total. Brackets force the order you meant: =(B2+C2)*0.2.

SUM adds a range, AVERAGE takes its mean, MIN and MAX find the extremes, and COUNT says how many cells hold a number. All five ignore blank cells and text rather than treating them as zero, which is why AVERAGE over a column with a gap divides by the number of real entries, not by the number of rows.

A reference without dollar signs is relative: copy the formula down a row and B4 becomes B5. A dollar sign freezes the part it precedes, so $B$1 stays B1 wherever the formula is copied. Mixed forms lock one half: B$1 keeps the row but lets the column move, and $B1 does the opposite.

IF takes three things: a test, what to return when the test is true, and what to return when it is false, as in =IF(B2>=50,"Pass","Fail"). The syntax is rarely the difficulty. The boundary is: >= includes the row sitting exactly on the threshold and > excludes it, and with a pass mark of 50 that is the difference between passing and failing a student who scored 50.

COUNTIF asks how many cells in a range meet one condition, and SUMIF adds up a second range for the rows that do. The condition is one argument, so a comparison has to be written as text in quotes, as in ">1000": the operator and the number are handed over together.

Text functions cut, join and clean text that arrived in the wrong shape. TRIM removes stray spaces, LEFT, MID and RIGHT pull out characters by position, CONCAT or the & operator join values together, and SUBSTITUTE swaps one piece of text for another. TRIM is the one to reach for first: an invisible trailing space is the usual reason a lookup reports a name as missing when it is plainly in the list.

VLOOKUP finds a row by the value in the table's first column and reads across to a column you number: =VLOOKUP("E-125", A2:D6, 2, FALSE). The fourth argument is the one that matters. FALSE demands an exact match; leaving it off tells Excel to accept the nearest match, which on unsorted data returns a row from somewhere else without any error at all.

Sorting must move whole rows, not one column. Select a single cell inside the table and let Excel work out its extent, or select every column before sorting: sorting one column on its own detaches it from the rest of its row and there is no error and no easy way back. Filtering is safe by comparison because it only hides rows; Remove Duplicates is not, because it deletes them.

The chart type follows from the question. A column chart compares categories and answers which is biggest. A line chart shows change over time and answers which way is this going. A pie chart shows parts of one whole and is only legitimate when the slices genuinely add up to 100% of something.

Percentage change is new minus old, divided by old: =(C2-D2)/D2. The denominator is always the earlier figure, and getting it the wrong way round produces a number that looks reasonable and is not. A weighted average is SUMPRODUCT of the values and their weights divided by the total weight, which is a different number from AVERAGE whenever the rows are different sizes.

Not every column of digits is numerical. A postcode is a label that happens to be written with numbers, and averaging it is meaningless. The four levels of measurement, nominal, ordinal, interval and ratio, each permit a different set of operations, and the level is a fact about what the data represents rather than about how it is stored.

An average with no measure of spread beside it is half a summary. Two sets of marks can share a mean of 64 and describe completely different classes: one where everybody scored in the low sixties and one running from 30 to 96. The mean says where the middle is; the standard deviation or the quartiles say whether anybody is actually near it.

Excel stores a date as a number counting days from a fixed starting point, and the date you see is that number wearing a format. This is why subtracting one date from another gives you a plain number of days, why a date typed as text does nothing useful, and why sorting dates stored as text puts them in alphabetical order.

The dangerous spreadsheet faults are the ones that produce no error. A total whose range stops one row short still calculates, a number stored as text is silently skipped by SUM, and a deleted leading zero makes a code stop matching. Every one of these leaves a sheet that looks finished and is wrong, which is why they are worth learning to spot deliberately.

The method for an unfamiliar spreadsheet is the same every time: find out where the numbers come from before changing anything, check the totals against something independent, and look hardest at the cells that currently look correct. A formula that happens to give the right answer today is a fault that has not surfaced yet.