Whether you run a household budget or a small business, the easiest way to see where money comes from and where it goes is a well-kept income and expense spreadsheet. Set up properly, it answers "where did the money go?" at the end of the month in a few seconds.
This guide explains step by step which columns the spreadsheet needs, how to choose categories and which Excel formulas build the monthly summary. We also share a free Excel template you can use right away, and for the point where a spreadsheet is no longer enough, we introduce our free app, GGAB.
What is an income and expense spreadsheet?
An income and expense spreadsheet is a list of all money coming in and going out over a period, each entry with a date, an amount and a category. Kept regularly, it answers three questions: How much did we earn this month, how much did we spend, and where did most of the money go?
An important note for businesses: the spreadsheet is a management tool. It does not replace your official books, invoices or tax returns, and the records kept by your accountant are what count for tax. Formal financial statements such as a balance sheet or an income statement are different documents too. What we describe here is a simple record for tracking money in and out.
Which columns does the spreadsheet need?
| Column | What goes in | Example |
|---|---|---|
| Date | The day of the transaction | 2026-11-04 |
| Type | Income or expense | Expense |
| Category | One item from a fixed list | Rent |
| Description | A short note | November office rent |
| Amount | A positive number, in one currency | 900 |
| Payment method | Cash, card, bank transfer | Bank transfer |
| Document no. | Invoice or receipt number | INV-2026-118 |
Always enter the amount as a positive number and let the Type column say whether it is income or an expense. This keeps the formulas simple and avoids sign errors. If you are VAT-registered, decide at the start whether you record amounts with or without VAT, and stick to it.
How to choose categories
Keep to 8–12 categories. Too few hide the detail; too many make entering records tedious. Example lists:
- Small business income: product sales, service revenue, other income.
- Small business expenses: rent, payroll, materials and stock, utilities (electricity, water, internet), travel, software and subscriptions, marketing, taxes and fees, other expenses.
- Household budget: salary and side income; rent, groceries, bills, transport, education, health, leisure, savings.
Keep the categories as a list on a separate sheet and link the Category column to that list. That way, small slips such as "Rents" or a stray trailing space will not break your summary.
Step by step in Excel
Write the columns
In the first row, type the headings Date, Type, Category, Description, Amount, Payment method and Document no.
Convert to a table
Select the range with its headings and press Ctrl+T to make it an Excel table and name it Records in the Table Name box on the Table Design tab; new rows are picked up by formulas automatically.
Add drop-down lists
Add drop-down lists to the Type and Category columns: Data > Data Validation, then choose List under Allow.
Add a month column
Add a helper column named Month that takes the month from the date: =MONTH([@Date])
Build the monthly summary
On a separate sheet, use SUMIFS to total each category per month, then create a chart from the result.
An example formula for the monthly summary (function names follow the language of your Excel and argument separators follow your regional settings; with English settings they look like this, while many European settings use semicolons instead of commas):
=SUMIFS(Records[Amount], Records[Type], "Expense", Records[Category], "Rent", Records[Month], 11)This formula adds up the rent expenses for November (11) in a table called Records. For total income, write "Income" instead of "Expense" and remove the Category condition (Records[Category], "Rent"); the net result is total income minus total expenses. If your records span more than one year, add a Year column (=YEAR([@Date])) and include the year in the conditions.
Free Excel template
We have built this structure for you: download the income and expense spreadsheet template (Excel, free)
The template has three sheets:
- Records: drop-down lists in the Type, Category and Payment method columns; Month and Year are calculated automatically. Delete the example rows and enter your own.
- Monthly summary: totals per category and month for the year you choose, monthly total income, total expenses and net result, plus an income and expense chart.
- Categories: your income and expense categories. Change the list to fit your business; new categories (up to 20) appear in the drop-down list and on the Monthly summary sheet automatically.
The template contains no macros and works in Excel 2010 and later.
When a spreadsheet is no longer enough
A spreadsheet works well when one person enters a few dozen records a month. It starts to struggle when:
- several people enter records and it gets unclear which copy of the file is current,
- you want to track receivables and payables by due date and get reminders before payments are due,
- you want to add records from your phone or check reports from anywhere,
- you do not want to risk the file being lost or corrupted.
GGAB: a free income and expense tracking app
For these needs we built our own product, GGAB. GGAB (short for the Turkish words for income, expense, receivable and payable) is a web-based app and completely free: sign-up, all features and use are free, and no credit card is needed. The app is available in English, German, Spanish and Turkish.
- Income, expenses, receivables and payables on one screen
- Tracking by category, charts, and monthly and yearly comparisons
- Receivables and payables by due date, with payment reminders
- A to-do list and notifications
You can use the same categories in GGAB as in your spreadsheet, so it is easy to start with Excel and move to the app as your needs grow. As a business grows, stock, invoicing and accounting usually move to accounting or ERP software; to connect such systems, see our ERP integration page.
Common mistakes
- Mixing personal and business spending: Use a separate spreadsheet for the business, and a separate bank account if you can.
- Skipping small cash expenses: Most unexplained gaps at the end of the month come from here; write the expense down as soon as you pay.
- Forgetting recurring payments: Add rows for rent, subscriptions and insurance at the start of each month.
- Too many categories: A spreadsheet with twenty categories soon stops being kept up.
- No backups: Keep the file in cloud storage or copy it regularly.
Frequently asked questions
Is an income and expense spreadsheet the same as a balance sheet?
No. A balance sheet is an accounting statement that shows assets and liabilities on a given date. An income and expense spreadsheet is a simple record for tracking money in and out over a period.
Does it replace an accountant?
No. The spreadsheet helps you see how your business is doing; for your official books, tax returns and tax calculations, your accountant's records are what count. A tidy spreadsheet does make conversations with your accountant easier.
Spreadsheet or app?
If you are one person entering a few dozen records a month, the Excel template is enough. If several people enter records, or you want to track due dates for receivables and payables and get reminders, an app takes less effort.
Is GGAB really free?
Yes. Sign-up, all features and use are free, and no credit card is required. You can try it with the demo account first.
If your business needs custom finance or reporting software, get in touch.