HHISAB 360 Get HISAB

Accounting in Excel: What It Can Do, the Controls It Lacks, and When to Move to QuickBooks, Xero or Zoho Books

Guide · By the HISAB 360 team · Published 16 September 2026 · Updated 24 October 2026 · ~13 min read

Tested in Excel. On 24 October 2026 we asked HISAB 360 to build every Excel example in this guide for a sample company, in Excel for Microsoft 365 on Windows: the journal and its balance check, the trial balance, customer balances, the invoice register with ageing and a PivotTable, and the cash book. We recomputed every balance separately; they match. We also re-checked each outside source linked below on the same day. The screenshots are from that workbook (select one to see it full size), and you can download it.

Accounting in Excel works for a small, simple business: a cash book, sales and purchase ledgers, a trial balance, invoices and a basic bank reconciliation can all live in one workbook. What Excel cannot give you are the controls: an audit trail, locked periods, safe multi-user entry, bank feeds and direct tax filing. This guide shows how to set up the workable version properly, where it breaks, and how to keep Excel for analysis once a real ledger holds the books.

Sources last checked: 24 October 2026. We make HISAB 360, an AI assistant inside Windows desktop Excel that connects to accounting systems, so it comes last, with its limits.

Can Excel be used as accounting software?

Excel can keep the books for a small, simple business, but it is not accounting software. It will add up whatever you type, balanced or not, and keeps no dependable record of who changed a number last March.

How to set up bookkeeping in Excel for a small business

Accounting in Excel holds together only if you follow one rule: enter each transaction once, in one table, and build every report from that table with formulas. Separately typed sheets drift apart within weeks.

Choose one of two designs.

DesignHow it worksGood forLimits
Single-entry cash bookOne row per receipt or payment: date, description, category, money in, money outSole traders and landlords who mainly need income and expenses by categoryNo balance sheet, no debtors or creditors, no trial balance
Double-entry journalOne row per debit or credit line, coded to a chart of accounts; every entry nets to zeroSmall companies, anyone who needs a trial balance, balance sheet or aged debtorsMore set-up, and Excel will not force entries to balance; your checks must

1. A chart of accounts and a journal table

Put the chart of accounts on its own sheet (code, name, and type: Asset, Liability, Equity, Income or Expense). On a second sheet, create an Excel table (Insert > Table) named tblJournal with these columns: Date, Ref, Account, Contact, Description, Debit, Credit, TaxCode and Source (the invoice or receipt number).

Add a drop-down on Account that lists only valid codes (Data > Data Validation > List), so nobody can post to an account that does not exist. If several people will key entries, a form is safer than typing into the table directly; see how to create a data entry form in Excel.

Then add a balance check column to the table. Each reference (one journal, invoice or payment) must net to zero:

=ROUND(SUMIFS([Debit],[Ref],[@Ref])-SUMIFS([Credit],[Ref],[@Ref]),2)

ROUND stops tiny decimal remainders flagging a balanced entry. Any row not showing 0 belongs to an unbalanced entry: add a conditional formatting rule that turns rows red when this column is <>0, and do not close a month while any remain (conditional formatting formulas need normal cell references, for example =$J2<>0 if the check is in column J).

Excel journal table with a balance check column; two rows of an unbalanced entry highlighted red
The journal table with the balance check in column K (its formula is in the formula bar). Entry J007 was left 50 short on purpose: both of its rows turn red.

Protect the workbook from the start: lock formula columns, save a dated backup after each session, and start each financial year in a new file with opening balances brought forward.

2. An Excel cash book

In a double-entry workbook, the cash book is every line posted to your bank account code: filter tblJournal on that code. In a single-entry cash book, give the table a running balance column. It adds all rows up to the current one, plus an opening balance in a cell named OpeningBalance:

=OpeningBalance+SUM(INDEX([Money in],1):[@[Money in]])-SUM(INDEX([Money out],1):[@[Money out]])

Keep rows in date order; the running balance follows row order, not dates.

Single-entry cash book in Excel with a running balance column
A single-entry cash book: the Balance column uses the INDEX formula above and starts from the named OpeningBalance cell.

