How to read this sheet

Every formula starts with =. Each card gives a pattern (the template) and usually an example with the result after an arrow. Words like range and criteria are placeholders, swap in your own cells.

AVERAGEfunctions$B$2:B13cell references"text"text values123numbers

One formula, all the moving parts

=ROUND(AVERAGE(B2:B13) * $E$1, 2)

AVERAGE runs over the range B2:B13, the result is multiplied by $E$1 (pinned, so filling the formula down never drags it off the rate cell), and ROUND trims the answer to 2 decimal places.

References & Fill

Visual reference

A $ pins the part of the reference it stands in front of.

A1No handsRow and column both slide when filled

$A1Column pinnedFill sideways and it stays in column A

A$1Row pinnedFill down and it stays on row 1

$A$1Both pinnedNever moves, point at one shared cell

=B2*$E$1=B3*$E$1=B4*$E$1

filled down three rows:B2 → B3 → B4 slides,$E$1 stays put.

Cell references

A formula reads other cells by address. Fill it down or across and plain references slide with it.

Pattern

=B2*C2

Example

=B2-C2      filled down a column
=SUM(B2:B13)   a range, corner to corner

$ pinning

A $ freezes part of a reference during fill. Pin the one shared cell (a rate, a total) and let the rest slide.

Example

=B2/$B$14     each row over one pinned total
=B$1*$A2      two-way fill across a grid

TipThe tell that a pin is missing: the first row works, then the fill turns to #DIV/0! or zeros as the shared cell slides off the data.

Cross-sheet references

Read another sheet with SheetName! in front of the address. Works inside ranges and lookup tables too.

Example

=Ledger!D14
=SUM(March!B2:B31)

Adding Up

SUM / AVERAGE / MIN / MAX

The aggregate family: total, mean, smallest, largest of a range.

Example

=SUM(B2:B13)
=AVERAGE(C2:C31)
=MIN(D2:D20)  =MAX(D2:D20)

COUNT vs COUNTA

COUNT counts numbers only. COUNTA counts any non-empty cell.

Example

=COUNT(B2:B20)    → 18  (two cells hold text)
=COUNTA(B2:B20)   → 20

TipA gap between the two is a finding: something in that column is text wearing a number's clothes, or a ghost entry.

ROUND / ROUNDUP / ROUNDDOWN

Trim a number to a set number of decimal places.

Example

=ROUND(B2*1.175, 2)
=ROUNDUP(C2, 0)     always away from zero

RANK / LARGE / SMALL

Where a value places, and the Nth biggest or smallest outright.

Example

=RANK(B2, $B$2:$B$13)
=LARGE($B$2:$B$13, 2)   second biggest
=SMALL($B$2:$B$13, 1)   the smallest

SUMPRODUCT

Multiply two ranges pair by pair and total the lot in one move.

Example

=SUMPRODUCT(B2:B13, C2:C13)
   qty × price, summed

Decisions

IF

One test, two outcomes. Quote text outcomes, leave numbers bare.

Pattern

=IF(test, value_if_true, value_if_false)

Example

=IF(B2>100, "FLAG", "")
=IF(C2="Cash", B2*0.05, 0)

IFS

Several tests in order; the first true one wins. Put the strictest band first.

Example

=IFS(B2>=500, "High",
     B2>=200, "Medium",
     B2>=0,   "Low")

TipOrder matters. Start the bands at the wrong end and every row lands in the first, loosest test.

AND / OR / NOT

Combine tests inside an IF: AND wants every test true, OR wants any.

Example

=IF(AND(B2="REAR", C2=""), "FLAG", "")
=IF(OR(D2>90, E2>90), "CHECK", "OK")

IFERROR

Catch a formula that errors and show something calmer instead.

Example

=IFERROR(VLOOKUP(A2, $F$2:$G$9, 2, FALSE),
         "not on file")

TipWrap the lookup only once you know what #N/A means there. An error you silence is an error you stop seeing.

Counting with Criteria

Visual reference

One rule, ">100", catches three rows. Every -IF function starts exactly here.

402509039060350

COUNTIF → 3 rows caughtSUMIF → 990 their totalAVERAGEIF → 330 their meanMAXIFS → 390 their biggest

COUNTIF / SUMIF / AVERAGEIF

