Softwiro
Free Excel template

Cash Book Template for Excel

A free Excel cash book for a shop, cafe, workshop or any small business that handles cash. You log each movement once: the date, a short description, a category, and the amount under Cash in or Cash out. The sheet shows the balance after every entry, totals each category month by month, draws the charts, and lets you count the till and see whether the cash you hold matches the book. It opens in Excel, LibreOffice and Google Sheets, and it works in any currency.

Excel file (.xlsx) · 148 KB · 8 tabs · works in Excel and Google Sheets · no sign-up

The blank copy has the same formulas, charts and lists without the example data; categories and settings are ready.

Try the sheet here, on the example

The sheet is open below with sample numbers. Change any white cell and watch the results update, or press the blank-copy button to type your own data from scratch. Nothing you type leaves your browser.

Example from the sheet

Example from the “Dashboard” tab, with the numbers and formulas that are in the file.
Year shown2026Change the year, opening balance and currency on the Settings sheet.
Opening balance5,000.00Book balance, all entries6,161.25
Cash in (year)13,575.00Entries logged30
Cash out (year)12,413.75Entries without a valid category0
Net cash flow (year)1,161.25Largest expense categoryWages
MonthCash inCash outNetClosing balance
Jan0.000.000.005,000.00
Feb0.000.000.005,000.00
Mar0.000.000.005,000.00
Apr0.000.000.005,000.00
May0.000.000.005,000.00
Jun0.000.000.005,000.00
Jul0.000.000.005,000.00
Aug0.000.000.005,000.00
Sep0.000.000.005,000.00
Oct7,170.007,641.45-471.454,528.55
Nov6,405.004,772.301,632.706,161.25
Dec0.000.000.006,161.25

Example from the “Dashboard” tab, with the numbers and formulas that are in the file.

What is inside

  • Dashboard: The year's cash in, cash out and net flow, the book balance, the number of entries without a valid category and the largest expense category, with three charts: cash in and out by month, the cash balance at each month end, and expenses by category.
  • Cash Book: The daily log: date, reference, description, category, Cash in, Cash out. It adds the running balance, the category type and a Check column that flags an entry with both amounts or with none. Room for 2,000 entries.
  • Monthly: Every category across the twelve months of the chosen year, with total cash in, total cash out and net cash flow for each month, and the year total of each category.
  • Categories: Your list of income and expense categories (up to 40). They feed the dropdown in the Cash Book and decide which column of the Cash Book each category adds up.
  • Reconcile: Pick a count date, enter how many notes and coins you hold of each denomination, and read the cash book balance on that date, the cash counted, the difference and whether you are balanced, over or short.
  • Settings: Business name, currency symbol, the year to report on, the opening balance and its date.

The file opens on a start tab (“Start here”) that explains how to use it.

What makes it different

Most cash book templates stop at a running balance. This one also totals every category by month, charts where the cash went, and ends with a cash count so the book is checked against the money in the drawer. The sample is a small shop with entries in October and November 2026, so you can see every sheet working before you replace it with your own.

How do I keep a cash book in Excel?

Start with the cash you hold on the day you begin and type it in Settings as the opening balance. From then on, add one row per movement in date order: money received goes under Cash in, money paid out goes under Cash out. Each row's balance is the opening balance plus everything in so far minus everything out so far, so you never type a balance yourself.

Give every row a category from the dropdown. That is what turns a list of entries into a summary you can read: how much came from sales, how much went on rent, wages or stock.

How do I check my cash book against the cash I counted?

Count the notes and coins at a set time, enter the count date on the Reconcile sheet and type how many you hold of each denomination. The sheet adds the book entries up to that date and compares the two figures. In the sample, the cash counted on 2026-11-14 is 5,368.75 and the book shows 5,378.75, so the sheet reports the difference of 10.00 as cash short.

A difference is a prompt to look, not a verdict. Check for an entry typed on the wrong side, a missing receipt or change given wrongly. Entries dated after the count date are left out on purpose.

How is a cash book different from a bank reconciliation?

