Formulas, Arithmetic Operators, and Cell References

Why Formulas Matter in Bookkeeping

Every bookkeeping task—tracking expenses, comparing budgets to actuals, calculating payroll totals—depends on accurate arithmetic. Excel lets you write formulas that do that arithmetic automatically and update instantly when the underlying numbers change. One changed budget figure can ripple through an entire spreadsheet in seconds, with no risk of the manual addition errors that plague paper ledgers. Understanding how formulas work is therefore a core skill for any bookkeeper or office administrator.

Arithmetic Operators

Excel uses five arithmetic operators inside formulas:

| Symbol | Operation | Example |

|--------|-----------|----------|

| `+` | Addition | `=A1+B1` |

| `-` | Subtraction | `=A1-B1` |

| `` | Multiplication | `=A1B1` |

| `/` | Division | `=A1/B1` |

| `^` | Exponentiation | `=A1^2` |

Every formula begins with an equals sign (`=`). Without it, Excel treats your entry as plain text.

Order of Operations

Excel follows standard mathematical order of operations, often remembered as PEMDAS:

1. Parentheses

2. Exponents

3. Multiplication and Division (left to right)

4. Addition and Subtraction (left to right)

This means `=2+34` returns 14, not 20, because multiplication happens before addition. To force addition first, use parentheses: `=(2+3)4` returns 20. In bookkeeping templates, misplaced parentheses are a common source of silent errors—your spreadsheet will calculate something, just not what you intended. Always predict results on paper before trusting a formula.

Cell References: Relative, Absolute, and Mixed

Cell references tell Excel where to find the values a formula needs. There are three types, and choosing the right one is the key to building templates you can copy without breaking.

Relative References

A relative reference such as `B3` adjusts automatically when you copy the formula to another location. If `=B3-B2` is in cell B4 and you copy it to C4, it becomes `=C3-C2`. This is exactly what you want for row-by-row or column-by-column repetition.

Absolute References

An absolute reference such as `$B$2` never changes when copied. The dollar signs lock both the column letter and the row number. Use absolute references when every copy of a formula must point to the same cell—for example, a single annual budget figure that all six monthly percentages divide by.

Mixed References

A mixed reference locks either the column or the row, but not both: `$B2` locks the column; `B$2` locks the row. Mixed references are useful in two-dimensional tables where one dimension should stay fixed and the other should shift.

Keyboard shortcut: While editing a formula, press F4 to cycle a cell reference through all four states: `B2` → `$B$2` → `B$2` → `$B2` → back to `B2`.

Building the Six-Month Expense Tracker

The expense tracker you will build in the lab uses all three reference types working together:

• Row 2 holds monthly budget figures (B2 through G2).
• Row 3 holds actual spending figures (B3 through G3).
• Row 4 calculates variance: `=B3-B2`. Copied across, the relative references shift correctly for each month.
• Row 5 calculates percent of budget: `=B3/$B$2`. The denominator is locked with `$B$2` only if a single master budget cell is used; if each month has its own budget, the formula becomes `=B3/B2` (both relative). Verify which design your template uses before copying.
• Row 6 accumulates a year-to-date total using a fixed annual budget cell with an absolute reference.

Copying formulas across all six columns and then changing a single budget value is how you confirm everything is wired correctly—all dependent cells should update simultaneously.

Common Formula Errors and How to Trace Them

Excel displays error codes when a formula cannot produce a valid result:

• #REF! — The formula points to a cell that no longer exists, usually because a row or column was deleted.
• #DIV/0! — The formula tries to divide by zero or by an empty cell.
• #VALUE! — The formula attempts arithmetic on a cell containing text instead of a number.

Finding errors: Go to Formulas → Error Checking to step through errors one at a time. Use Trace Precedents to draw blue arrows showing which cells feed into the selected formula, and Trace Dependents to show which cells depend on it. These tools make it fast to spot a broken reference or an empty divisor cell without reading through every formula manually.

Document every error you find in your lab log: note the cell address, the error code, the cause, and the correction you made. This habit mirrors professional practice—audit trails are expected in bookkeeping environments.

Data Integrity and Workplace Safety

In an office setting, the main risks around spreadsheet work are data-entry errors and file loss, not physical hazards. Protect your work:

• Save frequently with Ctrl+S and keep a backup copy, following your employer's file-naming and version-control policy.
• Never share a workbook containing sensitive financial figures via unsecured email or personal cloud storage.
• Lock formula cells (using worksheet protection under the Review tab) so that other users cannot accidentally overwrite them—this is a standard internal-control practice aligned with good bookkeeping procedure.
• Maintain good ergonomic posture and take short breaks to prevent repetitive-strain injury during extended data-entry sessions.