Count, total or average only the rows that match one condition.

Pattern

=COUNTIF(range, criteria)
=SUMIF(criteria_range, criteria, sum_range)

Example

=COUNTIF(C2:C31, "Cash")
=SUMIF(A2:A31, "Meridian", D2:D31)
=AVERAGEIF(B2:B31, ">0")

Criteria strings

Comparisons go in quotes. Build a criteria from a cell with &.

Example

">100"    "<>Cash"    "<=0"
=COUNTIF(B2:B31, ">"&E1)

COUNTIFS / SUMIFS / AVERAGEIFS

The plural forms take pairs of range-and-criteria; every pair must match.

Example

=COUNTIFS(A2:A31, "Meridian",
          B2:B31, ">100")
=SUMIFS(D2:D31, A2:A31, "Meridian",
        C2:C31, "<>Cash")

TipSUMIFS puts the sum range FIRST; SUMIF puts it last. The desk will remind you, but interviews will not.

MAXIFS / MINIFS

The biggest or smallest value among rows matching the criteria.

Example

=MAXIFS(D2:D31, A2:A31, "Meridian")
=MINIFS(B2:B31, C2:C31, "Cash")

Lookups

Visual reference

The wash is where the lookup SEARCHES; the arrow ends where the answer comes from.

VLOOKUPDown the FIRST column, then right

HLOOKUPAcross the first ROW, then down

XLOOKUPAny column to any other, left included

INDEX+MATCHMATCH finds the position, INDEX fetches it

Arrow found nothing? That is #N/A, evidence of absence, not a broken formula.

VLOOKUP

Find a value in the FIRST column of a table, return a cell from the same row.

Pattern

=VLOOKUP(value, table, col_number, FALSE)

Example

=VLOOKUP(A2, $F$2:$H$9, 3, FALSE)

TipEnd with FALSE for an exact match. Leave it off and VLOOKUP settles for the nearest value below, silently, which is how wrong numbers look right.

Approximate match (bands)

The one legitimate use of TRUE: a sorted band table, where each row means "this value and up".

Example

=VLOOKUP(B2, $E$2:$F$5, 2, TRUE)
   rate card: 0 / 100 / 250 / 500 bands

HLOOKUP

VLOOKUP turned sideways: searches the first ROW of a table.

Example

=HLOOKUP(B1, $A$1:$F$3, 3, FALSE)

XLOOKUP

Search one range, return from another. Looks left, no column counting, and says what to show when the value is missing.

Pattern

=XLOOKUP(value, search_range, return_range,
         if_not_found)

Example

=XLOOKUP(A2, Roll!B2:B9, Roll!A2:A9,
         "unknown")

MATCH / INDEX

MATCH finds WHERE a value sits; INDEX fetches BY position. End MATCH with 0 for exact.

Example

=MATCH("Whitcombe", A2:A9, 0)  → 4
=INDEX(C2:C9, 4)
=INDEX(B2:E9, 3, 2)   row 3, column 2

INDEX + MATCH

The combination: look up in ANY column, return from any other, including the left walk VLOOKUP refuses.

Example

=INDEX(A2:A9, MATCH(D2, C2:C9, 0))

Text & Cleaning

LEN / TRIM

LEN counts every character, including the spaces you cannot see. TRIM strips stray outer spaces.

Example

=LEN(A2)        "Meridian Co "  → 12
=TRIM(A2)       → "Meridian Co"

TipTwo entries that look identical but LEN differently are not identical. That one extra character is why COUNTIF finds nothing.

UPPER / LOWER / PROPER

Force a casing. PROPER capitalises Each Word.

Example

=PROPER("e. mortlake")  → "E. Mortlake"

LEFT / RIGHT / MID

Slice a fixed number of characters from either end, or from the middle by position.

Example

=LEFT(A2, 3)      first 3 characters
=RIGHT(A2, 4)     last 4
=MID(A2, 5, 2)    2 chars from position 5

FIND / SEARCH

The position of one text inside another. FIND is case-sensitive, SEARCH is not. Feed the position into MID.

Example

=FIND("-", A2)          first dash
=FIND("-", A2, 6)       from position 6
=MID(A2, FIND("-", A2)+1, 4)

SUBSTITUTE / VALUE

