Excel Fundamentals Refresher

This program builds on basic Excel skills, so we start with a quick refresher on the concepts every advanced technique depends on: cell referencing. Getting this right is the difference between a formula that works when copied across a sheet, and one that breaks.

• Relative reference (e.g. B2): changes automatically when you copy the formula to another cell.
• Absolute reference (e.g. $B$2): stays locked to the same cell no matter where you copy the formula. Use it for values like a tax rate or exchange rate that every row should reference.
• Mixed reference (e.g. $B2 or B$2): locks only the column or only the row.
• Press F4 after clicking a cell reference in a formula to quickly cycle between relative, absolute, and mixed.

Practical Skill: Build a price list where every item's total is Quantity × Unit Price × (1 + Tax Rate), with the tax rate in one locked cell referenced by every row using an absolute reference.

=== TAX CALCULATION SHEET ===       A            B          C            D1     Tax Rate:    0.0523     Item         Qty        Unit Price   Total4     Notebooks    10         1,500        =B4*C4*(1+$B$1)5     Pens         50         200          =B5*C5*(1+$B$1)6     Folders      20         800          =B6*C6*(1+$B$1) Notice: $B$1 stays locked to the tax rate cell when theformula in D4 is copied down to D5 and D6 — but B4, C4shift automatically to B5/C5 and B6/C6.