Every lesson

Every cell has an address: its column letter and its row number. B4 is column B, row 4.

The arrow keys move the selected cell. Hold Shift to grow the selection. Hold Ctrl to jump to the end of the data.

A range is a block of cells. You write it as two addresses with a colon: B2:D5 means every cell from B2 to D5.

SUM adds up every number you give it. That can be a range with a colon, or a list of cells with commas.

COUNT tells you how many cells in a range hold a number. COUNTA tells you how many cells hold anything.

Ctrl+D copies the top cell of a selection into the cells below it. Ctrl+R copies the left cell across. Every reference moves with the copy.

A relative reference describes where a cell is compared to the formula. Copy the formula somewhere else, and the reference moves by the same amount.

A dollar sign in front of part of a reference stops that part from changing when you copy the formula. So $B$7 always points at B7.

A mixed reference locks the column or the row, but not both. $B4 always reads column B while its row moves. B$4 always reads row 4 while its column moves.

Filling copies a formula into the cells next to it. Every relative reference moves by the same distance. So one formula can total 200 rows.

Copying a formula copies its meaning, not its text. Relative references move to match the new position. Locked references keep pointing at the same cell.

Relative references move when a formula is copied. Absolute references never move. Mixed references move in one direction only. Most real formulas use more than one kind.

F2 opens the selected cell for editing. Escape cancels the edit. Enter and Tab confirm it and move down or right. F4 changes the reference under the cursor between relative, absolute and mixed.

Ctrl+C, Ctrl+X and Ctrl+V copy, cut and paste cells. Ctrl+D and Ctrl+R fill a selection down or to the right from its first cell. Ctrl+Z undoes the last action.

Ctrl+B, Ctrl+I and Ctrl+U switch bold, italic and underline. Alt+= writes a SUM over the numbers above the cell. Ctrl+; enters today's date as a fixed value. Ctrl+Enter fills every selected cell with one entry.

Ctrl with an arrow key jumps to the edge of the filled data in that direction. Ctrl+Shift with an arrow selects everything up to that edge. Home returns to the first cell of the current row.

AVERAGE adds up the numbers in a range and divides by how many there are. It skips empty cells and text, but it counts every zero.

MAX returns the largest number in a range and MIN the smallest, both skipping empty cells and text.

ROUND takes a number and a number of decimal places and returns the nearest value. ROUNDUP always rounds away from zero. ROUNDDOWN always rounds towards it.

A percentage in Excel is a plain fraction. A share is the part divided by the whole. A change is the difference divided by the old value. The percent sign is only a cell format.

LARGE returns the k-th largest number in a range. SMALL returns the k-th smallest. So LARGE(range,2) is the second-highest value.

MEDIAN returns the middle value of a range after sorting. MODE returns the value that appears most often. One extreme number does not move either of them much.

A running total is SUM over a range with a locked start and a relative end. Each filled-down copy adds one more row.

A comparison such as B2>B8 is a formula that returns TRUE or FALSE. Every IF, COUNTIF and filter in Excel is built on one.

Excel has six comparison operators: greater than, less than, equal, not equal, at least and at most. Each returns TRUE or FALSE for any two values, numbers or text.

IF checks a test. It returns one value when the test is TRUE and another when it is FALSE. So a cell can decide for itself what to show.

AND returns TRUE only when every test inside it is TRUE. OR returns TRUE when any one is. NOT reverses a test. So a rule with several conditions can sit inside a single IF.

A nested IF puts a second IF in the first one's no-value. So several bands can be tested in order. Once the bands pass three, a band table with an approximate-match lookup does the same job.

IFS returns the value beside the first of its test-value pairs whose test is TRUE. SWITCH returns the result beside the first value that matches the one it is given. Each can have a default at the end.

SUMIF adds the cells in one range where the matching cell in another range meets a condition. So a total can be limited to one trader, one type, or everything over a limit.

SUMIFS adds the cells in one range where every condition in the pairs that follow holds. The range to add is named first. Each condition is joined by and.

COUNTIF counts the cells in a range that meet a condition. The condition can be a word, a number, a comparison in quotation marks, or a cell holding one.

COUNTIFS counts the rows where every pair of range and condition holds. An or-question is answered by adding two COUNTIFS together.

AVERAGEIF averages the cells in one range where the matching cell in another range meets a condition. It skips empty cells and divides only by the numbers it found.

One sheet can need a plain total, a count, a lookup, a conditional total and an IF. The skill is knowing which question each one answers.

VLOOKUP searches the first column of a table for a value. Then it returns a value from the same row, in a column you choose.

