A budget and expense tracking Excel template is a workbook that compares planned spending with recorded transactions, usually by month and category. In Excel for Microsoft 365, start with a transaction sheet and a budget sheet, then use SUMIFS to total expenses by category and month; you can build that structure yourself or look for a suitable budget workbook under File > New.
What a budget and expense template should include
A useful template makes it easy to record what happened and see how it compares with what you planned. Most personal or household workbooks need a place for transactions, a list of budget categories, and a monthly summary. A chart can help show patterns, but it is secondary to accurate entries and clear totals.
- Transactions: dates, income or expense type, category, description, and amount.
- Budget: planned amounts for the categories you want to manage.
- Summary: actual amounts, remaining budget, income, and net cash flow for a selected period.
- Checks: a way to spot missing categories, duplicate entries, or spending above plan.
Keep the workbook focused on the decisions you want to make. If the main question is whether grocery spending is within plan, categories such as groceries, housing, transport, and utilities may be enough. A long list of tiny categories can take more effort to maintain than it returns in useful insight.
Set up the workbook in Excel
Create two worksheets and name them Transactions and Budget. On Transactions, add headings in row 1: Date, Type, Category, Description, and Amount. Enter one transaction per row, using Expense or Income consistently in the Type column. Enter amounts as positive numbers; the Type field distinguishes money received from money spent. This convention keeps the entries readable and avoids confusion caused by mixing negative and positive signs.
On Budget, put category names in A2:A10 and each category’s planned monthly amount in B2:B10. Add headings such as Category, Planned, Actual, and Remaining in row 1. Put the first day of the month you want to review in F1, for example 1/1/2026, and format that cell as a date. The first-day convention gives the summary a clear month selector.
For easier entry, select the transaction headings and a few rows of data, then choose Insert > Table and confirm that the table has headers. A table makes filtering and sorting more convenient and adds new entries neatly when you type in the row below it. You can also use Data > Data Validation to make a dropdown for Type or Category, reducing spelling differences such as “Transport” and “Transportation.” The exact appearance of these menus can vary slightly by Excel update; the main menu names are available in Microsoft 365 for Windows, and Excel for Mac has corresponding Table and Data Validation commands.
Calculate actual spending by category
With transactions on the Transactions sheet, category names in Budget!A2:A10, the first month date in Budget!F1, and the transaction columns set up as described above, enter this formula in Budget!C2 and fill it down. It sums Expense entries for the category in A2 whose dates fall in the selected month:
=SUMIFS(Transactions!$E:$E,Transactions!$B:$B,"Expense",Transactions!$C:$C,$A2,Transactions!$A:$A,">="&$F$1,Transactions!$A:$A,"<"&EDATE($F$1,1))
For an illustrative check, suppose four January grocery transactions are 120, 85, 145, and 300, and they are all marked Expense and Grocery. The formula returns 650 for that category in January. The month boundary includes dates from the first day of the selected month and excludes the first day of the next month, so it also works when months have different lengths. Make sure the dates in Transactions are real Excel dates rather than text that merely looks like a date.
With the planned amount in Budget!B2 and the actual amount calculated in C2, put this formula in D2 and fill it down to show the amount remaining:
=B2-C2
If the Grocery plan is 700 and the actual total is 650, the result is 50 remaining. A negative result means actual spending is greater than the planned amount; it is a comparison, not a judgment about whether the spending was necessary. Use the same category wording on both sheets so each transaction is included in the intended total.
Track income and understand the summary
Record income on the same Transactions sheet with Type set to Income, then total it separately from expenses. This keeps a single chronological record while preventing income from inflating spending totals. You can create a separate income summary or add an Income total to the Budget sheet. For a household with multiple earners, a description such as “paycheck” can identify the source without making every income source a budget category.
Net cash flow for a period is income minus expenses. For example, if recorded income is 3,200 and expenses are 2,850, the period’s net cash flow is 350. Check that both totals use the same date range; comparing monthly income with expenses accumulated over several months produces a misleading result. Also decide how to treat transfers between your own accounts. Counting a transfer as both an expense and new income can double-count money, so many simple household budgets leave internal transfers out of income and spending totals.
Make the sheet easier to read and maintain
Apply currency formatting to budget and amount columns, and use a consistent date format in both sheets. In Excel for Windows, select the relevant cells and use Home > Number to choose a currency or date format. Use Home > Conditional Formatting > Highlight Cells Rules to call attention to negative remaining amounts. This gives overspending a visible cue without changing the underlying numbers. In Excel for Mac, the Home tab also includes number formatting and conditional formatting controls, though some dialog layouts differ.
Freeze the headings on a long transaction list with View > Freeze Panes, and use the table’s filter arrows to review one category or month. Avoid merged cells in the data area: they interfere with sorting, filtering, and copying. A simple chart of planned versus actual spending can be useful, but review the source totals first. A polished chart cannot correct a missing transaction or a category typed inconsistently.
Choose or download a template carefully
Excel’s File > New page may show budget or expense templates, but the available choices can change by region, account, and time. You can also use a workbook from a reputable provider, or create your own using the layout above. Before relying on a downloaded file, check whether it covers monthly or annual periods, whether income and expenses are separated, and whether formulas include all the rows where you will enter data.
- Look for clearly labeled input cells and instructions about where to add transactions.
- Check that totals update when you change a sample amount or date.
- Confirm that the categories fit your needs and can be edited.
- Inspect hidden sheets or unfamiliar macros before enabling anything; a basic tracker usually does not need macros.
Do not assume a template is correct just because it opens without an error. Test it with a small, clearly marked sample: enter a known transaction, confirm the category total, and remove the sample afterward. If a workbook contains formulas you do not understand, keep an untouched copy and check the formulas before entering sensitive financial details.
Common problems and how to fix them
If an actual total is unexpectedly zero, check that the category text matches, the Type value is exactly Expense, the transaction date is in the selected month, and the amount is numeric. A trailing space or slightly different spelling can prevent a match. If dates are stored as text, convert them to real dates; changing the cell’s display format alone may not convert the underlying value.
If totals are too high, look for duplicate transactions, repeated imports, or a transfer counted as spending. If a total stops changing, inspect the formula’s ranges and confirm new rows fall inside them. The example formula uses whole-column ranges, so it includes later entries, but very large workbooks with many formulas may calculate more slowly. Another common issue is entering refunds as positive expenses: decide on a consistent method, such as recording a refund as negative expense or as a separate adjustment, and apply it consistently so totals reflect the intended definition.
Costs, privacy, and alternatives
Excel may be available through a Microsoft 365 subscription, a one-time Office license, or an organization’s account; the cost and included features depend on the plan and region. A basic workbook does not require paid add-ins. Excel for the web can handle ordinary tables and formulas, although some desktop features and controls may differ. If sharing or editing the workbook, confirm that collaborators have access to the file and understand who may see its contents.
A spreadsheet is a manual record unless you connect it to another service. Do not share a workbook containing account numbers, passwords, or other details that are not needed for budgeting. Store it in a location with appropriate account security, and be cautious about uploading financial records to unfamiliar template sites. Alternatives include a paper ledger, a budgeting application, or a bank’s own spending tools. A spreadsheet is especially useful when you want control over categories and calculations; an app may reduce manual entry, but its features, data practices, and fees should be checked individually.
Frequently Asked Questions
Does Excel have a free budget template?
Excel’s File > New page can offer templates, but the selection and access can vary. If none fits, you can build a basic tracker with transaction, budget, and summary sheets; the formulas above use standard Excel functions.
How do I make an expense tracker in Excel?
Create columns for date, type, category, description, and amount, then record each transaction on its own row. Add a monthly category budget and use SUMIFS to total expenses for the selected month. Keep income marked separately so it is not included in expense totals.
Should expenses be entered as positive or negative numbers?
For the layout in this guide, enter both income and expenses as positive amounts and distinguish them with the Type column. If you use a different convention, ensure every total formula follows it; mixing conventions makes net totals hard to interpret.
Can I use the same template for multiple months?
Yes. Change the month selector in F1 to the first day of another month, and the SUMIFS formula will use that month’s date range. To preserve a historical snapshot, you can copy the summary as values to a separate record, or keep one summary per month.
Why does my Excel budget formula show zero?
Check the date, category, and Type entries against the criteria in the formula. Also check that amounts are numbers and dates are valid Excel dates. A text date or a category with an extra space can prevent a transaction from matching.
Related
- Audit Excel Spreadsheet Template: Build and Use One
- Audit Links in Excel: Find and Check Workbook Links
- Audit Checklist Excel: Build and Use a Reliable Template
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Find the Highest Average in ExcelTo find the highest average in Excel when you have several groups, calculate an average for each group…
- How to Create a Mail Signature in Outlook on Any DeviceTo create a mail signature in Outlook, open the signature settings for the account you use, create and…
- How to Make an A1 Size Poster in PowerPoint for PrintTo make an A1 poster in PowerPoint, set the slide to 59.4 × 84.1 cm (23.39 × 33.11…
- How to Link Excel to Another Sheet in Microsoft 365To link a cell to another worksheet in the same Excel workbook, enter an equals sign, select the…
- Excel Name Manager Spill Range: Create and Use OneTo name a spill range in Excel for Microsoft 365, open Formulas > Name Manager > New, give…
- Add Page Numbers in Microsoft Word: A Practical GuideTo add page numbers in Microsoft Word for Microsoft 365 on Windows, select Insert > Page Number, choose…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.