Daily Cash Book Ledger Excel Template

When different team members write down cash transactions, one person might type "Cleaning", another writes "Janitorial", and a third writes "Facilities". Later, when you want to see how much you spent on office upkeep, your formulas cannot group them together because the names do not match.

We fix this in two clicks by restricting the Account Category column to an approved list of options.

Select cells D17 through D27 in your table. On the top ribbon, click the Data tab, then click the Data Validation button.

In the dialog box, under Allow, choose List. In the Source box, type your standard categories separated by commas: "Sales Receipts, Facilities, Shipping, Travel, Consumables, Repairs". Make sure In-cell dropdown is checked and click OK.

Now, whenever someone logs an entry, a small arrow appears allowing them to pick the right category instantly without spelling mistakes.

Your cash ledger needs to show the exact dollar amount sitting in your cash drawer at any moment. To do this, we use a simple arithmetic formula that carries over the previous balance, adds today cash in, and subtracts today cash out.

Row 17 contains our opening balance forward: $18,500.00 in cell H17.

In Row 18, we receive $4,200.00 from customer sales (cell F18) with $0.00 paid out (cell G18). To find our new balance, click on cell H18 and type: =H17+F18-G18.

Press Enter. Excel calculates $22,700.00. Now grab the small green fill handle in the lower right corner of cell H18 and drag it down to Row 27. Every row now recalculates instantly as new receipts and expenses are recorded.

Physical cash cannot drop below zero. You cannot hand a customer minus ten dollars. If your cash book shows a negative balance, it means an expense was logged under the wrong date or a cash sale was forgotten.

We can ask Excel to immediately warn us with a soft red fill if the balance ever dips into the negative.

Highlight your running balance column (cells H17:H27). On the Home tab, click Conditional Formatting, then click New Rule.

Select "Format only cells that contain". In the rule description, choose "Cell Value", change the dropdown to "less than", and type 0 in the box. Click Format, pick a light red fill with dark red text, and click OK.

If someone accidentally enters an outflow larger than available cash, the cell instantly glows red, prompting the manager to review the register drawer before closing.

At the end of the month, business owners do not want to scroll through hundreds of individual receipts. They want to know the big picture: how much did we spend on shipping, repairs, and office supplies?

In the top right corner of our sheet (cells J8:K14), we have a clean summary table. We use the versatile =SUMIF() function to add up only the numbers matching each specific category.

In cell K8 (next to Facilities Maintenance), type: =SUMIF($D$17:$D$27, J8, $G$17:$G$27).

Notice the dollar signs ($) locking our ledger ranges. When you copy this formula down to other rows, the category reference (J8) adjusts naturally, while the source data ranges stay firmly anchored. In our example, Excel reports $650.00 for facilities, $120.00 for shipping, and a total monthly outflow of $2,400.00.