HLOOKUP searches the first row of a table for a value. Then it returns a value from the same column, in a row you choose.

XLOOKUP takes the range to search and the range to return as two separate arguments. So it can look in any column and return any other.

INDEX returns the value at a position inside a range. The third cell down a column, or the cell in row 2, column 3 of a block.

MATCH searches a range for a value and returns its position in that range, as a number.

MATCH finds which row a value is in. INDEX returns the value in that row from any column. Together they look up in any direction.

An approximate match is VLOOKUP's TRUE or XLOOKUP's match mode -1. It returns the row whose first-column value is the largest one not above the lookup value. That is how a band, a tax bracket or a shipping rate is found.

XLOOKUP takes its search column and return column separately. So it can return from the left. Its fourth argument is a not-found value. Its fifth is a match mode: -1 for next smaller, 1 for next larger.

XMATCH returns the position of a value in a range, exact by default. It has the same match modes as XLOOKUP. INDEX reads whatever sits at that position in another range.

A two-way lookup finds a value by a row label and a column label at once. INDEX over the block, with one MATCH supplying the row number and another the column number.

A lookup on two criteria has two answers. SUMIFS over the matching rows, when the value is a number and the pair of criteria matches exactly one row. Or a helper column that joins the criteria into one key for XLOOKUP to search.

Choosing a lookup comes down to three questions. Is the key unique? Does the answer sit to the right of it? Is the match exact or a band? A repeating key means a conditional function, not a lookup at all.

IFERROR runs a calculation and returns its result. If the result is an error, it returns the second argument instead.

IFNA returns a calculation's result, unless that result is #N/A. Then it returns the value you gave instead. Every other kind of error stays visible.

The IS functions ask a yes-or-no question about a cell: ISNUMBER, ISTEXT, ISBLANK, ISERROR and ISNA. They return TRUE or FALSE. So a formula can act on what a cell really holds, not what it looks like.

A division by zero in Excel returns #DIV/0!. The cleanest guard is an IF that tests the divisor first. It names the condition, and it can say something useful when it happens.

#N/A means VLOOKUP searched and did not find a match. There are only four reasons that ever happens.

Every Excel error is one of three things. A mistake in the formula to fix. A fact about the data to trap with IFNA or IFERROR. Or a fault to flag with an IS function. The skill is telling which.

LEN returns the number of characters in a piece of text. It counts spaces and punctuation the same as letters.

LEFT returns a given number of characters from the start of a piece of text. RIGHT returns them from the end.

MID returns a run of characters from anywhere inside a piece of text. You give it where to start and how many to take.

FIND returns the position of one piece of text inside another, counting from 1. It treats capitals and lower case as different characters.

SEARCH returns the position of one piece of text inside another. It ignores capitals, and it accepts wildcards where FIND does not.

Putting FIND inside LEFT splits text at a character whose position you cannot predict. So one formula takes the first name out of every row, whatever the names are.

Extracting text in Excel means deciding where a piece starts and how long it is. Count when the format is fixed. Find a character when it is not.

The ampersand joins pieces of text end to end. So a first name, a space in quotation marks and a last name become one full name.

TEXTJOIN joins everything in a range into one piece of text, with a separator between the parts. It can skip the empty cells while it does it.

UPPER turns text into capitals. LOWER turns it into small letters. PROPER capitalises the first letter of every word and lowers the rest.

TRIM removes spaces from the start and end of a piece of text. It also shrinks any run of spaces inside it down to one.

SUBSTITUTE replaces one piece of text inside another. It changes every occurrence unless you tell it which one you mean.

VALUE turns text that looks like a number into a real number. TEXT turns a number into text, formatted the way you say.

Cleaning imported text usually means four things. Trim the spaces, fix the case, replace the separators, and convert anything numeric back into a number.

TEXTBEFORE returns the part of a text before a delimiter you name. An optional third argument says which occurrence of the delimiter to stop at.

TEXTAFTER returns the part of a text after a delimiter you name. An instance of -1 makes it the part after the last occurrence.

REPLACE swaps a run of characters, chosen by starting position and length, for new text. It does not care what those characters are. A length of zero turns it into an insert.

A full name splits into first and last with TEXTBEFORE and TEXTAFTER at the space. An instance of -1 on TEXTAFTER makes the last name the last word, even when there is a middle name.

Any delimited line splits three ways. TEXTBEFORE for its first part. TEXTAFTER with -1 for its last. TEXTBEFORE around TEXTAFTER for a part in the middle.

