Spreadsheets

Petty cash register format in Excel for a small office

A petty cash register format in Excel for a small office: voucher number, payee, purpose, amount, approver, running balance, top-ups and a closing cash check.

Editorial illustration for Petty cash register format in Excel for a small office

A petty cash register is a running list of small cash payments with a balance after each line. Every row needs a voucher number, a date, who was paid, why, how much, and who approved it. When the cash tin is low, a top-up row restores the float, and a closing check compares the cash actually in the tin with the balance the register says should be there.

What columns does the register need?

Row 1 headers, one line per voucher:

  • A Date, B Voucher no., C Paid to, D Purpose, E Category (tea and refreshments, courier, stationery, travel, repairs)
  • F Received (top-ups), G Paid (vouchers), H Approved by, I Bill attached (Yes or No)
  • J Balance, the running cash that should be in the tin

Write the opening float in the first row under Received. In J2 write =F2-G2. From J3 on write =J2+F3-G3 and fill down. Every voucher reduces the balance, every top-up raises it.

How does the float or imprest system work?

In an imprest system the tin always starts a period with a fixed amount, say ₹10,000. Staff spend from it against vouchers, and when the balance is low the cashier asks for a top-up equal to what was spent, restoring the tin to ₹10,000. The top-up amount always equals the total of vouchers since the last top-up, which makes checking easy.

Made-up example: opening float ₹10,000. Vouchers during the week are tea and snacks ₹450, courier ₹1,200, stationery ₹380 and auto fare ₹2,150. The total is 450 + 1,200 + 380 + 2,150 = ₹4,180, so the balance is 10,000 - 4,180 = ₹5,820. The cashier requests ₹4,180, records it in column F, and the balance returns to ₹10,000.

How do I do the closing cash check?

At the end of each week or month, count the notes and coins and enter the total in a cell, say M2. Next to it, in M3, write =M2-LOOKUP(2,1/(J2:J500<>""),J2:J500), which subtracts the last filled balance in column J from your count. Zero means tin and register agree. A positive difference is excess cash; a negative one is a shortage.

Do not hide a difference with a made-up voucher. Record it in a line called Cash over/short with the reason, if known, and have the approver initial it. Small differences from rounding or a missed receipt are better written down than buried.

What about vouchers without bills?

Some small payments, such as a tip or an auto ride, come without a bill. Write a voucher for them with the date, amount, purpose and the payee's name, get it signed by the approver, and tick No under Bill attached. Keep these few, and do not use them for large amounts. How such payments are treated for accounts and tax is a question for your accountant, since rules and limits change.

Can I total by category?

Yes. In a small table to the right, list the categories and use =SUMIFS($G$2:$G$500,$E$2:$E$500,"Courier") for each. Add a month filter by including a date range, like ">="&$P$1 and "<"&EDATE($P$1,1), to get a monthly figure. This lets you see whether the ₹4,180 in the example is mostly travel, and whether a regular item deserves a proper account or card instead of cash.

Monthly closing checklist

  1. Count the cash and write the total on the register.
  2. Compare it with the last balance and record any difference.
  3. File vouchers in number order with their bills.
  4. Request the top-up, record it, and have it countersigned.
  5. Hand a copy of the register to your accountant. If some purchases are on a business card or UPI, SpendixAI can keep those next to the cash entries you add by hand.
SPENDIXAI JOURNAL

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

Explore your reports