Excel for Analysts · 12 min · 120 XP
Formulas worth knowing
The anatomy of a formula, the functions you'll use daily, and the difference between a value and how it's shown.
A formula starts with = and calculates from other cells, so when an input changes, every result that depends on it updates. That's the whole point of a spreadsheet, and it only works if you refer to cells rather than typing their numbers into formulas. =E2*F2 stays right when E2 changes; =2*24 doesn't.
=SUM(G2:G13) add up a range =AVERAGE(G2:G13) mean (blanks are skipped, zeros are not) =MIN(...) =MAX(...) smallest and largest =COUNT(A2:A20) how many cells hold numbers =COUNTA(A2:A20) how many cells aren't empty =ROUND(B2*1.25, 2) round the value itself, to 2 decimals =IF(I2>=1000, "Target met", "Below target") =IFERROR(B2/C2, 0) a fallback when the calculation errors
How a number is shown isn't what it is. Formatting a cell to two decimals changes the display, not the value: a cell showing 3.33 may hold 3.3333, and every formula that uses it sees 3.3333. When the rounded value is what should count (prices, tax), round it with ROUND.
Read the error values rather than fearing them: #DIV/0! (divided by zero or an empty cell), #VALUE! (text where a number was expected), #NAME? (a misspelled function), #REF! (a reference to a cell that's gone) and #N/A (a lookup found nothing, which is the next lesson but two).
In these challenges you write one line per formula: a cell, then its formula, like I2 =SUM(G2:G13). Give a range instead, like G2:G13 =E2*F2, and the formula is filled down it exactly as Excel's fill handle would do. The formulas run on a spreadsheet engine built for this course. It supports the everyday functions (SUM, AVERAGE, IF, SUMIFS, XLOOKUP, VLOOKUP, INDEX/MATCH and more) and behaves the way Excel does for them.
A1 holds =10/3 and is formatted to show 2 decimals: 3.33 B1 holds =A1*3 and shows 10.00
Loading your workspace…