Excel stores a date as a plain number. It counts the days since the start of 1900. The date you see on screen is a format laid over that number.

TODAY returns the current date as a serial number. NOW returns the current date with the time of day added as a decimal fraction.

YEAR, MONTH and DAY each take a date and return one part of it as a plain number.

DATE takes a year, a month and a day as three separate numbers. It returns the date they make.

Subtracting one date from another in Excel gives the number of days between them. Both are counts of days to begin with.

DATEDIF returns the number of complete years, months or days between two dates. The unit you ask for decides which.

WEEKDAY returns which day of the week a date falls on, as a number. EOMONTH returns the last day of a month a given number of months away.

Every date question in Excel comes back to the same fact. A date is a count of days. So subtracting gives days, adding gives a later date, and comparing sorts them correctly.

Days left until a deadline is the due date minus the as-of date. Dates are serial numbers, so the result is negative once the deadline has passed.

An age or a length of service in Excel is DATEDIF from the earlier date to the as-of date, with the unit y. That counts only the complete years between them.

NETWORKDAYS counts the Monday-to-Friday days between two dates, both ends included. Any holidays you list are left out. WORKDAY returns the date a given number of working days from a start.

A monthly total in Excel is SUMIFS with two conditions on the date column. On or after the month's first day, and on or before the day EOMONTH returns. So one formula fills down a column of months.

UNIQUE returns the distinct values in a range as an array. COUNTA around it counts how many different values there are.

SORT returns a range in order as an array, without changing the original. It takes the column to sort by, and 1 or -1 for the direction. INDEX reads any position of the result.

FILTER returns the rows of a range where a test is TRUE, as an array. Any aggregate wrapped round it, SUM, ROWS, AVERAGE or MAX, summarises just those rows.

SEQUENCE generates a series of numbers as an array, from a count, a number of columns, a start and a step. Wrapped in SUM or INDEX, it feeds another calculation without the numbers ever being typed.

A frequency table in Excel is a UNIQUE list of the labels, with a COUNTIF beside each one. SUMIF or AVERAGEIF in further columns give the other summaries.

The dynamic array functions produce lists. UNIQUE the distinct values, FILTER the rows that pass a test, SORT the range in order, SEQUENCE a series. Wrapped in COUNTA, SUM, ROWS or INDEX, each list becomes one number or one cell.

SUBTOTAL applies one of nine aggregates to the ranges that follow. Its first argument chooses which. 9 for SUM, 1 for AVERAGE, 3 for COUNTA, 4 for MAX. It is the function a sectioned report puts under each section.

AVERAGEIFS averages the cells in one range where every condition in the pairs that follow holds. The numbers come first and the range-condition pairs after, in the same order as SUMIFS.

MAXIFS returns the largest value in a range where every condition holds. MINIFS returns the smallest. Both use SUMIFS' argument order, and both return 0 when no row matches.

SUMPRODUCT multiplies ranges together row by row and adds the results. That gives an order value without a line-total column. Divided by the total weight, it gives a weighted average. With a comparison in brackets as one of the ranges, it gives a conditional sum.

A pivot table is a short summary of a long list: one row per group, with a total or a count for each. In Excel it is a built-in tool. Here you build the same summary with SUMIF and COUNTIF, so you can see exactly what the tool does.

A pivot table with rows and columns has one group down the side and another across the top, and a total where each row meets each column. Here you build it with one SUMIFS and mixed references, so the same formula fills every cell of the table.

A pivot table is right when its total equals the total of the list it summarises. This lesson makes that check in one cell, then reads each group's share of the whole and checks that the shares add to one.

A sales report is three things. A row-level total filled down. A summary block of SUMIFS with locked data ranges and unlocked labels. And a lookup that turns the biggest number into a name.

An invoice is a line total filled down, and a SUM for the subtotal. The discount and the tax are each rounded to pennies with ROUND, as values. The total then adds exactly what the customer sees.

A grade book is an average rounded as a value, and a letter grade from a boundary table by approximate match. A summary block of COUNTIF and AVERAGEIF reads those two columns.

A budget-against-actual sheet is actual minus budget for the variance, and a guarded division for the percentage. A nested IF gives the flag. SUMIF and COUNTIF over those columns give the summary.

A reorder list is an IF flag comparing stock with its reorder level, and a lookup from a code list for the supplier. Conditional functions over the flag column say what to chase.

A timesheet is overtime as MAX of hours-over-standard and zero. Pay is ordinary hours at one locked rate plus overtime at another. Underneath sit a SUM, a NETWORKDAYS and a SUMIF.