A single Excel workbook with the right structure can handle all your business income and expense tracking, from daily data entry through tax time. You need two core sheets: one for income, one for expenses, each built with columns that match the categories on your tax return. The setup takes about an hour, and once it’s in place, staying current takes just a few minutes per transaction.
Set Up Your Workbook Structure
Create a new Excel workbook with at least three sheets: one labeled “Income,” one labeled “Expenses,” and one labeled “Categories” (this last one will power your drop-down menus and keep your data consistent all year).
Your Income sheet needs these columns:
- Date: When the payment was received
- Client/Source: Who paid you
- Description: What the payment was for
- Amount: Dollar amount received
- Payment Method: Check, bank transfer, cash, credit card, etc.
- Invoice Number: If applicable, for matching payments to invoices
Your Expenses sheet needs these columns:
- Date: When the expense occurred
- Vendor/Payee: Who you paid
- Description: What you bought or paid for
- Amount: Dollar amount spent
- Category: The tax-relevant expense type (more on this below)
- Payment Method: How you paid
- Receipt: A yes/no column or a file name reference so you can confirm you have documentation
Format both sheets as Excel Tables (select your data range and press Ctrl+T). Tables automatically expand when you add rows, and they make formulas much easier to write because you can reference column names instead of cell ranges.
Use Tax-Aligned Expense Categories
The categories in your spreadsheet should mirror the expense lines on IRS Schedule C, the form sole proprietors and single-member LLCs use to report business profit or loss. When your categories already match the tax form, filing becomes a matter of pulling totals rather than re-sorting a year’s worth of transactions. The Schedule C expense lines include:
- Advertising
- Car and truck expenses
- Commissions and fees
- Contract labor
- Insurance (other than health)
- Legal and professional services
- Office expense
- Repairs and maintenance
- Supplies
- Taxes and licenses
- Travel
- Deductible business meals
- Utilities
- Business use of home
You don’t need every one of these. Pick the categories that apply to your business and list them on your Categories sheet in a single column. If you’re a freelance designer, you probably don’t need “Depletion” or “Pension and profit-sharing plans.” Start with the 8 to 12 categories you actually use, and add more if a new type of expense comes up.
Build Drop-Down Lists for Consistency
Inconsistent data entry is the single biggest problem with spreadsheet bookkeeping. If you type “Office Supplies” in one row and “office supply” in another, Excel treats them as two separate categories, and your totals will be wrong. The fix is data validation, which restricts a cell to only accept entries from a predefined list.
On your Categories sheet, type each expense category in its own row in Column A. Then go to your Expenses sheet, highlight the entire Category column (below the header), click the Data tab in the ribbon, and select Data Validation. Change the “Allow” setting from “Any value” to “List,” then set the source to your category list on the Categories sheet. Now every cell in that column shows a drop-down menu with only your approved categories.
Do the same thing for vendor names and payment methods. Create a vendor list and a payment method list on the Categories sheet, and apply data validation to those columns. When you need to add a new vendor, add them to the list first, and every future entry will be spelled exactly the same way. This matters when you need to total up payments to a single contractor (for example, to determine whether you need to send them a 1099).
Add Formulas That Do the Math
With your data in Excel Tables, a handful of formulas will give you running totals, monthly breakdowns, and category summaries without any manual adding.
For a running total of all income, use a simple SUM at the bottom of your Amount column. Inside a Table, Excel can auto-generate this as a “Total Row”: right-click the table, select “Table,” and toggle “Total Row” on. It adds a sum at the bottom automatically.
To total expenses by category, use SUMIF. If your category column is named “Category” and your amount column is named “Amount” in your Expenses table, this formula totals everything tagged as “Supplies”:
=SUMIF(Expenses[Category], "Supplies", Expenses[Amount])
To total expenses for a specific month, SUMIFS lets you filter by multiple criteria. This example totals all expenses from January 2025:
=SUMIFS(Expenses[Amount], Expenses[Date], ">="&DATE(2025,1,1), Expenses[Date], "<="&DATE(2025,1,31))
Combine both filters to get a single category’s total for a single month:
=SUMIFS(Expenses[Amount], Expenses[Category], "Advertising", Expenses[Date], ">="&DATE(2025,1,1), Expenses[Date], "<="&DATE(2025,1,31))
Create a summary sheet with a row for each expense category and a column for each month. Fill it with SUMIFS formulas, and you have a dashboard that updates every time you log a new transaction. Add a final column that sums across all months, and those annual totals transfer directly to the matching lines on Schedule C.
Use Pivot Tables for Deeper Analysis
Once your data set grows past a few dozen rows, Pivot Tables become faster than writing individual formulas. Click anywhere inside your Expenses table, go to Insert, and select PivotTable. Drag “Category” to the Rows area and “Amount” to the Values area, and you instantly see total spending by category. Drag “Date” to the Columns area and Excel groups it by month or quarter, giving you a full breakdown with no formulas at all.
You can do the same with your Income table to see revenue by client, by month, or by payment method. Pivot Tables refresh with one click when you add new data, so they stay current with minimal effort.
Protect Your Formulas and Structure
One accidental keystroke can overwrite a formula and throw off your totals without you noticing. Lock down the cells that contain formulas by selecting them, right-clicking, choosing Format Cells, and checking “Locked” on the Protection tab. Then go to the Review tab and click “Protect Sheet.” Set a password and uncheck the option to edit locked cells. You’ll still be able to enter data in your input columns, but the formula cells won’t budge.
Also protect your Categories sheet. If someone accidentally deletes a category name, every drop-down that references it breaks. Sheet protection prevents that.
Keep Up With Receipts
The IRS requires you to keep records long enough to prove the income or deductions on your tax return, which generally means at least three years from the filing date and up to seven years in certain situations. Employment tax records need to be kept for at least four years. You bear the burden of proof for any entry on your return, so if you claim a deduction, you need documentation to back it up.
The simplest approach: create a folder on your computer (or a cloud drive) organized by year and month. When you enter an expense in your spreadsheet, save the receipt as a file named with the date and vendor, like “2025-03-15_Staples.pdf.” Put that file name in your Receipt column so you can find it later. Snap photos of paper receipts with your phone and save them as PDFs the same way. The IRS accepts digital copies, so you don’t need to keep the originals as long as the digital version is legible and complete.
Enter Transactions on a Regular Schedule
The system only works if you use it consistently. Set a recurring time, whether that’s daily, weekly, or every time you make a purchase, to log transactions. Weekly works well for most small businesses: pull up your bank and credit card statements online, enter anything new, and file any receipts you’ve collected during the week.
At the end of each month, reconcile your spreadsheet against your bank statement. Add up total deposits in your Income sheet and compare them to the deposit total on your statement. Do the same for expenses. If the numbers don’t match, you’ve either missed a transaction or double-entered one. Catching discrepancies monthly is far easier than hunting through 12 months of data in April.
Back Up Your File
Your spreadsheet is your financial record. Save it to a cloud service like OneDrive, Google Drive, or Dropbox so it syncs automatically. Keep a local backup on an external drive or a second computer. Name your file with the year so you start fresh each January: “Business_Finances_2025.xlsx.” At year end, lock the prior year’s file (mark it read-only or move it to an archive folder) and create a new one with updated category lists and fresh formula sheets. Carrying forward the same template year after year means setup takes minutes instead of an hour.