Handwritten Ledger to Excel: Rebuilding an Account Book
By Schema LawPublished Updated
To get a handwritten ledger into Excel, scan every page, build a sheet whose columns match the ledger's own, and key each line with its page and line number. Then recompute the balance in a formula column and compare it with the balance the bookkeeper wrote. Every difference points to a line to recheck.
A handwritten account book has one advantage over every other handwritten document in a file: it checks itself. The bookkeeper added and subtracted as they went and wrote the answers down. That arithmetic lets you test your typing line by line, which no letter or diary allows. This post is built around using it, and around knowing what it cannot tell you.
How do you get a handwritten ledger into Excel?
You get a handwritten ledger into Excel by treating it as a keying job with a machine draft as an optional aid. Scan the book, mirror its columns in a sheet, key every line including the lines that carry no transaction, add a formula that recomputes the balance, and resolve each mismatch against the page image.
- Describe and paginate the book. Note what it is, who handed it over and when. Use the printed folio numbers if the pages have them. If not, count pages from the front and use your count.
- Scan every page in color. Include covers, blank pages and anything loose. Red ink and pencil carry meaning in an account book, and a black-and-white scan throws both away.
- Read the column headings and decide what each column is. Write that decision down. If a column has no heading, work out from several lines whether it holds money in, money out or a balance.
- Build the sheet. One Excel column per ledger column, plus columns for the page, the line, your computed balance and your notes.
- Key or convert, then correct. Try a page through recognition software. If the figures come back needing more correction than typing would take, key from the image.
- Recompute. Add a formula column that works out the balance from your keyed amounts.
- Compare and resolve. For each line where your computed balance differs from the written one, open the image and find out why.
- Have a second person check the lines the arithmetic cannot test. Dates, names and descriptions.
Where a tool fits
Schema Law reads uploaded pages into text and tables. Excel and CSV are among its export formats, and every plan, including Free, includes every export format; the plans differ in how many pages you can convert. Its homepage says recognition is strongest on clean print and weaker on handwriting, and that nothing marks a passage as uncertain. A converted ledger page is therefore a draft in which any digit may be wrong and none is marked. The arithmetic check is what turns that draft into something you can rely on, and it works equally well on figures you typed yourself.
A ledger with ruled columns is a table. Some account records are not: a list of payments written down a notebook page with no columns, for example. The post on OCR into Excel covers lists that have to be split into columns by hand.
Check the name, the date, the amount, and any initials in the cash book, the rent book, or the receipt book. Treat the book as the business record. Your sheet works as documentary evidence when the numbers agree. Keep the scans for authentication, note who gave you the book to start the chain of custody, and match a bank tick to the figure as corroborating evidence.
Account books, rent books and receipt books a small business hands over
The account books a small business hands over are bound or loose handwritten records of money: a cash book, a rent book, a duplicate receipt book, a day book of sales, a wages book, a petty cash book. Each has a different shape, and only some carry a running balance you can test your typing against.
| Book | A line records | Columns you can expect to find | Self-checking? |
|---|---|---|---|
| Cash book or general account book | One receipt or payment | Date, details, money in, money out, balance | Yes, where a balance column was kept |
| Rent book | One period's rent for one tenant | Date due, amount due, date paid, amount paid, arrears, initials | Yes, where arrears are carried line to line |
| Duplicate receipt book | One receipt, on a numbered slip with a carbon copy | Receipt number, date, received from, amount, for, signed | No balance; the receipt numbers give a sequence |
| Sales day book | One sale or one day's takings | Date, customer or description, amount, sometimes a daily total | Page or daily totals only |
| Wages book | One worker for one pay period | Name, hours or days, rate, gross, deductions, net | Across the row: gross less deductions equals net |
| Petty cash book | One small payment | Date, voucher number, details, amount, analysis columns | Page totals, and a float restored at intervals |
Before keying, establish three things about the book in front of you and write them at the top of your working notes.
Who wrote it. One bookkeeper throughout, or several hands. A change of hand partway through is worth noting by page.
What period it covers. The first and last dated entries, and any stretch with no entries.
What it was checked against at the time. Look for ticks beside entries, a bank statement balance pencilled in, an accountant's initials at a year end. Those marks tell you someone compared the book with another record, and they belong in your sheet.
Printed and computer-generated ledgers raise different issues. A landlord's printed account for one tenant is covered in the post on converting a tenant ledger to Excel. Where the book refers to payments by check and you hold the checks themselves, the post on OCR for handwritten checks deals with reading those.
Handwritten figures: sevens, ones and crossed-out amounts
Handwritten figures go wrong in ways printed figures do not: a seven with no bar looks like a one, a one with a flag and a foot looks like a seven or a two, an open four looks like a nine, and a corrected amount shows two numbers at once. Learn the bookkeeper's own digits before keying a single page.
Build a digit card for this writer
Find a place in the book where the figures are beyond doubt, such as a column of dates running 1 to 31 or a page whose total you have confirmed by adding. From it, note how this writer forms each digit from 0 to 9. Keep the note open while you key. The points to settle:
| Question | Why it matters |
|---|---|
| Does the 7 have a bar through the stem? | If so, an unbarred stroke is a 1. If never, you must tell 1 from 7 by the top stroke alone |
| Does the 1 have a flag, a foot or both? | A 1 with both can pass for a 2 or a 7 |
| Is the 4 open or closed at the top? | A closed 4 with a short stem resembles a 9 |
| Is the 0 closed? | An open 0 resembles a 6 or a C |
| How is the 5 topped? | A 5 whose top stroke detached looks like a 3 or an S |
| How does the writer mark pence or cents? | A point, a dash, a raised pair of digits, or a separate ruled column |
| How are thousands shown? | A comma, a space, or nothing |
| How is a nil amount shown? | A dash, "0", "nil" or an empty cell |
Misreads of printed digits by software have their own post, on why OCR gets numbers wrong. The digit card is the handwriting equivalent: it replaces general expectations with this one writer's habits.
Crossed-out and overwritten amounts
A bookkeeper who made a mistake had three ways to fix it, and each leaves a different trace.
- Struck through and rewritten beside or above. Both amounts are legible. Key the final amount in the amount column and the struck amount in a separate
struck_amountcolumn, so the correction is visible in the sheet. - Overwritten. A new digit sits on top of the old one. Key your best reading of the final digit only if the balance supports it; otherwise key
[overwritten]in the note column, leave the amount to be settled by the arithmetic, and record both candidate readings. - Erased or covered. Pencil rubbed out, or correction fluid. Record that the cell shows an erasure and what, if anything, is visible beneath.
Never tidy a correction away. In an accounting dispute the fact that an amount was changed, and from what, may matter as much as the final figure.
Other marks around figures
Note red ink, brackets around a figure, a "Cr" or "Dr" beside it, and a minus sign. How a given bookkeeper used them is something to establish from the book itself: test your assumption against the balance on several lines before applying it to the whole book.
Does the ledger's own running balance prove your typing?
The ledger's running balance proves a narrow thing: on a line where your recomputed balance equals the written one, your amounts on that line are consistent with what the bookkeeper used. It does not prove the dates, names or descriptions, and it does not prove the bookkeeper's amounts were true.
Setting up the check
Suppose money in is column E, money out is column F, the balance as written is column G and your computed balance is column H. Key the opening balance into H2 by hand. Then, from row 3 down:
H3: =ROUND(H2+E3-F3, 2)
I3: =IF(H3=G3, "", H3-G3)Column I stays empty where the two agree and shows the difference where they do not. This version chains from your own computed balance, so one wrong amount produces the same difference on every row beneath it. That is useful: the first row showing a difference is the row to open.
A second version restarts from the written balance on each row:
J3: =IF(ROUND(G2+E3-F3, 2)=G3, "", "CHECK")This one flags only the rows that do not follow from the written balance on the line before, which isolates each problem line instead of smearing it down the page. Use both.
A worked example
A fictional cash book kept by a shop, Pemberton Hardware, opens a page with a balance of 1,240.50.
| Line | Date | Details | In | Out | Balance as written | Computed | Difference |
|---|---|---|---|---|---|---|---|
| 1 | 3 Mar | Takings | 310.00 | 1,550.50 | 1,550.50 | ||
| 2 | 4 Mar | Rent | 700.00 | 850.50 | 850.50 | ||
| 3 | 5 Mar | Hale & Co | 171.40 | 733.10 | 679.10 | -54.00 | |
| 4 | 6 Mar | Takings | 275.00 | 1,008.10 | 954.10 | -54.00 |
The difference first appears on line 3 and stays at 54.00. Open the image at line 3. The amount on the page is 117.40, and it was keyed as 171.40: two digits swapped. Correct the cell and both differences clear.
Reading a difference
The size of a difference is a clue to its cause.
| The difference is | Look for |
|---|---|
| Exactly divisible by 9 | Two adjacent digits swapped, or a decimal point one place out |
| Equal to an amount on the page | A line left out, or keyed twice |
| Twice an amount on the page | That amount keyed in the wrong column, in for out |
| A single digit followed by zeros, such as 60.00 or 300.00 | One digit misread in that place, such as a 1 for a 7 in the tens (a difference of 60.00) |
| Present on one row only, by the restart formula, with later rows agreeing | The bookkeeper's own slip on that line, corrected in effect by continuing from the written figure, or a balance cell you mistyped |
What the balance cannot tell you
- Two errors that cancel. An amount keyed 10.00 too high on one line and another keyed 10.00 too low three lines later leave every later balance in agreement. The difference column shows 10.00 on the lines between the two and then clears, so read the whole column and never only its last cell. In a book tested by page totals alone, a cancelling pair on one page leaves no trace at all.
- Everything that is not an amount. A date keyed as the 13th for the 18th, or "Hale" for "Hall", passes the arithmetic untouched.
- The bookkeeper's own arithmetic. Where the written balance does not follow from the written amounts, your keying is right and the book is wrong. Do not "fix" it. Record it: page, line, written balance, computed balance. Such lines may be exactly what an accountant or the lawyer wants to see.
- Whether the entries happened. Agreement shows the book is internally consistent. Whether a payment recorded in it was made is a question for bank statements, receipts and witnesses.
Books with no running balance
Where there is no balance column, use the totals the bookkeeper did write: a total at the foot of each page, a weekly or monthly total, a year-end figure. Sum your keyed column over the same range and compare. The test is coarser, since a mismatch tells you only that an error lies somewhere on the page, and you then check that page line by line.
Laying out the Excel sheet to mirror the ledger page
Lay out the Excel sheet so that one spreadsheet row equals one ruled line of the ledger and the columns run in the ledger's order, with control columns added to the left and right. A checker should be able to hold the page image beside the sheet and read straight across both.
Columns
| Group | Columns | Notes |
|---|---|---|
| Locator, on the left | page, line | The page of the book and the ruled line counted from the top. Never blank |
| The ledger's own columns, in its order | For example date_as_written, details, folio_or_ref, in, out, balance_as_written | Use the book's headings as the column names where it has them |
| Normalized values | date_iso | A full date for sorting, with the year supplied from the page heading where the line gives only day and month |
| Checks | balance_computed, difference | Formulas only; no typing in these |
| Observations | struck_amount, ink_or_pencil, note | What the page shows that a number cannot |
| Control | row_type, keyed_by, checked_by | row_type is one of: entry, heading, total, brought forward, carried forward, blank |
Rules for the sheet
- Key every ruled line, including blank ones. A blank line gets a row with
row_typeof blank. The row count for a page then equals the line count, which is your first check that nothing was skipped. - Amounts are numbers. Type 117.40, not "117.40" with a currency sign or a trailing note. A cell that Excel aligns to the left is being held as text and will be ignored by a sum.
- Text that looks like a number stays text. Receipt numbers, folio references and account numbers with leading zeros go in columns formatted as text before you type.
- Nil is not blank. Where the bookkeeper wrote a dash or "nil", key 0 and say so in the note. Where the cell is empty on the page, leave the amount cell empty.
- One sheet per book, not per page. Keep all pages in one continuous sheet so the balance formula runs through page breaks. The
pagecolumn does the separating. - Do not sort the working sheet. The balance formulas depend on row order. If you need the entries in another order, copy the values to a second sheet and sort there.
- Freeze the header row and the locator columns so
pageandlinestay in view while you scroll.
Entries that take two lines
A long description that the bookkeeper continued on the next ruled line, with the amount on the first or the last of them, is keyed as two rows. The amount sits on the row where it sits on the page. Give the continuation row a row_type of entry and a note such as "continues line 14". The balance formula treats its empty amount cells as zero, so the arithmetic is unaffected.
Carrying forward, brought forward and other bookkeeping marks
"Carried forward" and "brought forward" are the bookkeeper's way of moving a balance or total from the foot of one page to the head of the next, as the terms are commonly used. Key them as rows of their own type, never as transactions, and use each pair as a checkpoint. Confirm any mark's meaning against the book itself.
The marks and what to do with each
| Mark on the page | Common use | How to key it |
|---|---|---|
| "c/f", "c/fwd", "carried forward", "carried over" | The figure at the foot of a page that continues overleaf | A row with row_type carried forward; the figure in the balance or total column, not in the in or out column |
| "b/f", "b/fwd", "brought forward", "brought over" | The same figure repeated at the head of the next page | A row with row_type brought forward |
| "c/d" and "b/d", "carried down", "brought down" | A balance struck at a period end and reopened beneath it on the same page | Rows of their own type; note the date of the balance |
| A single rule beneath a column | Figures over it are about to be totalled | No row; the total that follows is a total row |
| A double rule beneath a figure | That total or balance is final for the period | Note "double ruled" on the total row |
| A tick beside an entry | Someone checked that entry against another record | A ticked column with Y, plus the ink color if it differs |
| A number in a narrow column beside the details | A folio reference to another book or page | The folio_or_ref column, as text |
| "Contra" or a "C" in the folio column | An entry that appears on both sides of the same book | Key as written and note it |
| Initials or a signature at a period end | Someone signed off the page or the period | Note column, with the page |
These are common uses, not fixed rules. One bookkeeper's tick is another's "paid" mark. Test each assumption against lines where the balance confirms your reading, and write your working definitions at the top of the sheet so a second reader knows what you assumed.
Why forward rows must not be counted
If a carried forward figure is keyed in the "in" column, your computed balance doubles at every page turn. If it is left out altogether, you lose a check. Keying it as its own row type lets a formula skip it while you still compare it.
The page-turn check
At every page break, three figures should agree: the last computed balance on the page, the carried forward figure at its foot, and the brought forward figure at the head of the next page.
- Computed differs from carried forward. There is an error on the page, in your keying or in the book. The running balance check will locate the line.
- Carried forward differs from brought forward. The bookkeeper miscopied between pages, or a page is missing. Check the page numbers and the dates on either side of the break.
- All three agree. The page is closed. Record that in
checked_byand move on.
A missing page shows up here before it shows up anywhere else. If the brought forward figure at the head of page 23 does not match the foot of page 22, and the dates jump, count the leaves and look at the binding for a stub. Record what you find without explaining it.
What to hand over
Give the lawyer or accountant three things: the workbook, the color scans, and a short list of every line where the book does not agree with itself, each with its page and line. A handwritten log of events, with times and initials in place of money, has no arithmetic to lean on, and the post on OCR for handwritten logs sets out the two-person check used there. If the rebuilt ledger will go to someone outside the firm, Excel and CSV export are on every Schema Law plan, and the pricing page shows each plan's page allowance.