Free accounting software in Excel is usually a downloaded workbook with a transaction sheet, a chart of accounts and some summary formulas. It records money in and out, and small clients can run on it for years. It has no controls of its own, so the bookkeeper has to supply them.
What does free accounting software in Excel actually include?
A free Excel accounting template is a workbook, not a program. It typically holds a chart of accounts, one sheet where transactions are typed in, and report sheets that add those transactions up. Everything it does is a formula or a macro someone else wrote, and the client owns every cell.
Most templates share the same few parts:
- A chart of accounts sheet, listing income, expense, asset and liability names, often with a code beside each.
- A transaction or journal sheet, with one row per entry: date, description, an account, an amount.
- Summary sheets, such as a profit and loss or a monthly total, built with sum formulas that read the transaction sheet.
- Sometimes a bank sheet, where the client ticks off lines against a statement.
What a template does not include
A template has no built-in rule that every entry must balance. A cloud ledger refuses a transaction that does not have a matching other side; a workbook accepts whatever is typed. It also has no audit trail. If a client overwrites a number in March, the file does not record who changed it, when, or what it said before.
It has no bank connection and no statement import. Every line a client wants in the books is either typed or pasted. It also has no locking of closed periods unless the template author added one, and a sheet protection password is not much of a lock.
Why clients choose it
None of this makes the choice foolish. A sole trader with thirty transactions a month needs a list, a total and a place to keep receipts. Excel costs nothing extra, the client already knows it, and the data sits in a file they can open without a login. Your job is to know where that arrangement is safe and where it stops being safe.
If you are weighing the wider question of whether the client should use an installed program or a hosted one, the post on desktop versus cloud ledgers for client books covers that separately.
Spreadsheet books with formulas that break silently
A spreadsheet formula breaks silently when it keeps returning a number after it has stopped covering the data. Excel shows no error, so the report looks finished while it quietly leaves rows out. This is the main reason bookkeepers distrust client workbooks.
How the breakage happens
The common causes are ordinary, and none needs bad intent:
- A range that stops one row short. A total reads rows 2 to 400. The client adds row 401 and the total ignores it.
- Rows inserted below the formula. Data added under a total line is outside the sum.
- Numbers stored as text. An amount pasted from a statement PDF can arrive as text. Excel left-aligns it and sum formulas skip it.
- Hard-coded numbers over formulas. Someone types 1,250 over a cell that held a formula, and the sheet never updates again.
- Sorting part of a table. Sorting one column without the others scrambles which amount belongs to which description.
- Two copies of the file. The client emails a copy, edits both, and nobody knows which is current.
A quick test for a client workbook
You can check a workbook in ten minutes without reading a single formula:
- Add up the transaction sheet's amount column yourself, in a spare cell, and compare it with the total the report sheet shows.
- Count the rows in the transaction sheet and compare that with the count of rows the totals cover.
- Use the filter on the amount column and look for text entries or blanks.
- Compare the sheet's bank balance with the closing balance on the latest statement.
- Look at the file's modified date and ask the client if there is a second copy.
The fourth check is the one that matters most. If the workbook's balance does not equal the statement's closing balance, something is missing or doubled, and you need to find it before building anything on top of that file.
What the errors look like in the books
Silent formula errors rarely show as one big wrong number. They show as small gaps that never reconcile: a profit figure a few hundred off, an expense category that is lower than the bank shows, a balance that matches in February and not in March. When a client's year-end numbers disagree with their statements and nobody can say why, a broken range is the first thing to look for.
When should a client leave Excel accounting?
A client should leave Excel accounting when the risk of a wrong number costs more than the cost of a proper ledger, and the signs are usually visible in how the client works. One sign is rarely enough; two or three together mean the move is overdue.
Use the warning-sign checklist below. The examples in the second column are typical, not measured.
| Warning sign | What it looks like in practice | Why it matters |
|---|---|---|
| The workbook does not agree with the statements | Closing balance differs from the bank by an unexplained amount | The books cannot be trusted until the gap is found |
| More than one person edits the file | Two copies by email, or a shared drive file with no history | No one can say which version is right |
| Volume has grown | Hundreds of lines a month typed by hand | Typing errors scale with the number of lines |
| Sales tax or payroll is involved | Tax collected is tracked in a side column | Errors turn into filing mistakes |
| A lender, buyer or auditor wants records | Request for a ledger with history | A workbook cannot show who changed what |
| The client has several bank or card accounts | One sheet per account, copied by hand | Transfers get counted twice or not at all |
| You spend more time repairing the file than doing the books | Fixing ranges every month | The fee no longer covers the work |
Weigh the move honestly
Moving a client has a cost: setup time, a chart of accounts to map, a cutoff date, and a client who must learn a new tool. Do not move a client with a tidy workbook just because a ledger is the standard. Move them when the checklist above shows real exposure, and set the cutoff at the start of a month or quarter so that old and new records meet at a clean balance.
Cutoff date and opening balances
Pick the first day of a month as the start of the new books. Take the closing balance of every bank and card account on the day before, enter those as opening balances in the new ledger, and keep the workbook as a frozen, read-only record of everything earlier. The statement closing balances, not the workbook's figures, are the opening numbers, because the statements come from the bank.
Carrying an Excel ledger history into new books
Carrying history across means mapping the workbook's columns to the fields the new ledger expects, then importing the rows you actually need. For most clients that is the current year plus the prior year for comparison. Older years can stay in the archived workbook.
Mapping spreadsheet columns to ledger fields
Template columns rarely match ledger fields one to one. This table maps common ones. The column names are examples; yours will differ.
| Spreadsheet column | Ledger field | Notes |
|---|---|---|
| Date | Transaction date | Check the whole column is real dates, not text |
| Description or Payee | Payee and memo | Split into two if the template mixes them |
| Category or Account | Account | Map each name to the new chart of accounts first |
| Amount (one signed column) | Debit or credit | Decide which sign means money out |
| Money in, Money out (two columns) | Debit and credit | Combine only if the import needs one amount column |
| Balance | Not imported | Use it to check, then drop it |
| Notes or Receipt link | Memo or attachment | Keep a pointer to the document, not a pasted image |
| Reconciled tick | Cleared status | Only carry it over if the old tick was reliable |
Steps for the move
- Copy the workbook and work only on the copy.
- Clean the date and amount columns so every cell is a real date or number.
- Build a mapping from the template's category names to the new chart of accounts, and write it down.
- Set the opening balances from the bank statements on the cutoff date.
- Import the mapped rows in the new ledger's import format.
- Compare each account's balance in the new ledger with the statement at the same date.
Bringing the statements across
A workbook's history is only as good as the statements behind it. If you are rebuilding the current year from the bank's own records instead of from the client's entries, the statements are the better source. Converting the PDFs to import-ready lines takes less time than cleaning a doubtful workbook, and the lines come with a built-in check against the statement. AutoBankConvert converts a folder of client statements, categorizes each line, and exports QuickBooks or Xero files; the homepage shows how a batch runs.
Once the lines are in, the first problem is nearly always the same: a long list with no category. The guide to clearing uncategorized transactions each month walks through that clean-up.
Excel ledgers that are fine to keep
An Excel ledger is fine to keep when the client has little volume, one person edits it, and it agrees with the bank every month. In that case the workbook is doing its job, and your time is better spent on the clients who need it.
A client who suits a workbook
Look for these conditions together:
- Under a few dozen transactions a month, on one bank account.
- A single editor, and one master copy in a known place.
- No sales tax, payroll or inventory.
- A monthly bank check that the client or you perform and that actually ties.
- A list of categories that has not changed in a year.
Controls that make a workbook safer
If you keep a client on Excel, add the controls the software does not have:
- A check cell on the transaction sheet that compares the sheet's balance with the statement's closing balance, and shows a warning when they differ.
- A locked, dated copy of each closed month, saved read-only.
- Data validation on the account column, so only names from the chart of accounts can be typed.
- A row count cell next to every total, so a short range shows up as a mismatch.
- A file naming rule with the month in the name, so there is one current file.
Review the workbook against the statements monthly, the way you would review any ledger. At the end of the year, the workbook should pass the same checks as any other client file; the year end bookkeeping checklist applies to spreadsheet books as well, and it is a good moment to decide whether the client stays on Excel for another year.