Petty cash is a small amount of cash that a business keeps for minor expenses such as stationery, courier fees, tea and snacks. Because it is cash, it is easy to lose track of, so every payment and every top-up should be written down. A petty cash log in Excel does this simply. It lists each transaction, keeps a running balance, shows how much was spent in each category and helps you reconcile the cash in the box with the book. This guide shows how to set up the columns, write the running balance formula, add drop-downs for categories, total the spending, reconcile the cash and make a monthly report. A small example with numbers you can check by hand is used throughout.
Updated November 2026
In this guide
- How a petty cash system works
- Plan the columns
- Example data
- Step 1: enter the opening balance
- Step 2: write the running balance formula
- Step 3: use a table so formulas fill automatically
- Step 4: add category drop-downs
- Step 5: total the spending by category
- Step 6: reconcile the cash
- Step 7: calculate the top-up
- Step 8: check for missing vouchers
- Protect the log
- Common mistakes
- Practice problems
- Quick recap
How a petty cash system works
Most businesses use an imprest system. A fixed float, for example 500, is placed in the cash box. When someone needs money, they get it against a voucher or a receipt. At any time, the cash in the box plus the receipts should equal the float. When the cash runs low, the person in charge tops it up to the float, and the amount of the top-up equals the total of the receipts since the last top-up. A log in Excel records each step and shows whether the numbers agree.
Plan the columns
Use a table with these columns: Date, Voucher No, Description, Category, Cash In, Cash Out and Balance. Put the title and the float amount above the table, for example in cells A1 and B2. Add a space for the name of the person responsible. The voucher number links each line to a paper receipt, which is vital in an audit. Keep the columns in this order so that the log reads like a bank statement.
Example data
Start with a float of 500 on 1 March 2027. Then record these items. On 2 March, stationery of 45 with voucher 1. On 4 March, courier of 30 with voucher 2. On 8 March, tea and snacks of 60 with voucher 3. On 10 March, fuel of 85 with voucher 4. On 15 March, a top-up of 220 in cash. The total spent before the top-up is 45 plus 30 plus 60 plus 85, which is 220. That is why the top-up is 220, and the balance returns to 500.
Step 1: enter the opening balance
In the first row of the table, enter the date, the description Opening float and 500 in the Cash In column. Leave Cash Out empty. In the Balance column of this row, type =E5-F5 if Cash In is column E, Cash Out is column F and the row is 5. The result is 500. This row starts the log, and every later row builds on it, so make sure it is correct before you continue.
Step 2: write the running balance formula
In the next row, use =G5+E6-F6, where G is the Balance column. It takes the previous balance, adds the cash in and subtracts the cash out. After the stationery payment of 45 the balance is 500 plus 0 minus 45, which is 455. After the courier of 30 it is 425, after the tea and snacks of 60 it is 365, and after the fuel of 85 it is 280. After the top-up of 220 it is 500 again. Fill the formula down for as many rows as you need.
Step 3: use a table so formulas fill automatically
Select the headers and the data and press Ctrl+T. In a table, the running balance formula can be filled down automatically for new rows. If you prefer a simple formula that does not refer to the row above, use =SUM($E$5:E5)-SUM($F$5:F5), which adds all the cash in and subtracts all the cash out up to the current row. This version is easy to copy, and it does not break when you insert a row. Our table guide covers the benefits of tables in more detail.
Step 4: add category drop-downs
Create a list of categories on another sheet, such as Stationery, Courier, Refreshments, Fuel, Repairs and Other. Select the Category column, go to Data, Data Validation, choose List and point the source to the list. Everyone then picks the same spelling, so the totals work. A short list is better than a long one, because it is faster to use and the report is easier to read. Our drop-down list guide explains the steps.
Step 5: total the spending by category
On a Summary sheet, list the categories and use SUMIFS to total the cash out for each. For example =SUMIFS(PettyCash[Cash Out],PettyCash[Category],A2). With our example, Stationery is 45, Courier is 30, Refreshments is 60 and Fuel is 85, and the sum is 220. Our SUMIFS guide shows how to add a date criterion to get one month at a time. A pie chart of the categories, as shown in our pie chart guide, helps to explain where the money goes.
Step 6: reconcile the cash
At the end of each week or month, count the cash in the box and compare it with the balance in the log. Add a small block with three lines: the balance in the log, the cash counted and the difference. The formula for the difference is the counted cash minus the log balance. If the log says 280 and you count 280, the difference is 0. If you count 275, the difference is negative 5, which means that 5 is missing or a receipt was not recorded. Investigate the difference at once, and write the explanation in the log.
Step 7: calculate the top-up
The top-up equals the float minus the cash that is left. If the float is 500 and the log shows 280, the top-up is 220. Use a cell with the formula =B2-G9, where B2 holds the float and G9 holds the latest balance. After the top-up, enter it as a Cash In row, so that the balance returns to 500. This also gives you the total of the receipts to pass to the accountant or to the bookkeeping system.
Step 8: check for missing vouchers
A voucher number should never repeat or skip. To check, add a helper column with =COUNTIF([Voucher No],[@[Voucher No]]). A result above 1 means a duplicate. For gaps, compare the highest voucher number with the count of the numbers, since 4 vouchers with a highest number of 4 means no gaps. Add conditional formatting to highlight duplicates in red. Keep the paper receipts in numerical order in an envelope or a file, so you can find any receipt quickly.
Protect the log
Lock the Balance column and the summary formulas, and protect the sheet with a password that only the finance person knows. Keep a backup copy at the end of each month. Do not allow people to delete rows, because a deleted transaction can hide a loss. If you need to fix a mistake, add a correcting entry and explain it in the Description column. This leaves a clear trail, which auditors like to see.
Common mistakes
The most common mistake is to forget to record small payments, so the balance is wrong. Another is to type the balance by hand instead of using a formula. A third is to let several people keep the cash without a clear owner. A fourth is to mix personal money with petty cash. A fifth is to keep receipts that are unreadable, which is a problem for tax records. A sixth is to leave the voucher column empty. Use one person, one cash box and one log, and review it every week.
Practice problems
Try these. Problem 1: with a float of 500 and payments of 45, 30 and 60, the balance is 365. Problem 2: after a further payment of 85, the balance is 280 and the top-up needed is 220. Problem 3: if the box holds 270 when the log says 280, the difference is negative 10. Problem 4: total the spending by category for the example and check that the sum is 220. Problem 5: build the log with all the formulas and add an extra expense of 40 for repairs, which gives a balance of 240.
Quick recap
Create a table with Date, Voucher No, Description, Category, Cash In, Cash Out and Balance. Enter the opening float, and use a running balance formula that adds cash in and subtracts cash out. Use drop-downs for categories, total the spending with SUMIFS and count the cash regularly against the balance. Calculate the top-up as the float minus the balance, and keep the vouchers in order. A clear log makes the cash easy to control and the accounts easy to close.
Frequently asked questions
How do I make a petty cash log in Excel?
Create a table with date, voucher, description, category, cash in, cash out and a running balance formula.
What is the running balance formula?
Add the previous balance and the cash in, then subtract the cash out.
How do I calculate the petty cash top-up?
Subtract the current balance from the float.
How do I total petty cash by category?
Use SUMIFS with the category as the criterion.
How often should I reconcile petty cash?
At least weekly, and always before a top-up.