3. Sales and purchase ledgers

The sales ledger (debtors) is the set of customer balances inside your trade debtors control account. With a Contact column in the journal, one formula gives each customer's balance. Here 1100 is the debtors control account and A2 holds the customer name:

=SUMIFS(tblJournal[Debit],tblJournal[Account],1100,tblJournal[Contact],$A2)-SUMIFS(tblJournal[Credit],tblJournal[Account],1100,tblJournal[Contact],$A2)

Customer balances must add up to account 1100; if not, a line was posted without a contact or with a misspelt one. The purchase ledger works the same way on the creditors control account, signs reversed.

For aged debtors, keep an invoice register table named tblInvoices with InvoiceNo, Contact, DueDate and Outstanding columns, put a report date in a cell named ReportDate, then bucket each invoice:

=IF([@Outstanding]=0,"Paid",IF(ReportDate<=[@DueDate],"Current",IF(ReportDate-[@DueDate]<=30,"1-30",IF(ReportDate-[@DueDate]<=60,"31-60",IF(ReportDate-[@DueDate]<=90,"61-90","Over 90")))))

A PivotTable on that column, with customers as rows, gives you an aged debtors report. Drag the column headings into age order (Current, 1-30, 31-60, 61-90, Over 90) once; the PivotTable keeps that order when refreshed. Filter out Paid in the PivotTable, and set ReportDate to today: Outstanding is the current balance, so an older report date will not give historical ageing.

Excel invoice register with an age bucket column and the next invoice number
The invoice register: InvoiceNo is a plain number shown as INV-1001 by a number format, so the next number is =MAX(tblInvoices[InvoiceNo])+1.
Aged debtors PivotTable by customer with Current, 1-30, 31-60, 61-90 and Over 90 columns
The aged debtors PivotTable. The buckets first came out in alphabetical order; one drag of Current to the left fixed it, and the order held after a refresh.

4. The trial balance

On a Trial Balance sheet, list every account code in column A and its name in column B, and put the period-end date in a cell named TBDate. In column C, the net balance for each account is:

=SUMIFS(tblJournal[Debit],tblJournal[Account],$A2,tblJournal[Date],"<="&TBDate)-SUMIFS(tblJournal[Credit],tblJournal[Account],$A2,tblJournal[Date],"<="&TBDate)

Split it into a debit column D with =MAX(C2,0) and a credit column E with =MAX(-C2,0), total both, and add a check cell below the totals (for example =ROUND(SUM(D2:D200)-SUM(E2:E200),2)=0). If it says FALSE, the balance check column above will show you where.

Excel trial balance whose debit and credit totals differ by 50, with the check showing FALSE
With J007 still 50 short, debits exceed credits by 50 and the check cell reads FALSE.

One catch: a cumulative trial balance keeps adding income and expenses year after year. At each year end you need a closing journal that moves the year's profit into retained earnings, or income and expense formulas that only sum dates within the financial year. From the trial balance you can build a balance sheet with a built-in balance check (free template).

5. Invoices

Microsoft's accounting templates include several invoice designs. Pair the template with the tblInvoices register and take the next number from it (=MAX(tblInvoices[InvoiceNo])+1) so numbers stay sequential. Keep InvoiceNo as a plain number and add any prefix with a number format. Once an invoice is issued, record its number as a value so it cannot change when the next row is added, and save the invoice as a PDF.

6. A simple bank reconciliation

Export the bank statement to CSV, paste it next to your cash book lines, and match on amount and date. Our free bank reconciliation template does the matching with COUNTIFS on amount; a deposit that covers several invoices still needs a person to match it.

Free accounting software in Excel: what the downloads actually are

Most "free accounting software in Excel" downloads are workbooks, not software: formula templates or macro-driven files with menus and forms. They keep simple books but add none of the controls below. For those controls at no cost, look at accounting products with free plans.

