Petty Cash Log in Excel With a Running Balance

A petty cash log in Excel is a simple table that records every coin and note that goes in or out of the office cash box, with a running balance that always tells you how much should be left. You need three formulas: SUM for the balance, SUMIF for category totals and a subtraction to check the cash count. This guide builds the whole log step by step, using numbers you can type in and check.

What a petty cash log needs#

Petty cash is a small amount of cash kept for minor costs such as stamps, taxis and milk. The fixed starting amount is called the float. A good log answers three questions at any time: how much should be in the box, where the money went, and whether the box matches the log.

Use one row per transaction and these columns:

Keep cash in and cash out in separate columns, and leave the unused one empty. That makes the totals easy and stops anyone typing a minus sign in the wrong place. Many offices add a receipt number column too, so each payment can be matched to its paper receipt.

How to set up the petty cash log in Excel#

1. Type the headers in row 1 and make them bold.
2. In row 2, enter the opening float as the first transaction: description "Opening float", category "Float", and the amount in Cash in.
3. Format columns D, E and F as numbers with two decimal places (Home > Number Format > Number, or Accounting).
4. Freeze the header row with View > Freeze Panes > Freeze Top Row, so it stays visible as the log grows.

Here is a log for the first two weeks of November, after the formulas in the next section are added:

Add a running balance formula#

The balance column shows how much cash should be in the box after each row. In F2, type:

=SUM($D$2:D2)-SUM($E$2:E2)

Then fill it down to the last row (select F2:F8 and press Ctrl+D; Mac: ⌘D). Each row adds all the cash in so far and subtracts all the cash out so far. In row 8 the formula has become =SUM($D$2:D8)-SUM($E$2:E8): 300.00 in minus 80.64 out gives 219.36.

The $ signs make $D$2 an absolute reference: it stays on row 2 however far you fill. The second half, D2, is relative, so it moves down one row at a time. This "locked start, moving end" range is the core of every running total. The SUM function guide covers the basics, and running totals in Excel compares this with the shorter =F2+D3-E3 style.

Why not use the shorter style? If someone deletes a row, a formula like =F2+D3-E3 in the row below breaks with #REF!. The SUM version just recalculates. Empty cells count as 0 in SUM, so rows with only cash out work fine.

Total spending by category with SUMIF#

SUMIF adds the numbers in one column when another column matches a condition. Put the category names in a small summary table, for example in H2:H5, and in I2 type:

=SUMIF($C$2:$C$8,H2,$E$2:$E$8)

Fill it down to I5:

The totals add up to 80.64, the same as =SUM(E2:E8). Checking that the category total equals the overall total is a quick way to catch a missing or misspelt category: "postage " with a trailing space would drop out of the SUMIF. Use the SUMIF function guide for the argument order, and summing by category for a version that lists the categories automatically.

Reconcile the cash box#

To reconcile means to check that two records agree. Count the cash in the box, type the amount in a cell, say I8, and in I9 type:

=I8-F8

If you count 219.36, the difference is 0, and the log is right. If you count 215.36, the result is -4.00: the box is four short, so look for a missing receipt or a mistyped amount.

Many offices use a fixed float, called the imprest system: the box is always topped back up to the same amount. Here the float is 300 (200 at the start plus the 100 top-up). To restore it, top up 300 - 219.36 = 80.64, which is exactly the total spent. Record the top-up as cash in, and the balance returns to 300.

Tips to keep the log reliable#

• Use the same category names every time. A drop-down list (Data > Data Validation > List) stops "Postage", "postage" and "Post" from becoming three categories.
• Never type over a formula. If the balance looks wrong, fix the amounts, not the Balance cell.
• One log per period. Start a new sheet each month, with the last balance as the new opening float.
• Turn it into a Table (Ctrl+T) so new rows pick up the formulas automatically.

If you need a bigger picture than petty cash, the same layout grows into an expense tracker or a monthly budget, and the running-balance idea is the base of a cash flow forecast.

Practise it#

A petty cash log uses three skills that office and admin job tests check again and again. Practise the SUM function in a live spreadsheet: you type the formula and it checks it instantly. Then lock ranges in the absolute references lesson and build category totals in the SUMIF lesson, or try the exercise on this page first.