Spreadsheets in accounting

The essentials

A spreadsheet is a worksheet made up of rows (numbered) and columns (lettered) whose intersections are cells, each identified by an address such as C5. Cells can hold text, numbers or formulae. Because formulae recalculate automatically when data changes, spreadsheets are widely used for accounting tasks such as preparing ledgers, schedules, trial balances and reports.

Key points

• A formula begins with "=", for example =B2+B3 or =SUM(B2:B10).
• Common functions: SUM, AVERAGE, MAX, MIN, COUNT, ROUND, IF, and lookup functions such as VLOOKUP.
• Relative reference (A1) changes when copied; absolute reference ($A$1) stays fixed; mixed reference ($A1 or A$1) fixes only the column or row.
• Pivot table: summarises large data by grouping and totalling, for example sales by region and month.
• Sorting and filtering help locate and arrange data.
• Common errors: #DIV/0! (division by zero), #REF! (invalid cell reference), #NAME? (unrecognised function name), #VALUE! (wrong type of value), ##### (column too narrow), and circular reference (a formula referring to its own cell).

Exam tips

Practise predicting how a formula changes when copied down or across. Remember what each error message means.

Common mistakes

• Forgetting the "=" sign, so the formula is treated as text.
• Using a relative reference for a fixed rate (such as a tax rate), which breaks when copied.