Small business expense tracking in Excel: template and method

In a small business, expenses slip through in many places: invoices that arrive by email, receipts in an employee’s wallet, automatic software charges nobody remembers signing up for. When the quarter closes, rebuilding all of that from loose paperwork takes hours and leaves gaps.

This guide offers a simple Excel template and, above all, a method to keep it up to date: which columns to record, how to summarize by category and month, how to track what is still unpaid and how to combine records from several people without errors.

What a good expense log should record

Each row is one expense (usually an invoice or a receipt). These are the columns we recommend for the Expenses sheet:

• Date (A): the invoice date, not the payment date.
• Supplier (B) and Invoice no. (C): essential to find the document and spot duplicates.
• Category (D): rent, utilities, office supplies, transport, software, marketing…
• Net (E) and Tax (F): the amount before tax and the rate applied, as a percentage.
• Total (G): calculated with a formula.
• Status (H): Paid or Unpaid.

If your business has several departments or projects, add a Cost center column. Don’t add more columns than you will use: each extra column is one more field someone has to fill in.

Step 1: set up the log sheet

Type the headers in row 1 and a few sample rows. With made-up data it looks like this:

The tax rates in the example are for illustration only: use the ones that apply in your country. In G2, calculate the total and copy it down:

=E2*(1+F2)

Then make two adjustments that prevent a lot of errors:

1. Select column D, go to Data › Data Validation, choose List and type your categories separated by commas (or point to a range containing the list). Do the same in column H with Paid,Unpaid.
2. Press Ctrl + T inside the data to turn it into a table. Formulas and formatting then extend automatically to every new row.

Step 2: summary by category and month

Create a Summary sheet. Put the categories in A3:A8 and, in row 2 starting at B2, the first day of each month (January 1, February 1…) with a custom month format so they read “Jan”, “Feb”… In B3, type this formula, which adds up a category’s total between the first and last day of the month:

=SUMIFS(Expenses!$G:$G,Expenses!$D:$D,$A3,Expenses!$A:$A,">="&B$2,Expenses!$A:$A,"<="&EOMONTH(B$2,0))

Thanks to the $ signs, you can copy it across the whole grid without changing it. EOMONTH returns the last day of the month, so you don’t have to type closing dates by hand. In column N, add up each row with =SUM(B3:M3) to get the annual total per category.

Alternative: PivotTable

If you’d rather not write formulas, select the expenses table and go to Insert › PivotTable. Drag Category to rows, Date to columns (Excel groups it by month) and Total to values. Remember to click Refresh every time you add expenses.

Step 3: keep an eye on unpaid invoices

Knowing how much you owe is as important as knowing how much you have spent. In the Summary sheet, add a cell with the amount still to be paid:

=SUMIF(Expenses!H:H,"Unpaid",Expenses!G:G)

And another that counts unpaid invoices older than 30 days, which are the ones to review first:

=COUNTIFS(Expenses!H:H,"Unpaid",Expenses!A:A,"<"&TODAY()-30)

To see them in the log sheet, apply conditional formatting to column H that colors cells containing the text “Unpaid” orange.

Step 4: catch duplicate invoices

Recording the same invoice twice is one of the most common mistakes, especially when it arrives both by email and on paper. Add a check column in I2 and copy it down:

=COUNTIFS($B:$B,B2,$C:$C,C2)>1

It returns TRUE when the same supplier and invoice number appear more than once. Filter by TRUE, review and delete the extra copy. If the file is very large, you can also remove duplicates with our online tool.

When several people log expenses

In many companies, each employee or each store keeps its own sheet and sends it in at the end of the month. Copying and pasting those files by hand is slow and it is easy to skip rows. For a clean merge:

• Give everyone the same template, with the same columns in the same order.
• Ask them not to change the headers or add columns on their own.
• Use clear file names, such as Expenses_NorthStore_03.xlsx.

Once you have the files, our free tool to merge Excel files combines them into a single workbook in a few seconds. Then just paste the result into your Expenses sheet and the summary updates.

A 15-minute weekly routine

The template is only useful if it is up to date. This method works well in small businesses:

1. Monday: gather the previous week’s invoices and receipts (email, paper, banking app).
2. Log them in the Expenses sheet and save each document with a name that includes the invoice number.
3. Check the duplicates column.
4. Review the unpaid amount and the invoices older than 30 days.
5. At the end of the month, compare the summary with the bank statement and mark as Paid whatever has already been charged.

Common mistakes

• Dates stored as text: SUMIFS ignores them and the summary comes out incomplete. If a date is aligned to the left, it is text.
• Categories typed by hand with variations (“Software”, “software”, “Programs”). Data validation prevents this.
• Mixing personal and business expenses in the same sheet.
• Logging the payment instead of the invoice: if you pay an invoice in two installments, it is still one expense.

Conclusion

Tracking a small business’s expenses in Excel doesn’t require accounting software: a well-designed log sheet, a summary with SUMIFS, a couple of formulas for unpaid invoices and duplicates, and a weekly routine. With that you will know where the money goes each month and how much you owe, and you will reach the period close with the data ready for your accountant.

Frequently asked questions