OptionWhat it isWatch out for
Free templatesFormula workbooks for invoices, ledgers and statements from Microsoft, or reconciliation and statement sets such as our free Excel templates for accountantsYou maintain and protect the formulas yourself
Macro-based Excel "accounting systems"VBA workbooks with menus and entry forms that look like softwareOnly enable macros in files from a source you trust; still no audit trail, period locks or bank feeds
Free plans of accounting softwareReal ledgers at no charge, for example Zoho Books' US Free plan, available while revenue for the financial year does not exceed $50KLimits on users and volumes; check the plan page

The controls Excel does not have

Excel has no reliable audit trail, period locks, bookkeeping roles, enforced double entry or bank feeds. The gaps rarely show on day one; they show when you need to prove what happened.

No reliable audit trail

Excel for Microsoft 365 has Show Changes, which lists who changed which cell and its previous value for up to 365 days. Useful, but not an audit trail. Microsoft's troubleshooting page says edits made in a one-time-purchase or older version of Excel cannot be tracked and clear the Changes pane, and that formatting changes, chart and shape edits, PivotTable operations, filtering and hiding are not tracked. Version history only works for files stored in OneDrive or SharePoint.

Compare QuickBooks Online: its audit log records the user, the date and the original transaction details, keeps events for two years, and Intuit says you cannot turn it off.

No way to lock a closed period

Once a VAT return is filed or a month closed, the figures behind it should not change. In Excel, anyone who can edit the file can change a January invoice in September, and every total quietly recalculates. Sheet protection does not solve this: Microsoft's own worksheet protection page says it is not intended as a security feature.

Accounting software builds this in. QuickBooks Online lets you close the books at a date, with changes to earlier transactions allowed only after a warning or a password. Zoho Books' transaction locking freezes transactions before a lock date and asks for a reason when you lock or unlock.

Multi-user entry without roles

Several people can work in one workbook at the same time through co-authoring, but Microsoft says that needs a Microsoft 365 subscription and a file stored on OneDrive, OneDrive for Business or SharePoint Online. Even then, anyone with edit access can change the chart of accounts, a formula or last year's journals; accounting software separates who can raise, approve and post.

Double entry is not enforced

Excel will save an unbalanced journal or a number typed over a formula. Your checks catch it only if someone looks; accounting software will not save an unbalanced journal at all.

No bank feeds

Excel has no business bank feed. Microsoft's consumer add-on Money in Excel, for Microsoft 365 Personal and Family subscribers, ended on 30 June 2023. In Excel you import statements yourself; accounting software pulls transactions from connected bank accounts.

Tax filing: what the rules actually say

Tax authorities rarely ban spreadsheets. What they increasingly require is digital records plus software that submits to them.

Excel vs QuickBooks, Xero or Zoho Books: which should you use?

QuickBooks Online, Xero and Zoho Books enforce double entry, lock closed periods, keep an audit log and pull bank feeds; Excel does none of that, but it is far better for custom analysis. Features vary by plan, so check the vendor's plan page before you buy.

JobExcelAccounting software (QuickBooks Online, Xero, Zoho Books)
Enforce balanced double entryNo; check formulas onlyYes
Trial balance, P&L, balance sheetYes, with SUMIFS and PivotTables you build and maintainBuilt in
Invoices and aged debtorsTemplate, register and formulasBuilt in
Bank feedsNo; manual CSV or PDF importYes, for supported banks
Bank reconciliationManual or formula matchingSuggested matches against the feed
Audit trailShow Changes, with gaps; version history on OneDrive or SharePointSystem audit log
Lock closed periodsNo; sheet protection is not a security featureYes (closing date or lock date)
Multi-user with rolesCo-authoring, no bookkeeping rolesUser roles and permissions
UK MTD submissionsOnly with bridging software and digital linksCompatible products submit directly; HMRC has a software finder
Custom analysis and modelsExcellentLimited to built-in and custom reports

When is Excel enough, and when do you need accounting software?

If any row in the right-hand column describes you, Excel should stop being your ledger.

