Excel Fundamentals: Reference & Formulas

Why Excel still matters

Excel is still the first tool most analysts open every morning. Before dashboards or automation, you need fluency in reading and writing formulas correctly, and in keeping data clean enough that formulas do not silently break.

Relative vs absolute references

Every cell has an address like B4. A relative reference (=A2*B2) shifts when you copy the formula down; an absolute reference (=A2*$B$1) stays fixed to B1 no matter where you paste it.

Use absolute references when you are multiplying by a fixed tax rate, a conversion factor, or a single lookup table that should not move.

• Relative: =A2*B2 (shifts when copied)
• Absolute: =A2*$B$1 (stays locked to B1)
• Mixed: =A2*$B2 (row shifts, column stays fixed)

The five formulas that carry 80% of analyst work

SUM adds a range; AVERAGE divides the total by the count; COUNT counts only numbers, COUNTA counts any non-empty cell. MAX and MIN return the largest and smallest value.

These five are the foundation. Everything else is built on recognizing when to apply one of them.

• SUM(B2:B20) — total of a range
• AVERAGE(B2:B20) — arithmetic mean
• COUNT(B2:B20) — count numeric cells; COUNTA counts any cell
• MAX(B2:B20) and MIN(B2:B20) — extremes

Sorting without breaking your data

The single most common way analysts corrupt a dataset is sorting one column while leaving the rest of the row behind. Always select the full table first (Ctrl+A inside it) before you sort.