Spreadsheets

Running balance column for a cash book in Google Sheets

Build a cash book in Google Sheets with receipts, payments and a running balance formula that handles blank rows and shows the closing balance.

Editorial illustration for Running balance column for a cash book in Google Sheets

A cash book in Google Sheets needs four columns: date, particulars, receipts and payments, plus a balance column. Type the opening balance once, then each row's balance is the opening balance plus all receipts so far minus all payments so far. One formula copied down handles it, and a blank guard keeps unused rows empty.

The layout

ColumnHeadingWhat goes in it
ADateThe date of the entry, as a real date
BParticularsA short description
CReceiptsMoney coming in, as a number
DPaymentsMoney going out, as a number
EBalanceThe formula

Row 1 holds the headings. Row 2 is the opening balance row: write Opening balance in B2 and the amount in E2, typed as a plain number such as 5000, not as text like Rs 5,000. Entries start in row 3.

What is the running balance formula?

In E3, enter:

=IF(COUNTA(C3:D3)=0,"",$E$2+SUM($C$3:C3)-SUM($D$3:D3))

Read it as: if both the receipt and payment cells in this row are empty, show nothing; otherwise take the opening balance, add every receipt from row 3 down to this row, and subtract every payment up to this row. Copy it down a few hundred rows now, and new entries will fill themselves in as you type them. No ARRAYFORMULA or SCAN is needed.

The reason to avoid the shorter chain, =E2+C3-D3, is the blank rows. In a blank row it would return the previous balance again; once you add the empty-string guard, the next row would try to add text to a number and fail. Anchoring everything to the opening balance cell sidesteps that: each row's answer depends on the amounts above it, not on the previous result.

How do I show the closing balance?

In a cell outside the table, say G1, enter:

=E2+SUM(C3:C500)-SUM(D3:D500)

It returns the balance after every entry. To check your work, the last filled balance in column E must equal it. Hypothetical entries to test with:

DateParticularsReceiptsPaymentsBalance
01 OctOpening balance--₹5,000
02 OctPetty cash top-up₹10,000-₹15,000
03 OctCourier charges-₹450₹14,550
05 OctStationery-₹1,275₹13,275
08 OctCash from customer₹3,200-₹16,475

Closing balance: 5,000 plus receipts of 13,200 (10,000 + 3,200), minus payments of 1,725 (450 + 1,275), gives ₹16,475.

Can I stop the balance going negative without noticing?

Add a conditional format on column E: Format, Conditional formatting, apply to E3:E500, rule Custom formula is =AND(ISNUMBER(E3),E3<0), fill red. A negative balance in a cash book usually means a missing receipt or a typing slip, such as a payment entered in the receipts column. A cash book balance below what is physically in the drawer is worth investigating the same day.

How do I stop mistakes creeping in?

Two habits help. Enter every cash movement on the day it happens, even small ones, because a forgotten ₹60 auto-rickshaw fare is how a small difference becomes a puzzle at month end. And have one person responsible for the book, with a second person checking it against the physical cash weekly. A cash book is only as trustworthy as the discipline of its entries, not just its formula.

What do I do at month end?

  1. Compare the closing balance with the cash you actually count.
  2. If they differ, search the month for entries in the wrong column or a missing date.
  3. Copy the closing balance into the next month's opening cell, as a number.
  4. Keep each month on its own tab, or add a month column if you prefer one long sheet.

When is a cash book enough?

For a small shop, a society's petty cash, or a household's cash envelope, a cash book is a good fit because it records what you physically hold. It is a poor fit for card and UPI spending, which is already recorded by your bank. For those, uploading the statement is less typing than copying lines. SpendixAI lets you add cash expenses by hand, several at once, and import bank statements, which can sit alongside a cash book you keep separately.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports