How to Track Business Expenses in Excel (Without Accounting Software)

Oct 9

If you're running a small business and your "expense tracking system" is a shoebox of receipts and a vague sense of dread at tax time, this is for you.

You don't need QuickBooks yet. You don't need a bookkeeper yet. You need a single spreadsheet, set up correctly, updated consistently. This guide shows you exactly how to build one — the columns, the formulas, the dropdowns, and the monthly routine that keeps it useful.

Total build time: about 30 minutes. Maintenance: 10 minutes a week.

A note on examples: Every business name and dollar figure in this article is a fictional, hypothetical example created to illustrate the method. Plug in your own real numbers.

What you're building

One workbook with two sheets:

1. Expenses — every business purchase, one row each, categorized.

2. Summary — automatic totals by category and month, so you always know where the money went.

That's it. No macros, no plugins, no paid add-ins. Everything here works in Excel (2016 and newer), Google Sheets, and LibreOffice.

Step 1: Set up your columns

A few decisions baked into this layout:

Date first. You’ll sort and filter by date constantly. Put it in column A.

- Vendor separate from Description. "Amazon" is the vendor; "printer paper, 2 reams" is the description. Separating them lets you answer "how much did I spend at Amazon this year?" in seconds.

- Tax Deductible? as Yes/No. Not every business expense is deductible (fines, some meals, personal mixed-use). Flagging it now saves your tax preparer hours later — and saves you money.

Format column A as Date, column F as Currency. Select row 1 and turn on Filter (Data tab → Filter). This one click makes the whole sheet searchable.

Step 2: Build your category list (and lock it with a dropdown)

Inconsistent categories are the #1 reason expense spreadsheets become useless. "Supplies," "Office supplies," and "supplys" are three different categories to Excel, and your totals will be wrong.

Fix it with a dropdown. Here's how:

1. Create a new sheet called Lists.

2. In column A, type your categories — one per row. Start with this list and adjust for your business: Rent / Utilities / Payroll-Contractors / Inventory-COGS / Marketing & Advertising / Software & Subscriptions / Office Supplies / Travel / Meals (Business) / Insurance / Professional Services / Taxes & Licenses / Miscellaneous

3. Go back to the Expenses sheet. Select all of column D (click the "D" header).

4. Go to Data → Data Validation → List. In the Source box, select your category list on the Lists sheet.

5. Click OK.

Now every cell in column D gets a dropdown arrow. No typos, no duplicates, no "supplys."

Hypothetical example: Say you run a fictional bakery — call it Cedar Street Bakery. Your categories might add "Ingredients / COGS" and "Equipment Maintenance." A freelance designer would add "Stock Photos" and "Client Entertainment" instead. The method is the same; the list is yours.

Step 3: Enter expenses (the 10-minute weekly habit)

Once a week, same day, 10 minutes:

1. Gather receipts — email confirmations, card statements, the paper ones.

2. Add one row per expense. Fill every column. Use the dropdown for Category.

3. Amounts are always positive numbers. (Refunds get their own row as a negative amount — don't delete the original; you want the audit trail.)

Rule: if it takes more than 10 minutes a week, your categories are too granular. Merge them.

Step 4: Add automatic category totals with SUMIF

On the Summary sheet, set up a Category / Total table. The formula reads: In the Expenses sheet, look at column D (Category); wherever it matches the category name, add up the corresponding values in column F (Amount). Example: `=SUMIF(Expenses!D:D, A2, Expenses!F:F)`

Copy the formula down for every category. Your category totals update themselves every time you add a row. Pro move: add a grand total at the bottom with `=SUM(B2:B14)`. That's your total business spending, always current.

Step 5: See spending by month with SUMIFS

SUMIFS handles multiple conditions — here, "date is in January." Example: `=SUMIFS(Expenses!F:F, Expenses!A:A, ">=2026-01-01", Expenses!A:A, "<=2026-01-31")`. Simpler alternative: add a helper column on the Expenses sheet (header "Month", formula `=TEXT(A2,"mmm yyyy")` copied down), then your monthly summary is `=SUMIF(Expenses!H:H, "Jan 2026", Expenses!F:F)`.

Step 6: Flag overspending with conditional formatting

1. Select your category totals on the Summary sheet.

2. Go to Home → Conditional Formatting → Color Scales (green-yellow-red).

Even better: set a monthly budget per category (add a Budget column next to Total), then use Conditional Formatting → Highlight Cell Rules → Greater Than on the variance column to flag any category where actual spending exceeds budget.

The monthly 20-minute routine

On the first weekend of each month:

1. Reconcile. Compare your spreadsheet's month-end total against your bank/card statements. If they don't match, find the missing rows.

2. Review the Summary sheet. Which categories grew vs. last month? Any surprises?

3. Check the Tax Deductible column. Anything marked "No" that shouldn't be there? Anything missing a receipt?

4. Back up the file. Save a copy with the month in the filename: expenses_2026-01.xlsx. Cloud storage counts.

5 mistakes that break expense spreadsheets

1. Deleting rows. Never delete. Refunds and corrections get new rows with negative amounts.

2. Too many categories. 12–15 is the sweet spot.

3. Mixing personal and business without flagging. Log it and note the card.

4. No backup. Automate it — OneDrive, Google Drive, whatever you already use.

5. Building it and never opening it. The Summary sheet exists so you can check it in 60 seconds.

When Excel stops being enough

Be honest with yourself about these signs: you're spending more than an hour a week on data entry; you have employees or contractors and need payroll records tied in; you need invoices, not just expenses; tax time still takes days despite the spreadsheet; you dread opening the file. None of these mean you did anything wrong — they mean the business outgrew the tool, which is a good problem. Your two upgrades: accounting software (QuickBooks, Xero), or handing the books to someone else entirely.

If you'd rather skip the setup and start from a finished template, a pre-built Business Expense Tracker with all of the above — dropdowns, SUMIF summaries, monthly views, conditional formatting — saves you the 30-minute build (coming soon). And if the monthly routine sounds like time you'd rather spend running the business, that's exactly what a bookkeeping service does: clean books, reconciled monthly, without you touching a spreadsheet. Contact T-Account Group for bookkeeping services.

Quick recap

- 7 columns: Date, Vendor, Description, Category, Payment Method, Amount, Tax Deductible?

- Lock categories with a Data Validation dropdown — no typos, ever.

- 10 minutes a week entering rows; 20 minutes a month reviewing.

- SUMIF for category totals, SUMIFS (or a Month helper column) for monthly totals.

- Conditional formatting so problems jump out at you.

- Back up monthly. Never delete rows.

Build it this weekend. Your tax preparer will thank you, and so will you — the first time you answer "how much did we spend on marketing last quarter?" in under ten seconds.

Nate Light