A cash book records movements of cash as they happen and keeps a balance. A bank reconciliation compares your records with a bank statement. This template does the first, and its Reconcile sheet compares the book with physical cash. If you also want to plan ahead, see the cash flow forecast template, and for a spending plan the business expenses budget.

How to use it

  1. On the Settings sheet type your business name, currency symbol, the year to report on, and the opening balance: the cash you had on the opening date, before the first entry in the Cash Book.
  2. On the Categories sheet list your income and expense categories and mark each one Income or Expense. The sample categories can be renamed.
  3. On the Cash Book sheet log every movement in date order: date, reference, description, category, then the amount under Cash in or Cash out.
  4. The running balance appears after every entry and turns red if cash goes below zero. A Check column flags entries with both amounts or none.
  5. Open Monthly to see each category month by month, and Dashboard for the year totals and charts.
  6. On Reconcile count the cash by denomination (or type the notes and coins you hold), pick the count date, and read the difference from the book balance.

Before you start:

  • Capacity: 2,000 cash book entries and 40 categories.
  • Log entries in date order so the running balance reads correctly. The opening balance is the cash you held before the first entry.
  • Monthly and Dashboard show the year typed on Settings. Change the year to look at another one.
  • An entry whose category is not on the Categories sheet is labelled Uncategorised and stays out of the Monthly summary.
  • The Dashboard expense chart shows the first 12 expense categories on your list.
  • Use one currency in one workbook. The currency symbol on Settings is only a label.

Excel file (.xlsx) · 148 KB · 8 tabs · works in Excel and Google Sheets · no sign-up

The blank copy has the same formulas, charts and lists without the example data; categories and settings are ready.

Still running this on Excel?

This sheet needs you to open it and type in every expense. In MyAccBot you send a photo of the receipt or a voice message on Telegram, and it handles the rest.

In this sheetIn MyAccBot
You open the sheet and type in every expense by hand.You send a photo of the receipt, a line of text or a voice message on Telegram, and the data is extracted and classified as income or expense.
You find out about a budget overrun at the end of the month.You set a monthly spending limit and get an alert at 80% and at 100%.
Summing up the month takes hours.An Arabic PDF report on the first of every month, plus a formatted XLSX file you hand to your accountant.
You dig through old receipts looking for an amount.You ask the bot for the total of a month, between two dates, or for a particular supplier, and it answers from your data.

See MyAccBot · Ask for a demo

Frequently asked questions

Is this cash book template really free?

Yes. Download it, use it for your business and change anything you like. There is nothing to sign up for to open the file.

Does it work in Google Sheets and LibreOffice?

Yes. It uses only ordinary formulas such as SUMIFS, INDEX and MATCH, so it works in Excel 2016 and later, LibreOffice and Google Sheets.

How many entries can it hold?

2,000 cash book entries and 40 categories. When the log is full, save a copy, enter the closing balance as the new opening balance and start a fresh one.

Can I use my own currency?

Yes. The sheet has no fixed currency. Type your symbol in Settings, and change the denominations on the Reconcile sheet to match your notes and coins.

Why does an entry say Uncategorised?

Its category is blank or is not on the Categories sheet. Pick it from the dropdown or add the category to the list. Until then it stays out of the Monthly summary.

Is the template free?

Yes, with no sign-up and no email address. Use it, edit it and share it as you like.

Does it work in Google Sheets and on mobile?

The sheet uses formulas supported by Excel 2016 and later, Google Sheets and LibreOffice, so you can upload it to Google Drive and open it with Google Sheets on a computer or a phone.

What is the difference between the white and the gray cells?

You type in the white cells; the gray cells are calculated automatically. Don't type in a gray cell, or you will erase its formula.

Can I use the sheet from an AI assistant?

Yes. Add our MCP server (URL https://softwiro.com/api/mcp, no key needed) to your assistant and ask it to do the calculation in this template. The numbers come from the sheet's own formulas, and we do not store your data. The steps and a ready-made prompt are in this article: https://softwiro.com/blog/mcp-excel-templates-ai-agent-guide.html

More free templates for: Shops and stores

More free templates

All templates · Free tools