Cellwise Start free

Excel guide · 5 min read

How to make a petty cash log in Excel

Quick answerList each petty cash movement on its own row with date, description, category, amount out, amount in and whether there's a receipt. A running balance (previous balance + in - out) shows what should be in the tin. The top-up amount is the float minus the current balance, SUMIFS totals spend by category, and a check cell compares the cash you count with the log balance.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

RowABCDEFGHIJK
1DateDescriptionCategoryOutInBalanceReceiptFloatCash counted
2Nov 2, 2026Float from bankFloat20020020074.26
3Nov 3, 2026Milk and coffeeKitchen14.6185.4Yes
4Nov 4, 2026Courier to clientPostage22.5162.9Yes
5Nov 5, 2026Printer paperOffice supplies31.99130.91No
6Nov 6, 2026Advance to Sam for site parkingTravel20110.91Yes
7Nov 6, 2026Change returned by SamTravel8118.91
8Nov 10, 2026StampsPostage18.4100.51Yes
9Nov 12, 2026Biscuits for client meetingKitchen8.7591.76No
10Nov 13, 2026Taxi to bankTravel16.575.26Yes

Download this sheet as an Excel practice file (.xlsx)

Set up the log

Petty cash usually runs on an imprest float: the tin starts with a fixed amount (here 200.00 in J2), small purchases come out of it, and when it runs low you top it back up to the float. At any moment, cash in the tin plus receipts should add up to the float.

One row per movement, with separate Out and In columns rather than a single signed amount. People recording petty cash aren't always spreadsheet users, and "put the amount in the Out column" is harder to get wrong than "type a minus sign". Keep categories to a short fixed list (Data > Data Validation > List) so totals don't split between "Postage" and "postage stamps".

The first row is the float coming in from the bank. Advances need two rows: the 20.00 given to Sam for parking (Out) and the 8.00 change he brought back (In). Logging only the 12.00 he spent leaves the tin out by 8.00 for the days in between.

Step 1: Running balance

The first balance is just the first row: =E2-D2. Every row after adds what came in and subtracts what went out, starting from the balance above. Column F holds that formula, copied down; F10 is the latest balance. More running-total patterns, including one that restarts for each category, are in running total in Excel.

=F9+E10-D10

Result: 75.26

75.26: after the taxi on 13 November, the tin should hold 75.26. Blank In or Out cells count as 0, so every row works the same way.

Cross-check the running column

Total in minus total out gives the same balance without depending on every row's formula. If someone types over a balance cell, the two stop agreeing.

=SUM(E2:E10)-SUM(D2:D10)

Result: 75.26

75.26: 208.00 in (the float and Sam's change) minus 132.74 out.

Step 2: Count the cash and check it

Count the tin regularly, at least before every top-up, and type the total into K2. The difference between the count and the log balance should be 0. Round it, because adding decimals in Excel can leave tiny floating-point remainders.

  1. When it's short, look first for a purchase with no row (someone took cash and forgot to log it), then an advance with no change logged, then a typo: an amount entered as 18.40 instead of 18.04 leaves a gap divisible by 9.
  2. Record the final difference on its own row (category "Cash difference") with a note of who agreed it. Don't adjust other rows to make it balance.
=ROUND(K2-F10,2)

Result: -1

-1: the tin is 1.00 short of what the log says.

A check that reads in words

Easier for whoever does the count to understand.

=IF(ROUND(K2-F10,2)=0,"Balanced",IF(K2<F10,"Short by ","Over by ")&TEXT(ABS(K2-F10),"0.00"))

Result: Short by 1.00

Short by 1.00.

Step 3: How much to top up

With an imprest float, the top-up is whatever brings the tin back to the float: float minus the current balance. It always equals what was spent since the last top-up, less any cash that came back in.

=J2-F10

Result: 124.74

124.74: withdraw 124.74 to bring the tin back to 200.00. That's 132.74 spent less Sam's 8.00 change.

Top up from the count, not the log?

If the count is short, topping up from the log (124.74) leaves the tin at 199.00, and the shortfall carries forward quietly. Topping up from the count (200 - 74.26 = 125.74) restores 200.00 but needs the 1.00 difference recorded and approved. Decide which one your business uses and stick to it.

Step 4: Spend by category

For each category, total out minus total in, so returned change reduces the category it came from. One SUMIFS pair gives one category:

=SUMIFS(D2:D10,C2:C10,"Travel")-SUMIFS(E2:E10,C2:C10,"Travel")

Result: 28.5

28.5: the 20.00 advance less 8.00 change, plus the 16.50 taxi.

Every category at once (Excel 365 / 2021)

UNIQUE lists the categories after the float row, and SUMIFS returns one total for each. Put =UNIQUE(C3:C10) in the column to the left as labels.

=SUMIFS(D2:D10,C2:C10,UNIQUE(C3:C10))-SUMIFS(E2:E10,C2:C10,UNIQUE(C3:C10))

Result: spills 4 rows × 1 column

23.35
40.9
31.99
28.5

Kitchen 23.35, Postage 40.90, Office supplies 31.99, Travel 28.50. In older versions, type the category list and use one SUMIFS pair per row.

Step 5: Flag missing receipts

Every Out row should have a receipt. COUNTIFS counts the ones that don't, so they can be chased before the top-up is approved. Add conditional formatting on column G (Home > Conditional Formatting > Highlight Cells Rules > Equal To > No) to make them stand out in the list.

=COUNTIFS(D2:D10,">0",G2:G10,"No")

Result: 2

2: the printer paper and the biscuits have no receipt yet.

Which ones? (Excel 365 / 2021)

FILTER lists the descriptions so you know whose desk to visit.

=FILTER(B2:B10,(D2:D10>0)*(G2:G10="No"))

Result: spills 2 rows × 1 column

Printer paper
Biscuits for client meeting

Printer paper and Biscuits for client meeting.

Common mistakes

Logging only the net cost of an advance

Record the cash given out and the change returned as two rows, or the tin won't match the log until the change comes back.

Typing over the running balance

Keep F as formulas and check it against total in minus total out.

Adjusting rows to hide a shortage

Record the difference on its own row with a note. A log that always balances perfectly is a warning sign.

Free-typed categories

Use a Data Validation list so SUMIFS doesn't miss "postage" spelled another way.

FAQ

How do I keep track of petty cash in Excel?

One row per movement with date, description, category, out, in and receipt. A running balance column shows what should be in the tin, and a cell for the counted cash checks it.

How do I calculate the petty cash top-up?

Float minus the current log balance: =J2-F10. With an imprest float this equals what was spent since the last top-up, less any cash returned.

What formula gives a running balance for petty cash?

First row =E2-D2, then =F2+E3-D3 copied down: previous balance plus cash in minus cash out.

What should I do if petty cash doesn't balance?

Look for an unlogged purchase, an advance whose change wasn't recorded, or a typo. Record any remaining difference on its own row, with a note of who checked it.

Functions used

Related guides

Updated