What a spreadsheet does in bookkeeping
A spreadsheet is a grid of cells. Each cell is named by its column letter and then its row number, so C2 is the cell in column C on row 2. A formula starts with an equals sign and points at other cells.
Much of bookkeeping is a list with money columns: a day book, a cash book, a list of unpaid invoices. A sheet adds those lists up, picks out one customer, flags what is late and totals a year by month.
Sorrel Workwear keeps its sales invoices in a sheet. Row 1 holds the headings.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Invoice | Customer | Net | Paid |
| 2 | 4101 | Orrin Cafe | 328.00 | Yes |
| 3 | 4102 | Pell Motors | 740.00 | No |
| 4 | 4103 | Orrin Cafe | 185.50 | No |
| 5 | 4104 | Wick Nursery | 415.00 | Yes |
| 6 | 4105 | Pell Motors | 96.00 | No |
| Formula | What it does | Returns |
|---|---|---|
| =SUM(C2:C6) | Adds every net amount | 1,764.50 |
| =SUMIF(B2:B6,"Orrin Cafe",C2:C6) | Adds the net amount on the rows where column B says Orrin Cafe | 513.50 |
| =COUNTIF(D2:D6,"No") | Counts the invoices that are not paid | 3 |
| =SUMIF(D2:D6,"No",C2:C6) | Adds the net amount of the invoices that are not paid | 1,021.50 |
Change one figure in column C and all four answers are worked out again. That is why a formula points at cells, and why a figure is never typed into it.
The unpaid invoices are the detail behind trade receivables: what customers owe.
What you need to be able to do
- Name a cell and a range, and write a formula that points at cells.
- Add up a column with SUM, and check that its range covers every row.
- Copy a formula down, and lock a cell with dollar signs when every row needs the same one.
- Use brackets so that a formula is worked out in the right order.
- Count and total only the rows that pass a test, with COUNTIF and SUMIF.
- Round to pence with ROUND, and say when ROUNDUP or ROUNDDOWN is the one to use.
- Make a cell decide with IF, AND and OR, and flag the invoices that are overdue.
- Fetch a price or a customer from a list with a lookup, and fix a lookup that returns #N/A.
- Work out a due date, and the days an invoice is overdue.
- Summarise a list by customer or by month with a pivot table, and check a sheet for mistakes.
The order to learn it in
The path is written for any office job, and it works in Excel and in Google Sheets. Its examples are lists of invoices, sales and stock, so the accounts work is already in the practice. These are its modules, first to last.
- Cells and formulas. Cells and ranges, writing a formula, SUM, relative and absolute references, and the order a formula is worked out in.
- Everyday functions. AVERAGE, MIN and MAX, the COUNT functions, SUMIF, the ROUND functions and percentages.
- Making decisions. IF, IF with AND and OR, nested IF, IFERROR, and a flag for overdue invoices.
- Looking things up. VLOOKUP with an exact or a nearest match, XLOOKUP, INDEX with MATCH, and how to fix a lookup that fails.
- Dates and text. How a date is stored, the days between two dates, TODAY and due dates, joining and splitting text, and cleaning up imported data.
- Summarising data. Sorting and filtering, pivot tables, totals by month, and three checks that catch most mistakes.
Lookups and pivot tables
A lookup fetches one value from a list, such as the price of a product code. There are three ways to write one.
| Function | What it does | Worth knowing |
|---|---|---|
| VLOOKUP | Looks down the first column of a table and returns a cell from the same row | It cannot look to the left. Type FALSE as its last part for a code or a name, and TRUE for a band |
| XLOOKUP | Searches one column and returns from another | It can look to the left, and it can say what to show when nothing is found. It is not in older versions of Excel |
| INDEX with MATCH | MATCH finds the position of a value, and INDEX fetches the cell at that position | The pair work in every version of Excel and in Google Sheets, and they can look to the left |
A pivot table answers a question about the whole list. It groups the rows that share a value, such as the customer, and gives one total for each group. It does not change the list: it is a separate summary that reads from it.
Where people go wrong
- A SUM whose range stops a row too soon. It happens when rows are added below a total that was written earlier. Click the total and read its range.
- Typing a figure such as a rate into every formula. Keep it in one cell and point at that cell, so a change is typed once.
- Copying a formula down without locking the cell that every row needs. The reference moves down a row each time.
- Leaving out the last part of VLOOKUP. It then looks for the nearest match, and can return the wrong row.
- A number stored as text. It sits on the left of its cell, and SUM leaves it out without any warning.
- Using IFERROR to hide a problem. It hides every kind of error, including the ones worth seeing. Fix the cause first.
- Sorting one column on its own. Each amount ends up beside the wrong customer.
- Totalling a filtered list with SUM. SUM adds the hidden rows too. SUBTOTAL adds only the rows on show.
- Typing a figure over a formula. It looks just like the answer of a formula until the formulas are shown.
What the practice looks like here
Ledger Drill teaches spreadsheets through questions, with no videos. Every question shows a small table of cells, so no spreadsheet program has to be open.
- Type what a formula returns.
- Pick the formula that does the job.
- Find the one cell or row that is wrong.
- Match each function to what it does.
- Put the steps of a task in order.
Every answer is marked at once, with the working behind it.
The modules and rooms are listed below, in order. A room that costs nothing carries a Free tag.
Practise it
Words used here
- Invoice
- An invoice is the seller's request for payment. It lists what was sold and what is owed.
- Day book
- A day book is a book of prime entry that lists credit sales or credit purchases, one line for each invoice.
- Cash book
- The cash book records every receipt into the bank and every payment out of it. It is also the Bank account in the ledger.
- Trade receivables
- Trade receivables is the money that customers owe a business for sales made on credit. It is an asset.
Questions
Which spreadsheet formulas does a bookkeeper need?
SUM, SUMIF and COUNTIF for totals, ROUND for pence, IF for flags, a lookup such as VLOOKUP or XLOOKUP, date sums for due dates, and a pivot table for summaries. The rooms teach each one.
Does the practice work for Excel and for Google Sheets?
Yes. The rooms are written to work in both, and they say where the two differ. XLOOKUP is one example: it is in Google Sheets and in newer versions of Excel, but not in older ones.
Is the spreadsheet practice free?
In part. 5 of the 30 rooms in these modules are free, and you can start one with no account: every room in Cells and formulas. The other rooms are part of Premium.
How long does it take to learn spreadsheets for bookkeeping?
The 30 rooms in these modules take from 6 to 10 minutes each, by their own estimates. Added up that is 256 minutes, which is about 4 and a half hours of practice.
Do I need a spreadsheet program to practise?
No. Every question shows the cells it needs on the screen, and you type or pick the answer.
Do I need to know bookkeeping first?
No. The spreadsheet rooms start from naming a cell, and none of their questions asks for a debit or a credit.