Excel guide · 5 min read
How to make a petty cash log in Excel
Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E | F | G | H | I | J | K |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Date | Description | Category | Out | In | Balance | Receipt | Float | Cash counted | ||
| 2 | Nov 2, 2026 | Float from bank | Float | 200 | 200 | 200 | 74.26 | ||||
| 3 | Nov 3, 2026 | Milk and coffee | Kitchen | 14.6 | 185.4 | Yes | |||||
| 4 | Nov 4, 2026 | Courier to client | Postage | 22.5 | 162.9 | Yes | |||||
| 5 | Nov 5, 2026 | Printer paper | Office supplies | 31.99 | 130.91 | No | |||||
| 6 | Nov 6, 2026 | Advance to Sam for site parking | Travel | 20 | 110.91 | Yes | |||||
| 7 | Nov 6, 2026 | Change returned by Sam | Travel | 8 | 118.91 | ||||||
| 8 | Nov 10, 2026 | Stamps | Postage | 18.4 | 100.51 | Yes | |||||
| 9 | Nov 12, 2026 | Biscuits for client meeting | Kitchen | 8.75 | 91.76 | No | |||||
| 10 | Nov 13, 2026 | Taxi to bank | Travel | 16.5 | 75.26 | Yes |
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.
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.
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.
- 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.
- 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.
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.
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.
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:
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.
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.
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.
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
SUMAdds numbersSUMIFSAdds with several conditionsCOUNTIFSCounts with several conditionsUNIQUERemoves duplicates, spillsFILTERKeeps matching rows, spillsROUNDRounds to digitsIFPicks a result from a testABSAbsolute valueTEXTNumber to formatted text
Related guides
- How to make an expense report in Excel
- How to make a running total (cumulative sum) in Excel
- How to do a bank reconciliation in Excel
- SUMIF with multiple criteria in Excel (SUMIFS)
- Excel formulas cheat sheet: the 25 everyone should know
- How to calculate agent occupancy in Excel
- How to do a price volume mix analysis in Excel
Updated