How to Make a Simple Daily Sales and Expense Tracker in Excel

If you run a small shop, home business, or freelance service, scattered notes can make it hard to review day-to-day money coming in and going out. A basic spreadsheet can organise the records. It is not a substitute for accounting advice or tax records required in your situation; use it as a simple tracking tool and keep receipts and source documents.

1. Choose a clear set of columns

Start a blank workbook and add one header row: Date, Description, Type, Category, Payment method, Amount, Notes. Use “Sale” or “Expense” consistently in the Type column. Write brief descriptions such as “notebook sale” or “delivery charge.” Avoid storing full payment-card details or other sensitive customer data in the sheet.

2. Enter one transaction per row

Record a transaction when it happens, or set a regular time each day to update the sheet. Use the same date format and category names each time. If the business uses multiple currencies, add a currency column and keep amounts separate rather than adding unlike currencies together.

3. Convert the range into a table

Select a cell inside the data and use Excel’s Format as Table option. A table helps keep headers visible and makes it easier to sort or filter by date, type, or category. Check that the first row is marked as headers.

4. Calculate simple totals

If Amount is column F, a basic total for all entries is =SUM(F2:F1000). To total only sales in a fixed range, use =SUMIF(C2:C1000,"Sale",F2:F1000); replace “Sale” with the exact label you use. For expenses, use “Expense.” Keep the formula ranges large enough for your entries, and check the result against a small hand-calculated sample before relying on it.

A net figure can be calculated as total sales minus total expenses. This is a simple tracking comparison, not necessarily profit: stock costs, unpaid invoices, taxes, depreciation, and other accounting treatments may matter.

5. Review and protect the file

At the end of each week, filter by date and compare entries with receipts, bank records, and payment-provider statements. Keep a backup in a secure location with access limited to people who need it. Do not share a public link to a workbook that contains private business information.

Start small. A tracker is useful when the entries are consistent and the totals are checked. Add more detail only when it helps answer a real question about the business.