Formulas & Functions

Operators, references, lookup formulas, logical tests, text cleanup, date math, error handling, and modern dynamic array patterns.

View subtopics All topics

=SUBTOPICS

What to learn inside Formulas & Functions

Each item pairs the learning structure with curated public YouTube resources where available, so learners can move from outline to video practice quickly.

01Beginner

Formula syntax and operators

Learn how formulas are structured, how order of operations works, and how to read nested formulas.

SUM()AVERAGE()

YOUTUBE

02Beginner

Relative, absolute, and mixed references

Use A1, $A$1, A$1, and $A1 correctly when copying formulas across rows and columns.

QUICK NOTES

Press F4 while a reference is selected in the formula bar to cycle through A1, $A$1, A$1, and $A1 without retyping the $ signs.
$ locks whatever it's directly in front of -- $A1 locks the column, A$1 locks the row, $A$1 locks both.
Before copying a formula across a range, drag-fill one row/column first and check the result matches expectations -- a missing $ usually shows up immediately as a wrong number.

03Intermediate

Lookup formulas

Find matching values across tables using modern and classic lookup functions.

XLOOKUP()VLOOKUP()INDEX()MATCH()

YOUTUBE

04Intermediate

Conditional logic

Create formulas that branch, test multiple conditions, and handle fallback cases cleanly.

IF()IFS()AND()OR()IFERROR()

YOUTUBE

05Intermediate

Text formulas

Clean, split, join, and extract text values from messy spreadsheet fields.

LEFT()RIGHT()MID()TRIM()TEXTJOIN()

QUICK NOTES

TEXTJOIN(delimiter, ignore_empty, range) skips blank cells automatically -- plain concatenation with & doesn't.
LEFT/RIGHT/MID need a fixed length unless paired with FIND or SEARCH to locate a delimiter dynamically (e.g. LEFT(A1, FIND(" ", A1)-1) for the first word).
TRIM only removes extra regular spaces -- text pasted from the web often has a non-breaking space (CHAR(160)) that TRIM won't touch; SUBSTITUTE it out first.

06Intermediate

Date and time formulas

Calculate deadlines, ages, tenure, month ends, workdays, and date differences.

DATE()DATEDIF()TODAY()

QUICK NOTES

Dates are stored as serial numbers (days since Jan 1, 1900), which is why you can subtract one date from another directly to get a day count.
DATEDIF(start, end, "y"/"m"/"d") is undocumented in Excel's own function list but works reliably for age/tenure calculations.
NETWORKDAYS and WORKDAY count or add business days, excluding weekends (and optional holidays) -- don't hand-roll that logic with plain subtraction.

07Advanced