Excel for Beginners Part 3 Formulas and Cell References

Build accurate formulas and understand relative, absolute and mixed references before copying calculations.

What you will learn

• Write formulas with arithmetic operators
• Understand relative and absolute references
• Diagnose common formula errors

Before you begin

Open Microsoft Excel or another spreadsheet application that supports standard Excel formulas. Create a practice copy so mistakes are safe to undo. Complete each action yourself rather than only reading it.

Formula foundations

Every formula begins with an equals sign. For example, =B2*C2 multiplies quantity by unit price.

Excel follows the normal order of operations. Use parentheses when you want a calculation performed first, such as =(B2+C2)*D2.

References that copy correctly

A relative reference such as B2 changes when copied. An absolute reference such as $F$1 stays fixed.

If F1 contains a tax rate, =D2*$F$1 can be copied down while always using the same tax cell. Mixed references lock only a row or column.

Read errors instead of guessing

#DIV/0! means a formula divided by zero or an empty cell. #NAME? often means a function name or named range is misspelled.

Select the formula and inspect each referenced cell. Use Formulas then Evaluate Formula for complex calculations.

Guided practice

1. Create Quantity, Unit Price, Subtotal, Tax and Total columns.
2. Calculate Subtotal with multiplication.
3. Put the tax rate in one cell and use an absolute reference.
4. Copy the formulas down and verify three rows manually.

Knowledge check

1. What character starts a formula?
2. What is the difference between B2 and $B$2?
3. What usually causes #DIV/0!?

Completion standard

You are ready for the next part when you can complete the guided practice without copying the instructions word for word and can answer the knowledge-check questions in your own language.