Financial Management & Digital Accounting · Beginner level

Beginner level · Module 5: Digital Accounting Tools · Lesson 1 of 3

Chart of Accounts in a Spreadsheet

Set up a chart of accounts and a spreadsheet cash book with built-in checks.

In this lesson you will

  • Create a chart of accounts
  • Build a spreadsheet cash book with checks

Key concepts

chart of accountsaccount codesdata validationcheck totals

The lesson

A chart of accounts lists every account with a code: for example 1000s for assets, 2000s for liabilities, 3000s equity, 4000s income and 5000s expenses.

In a spreadsheet cash book, use a drop-down list of account codes, formulas for running balances and a SUMIF summary by account.

Add checks: total receipts minus payments must equal the change in balance, and the balance must match the cash count.

Step by step

  1. Create the chart of accounts.
  2. Build the cash book with a drop-down for codes.
  3. Add balance formulas and a summary.
  4. Add check cells.

Practice activity

Build the spreadsheet cash book and enter the week's transactions.

Expected result: A working spreadsheet cash book with checks that pass.

Check your understanding

  1. Why give accounts codes?

    Show answer

    To group and sort them consistently.

  2. What does a check cell do?

    Show answer

    It flags when totals or balances do not agree.

Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.