SUBSTITUTE swaps text out; VALUE turns number-shaped text into a real number.

Example

=VALUE(SUBSTITUTE(B2, ",", ""))
   "1,250" the text  → 1250 the number

TipThe tell for text-stored numbers: SUM over a full column returns 0. The cells hold writing, not values.

TEXT and the & glue

TEXT formats a number as text (padding, date dressing); & joins pieces together. TEXTJOIN glues a range with a divider.

Example

=TEXT(B2, "000")   7  → "007"
=A2 & "-" & B2     manufactured key
=TEXTJOIN(", ", TRUE, A2:A5)

Dates

Visual reference

Peel the date format back and there is a plain number underneath. That number is what subtracts, compares and sorts.

14/02/202646067

14/03/202646095

=C2-B2→ 28 days

The serial underneath

A date IS a number: day N of Excel's count, which starts at 1 January 1900, wearing a date format. That is why dates subtract, compare and sort.

Example

=C2-B2      days between two dates
=IF(B2>C2, "BACKDATED", "")

TODAY / YEAR / MONTH / DAY

Today's date, and the parts of any date.

Example

=TODAY()-B2     age in days
=MONTH(B2)      → 3   for a March date

WEEKDAY

Which day of the week a date is (1 = Sunday through 7 = Saturday).

Example

=WEEKDAY(B2)   → 1 means a Sunday stamp

EOMONTH / DATEDIF

The end of a month relative to a date, and the gap between dates in days, months or years.

Example

=EOMONTH(B2, 0)     end of B2's month
=DATEDIF(B2, TODAY(), "d")   days since

NETWORKDAYS / DATEVALUE

Working days between two dates (weekends out), and the rescue that turns date-shaped text into a real date serial.

Example

=NETWORKDAYS(B2, C2)
=DATEVALUE(SUBSTITUTE(B2, ".", "/"))

Errors Decoded

Error	What it means	First thing to check
#NAME?	Excel does not recognise a name in the formula.	Usually a misspelt function: =SUMM(...) instead of =SUM(...).
#VALUE!	A value of the wrong kind, like text where a number belongs.	Check what each argument holds. Text-stored numbers are the classic cause.
#DIV/0!	A division hit zero or an empty cell.	After a fill, this is the tell that a shared cell needed a $ pin.
#REF!	The formula points at cells that no longer exist.	A referenced row or column was deleted, or INDEX asked past the range.
#N/A	A lookup searched and found nothing.	Not breakage, evidence: the value is absent. Wrap in IFERROR only when absence is expected.

Hover any error on the desk and it explains itself. A formula that refers back to its own cell is refused as a circular reference too, so a running total can never quietly eat itself.

The Ribbon

Visual reference

The same four numbers, twice. The only difference is where the value axis starts.

Axis from 0: a gentle drift

Axis from 340: a manufactured cliff

Before trusting any chart, read the value axis floor. Format Axis resets it to automatic.

Sort & Freeze Panes

Sort re-orders whole rows together, never one column alone. Freeze Panes (View tab) keeps headers in sight while you scroll.

AutoFilter

A filter is a lens, not a knife: hidden rows still exist and plain SUM still reads straight through them. Use the criteria family when the number must respect a condition.

Find & Replace

Replace All has a blast radius: it hits every match on the sheet, including ones inside longer words. Sharpen the needle before firing, and remember undo is a tool.

Text to Columns / Remove Duplicates

Text to Columns splits one packed column on a divider. Remove Duplicates keeps the first of each repeated row, so check what it counts as a duplicate first.

Paste Values

Pastes the results of formulas as plain values, cutting them loose from the cells they read. The way to ship a clean sheet.

Conditional Formatting

Paint cells by rule: Greater Than, Duplicate Values, colour scales, data bars. Rules STACK on a range. Colour is for seeing; prove the finding with COUNTIF in ink.

Charts

A chart is a live view of its cells, floating on the sheet: fix the cell and the picture follows. Columns compare, lines trend. Read the value axis before trusting a cliff, a truncated axis turns drift into drama.

PivotTables

Drag fields into Rows and Values to summarise a table without formulas: Sum, Count, or Show Values As % of Grand Total. A pivot is a snapshot, Refresh after the data changes. Double-click a number to see the rows behind it.