Building a spreadsheet to track your business expenses and income sounds simple but most people get it wrong in one of two ways. Either they build something too basic that gives them no useful information, or they build something too complicated that they stop updating after two weeks. This guide walks through building one that is genuinely useful and simple enough to actually maintain.
By the end you will have a spreadsheet with an income sheet, an expense sheet, and a summary that shows your monthly profit automatically — no manual calculations, no rebuilding every month, just paste in new data and everything updates.
The finished spreadsheet is one Excel workbook with three sheets. An Income sheet where you log every payment you receive. An Expenses sheet where you log every business expense. And a Summary sheet that pulls from both and shows your monthly totals and profit automatically. Here is what the Summary sheet looks like once it is running:
| Month | Total Income | Total Expenses | Net Profit | Profit Margin |
|---|---|---|---|---|
| January | $8,400 | $5,200 | $3,200 | 38% |
| February | $9,100 | $5,400 | $3,700 | 41% |
| March | $7,800 | $5,100 | $2,700 | 35% |
| April | $11,200 | $5,800 | $5,400 | 48% |
| YTD Total | $36,500 | $21,500 | $15,000 | 41% |
This updates automatically every time you add new entries to your Income or Expenses sheets. You never have to manually calculate a total or rebuild the summary.
Open a new Excel workbook and create three sheets by right-clicking the sheet tab at the bottom and clicking Insert Sheet. Name them exactly as follows because your formulas will reference these names:
Click on your Income sheet and set up these column headers in row 1:
| Column | Header Name | What to Enter |
|---|---|---|
| A | Date | Date the payment was received |
| B | Source | Who paid you or where the income came from |
| C | Category | Type of income, Services, Products, Consulting, etc. |
| D | Description | Brief note about what the payment was for |
| E | Amount | The dollar amount received |
Now the most important setup step. Click anywhere in your data area and press Ctrl+T to convert it to an Excel Table. Name the table IncomeData when prompted. This single step makes every formula on your Summary sheet update automatically as you add new rows.
Click on your Expenses sheet and set up these column headers:
| Column | Header Name | What to Enter |
|---|---|---|
| A | Date | Date the expense was paid |
| B | Vendor | Who you paid |
| C | Category | Rent, Software, Marketing, Supplies, etc. |
| D | Description | Brief note about what the expense was for |
| E | Amount | The dollar amount paid, always enter as positive |
| F | Tax Deductible | Yes or No |
Convert this to an Excel Table too. Press Ctrl+T and name it ExpenseData. Add a category dropdown to your Category column the same way you did for Income, using your expense categories.
Common expense categories for most small businesses: Rent and Utilities, Software and Subscriptions, Marketing and Advertising, Supplies and Materials, Contractors and Payroll, Professional Services, Travel, Insurance, Bank Fees.
This is where the spreadsheet comes to life. Click on your Summary sheet and set up a table with months down the left and three columns to the right: Total Income, Total Expenses, and Net Profit.
For each month enter these formulas. The number after MONTH= changes for each row, 1 for January, 2 for February, and so on through 12 for December.
Total income for January:
Total expenses for January:
Net profit for January:
Profit margin for January:
Copy these formulas down for each month, changing the month number in each row. Then add a Year to Date total row at the bottom:
Add two conditional formatting rules to your Summary sheet that flag issues automatically without you having to interpret numbers each month.
Flag months where profit margin drops below your target: Select your Profit Margin column, go to Conditional Formatting, Highlight Cell Rules, Less Than, and enter your minimum acceptable margin, say 30%. Format it red. Any month where you fall below your target turns red automatically.
Flag months where expenses increased significantly: Select your Total Expenses column, go to Conditional Formatting, New Rule, Use a formula, and enter a formula that compares this month to last month. A month where expenses jumped more than 20% over the previous month gets highlighted amber so you can investigate why.
The spreadsheet is only as useful as the data in it. The single most important thing you can do is set a recurring time every week, fifteen minutes is plenty, to enter everything from the past seven days. Open your bank and credit card statements, log each business transaction in the appropriate sheet, and glance at your Summary to make sure the numbers look right.
Done weekly, this takes fifteen minutes. Done monthly, it takes two hours and is much more stressful because you have forgotten what things were for. Done quarterly, it is a nightmare. Weekly is the only habit that actually works long term.
If you would rather have a custom version of this spreadsheet built for your specific business — with your income categories, your expense categories, and any additional tracking you need — that is exactly what we do at HelpMyData.
Tell us about your income sources and expense categories and we'll build a custom income and expense tracker tailored to exactly how your business works. Starting at just $75.
📧 Get Started Today