Excel formulas for beginners: calculate a total you can check

A useful spreadsheet formula starts with a clear question. Suppose a fictional office sells two notebooks at $8 each and three pens at $5 each. Each row has Units in column D and UnitPrice in E. The question is total sales, before tax and without returns.

In F2 enter =D2*E2, then copy the formula to F3. The first row is $16 and the second is $15. Use =SUM(F2:F3) for the total of $31. Multiplying the sum of all quantities by the sum of all prices would give $65, which combines values from different orders. It is a valid expression for the wrong question.

When a formula is copied, a relative reference moves. D2 becomes D3 one row lower. If a fixed practice fee rate is in J2, use =F2*$J$2 so the sales reference changes but the rate stays fixed. The dollar signs lock the address; currency formatting is a separate setting.

Name your average carefully. These two orders contain five units. Average order value is $31/2, or $15.50. Revenue per unit is $31/5, or $6.20. Both calculations can be correct, but they answer different questions.

Check a formula with a small case you can solve by hand. Then test a boundary and a missing value. If a rule includes sales of at least $20, an IF test should use >=20. If quantity is unknown, do not silently treat it as a verified zero. A clear exception is more useful than a misleading clean-looking total.

AI can propose formulas, but give it the field meanings and a known-result test. Ask it to explain why the result should be $31, then verify the arithmetic yourself. The linked READY course develops these ideas through a complete fictional workplace report.

Continue with the full course

Learn Excel formulas, data cleaning, lookups, PivotTables, and charts through original workplace exercises. Free READY learning with checked AI examples.

This is original, independent READY training. READY is not affiliated with or endorsed by Microsoft. Course completion does not confer a provider credential.