Excel is usually enough when…Move the ledger to accounting software when…
One person keeps the booksTwo or more people enter or approve transactions
You can review every transaction in a month in one sittingKeying and matching bank lines takes days each month
You are not VAT-registered and below the MTD for Income Tax threshold, or you use bridging software with digital linksYou are VAT-registered or in MTD for Income Tax and want to submit without bridging
No employees, or payroll is outsourcedYou run payroll yourself
Only you rely on the numbersA lender, investor, auditor or external accountant relies on them
Invoices are simple PDFsYour business-to-business invoices fall within a mandatory e-invoicing regime, such as the UAE's

The real cost of staying too long is the clean-up when an accountant has to rebuild a year from a workbook nobody can audit.

The hybrid: a real ledger for the books, Excel for the analysis

Moving to accounting software does not mean giving up Excel. A common, sensible set-up for small finance teams splits the work:

The weak point is the connection between them. Free routes:

  1. Export reports (trial balance, general ledger, aged debtors) to Excel. Simple, but stale as soon as someone posts a late entry.
  2. Import journals and lists from a spreadsheet using each product's CSV import. See how to import journal entries into QuickBooks Online from Excel.
  3. Use a connector or add-in that pulls ledger data into Excel and, for some, sends changes back. Our guides to connecting QuickBooks and Xero to Excel, getting Xero data into Excel and ERP to Excel integration compare the options.

Migrating from Excel? Begin with a new VAT quarter or financial year. Import the chart of accounts and contacts, post opening balances from your Excel trial balance, then enter open invoices and bills individually so aged reports are right, following your software's conversion guide so debtors and creditors are not counted twice (in most products the opening journal leaves those control-account balances out, and the open invoices and bills rebuild them). Keep the old workbook read-only.

How HISAB 360 handles this

HISAB 360 is for the hybrid set-up, not a replacement for your ledger. It is an AI assistant inside Windows desktop Excel that connects to QuickBooks Online, Xero, Zoho Books, Odoo and FreshBooks.

The workbook in this guide was built inside Excel the same way, from requests typed into the HISAB panel. The first was:

Set up a small double-entry bookkeeping workbook for a sample company, Northwind Trading, for January 2026:

  1. A COA sheet with the chart of accounts (code, name, type): 1000 Bank, 1100 Trade debtors, 2100 Trade creditors, 2200 VAT, 3000 Capital, 3100 Retained earnings, 4000 Sales, 5000 Purchases, 6100 Rent, 6200 Software.
  2. A Journal sheet with an Excel table named tblJournal with columns Date, Ref, Account, Contact, Description, Debit, Credit, TaxCode, Source. Add about 14 sample lines for January: capital introduced, sales invoices to Acme Ltd and Brightside Co, a receipt from Acme, a stock purchase on credit, rent and software. Make every entry balance except one deliberately unbalanced entry, so we can see the checks catch it. Give Account a drop-down that only allows the COA codes, and add a balance check column =ROUND(SUMIFS([Debit],[Ref],[@Ref])-SUMIFS([Credit],[Ref],[@Ref]),2) with conditional formatting that turns the row red when it is not 0.
  3. A Trial Balance sheet: every account code and name, the period-end date in a cell named TBDate (31/01/2026), the net balance with SUMIFS on the journal up to TBDate, debit and credit columns with MAX, totals, and a check cell that must be TRUE.
  4. A Debtors sheet with each customer's balance on account 1100, and a check that they add up to the 1100 balance on the trial balance.

Use live formulas and make it look professional: Aptos font, a dark green header row with white text.

It used the balance-check, trial balance and SUMIFS formulas shown above, and both checks caught the deliberate error. Follow-up requests fixed J007, then added the invoice register, the aged debtors PivotTable and the cash book. Two things it could not do with its own tools: hide a table's filter buttons and set the column order inside a PivotTable. Both are one click for you (Table Design > Filter Button, and dragging the heading).

HISAB 360 panel in Excel showing the request to fix entry J007, next to a trial balance whose check reads TRUE
HISAB 360 in Excel: the request to fix J007 in the panel, and the trial balance balancing afterwards.

The limits:

Not for: a business whose books live only in Excel and intends to keep it that way. For those, the formulas and templates above are the right tools.

Start free → See pricing