Small business income and expense spreadsheet: layout, formulas and a worked month

A small business income and expense spreadsheet does three jobs: it tells you whether you made money this month, it gives you the totals your tax return asks for, and it points to the receipt behind every number. You do not need accounting software to get there. One sheet with eight columns, a category list and a monthly summary built from two formulas is enough for most freelancers, side businesses and one-person shops. This guide gives you the exact layout, the formulas for Google Sheets and Excel, and a filled-in month you can check your own sheet against.

The layout: one row per transaction

Keep income and expenses in the same table, one row for each payment in or out. A single table is easier to sort, filter and total than separate tabs, and it is how bank exports arrive, so you can paste them straight in.

Two habits keep it clean. Enter amounts as positive numbers and let column B decide whether they add or subtract. And turn columns B and C into dropdowns (Data, then Data validation in both Google Sheets and Excel) so a category can never be spelled two ways, which is what breaks totals later.

A category list that matches your tax return

If you file as a US sole proprietor, your expenses end up on Schedule C, so it saves time to use categories that map onto its lines. A short list covers most small businesses:

• Income: Sales or services, Refunds received, Other income.
• Advertising: ads, listing fees, printed flyers.
• Car and truck: business miles at the IRS standard rate, or actual costs if you use that method.
• Contract labor: freelancers and subcontractors you pay.
• Insurance: business liability and similar policies.
• Legal and professional: accountant, lawyer, bookkeeping.
• Office and software: subscriptions, printer ink, postage.
• Supplies: materials used up in the work.
• Travel and meals: keep meals separate, because business meals are generally only 50% deductible.
• Utilities and phone: only the business share of a phone or internet bill you also use personally.

Resist adding a new category for every vendor. Ten to fifteen categories is plenty; anything rare goes in "Other" with a note.

The two formulas that build your monthly summary

On a second tab, list the months down column A (as the first day of each month, for example 2026-01-01) and useSUMIFS, which works the same way in Google Sheets and Excel. If your transactions tab is called Log, total income for the month in cell A2 is:

=SUMIFS(Log!E:E, Log!B:B, "Income", Log!A:A, ">="&A2, Log!A:A, "<"&EDATE(A2,1))

Expenses are the same formula with "Expense" in place of "Income". Profit is simply income minus expenses.

To total one category for the month, add one more condition, with the category name in cell B1:

=SUMIFS(Log!E:E, Log!C:C, B$1, Log!A:A, ">="&$A2, Log!A:A, "<"&EDATE($A2,1))

Put category names across row 1, months down column A, fill the formula across and down, and you have a month-by-category table that becomes your year-end totals with a single SUM per column.

Worked example: a freelance designer's January

Here is one month in the layout above, with every figure you need to check your own formulas:

1. Income: $2,500 + $1,700 = $4,200.00.
2. Expenses: $54 + $120 + $63 + $40 + $152.25 = $429.25.
3. Profit: $4,200.00 − $429.25 = $3,770.75.
4. Self-employment tax estimate: 15.3% applies to 92.35% of net earnings, so $3,770.75 × 0.9235 × 0.153 = $532.79 for the month, before any income tax.

The mileage line uses the IRS rate for business miles driven from January 1 to June 30, 2026 (72.5 cents a mile; it rises to 76 cents from July 1). Our 2026 mileage deduction guide explains the two rates, and the free mileage calculator prices a log for you. Only the business half of the phone bill goes in; the personal half never enters the sheet.

Set aside tax money from the same sheet

Add a row under your monthly summary that multiplies profit by a set-aside percentage and move that amount to a separate savings account each month. Self-employment tax alone is about 14.1% of profit (15.3% × 92.35%), so many sole proprietors set aside 25% to 30% to cover income tax as well; your own figure depends on your bracket and state. US estimated tax payments are normally due April 15, June 15, September 15 and January 15, and the quarter totals come straight from three rows of your summary.

Keep it reconciled in ten minutes a month

1. Download last month's statement for each business account and card.
2. Filter the sheet by month and by the "Paid with" column, and tick off each line against the statement.
3. Anything on the statement but not in the sheet: add it. Anything in the sheet but not on the statement: find out why.
4. Check every expense row has a receipt in column G, and file the missing ones now while you remember them.

Keep the sheet and receipts for at least three years after you file; the IRS can ask for longer in some situations, so many owners keep seven.

Common mistakes

• Mixing personal and business spending. A separate bank account or card makes the sheet half as much work.
• Recording invoices instead of payments. If you use the cash method, a row goes in when money arrives, not when you send the invoice.
• Text instead of dates or numbers. A date typed as "Jan 9th" or an amount with a stray space will be skipped by SUMIFS.
• Counting transfers as income. Moving money from savings to checking is not a sale.

Templates and tools to start from

If you would rather not build the sheet yourself, the free expense tracker exports to CSV and PDF, and the profit and loss sheet turns monthly totals into a P&L. For the other spreadsheets a small business runs on, see the eight templates every small business needs, and if mileage is a big expense for you, compare the best free mileage tracker apps.